跨子组按序列填充字段值

问题描述

某库表的前两个字段 Group1、Group2 是分组字段，LineID 是组内序号，TargetField 是目标字段。按 Group1、Group2、LineID 排序后，同一个 Group1 内每个 Group2 的记录数相同；只有最后一个 Group2 的 TargetField 有值，其余为 NULL。现在要将最后一个小组的 TargetField 按序列顺序复制到同一大组的其他小组中。

源数据

期望结果

Group1=2 有 2 个小组（Group2=4,5），每个小组 3 条记录。最后一个小组 Group2=5 的 TargetField 为 [5,3,4]，要复制到其他小组，即 Group2=4。

SQLazy 分步实现

核心思路：先用 rank 在每个 (Group1, Group2) 内生成组内行号 rn，然后利用“只有最后一个小组有值”的特点，按 (Group1, rn) 分组用 sum 汇总 TargetField。每个 (Group1, rn) 分组内只有一行有非空值，sum 返回该值，从而自然地将最后一个小组的值填充到其他小组的对应行。

【点击在线运行本例】

下面逐一解释这些步骤。

第 1 步：按分组和序列排序

sort Group1, Group2, LineID

将数据按 Group1、Group2、LineID 升序排列，确保同一大组内记录按小组和行号顺序处理。

第 2 步：在每个小组内生成行号

rank as rn; partition Group1, Group2

在每个 (Group1, Group2) 分区内用 rank 函数生成行号 rn。由于每个小组的记录数相同，同一大组内 rn=1 的行来自各个小组的第一条记录，rn=2 来自第二条，依此类推。

第 3 步：按行号汇总填充值（核心）

compute TargetField sum as tempTarget; partition Group1, rn

这是最核心的一步。按 (Group1, rn) 分组用 sum 汇总 TargetField。由于只有最后一个小组的 TargetField 有非空值，其他小组为 NULL，而 sum 会忽略 NULL。因此每个 (Group1, rn) 分组内只有一个有效值，sum 返回该值，从而将最后一个小组的 TargetField 按行号对应地填充到其他小组。

第 4 步：选取最终结果列

derive Group1, Group2, LineID, tempTarget as TargetField

编译生成 SQL

确认上述步骤后，SQLazy 编译器自动生成原生 SQL（PostgreSQL 语法）：

WITH t1 AS (
  SELECT Group1, Group2, LineID, TargetField
  FROM lines
),
sub__6 AS (
  SELECT
    sub__5.*,
    RANK() OVER (PARTITION BY Group1, Group2 ORDER BY Group1, Group2, LineID) AS rn
  FROM t1 sub__5
),
t3 AS (
  SELECT
    Group1, Group2, LineID, TargetField, rn,
    SUM(TargetField) OVER (PARTITION BY Group1, rn) AS tempTarget
  FROM sub__6
)
SELECT Group1, Group2, LineID, tempTarget AS TargetField
FROM t3

SQLazy 让你用业务语言描述逻辑，而不是用 SQL 语法写嵌套查询。

整个解题过程分 4 步，每步都可独立验证中间结果，降低了复杂逻辑的出错概率。SQLazy 的 rank 函数可以在分区内直接生成行号，比 SQL 的 RANK() OVER 更简洁。compute 功能配合 sum 函数，将多个小组的同位置行的值自然汇总为唯一的非空值，代替了显式的跨行引用。

官方链接

SQLazy 在线体验：sqlazy.com（免费，无需注册）
SQLazy 项目仓库：github.com/SPLWare/SQLazy