问题描述

某库记录了人员刷卡进出建筑的流水, 每个时间点有一条记录, 字段为 username、building、action(IN/OUT)、timestamp。正常情况下同一人同一建筑的记录成对出现, 先 IN 后 OUT; 但实际数据会出现不成对、连续同向动作等脏数据。现在要把每个人每栋建筑的每对记录由行转列变成一条记录, 不成对的记录单独转为一条记录, 空缺部分填 NULL，即按配对顺序把纵向流水转为横向会话。

源数据

期望结果

以 user-3/building-1 为例，原始序列为 OUT, IN, IN, IN, OUT, OUT, OUT，按配对规则切为 6 段：首条 OUT 落单，随后两条 IN 各落单，第四条 IN 与首条 OUT 配对成一行，最后两条 OUT 各落单。连续同向动作不会被强行配对，保证会话边界正确，13 行结果中该用户占 6 行正是此逻辑的体现。

SQLazy 分步实现

核心思路：先按 username、building、timestamp 排序，把同一人同一建筑的流水排成时间顺序；再用条件分段识别会话边界，上一条是 OUT 或当前是 IN 就新开一组，这样每组恰好包含至多一个 IN 和至多一个 OUT；最后按 username、building、seg 分组，用带条件的 max 聚合把组内 IN 时间与 OUT 时间分别收敛到同一行，落单则为 NULL。

【点击在线运行本例】

下面逐一解释这些步骤。

第 1 步: 按人、建筑、时间排序

sort username, building, timestamp asc

将同一人同一建筑的记录按 timestamp 升序排列, 确保后续按时间顺序判断配对边界; 用户名与建筑作为排序前置键, 保证分区内顺序与分区键一致, 排序是后续分段与汇总的前提。

第 2 步: 按配对语义条件分段, 生成 seg

segment condition ((action[-1] = "OUT")or (action[-1] = "IN" and action = "IN")) partition username, building as seg

最关键的一步是用条件分段表达会话边界：上一条是 OUT，或上一条是 IN 且当前也是 IN 就新开一组。上一条 OUT 表示上一会话已闭合，应新开；连续 IN 表示多刷进入，每多一次 IN 就切一组，避免把多条 IN 塞进同一会话。partition username, building 保证不同人、不同建筑互不干扰，各自独立编号 seg。条件中 action[-1] 是 SQLazy 的相对位置写法，等价于 LAG(action,1)，无需手写窗口函数。

第 3 步: 按人、建筑、段号分组, 条件汇总行转列

summarize condition (action = "IN") max timestamp as 'IN', condition (action = "OUT") max timestamp as 'OUT'; group username, building, seg

按 username、building、seg 分组, 每组至多包含一进一出。汇总时用条件聚合:action="IN" 时取 timestamp 的 max 作为 IN 列,action="OUT" 时取 timestamp 的 max 作为 OUT 列。max 与 first 在此等价, 因为组内同类动作至多一条; 使用带条件的 max 可使落单组的另一侧自然为 NULL。注意新版语法中求值聚合算法 (max) 在被聚合式 (timestamp) 之前, 分组键通过 "group username, building, seg" 指定。

第 4 步: 清理掉辅助列

derive delete seg

删除分段产生的辅助列 seg, 仅保留 username、building、IN、OUT 四列, 得到最终结果, 表格更干净。

编译生成 SQL

确认上述 4 步逻辑后,SQLazy 编译器自动生成原生 SQL(这里是 Oracle 语法):

SELECT MAX(CASE
        WHEN (action = 'OUT') THEN timestamp
        ELSE NULL
    END) AS "OUT"
    , MAX(CASE
        WHEN (action = 'IN') THEN timestamp
        ELSE NULL
    END) AS "IN"
    , building, username
FROM (
    SELECT username, building, action, timestamp
        , 1 + SUM(CASE
            WHEN (col__2 = 'OUT'
                OR action = 'IN')
            THEN 1
            ELSE 0
        END) OVER (PARTITION BY username, building ORDER BY username ASC, building ASC, timestamp ASC ROWS UNBOUNDED PRECEDING) AS seg
    FROM (
        SELECT t1.*, LAG(action) OVER (PARTITION BY username, building ORDER BY username ASC, building ASC, timestamp ASC) AS col__2
        FROM t1
    ) sub__3
) t_4
GROUP BY username, building, seg
ORDER BY username, building, seg;

SQLazy让你用业务语言描述逻辑，而不是用 SQL 语法写嵌套查询。上面的 NLC 代码，用一句条件分段就能把业务规则说清：segment condition ((action[-1] = "OUT")or (action[-1] = "IN" and action = "IN"))partition username, building，也就是“上一段已结束或连续刷入就新开会话”。手写 SQL 时，你得自己写 LAG 取上一行、SUM OVER 累计段号，再套两层子查询封装窗口列，最后用条件聚合 MAX(CASE...) 做行转列，还要处理分区与排序的一致性。SQLazy 把这些压缩成排序、分段、条件汇总、清理四步，每步都能单独验证，相对位置和分区会编译成窗口函数，条件汇总自动处理 NULL。

官方链接

SQLazy 在线体验: https://sqlazy.com (免费, 无需注册)

SQLazy 项目仓库: https://github.com/SPLWare/SQLazy