Snowflake中SUM窗口函数计算异常及分组求和需求实现

问题背景 #

现有Snowflake数据表如下：

原始数据表 #

需求：按ACID、ISSUE_DATE、HARMED_BILL_DUE_DATE分组，求和FLOW_AMT并合并重复行，得到如下预期结果：

预期结果表 #

用户尝试使用窗口函数：

SUM(FLOW_AMT) over(partition by ASSOC_ID, ACID, HARMED_BILL_DUE_DATE, TRAN_ID)

但SM100对应的FLOW_AMT返回665.80，与预期不符。

错误原因分析 #

1. 窗口函数的特性误解：SUM() OVER()仅在分组内计算聚合值，但不会对原始行去重。如果原表中存在更多符合分组条件的行（比如实际数据中SM100的记录不止2条），会导致求和结果偏大；同时窗口函数会为每行返回分组总和，若未做去重处理，会出现重复的总和行。
2. 分组维度冗余：需求是按ACID、ISSUE_DATE、HARMED_BILL_DUE_DATE分组，而原窗口函数额外加入了ASSOC_ID和TRAN_ID，虽然示例中这些值在同组内一致，但如果实际数据中存在不同值，会导致分组被错误拆分。

正确实现方案 #

方案1：使用GROUP BY聚合（推荐） #

需求本质是分组后合并重复行并显示总和，直接用GROUP BY更贴合需求，确保每组仅返回一条记录：

SELECT 
    ASSOC_ID,
    ACID,
    ISSUE_DATE,
    HARMED_BILL_GEN_DATE,
    HARMED_BILL_DUE_DATE,
    UNSAT_PORTION,
    STATUS,
    HARMED_BILL_SAT_DATE,
    TRAN_ID,
    SUM(FLOW_AMT) AS FLOW_AMT,
    REF_NUM
FROM your_table_name
GROUP BY 
    ASSOC_ID,
    ACID,
    ISSUE_DATE,
    HARMED_BILL_GEN_DATE,
    HARMED_BILL_DUE_DATE,
    UNSAT_PORTION,
    STATUS,
    HARMED_BILL_SAT_DATE,
    TRAN_ID,
    REF_NUM;

说明：GROUP BY需包含所有非聚合列，确保同组内这些列的值一致（从示例数据看，同ACID组的非聚合列值均相同，可安全分组）。

方案2：窗口函数+去重 #

若必须使用窗口函数，可先计算分组总和，再通过DISTINCT或ROW_NUMBER()去重：

-- 方式1：DISTINCT去重
SELECT DISTINCT
    ASSOC_ID,
    ACID,
    ISSUE_DATE,
    HARMED_BILL_GEN_DATE,
    HARMED_BILL_DUE_DATE,
    UNSAT_PORTION,
    STATUS,
    HARMED_BILL_SAT_DATE,
    TRAN_ID,
    SUM(FLOW_AMT) OVER(PARTITION BY ACID, ISSUE_DATE, HARMED_BILL_DUE_DATE) AS FLOW_AMT,
    REF_NUM
FROM your_table_name;

-- 方式2：ROW_NUMBER()取每组第一条
WITH summed_data AS (
    SELECT 
        *,
        SUM(FLOW_AMT) OVER(PARTITION BY ACID, ISSUE_DATE, HARMED_BILL_DUE_DATE) AS total_flow,
        ROW_NUMBER() OVER(PARTITION BY ACID, ISSUE_DATE, HARMED_BILL_DUE_DATE ORDER BY (SELECT NULL)) AS rn
    FROM your_table_name
)
SELECT 
    ASSOC_ID,
    ACID,
    ISSUE_DATE,
    HARMED_BILL_GEN_DATE,
    HARMED_BILL_DUE_DATE,
    UNSAT_PORTION,
    STATUS,
    HARMED_BILL_SAT_DATE,
    TRAN_ID,
    total_flow AS FLOW_AMT,
    REF_NUM
FROM summed_data
WHERE rn = 1;

原错误排查关键点 #

1. 核对原始数据行数：若窗口函数返回665.80，说明分组内的FLOW_AMT总和确实为该值，需检查原表中SM100的实际记录数是否多于示例中的2条。
2. 修正分组维度：将窗口函数的PARTITION BY调整为需求指定的ACID, ISSUE_DATE, HARMED_BILL_DUE_DATE，移除冗余的ASSOC_ID和TRAN_ID。
3. 验证去重逻辑：窗口函数本身不会去重，需额外添加去重步骤才能得到预期的单行分组结果。

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