SQL基于PARTITION BY或循环实现主表聚合生成子表的方法

现有思路的核心问题 #

• 仅用基础窗口函数做聚合的写法，需要先算出MIN(created_at)这类聚合值，再回关联原表取对应的created_by字段，会产生大量嵌套子查询，代码冗余且全量运行时性能开销大
• 基于id_seq逐行循环的写法，把SQL当过程化脚本运行，需要多次和数据库引擎交互，数据量稍大就会出现执行超时，也很难直接生成结构化的单行结果表

推荐实现：条件聚合 + 定向窗口取值 #

完全不需要循环，通过一次表扫描就能完成所有id的单行聚合，所有取数逻辑和你之前给单个id写的硬编码规则完全一致，只是把硬编码的id筛选换成窗口内的通用条件判断。

核心实现逻辑 #

• 碰到“取每个id最早创建时间对应创建人”的场景，不用先算出最小创建时间再回表匹配对应行，直接用FIRST_VALUE()窗口函数，按created_at升序排序后，取每个id分区内第一行的字段值，一步到位
• 碰到不同审计状态下的字段提取需求，不用单独为每个状态写独立子查询，直接在聚合函数中加状态判断条件，命中规则的记录直接返回对应字段值，未命中就返回空值
• 碰到“优先取Status9的审批时间、没有就取Status10的”这类兜底逻辑，先把两个状态的对应时间分别计算出来，最后统一做空值替换，不用嵌套多层ISNULL子查询

可直接运行的完整查询 #

WITH cfr_pr_audit AS (
  -- 保留你已写好的原有主表逻辑，无需修改
  WITH cfr_pr AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY id ASC) AS id_seq,
      id,
      status AS pr_status
    FROM infrabi_stg.o_infr_dly_cfr_purchase_request
    WHERE snapshot_day = (SELECT max(snapshot_day) FROM infrabi_stg.o_infr_dly_cfr_purchase_request)
  ), 
  cfr_pr_audit_pre AS (
    SELECT 
      purchase_request,
      status AS audit_status,
      created_by,
      created_at
    FROM infrabi_stg.o_infr_dly_cfr_purchase_request_audit
    WHERE snapshot_day = (SELECT max(snapshot_day) FROM infrabi_stg.o_infr_dly_cfr_purchase_request_audit)
    ORDER BY created_at DESC
  )
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY cfr_pr.id, cfr_pr_audit_pre.audit_status ORDER BY created_at DESC) AS row_seq
  FROM cfr_pr 
  LEFT JOIN cfr_pr_audit_pre ON cfr_pr.id = cfr_pr_audit_pre.purchase_request
),
pr_agg_calc AS (
  SELECT
    id AS purchase_request,
    -- 提取最早创建时间、对应创建人
    FIRST_VALUE(created_at) OVER (PARTITION BY id ORDER BY created_at ASC) AS created_at,
    FIRST_VALUE(created_by) OVER (PARTITION BY id ORDER BY created_at ASC) AS created_by,
    -- 提取有效提交的时间、操作人（排除NO PLACED状态）
    MAX(CASE WHEN audit_status = 'Status 8' AND row_seq = 1 AND pr_status != 'NO PLACED' THEN created_at END) AS submitted_at,
    MAX(CASE WHEN audit_status = 'Status 8' AND row_seq = 1 AND pr_status != 'NO PLACED' THEN created_by END) AS submitted_by,
    -- 分别提取两个状态的审批时间，后续做空值兜底
    MAX(CASE WHEN audit_status = 'Status 9' AND row_seq = 1 THEN created_at END) AS approved_at_s9,
    MAX(CASE WHEN audit_status = 'Status 10' AND row_seq = 1 THEN created_at END) AS approved_at_s10,
    -- 提取PO创建时间
    MAX(CASE WHEN audit_status = 'Status 11' AND row_seq = 1 THEN created_at END) AS po_created_at
  FROM cfr_pr_audit
)
-- 去重输出每个id的单行结果
SELECT DISTINCT
  purchase_request,
  created_at,
  created_by,
  submitted_at,
  submitted_by,
  ISNULL(approved_at_s9, approved_at_s10) AS approved_at,
  po_created_at
FROM pr_agg_calc

方案优势 #

• 无逐行循环逻辑，一次扫描完成全量计算，执行效率远高于过程化遍历写法
• 无硬编码的单个id筛选条件，自动适配全量采购请求数据，后续新增业务数据不需要修改查询逻辑
• 所有字段匹配逻辑完全对齐你之前验证过的单id取数规则，不存在逻辑偏差

内容的提问来源于stack exchange，提问作者Pericles Faliagas