如何在SQL中计算每个唯一evt_type最新与次新记录的value差值？

现有数据表events结构及数据如下：

evt_type | value      | time
  ------------+------------+--------------------
   2          | 5          | 2015-05-09 12:42:00
   4          | -42        | 2015-05-09 13:19:57
   2          | 2          | 2015-05-09 14:48:30
   2          | 7          | 2015-05-09 12:54:39
   3          | 16         | 2015-05-09 13:19:57
   3          | 20         | 2015-05-09 15:01:09

需求：计算每个拥有至少2条记录的evt_type，其最新与次新记录的value列差值。

目前已写出筛选并排序的查询：

SELECT *
FROM events
WHERE evt_type IN (
    SELECT evt_type
    FROM events
    GROUP BY evt_type
    HAVING COUNT(*) >= 2
)
ORDER BY evt_type ASC, time DESC

返回结果：

evt_type   value   time
+----------+------+---------------------+
| 2        |  2   | 2015-05-09 14:48:30 |
| 2        |  7   | 2015-05-09 12:54:39 |
| 2        |  5   | 2015-05-09 12:42:00 |
| 3        | 20   | 2015-05-09 15:01:09 |
| 3        | 16   | 2015-05-09 13:19:57 |
+----------+------+---------------------+

提问：如何获取每个唯一evt_type前两条记录的value差值？能否基于现有查询作为子查询实现，还是需要其他方法？

方法一：使用窗口函数（推荐） #

利用ROW_NUMBER()给每个evt_type下的记录按时间倒序编号，筛选前两条后计算差值，这是现代数据库最高效简洁的方式：

WITH ranked_events AS (
    SELECT 
        evt_type,
        value,
        ROW_NUMBER() OVER (PARTITION BY evt_type ORDER BY time DESC) AS rn
    FROM events
    WHERE evt_type IN (
        SELECT evt_type FROM events GROUP BY evt_type HAVING COUNT(*) >= 2
    )
)
SELECT 
    evt_type,
    MAX(CASE WHEN rn = 1 THEN value END) - MAX(CASE WHEN rn = 2 THEN value END) AS value_diff
FROM ranked_events
WHERE rn <= 2
GROUP BY evt_type;

执行结果：

evt_type | value_diff
----------+------------
        2 |         -5
        3 |          4

方法二：基于现有查询作为子查询实现 #

可以把你写的排序查询作为子查询，通过自关联获取最新和次新值：

SELECT 
    t1.evt_type,
    t1.value - t2.value AS value_diff
FROM (
    SELECT 
        evt_type, 
        value, 
        ROW_NUMBER() OVER (PARTITION BY evt_type ORDER BY time DESC) AS rn
    FROM events
    WHERE evt_type IN (SELECT evt_type FROM events GROUP BY evt_type HAVING COUNT(*) >= 2)
) t1
JOIN (
    SELECT 
        evt_type, 
        value, 
        ROW_NUMBER() OVER (PARTITION BY evt_type ORDER BY time DESC) AS rn
    FROM events
    WHERE evt_type IN (SELECT evt_type FROM events GROUP BY evt_type HAVING COUNT(*) >= 2)
) t2 
ON t1.evt_type = t2.evt_type 
AND t1.rn = 1 
AND t2.rn = 2;

更简化的写法可以用LAG()窗口函数，直接获取上一条（次新）的value：

WITH ranked_events AS (
    SELECT 
        evt_type,
        value,
        LAG(value) OVER (PARTITION BY evt_type ORDER BY time DESC) AS prev_value,
        ROW_NUMBER() OVER (PARTITION BY evt_type ORDER BY time DESC) AS rn
    FROM events
    WHERE evt_type IN (SELECT evt_type FROM events GROUP BY evt_type HAVING COUNT(*) >= 2)
)
SELECT evt_type, value - prev_value AS value_diff
FROM ranked_events
WHERE rn = 1;

方法三：兼容旧版本数据库（无窗口函数支持） #

如果你的数据库不支持窗口函数（如MySQL 5.x），可以用关联子查询：

SELECT 
    e1.evt_type,
    e1.value - e2.value AS value_diff
FROM events e1
JOIN events e2 ON e1.evt_type = e2.evt_type
WHERE 
    -- 获取当前evt_type的最新时间记录
    e1.time = (SELECT MAX(time) FROM events WHERE evt_type = e1.evt_type)
    -- 获取当前evt_type的次新时间记录
    AND e2.time = (SELECT MAX(time) FROM events WHERE evt_type = e1.evt_type AND time < e1.time)
    -- 过滤出至少2条记录的evt_type
    AND (SELECT COUNT(*) FROM events WHERE evt_type = e1.evt_type) >= 2;

内容的提问来源于stack exchange，提问作者Mosky1970