如何将人员出入日期的成对记录行合并为单行？

现有人员出入记录表结构及数据如下：

期望转换为以下3行数据：

猜测可以用LAG函数实现，但正确的实现方式是什么？

你的思路是对的，这类配对出入记录的场景可以用窗口函数实现，下面给出两种可行方案：

方案1：使用LEAD函数（更直接） #

先按人员和记录时间排序，为每条入口记录匹配对应的下一条出口记录：

WITH sorted_records AS (
    SELECT 
        Person,
        Entrance,
        Exit,
        -- 合并非空日期作为排序依据
        COALESCE(Entrance, Exit) AS record_date,
        -- 标记是否为入口记录
        CASE WHEN Entrance IS NOT NULL THEN 1 ELSE 0 END AS is_entrance
    FROM your_table
),
paired_records AS (
    SELECT 
        Person,
        Entrance,
        -- 取同人员下一条非入口记录的Exit值
        LEAD(Exit) OVER (PARTITION BY Person ORDER BY record_date) AS Exit
    FROM sorted_records
    WHERE is_entrance = 1
)
SELECT Person, Entrance, Exit
FROM paired_records
WHERE Exit IS NOT NULL;

方案2：使用LAG函数实现 #

如果一定要用LAG，可以为每条出口记录关联上一条入口记录：

WITH sorted_records AS (
    SELECT 
        Person,
        Entrance,
        Exit,
        COALESCE(Entrance, Exit) AS record_date,
        CASE WHEN Entrance IS NOT NULL THEN 1 ELSE 0 END AS is_entrance
    FROM your_table
),
paired_records AS (
    SELECT 
        Person,
        -- 取同人员上一条入口记录的Entrance值
        LAG(Entrance) OVER (PARTITION BY Person ORDER BY record_date) AS Entrance,
        Exit
    FROM sorted_records
    WHERE is_entrance = 0
)
SELECT Person, Entrance, Exit
FROM paired_records
WHERE Entrance IS NOT NULL;

关键说明 #

• COALESCE(Entrance, Exit)用来统一排序字段，确保记录按时间顺序排列；
• PARTITION BY Person保证只在同一人员的记录范围内进行配对；
• 过滤条件is_entrance = 1（或0）用来筛选有效记录，避免生成空行。

内容的提问来源于stack exchange，提问作者süleyman