SQL查询优化：修正Base Table逻辑错误，补全缺失ord_type记录

Base Table存在逻辑错误无法直接修改，需通过SQL修正数据。现有查询因自嵌套依赖原表存在Beta记录，导致部分日期的Beta记录缺失，且Alpha的act_returns未正确转移到Beta，最终结果有记录遗漏和数值偏差。

背景规则 #

每个out_id单日可对应Alpha、Beta、Gamma等多种ord_type，日期不一定包含所有类型。需实现：

• Alpha类型的act_returns强制设为0
• Alpha原有的act_returns值需全部归属到同日期同out_id的Beta类型
• 若某日某out_id无Beta记录，需生成该Beta记录，Non_Recon_Amnt设为0，act_returns取当日Alpha的act_returns总和

实际表数据 #

期望查询结果 #

当前查询问题 #

现有查询结果缺失18/12的Beta记录，且该日Alpha的act_returns未转移到Beta。原因是查询依赖原表存在Beta记录才能生成对应行，无法自动补全缺失的Beta类型行。

现有查询语句 #

SELECT
    dt AS date_,
    out_id,
    out_name,
    ct,
    ord_type,
    identifier1,
    identifier2,
    SUM(Non_Recon_Amnt),
    SUM(ret_loss),
    SUM(act_returns)

FROM
    (
        SELECT
            dt,
            out_id,
            out_name,
            ct,
            ord_type,
            identifier1,
            identifier2,
            SUM(Non_Recon_Amnt) as Non_Recon_Amnt,
            CASE WHEN ord_type='Alpha' AND identifier1='true' THEN SUM(ret_loss) ELSE 0 END as ret_loss,
            CASE
                WHEN ord_type = 'Alpha' AND identifier1 = 'true' THEN 0
                WHEN ord_type = 'Beta' THEN
                    (
                        SELECT SUM(act_returns)
                        FROM generic_table
                        WHERE dt=G.dt AND out_id=G.out_id
                        AND ord_type = 'Alpha' AND identifier1 = 'true'
                        OR dt=G.dt AND out_id=G.out_id
                        AND ord_type = 'Beta'
                    )
                ELSE SUM(act_returns)
            END AS act_returns,
            FROM
            generic_table G
        WHERE
            dt >= current_date - 30 AND dt < current_date
        GROUP BY
            1, 2, 3, 4, 5, 6, 7
    ) AS subquery
GROUP BY 1, 2, 3, 4, 5, 6, 7

优化方案 #

核心思路是先生成所有需要的(dt, out_id, ord_type)组合，确保每个日期每个out_id都包含所有ord_type，再左连接原表处理数据：

步骤说明 #

1. 生成全量组合：提取原表中所有唯一的(dt, out_id)对，与所有可能的ord_type（Alpha、Beta、Gamma）做笛卡尔积，补全缺失的类型行。
2. 预计算Alpha转移值：提前计算每个(dt, out_id)下Alpha类型的act_returns总和，用于后续Beta类型的数值计算。
3. 左连接原表并处理规则：将全量组合与原表分组聚合后的结果左连接，按规则填充各字段：
   • Alpha的act_returns设为0
   • Beta的act_returns = 原Beta的act_returns + 当日Alpha的转移值
   • 缺失的Non_Recon_Amnt设为0
   • 按类型设置默认的identifier1值

优化后的SQL #

WITH all_combinations AS (
    -- 生成所有需要的(dt, out_id, ord_type)组合
    SELECT DISTINCT
        dt,
        out_id,
        ord_type
    FROM
        generic_table,
        (SELECT 'Alpha' AS ord_type UNION ALL SELECT 'Beta' UNION ALL SELECT 'Gamma') AS all_types
    WHERE
        dt >= current_date - 30 AND dt < current_date
),
alpha_transfer AS (
    -- 计算每个(dt, out_id)下Alpha的act_returns总和
    SELECT
        dt,
        out_id,
        SUM(act_returns) AS alpha_returns
    FROM
        generic_table
    WHERE
        ord_type = 'Alpha' AND identifier1 = 'true'
        AND dt >= current_date - 30 AND dt < current_date
    GROUP BY
        dt, out_id
),
original_agg AS (
    -- 原表数据分组聚合
    SELECT
        dt,
        out_id,
        ord_type,
        identifier1,
        out_name,
        ct,
        identifier2,
        SUM(Non_Recon_Amnt) AS Non_Recon_Amnt,
        SUM(CASE WHEN ord_type='Alpha' AND identifier1='true' THEN ret_loss ELSE 0 END) AS ret_loss,
        SUM(act_returns) AS act_returns
    FROM
        generic_table
    WHERE
        dt >= current_date - 30 AND dt < current_date
    GROUP BY
        dt, out_id, ord_type, identifier1, out_name, ct, identifier2
)
SELECT
    ac.dt AS date_,
    ac.out_id,
    COALESCE(oa.out_name, '') AS out_name, -- 根据实际情况设置默认值
    COALESCE(oa.ct, '') AS ct,
    ac.ord_type,
    CASE
        WHEN ac.ord_type = 'Alpha' THEN 'true'
        ELSE 'false'
    END AS identifier1,
    COALESCE(oa.identifier2, '') AS identifier2,
    COALESCE(oa.Non_Recon_Amnt, 0) AS Non_Recon_Amnt,
    COALESCE(oa.ret_loss, 0) AS ret_loss,
    CASE
        WHEN ac.ord_type = 'Alpha' THEN 0
        WHEN ac.ord_type = 'Beta' THEN COALESCE(oa.act_returns, 0) + COALESCE(at.alpha_returns, 0)
        ELSE COALESCE(oa.act_returns, 0)
    END AS act_returns
FROM
    all_combinations ac
LEFT JOIN original_agg oa
    ON ac.dt = oa.dt AND ac.out_id = oa.out_id AND ac.ord_type = oa.ord_type
LEFT JOIN alpha_transfer at
    ON ac.dt = at.dt AND ac.out_id = at.out_id
ORDER BY
    ac.dt, ac.out_id, ac.ord_type;

关键改进点 #

• 使用CTE生成全量组合，确保不会缺失任何日期的任何ord_type记录
• 预计算Alpha的转移值，避免子查询嵌套导致的性能问题和依赖限制
• 用COALESCE处理缺失值，确保数值字段不会出现NULL
• 明确设置各类型的默认值，符合业务规则

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