问题描述某库记录了人员刷卡进出建筑的流水, 每个时间点有一条记录, 字段为 username、building、action(IN/OUT)、timestamp。正常情况下同一人同一建筑的记录成对出现, 先 IN 后 OUT; 但实际数据会出现不成对、连续同向动作等脏数据。现在要把每个人每栋建筑的每对记录由行转列变成一条记录, 不成对的记录单独转为一条记录, 空缺部分填 NULL即按配对顺序把纵向流水转为横向会话。源数据usernamebuildingactiontimestampuser-1building-1IN2024-04-10 01:00:00.000user-1building-1OUT2024-04-10 02:00:00.000user-1building-1IN2024-04-10 02:30:00.000user-1building-1OUT2024-04-10 04:00:00.000user-1building-1IN2024-04-11 10:00:00.000user-1building-1OUT2024-04-11 11:00:00.000user-2building-1IN2024-04-12 10:00:00.000user-2building-1OUT2024-04-12 11:00:00.000user-2building-2IN2024-04-10 08:00:00.000user-2building-2OUT2024-04-10 09:00:00.000user-2building-3OUT2024-04-11 02:30:00.000user-2building-4IN2024-04-11 04:00:00.000user-3building-1OUT2024-04-10 01:00:00.000user-3building-1IN2024-04-10 10:00:00.000user-3building-1IN2024-04-10 11:00:00.000user-3building-1IN2024-04-10 12:00:00.000user-3building-1OUT2024-04-10 13:00:00.000user-3building-1OUT2024-04-10 14:00:00.000user-3building-1OUT2024-04-10 15:00:00.000期望结果usernamebuildingINOUTuser-1building-12024-04-10 01:00:00.0002024-04-10 02:00:00.000user-1building-12024-04-10 02:30:00.0002024-04-10 04:00:00.000user-1building-12024-04-11 10:00:00.0002024-04-11 11:00:00.000user-2building-12024-04-12 10:00:00.0002024-04-12 11:00:00.000user-2building-22024-04-10 08:00:00.0002024-04-10 09:00:00.000user-2building-32024-04-11 02:30:00.000user-2building-42024-04-11 04:00:00.000user-3building-12024-04-10 01:00:00.000user-3building-12024-04-10 10:00:00.000user-3building-12024-04-10 11:00:00.000user-3building-12024-04-10 12:00:00.0002024-04-10 13:00:00.000user-3building-12024-04-10 14:00:00.000user-3building-12024-04-10 15:00:00.000以 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。NameAnchorStatementt1userBuildingsort username, building, timestamp asct2t1segment condition ((action[-1] OUT)or (action[-1] IN and action IN)) partition username, building as segt3t2summarize condition (action IN) max timestamp as IN, condition (action OUT) max timestamp as OUT; group username, building, segt4t3derive delete seg下面逐一解释这些步骤。第 1 步: 按人、建筑、时间排序sort username, building, timestamp asc将同一人同一建筑的记录按 timestamp 升序排列, 确保后续按时间顺序判断配对边界; 用户名与建筑作为排序前置键, 保证分区内顺序与分区键一致, 排序是后续分段与汇总的前提。第 2 步: 按配对语义条件分段, 生成 segsegment 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 分组, 每组至多包含一进一出。汇总时用条件聚合:actionIN 时取 timestamp 的 max 作为 IN 列,actionOUT 时取 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。