PostgreSQL如何计算刷卡进出时间（含不完整记录处理）

原始数据 #

需求说明 #

统计用户的刷卡进入（IN）和离开（OUT）时间，需保留所有不完整记录（即单独出现的IN或OUT），最终输出结构为：username、building、in_time、out_time。

构造数据SQL #

WITH _data AS (
    SELECT 'user-1' AS username, 'building-1' AS building, 'IN' AS action, '2024-04-10 01:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-1' AS username, 'building-1' AS building, 'OUT' AS action, '2024-04-10 02:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-1' AS username, 'building-1' AS building, 'IN' AS action, '2024-04-10 02:30:00'::timestamp AS timestamp
    UNION ALL
    SELECT 'user-1' AS username, 'building-1' AS building, 'OUT' AS action, '2024-04-10 04:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-1' AS username, 'building-1' AS building, 'IN' AS action, '2024-04-11 10:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-1' AS username, 'building-1' AS building, 'OUT' AS action, '2024-04-11 11:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-2' AS building, 'IN' AS action, '2024-04-10 08:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-2' AS building, 'OUT' AS action, '2024-04-10 09:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-3' AS building, 'OUT' AS action, '2024-04-11 02:30:00'::timestamp AS timestamp
    UNION ALL
    SELECT 'user-2' AS username, 'building-4' AS building, 'IN' AS action, '2024-04-11 04:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-1' AS building, 'IN' AS action, '2024-04-12 10:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-1' AS building, 'OUT' AS action, '2024-04-12 11:00:00'::timestamp AS timestamp
)
SELECT * FROM _data;

解决方案SQL #

WITH _data AS (
    SELECT 'user-1' AS username, 'building-1' AS building, 'IN' AS action, '2024-04-10 01:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-1' AS username, 'building-1' AS building, 'OUT' AS action, '2024-04-10 02:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-1' AS username, 'building-1' AS building, 'IN' AS action, '2024-04-10 02:30:00'::timestamp AS timestamp
    UNION ALL
    SELECT 'user-1' AS username, 'building-1' AS building, 'OUT' AS action, '2024-04-10 04:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-1' AS username, 'building-1' AS building, 'IN' AS action, '2024-04-11 10:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-1' AS username, 'building-1' AS building, 'OUT' AS action, '2024-04-11 11:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-2' AS building, 'IN' AS action, '2024-04-10 08:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-2' AS building, 'OUT' AS action, '2024-04-10 09:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-3' AS building, 'OUT' AS action, '2024-04-11 02:30:00'::timestamp AS timestamp
    UNION ALL
    SELECT 'user-2' AS username, 'building-4' AS building, 'IN' AS action, '2024-04-11 04:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-1' AS building, 'IN' AS action, '2024-04-12 10:00:00'::timestamp AS timestamp
    UNION ALL 
    SELECT 'user-2' AS username, 'building-1' AS building, 'OUT' AS action, '2024-04-12 11:00:00'::timestamp AS timestamp
),
record_sequence AS (
    SELECT
        username,
        building,
        action,
        timestamp,
        ROW_NUMBER() OVER (PARTITION BY username ORDER BY timestamp) AS seq_num,
        LAG(action) OVER (PARTITION BY username ORDER BY timestamp) AS prev_action,
        LEAD(action) OVER (PARTITION BY username ORDER BY timestamp) AS next_action
    FROM _data
),
complete_pairs AS (
    -- 匹配连续的IN和OUT记录
    SELECT
        rs1.username,
        rs1.building,
        rs1.timestamp AS in_time,
        rs2.timestamp AS out_time
    FROM record_sequence rs1
    JOIN record_sequence rs2 
        ON rs1.username = rs2.username 
        AND rs2.seq_num = rs1.seq_num + 1
        AND rs1.action = 'IN'
        AND rs2.action = 'OUT'
),
unmatched_ins AS (
    -- 提取未配对的IN记录（无后续OUT）
    SELECT
        username,
        building,
        timestamp AS in_time,
        NULL AS out_time
    FROM record_sequence
    WHERE action = 'IN'
        AND (next_action != 'OUT' OR next_action IS NULL)
        AND seq_num NOT IN (SELECT seq_num FROM complete_pairs cp JOIN record_sequence rs ON cp.in_time = rs.timestamp)
),
unmatched_outs AS (
    -- 提取未配对的OUT记录（无前序IN）
    SELECT
        username,
        building,
        NULL AS in_time,
        timestamp AS out_time
    FROM record_sequence
    WHERE action = 'OUT'
        AND (prev_action != 'IN' OR prev_action IS NULL)
        AND seq_num NOT IN (SELECT seq_num FROM complete_pairs cp JOIN record_sequence rs ON cp.out_time = rs.timestamp)
)
SELECT * FROM complete_pairs
UNION ALL
SELECT * FROM unmatched_ins
UNION ALL
SELECT * FROM unmatched_outs
ORDER BY username, COALESCE(in_time, out_time);

结果说明 #

执行上述SQL后将得到符合需求的统计结果：

• 所有连续的IN-OUT记录会被配对显示
• 单独的IN或OUT记录会以NULL填充对应字段的形式保留
• 结果按用户名和时间顺序排序

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