sql - 最新和第二个最新行之间的SQLite差异

Question

我有这样的表：

create table events(
 event_type integer not null,
 value integer not null,
 time timestamp not null,
 unique (event_type ,time)
);

insert into events values
(2,   5,  '2015-05-09 12:42:00'),
(4, -42,  '2015-05-09 13:19:57'),
(2,   2,  '2015-05-09 14:48:39'),
(2,   7,  '2015-05-09 13:54:39'),
(3,  16,  '2015-05-09 13:19:57'),
(3,  20,  '2015-05-09 15:01:09')

我想查询一个查询，对于已多次注册的每个 event_type返回最新值与第二个最新值之间的差。

鉴于上表，我期望以下输出：

event_type  value
  2          -5
  3           4

如我在 SQL Sever/Oracle中所知，可以使用 row_number() over (partition by)来实现。在此先感谢您的帮助。

Answer 1

您总是可以模拟ROW_NUMBER：

WITH cte AS
(
     SELECT *,
           (SELECT COUNT(*) + 1 
            FROM "events" e1
            WHERE e1.event_type = e.event_type
              AND e1.time > e.time) AS rn
     FROM "events" e
)
SELECT c.event_type, c."value" - c2."value" AS "value"
FROM cte c
JOIN cte c2
  ON c.event_type = c2.event_type
 AND c.rn = 1 AND c2.rn = 2
ORDER BY event_type, time;

SqlFiddleDemo

输出：

╔═══════════════╦═══════╗
║ event_type    ║ value ║
╠═══════════════╬═══════╣
║            2  ║    -5 ║
║            3  ║     4 ║
╚═══════════════╩═══════╝

像 time/events/value这样的标识符在某些SQL方言中是重新输入字词。