如何使用PostgreSQL查找用户的START/STOP事件配对？

先看你的原始events表数据：

ts                        | user   | reason
----------------------------+--------+--------
2018-06-01 10:44:15.52+01 | 359999 | START
2018-06-01 10:44:29.521+01| 359999 | STOP
2018-06-01 10:44:43.52+01 | 359998 | START
2018-06-01 10:44:55.52+01 | 359999 | START
2018-06-01 10:44:59.521+01| 359998 | STOP
2018-06-01 10:45:07.52+01 | 359999 | STOP
2018-06-01 10:46:16.52+01 | 359999 | START

你需要把每个用户的START和紧随其后的STOP配对，同时保留没有对应STOP的START记录，这里有两种简洁的实现方式：

方法一：使用窗口函数LEAD()（推荐，代码更简洁） #

SELECT
    "user",
    ts AS start,
    -- 仅当下一个事件是STOP时，才取其时间戳作为stop
    CASE 
        WHEN LEAD(reason) OVER (PARTITION BY "user" ORDER BY ts) = 'STOP'
        THEN LEAD(ts) OVER (PARTITION BY "user" ORDER BY ts)
        ELSE NULL
    END AS stop
FROM events
WHERE reason = 'START'
ORDER BY ts;

思路说明： #

• LEAD()窗口函数可以获取同一个用户分组内，按时间排序后的下一条记录的字段值；
• 先筛选出所有START事件，然后判断它的下一个事件是否是STOP，如果是就把下一个事件的时间戳作为stop，否则留空；
• 最后按时间排序，就能得到你要的配对结果。

方法二：使用自连接+序号分组 #

如果你的数据库不支持LEAD()（不过现在主流数据库都支持），可以用这种方式：

WITH ranked_events AS (
    SELECT
        "user",
        ts,
        reason,
        -- 给每个用户的事件按时间生成递增序号
        ROW_NUMBER() OVER (PARTITION BY "user" ORDER BY ts) AS rn
    FROM events
)
SELECT
    s."user",
    s.ts AS start,
    t.ts AS stop
FROM ranked_events s
LEFT JOIN ranked_events t
    ON s."user" = t."user"
    AND s.reason = 'START'
    AND t.reason = 'STOP'
    AND t.rn = s.rn + 1  -- 确保STOP是START的下一个事件
WHERE s.reason = 'START'
ORDER BY s.ts;

思路说明： #

• 先用ROW_NUMBER()给每个用户的事件按时间排序编号；
• 把标记为START的记录和标记为STOP的记录自连接，匹配规则是同一个用户、且STOP的序号刚好是START的序号+1（确保是紧随其后的事件）；
• 使用LEFT JOIN可以保留没有对应STOP的START，此时stop字段为NULL。

两种方法都能得到你想要的结果：

user   | start                      | stop
--------+----------------------------+----------------------------
359999 | 2018-06-01 10:44:15.52+01 | 2018-06-01 10:44:29.521+01
359998 | 2018-06-01 10:44:43.52+01 | 2018-06-01 10:44:59.521+01
359999 | 2018-06-01 10:44:55.52+01 | 2018-06-01 10:45:07.52+01
359999 | 2018-06-01 10:46:16.52+01 | 

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