SQL中补全缺失连续Phase的行数据需求及实现问询

需求说明 #

给定包含ID、Phase、Phase Order的原始数据，需实现以下处理规则：

• 对每个ID，若某Phase的出现次数为N，则该Phase之前所有Phase Order更小的阶段，出现次数至少为N
• 补全原本缺失的连续阶段行，满足上述次数要求

场景示例 #

• ID=12的原始数据中，'Check Done'出现2次，需将其之前的Found、Round 1、Round 2各补至至少2次
• ID=100的原始数据中，Phase Order=2出现3次、Phase Order=3出现2次，需补全Phase Order=1至3次，Phase Order=2保留3次
• ID=111的原始数据中，Phase Order=3出现3次，需将Phase Order=1、2均补至3次
• ID=14的原始数据需参照ID=12的规则补全行

尝试代码 #

WITH phases AS (
  SELECT 'Found' AS phase, 1 AS phase_order UNION ALL
  SELECT 'Round 1', 2 UNION ALL
  SELECT 'Round 2', 3 UNION ALL
  SELECT 'Check', 4 UNION ALL
  SELECT 'Check Done', 6 UNION ALL
  SELECT 'Wanted', 7 UNION ALL
  SELECT 'Captured', 9
), 
max_phase_id AS (
SELECT id, MAX(phase_order) AS max_phase
FROM orginal_data
GROUP BY id
)
SELECT * FROM (SELECT * from max_phase_id
cross join phases
where phases.phase_order <= max_phase_id.max_phase) t
RIGHT JOIN  orginal_data
ON  orginal_data.id = t.id and t.phase = orginal_data.phase
order by phase_order

正确SQL实现方案 #

以下是满足需求的完整SQL逻辑，基于PostgreSQL语法编写（其他数据库可调整序列生成部分）：

WITH phases AS (
  -- 定义所有基准阶段及其顺序
  SELECT 'Found' AS phase, 1 AS phase_order UNION ALL
  SELECT 'Round 1', 2 UNION ALL
  SELECT 'Round 2', 3 UNION ALL
  SELECT 'Check', 4 UNION ALL
  SELECT 'Check Done', 6 UNION ALL
  SELECT 'Wanted', 7 UNION ALL
  SELECT 'Captured', 9
),
phase_counts AS (
  -- 统计每个ID下各阶段的实际出现次数
  SELECT 
    od.id,
    p.phase,
    p.phase_order,
    COUNT(*) AS actual_count
  FROM orginal_data od
  JOIN phases p ON od.phase = p.phase
  GROUP BY od.id, p.phase, p.phase_order
),
required_counts AS (
  -- 计算每个阶段需要达到的最小次数：取该阶段及后续所有阶段的最大出现次数
  SELECT
    pc.id,
    pc.phase,
    pc.phase_order,
    MAX(pc2.actual_count) AS required_count
  FROM phase_counts pc
  JOIN phase_counts pc2 
    ON pc.id = pc2.id 
    AND pc2.phase_order >= pc.phase_order
  GROUP BY pc.id, pc.phase, pc.phase_order
),
id_phases AS (
  -- 生成每个ID需要覆盖的所有阶段（到该ID的最大阶段顺序）
  SELECT 
    od.id,
    p.phase,
    p.phase_order
  FROM (SELECT DISTINCT id FROM orginal_data) od
  CROSS JOIN phases p
  JOIN (SELECT id, MAX(phase_order) AS max_phase FROM orginal_data GROUP BY id) mp
    ON od.id = mp.id 
    AND p.phase_order <= mp.max_phase
),
final_required AS (
  -- 关联每个阶段的需求次数，缺失阶段默认需求次数为0（实际会被后续逻辑处理）
  SELECT
    ip.id,
    ip.phase,
    ip.phase_order,
    COALESCE(rc.required_count, (SELECT MAX(required_count) FROM required_counts WHERE id = ip.id)) AS required_count
  FROM id_phases ip
  LEFT JOIN required_counts rc 
    ON ip.id = rc.id 
    AND ip.phase = rc.phase
),
generate_rows AS (
  -- 生成对应数量的行，补全次数不足的部分
  SELECT
    id,
    phase,
    phase_order,
    ROW_NUMBER() OVER (PARTITION BY id, phase ORDER BY (SELECT NULL)) AS row_num
  FROM final_required
  CROSS JOIN GENERATE_SERIES(1, (SELECT MAX(required_count) FROM final_required)) AS s
  WHERE s <= required_count
)
-- 输出最终结果
SELECT id, phase, phase_order
FROM generate_rows
ORDER BY id, phase_order, row_num;

逻辑说明 #

1. 先通过phasesCTE定义所有可能的阶段及其顺序基准
2. 统计每个ID下各阶段的实际出现次数
3. 对每个阶段，计算其需要达到的最小次数：即该阶段及之后所有阶段中的最大出现次数，保证前面的阶段次数不低于后面的阶段
4. 生成每个ID需要覆盖的所有阶段范围，关联需求次数
5. 通过序列生成函数扩展行，补全缺失的阶段和次数不足的部分

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