按配对顺序将刷卡进出记录转为单行记录-AI解法
## 问题描述 某库记录了人员刷卡进出建筑的流水, 每个时间点有一条记录, 字段为 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。