Redshift递归SQL报错及患者等效数计算逻辑实现求助

问题1：递归SQL精度错误解决 #

出现SQL Error [XX000]: ERROR: Numeric column 2 precision and scales cannot be merged是因为Redshift递归CTE要求锚点成员与递归成员返回的数值列精度、刻度完全一致。解决方法是显式将所有相关数值列（如PEQ、新增患者数等）CAST为统一的数值类型（例如DECIMAL(18,6)），避免类型不匹配。

问题2：替代Oracle MODEL子句实现PEQ计算逻辑 #

Redshift不支持Oracle的MODEL子句，可通过递归CTE模拟迭代计算逻辑，以下是适配方案：

测试数据 #

先准备与Oracle逻辑对齐的测试数据集：

CREATE TABLE test_data (
    PERIOD_START_DATE DATE,
    SU_VALUE DECIMAL(18,6)
);

INSERT INTO test_data VALUES
('2023-01-01', 100.5),
('2023-02-01', 150.2),
('2023-03-01', 80.7),
('2023-04-01', 200.0);

Oracle参考逻辑（MODEL子句） #

假设原Oracle代码的迭代计算逻辑示例如下：

SELECT PERIOD_START_DATE, SU_VALUE, PEQ, NEW_PATIENTS, FINAL_PEQ
FROM test_data
MODEL
  PARTITION BY ()
  ORDER BY PERIOD_START_DATE
  DIMENSION BY (PERIOD_START_DATE AS dt)
  MEASURES (SU_VALUE, 0 AS PEQ, 0 AS NEW_PATIENTS, 0 AS FINAL_PEQ)
  RULES (
    PEQ[ANY] = NVL(FINAL_PEQ[PREV(dt)], 0) + SU_VALUE[cv(dt)] * 0.8,
    NEW_PATIENTS[ANY] = SU_VALUE[cv(dt)] * 0.2,
    FINAL_PEQ[ANY] = PEQ[cv(dt)] + NEW_PATIENTS[cv(dt)]
  );

Redshift适配方案（递归CTE） #

通过递归CTE按日期顺序迭代计算，同时统一数值类型避免精度错误：

WITH ordered_data AS (
    -- 为数据添加顺序编号，确保递归按周期顺序执行
    SELECT 
        PERIOD_START_DATE,
        SU_VALUE,
        ROW_NUMBER() OVER (ORDER BY PERIOD_START_DATE) AS rn
    FROM test_data
),
recursive_calc AS (
    -- 锚点成员：第一个周期的初始计算
    SELECT
        PERIOD_START_DATE,
        SU_VALUE,
        CAST(SU_VALUE * 0.8 AS DECIMAL(18,6)) AS PEQ,
        CAST(SU_VALUE * 0.2 AS DECIMAL(18,6)) AS NEW_PATIENTS,
        CAST((SU_VALUE * 0.8) + (SU_VALUE * 0.2) AS DECIMAL(18,6)) AS FINAL_PEQ,
        rn
    FROM ordered_data
    WHERE rn = 1

    UNION ALL

    -- 递归成员：基于上一周期结果计算当前周期
    SELECT
        od.PERIOD_START_DATE,
        od.SU_VALUE,
        CAST(rc.FINAL_PEQ + (od.SU_VALUE * 0.8) AS DECIMAL(18,6)) AS PEQ,
        CAST(od.SU_VALUE * 0.2 AS DECIMAL(18,6)) AS NEW_PATIENTS,
        CAST((rc.FINAL_PEQ + (od.SU_VALUE * 0.8)) + (od.SU_VALUE * 0.2) AS DECIMAL(18,6)) AS FINAL_PEQ,
        od.rn
    FROM ordered_data od
    JOIN recursive_calc rc ON od.rn = rc.rn + 1
)
SELECT PERIOD_START_DATE, SU_VALUE, PEQ, NEW_PATIENTS, FINAL_PEQ
FROM recursive_calc
ORDER BY PERIOD_START_DATE;

关键说明 #

1. 统一数值类型：所有计算后的数值列都显式CAST为DECIMAL(18,6)，彻底解决递归时的精度/刻度不匹配错误。
2. 递归顺序控制：通过ROW_NUMBER()为周期数据编号，确保递归严格按日期顺序执行，与Oracle MODEL子句的ORDER BY逻辑一致。
3. 逻辑对齐：递归成员中，当前周期的PEQ基于上一周期的FINAL_PEQ计算，与Oracle MODEL的PREV(dt)逻辑完全匹配。

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