Redshift递归SQL未返回全量数据，请求修正查询语句

原查询仅返回单一行的核心问题及修正方案如下：

问题根源 #

1. 锚点过滤错误：原锚点查询中WHERE peq != prev_peq会过滤掉第一行（其prev_peq为NULL，不满足非等值判断），导致递归没有正确的起始基础。
2. 递归数据引用错误：递归部分返回的是上一周期（build）的日期和剂量数据，而非当前关联的新周期（t）数据，导致所有递归结果重复同一行。
3. 递归计数器逻辑错误：初始cur_rn设为2，且max_rn基于过滤后的行数计算，限制了递归推进的范围。
4. 计算逻辑变量混淆：repeated_patient的计算错误关联了变量，未正确用上一周期的剩余量和当前周期总量做比较。

修正后的查询语句 #

WITH recursive build (period_start_date, peq, prev_peq, repeated_patient, cur_rn, max_rn) AS 
(
    -- 锚点：取第一个周期，初始重复患者数为0
    SELECT 
        period_start_date,
        peq,
        prev_peq,
        0::FLOAT AS repeated_patient,
        rn AS cur_rn,
        max_rn
    FROM  
        (SELECT  
             period_start_date,
             peq,
             prev_peq,
             ROW_NUMBER() OVER (ORDER BY period_start_date) AS rn,
             COUNT(1) OVER () AS max_rn
         FROM  
             (SELECT  
                  period_start_date,
                  ROUND(1.0 * su_value / 6, 1)::FLOAT peq,
                  LAG(ROUND(1.0 * su_value / 6, 1), 1) OVER (ORDER BY period_start_date)::FLOAT prev_peq
              FROM testoss
             ) base_data
        ) numbered_data
    WHERE rn = 1

    UNION ALL

    -- 递归：关联下一个周期，计算当前周期的重复患者数
    SELECT 
        t.period_start_date,
        t.peq,
        t.prev_peq,
        ROUND(
            LEAST(
                b.peq - b.repeated_patient,  -- 上一周期剩余可复用的患者量
                t.peq                        -- 当前周期的总患者量
            ), 1
        ) AS repeated_patient,
        b.cur_rn + 1 AS cur_rn,
        b.max_rn
    FROM build b
    JOIN (
        SELECT  
            period_start_date,
            peq,
            prev_peq,
            ROW_NUMBER() OVER (ORDER BY period_start_date) AS rn
        FROM  
            (SELECT  
                 period_start_date,
                 ROUND(1.0 * su_value / 6, 1)::FLOAT peq,
                 LAG(ROUND(1.0 * su_value / 6, 1), 1) OVER (ORDER BY period_start_date)::FLOAT prev_peq
             FROM testoss
            ) base_data
    ) t ON t.rn = b.cur_rn + 1
    WHERE b.cur_rn < b.max_rn
)
SELECT 
    period_start_date,
    peq,
    prev_peq,
    repeated_patient
FROM build
ORDER BY period_start_date;

修正说明 #

• 移除锚点中的peq != prev_peq过滤，保留所有周期的基础数据，确保递归从第一个周期开始。
• 递归部分直接引用当前周期（t）的日期和剂量数据，保证每轮递归返回新的周期记录。
• 调整cur_rn初始值为1，max_rn使用全量周期数，确保递归覆盖所有周期。
• 修正repeated_patient计算逻辑：取上一周期总患者量减去上一周期重复患者量与当前周期总患者量的最小值，符合业务逻辑。

运行该查询后，会返回所有11个周期的完整数据，且repeated_patient计算正确。

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