Teradata按月份分组嵌套聚合问题：去重获取月最小支付额

问题核心 #

你需要计算每日支付余额，提取每个客户每月的最小支付额并去除重复行。当前查询存在两个问题：

• 未加GROUP BY时，表关联产生重复行，结果冗余；
• 添加GROUP BY后，要么触发嵌套聚合错误，要么得到全周期最小值而非每月最小值，本质是聚合逻辑与分组维度不匹配。

最优解决方案 #

方案1：先计算明细再聚合（性能优先） #

先在子查询中算出每日支付余额，再按客户ID、币种、月末日期分组取最小值，从根源避免重复行：

SELECT
    t.Customer_Id,
    t.Currency,
    t.Date_Period,
    MIN(t.Daily_Balance) AS Monthly_Min_Amount
FROM (
    SELECT
        table1.Customer_Id,
        table1.Currency,
        table1.Date_Period,
        -- 计算每日支付余额逻辑
        CASE 
            WHEN table1.Criterion1 = supporttable1.Criterion1 
            THEN CAST((table2.Balance_Amount - table2.Reserved_Balance_Amount) AS FLOAT) * CAST(table3.Currency_Rate AS FLOAT)
            ELSE CAST(table2.Balance_Amount AS FLOAT) * CAST(table3.Currency_Rate AS FLOAT)
        END AS Daily_Balance
    FROM table3
    INNER JOIN table1 
        ON table1.date_period = table3.date_period
    FULL JOIN supporttable1 
        ON table1.Criterion1 = supporttable1.Criterion1
    INNER JOIN table2 
        ON table1.customer_id = table2.customer_id
) t
GROUP BY t.Customer_Id, t.Currency, t.Date_Period
ORDER BY t.Date_Period;

方案2：窗口函数去重（保留明细场景） #

若需保留每日明细同时标记每月最小值，用ROW_NUMBER()去重：

SELECT
    Customer_Id,
    Currency,
    Date_Period,
    Monthly_Min_Amount
FROM (
    SELECT
        table1.Customer_Id,
        table1.Currency,
        table1.Date_Period,
        MIN(CASE 
            WHEN table1.Criterion1 = supporttable1.Criterion1 
            THEN CAST((table2.Balance_Amount - table2.Reserved_Balance_Amount) AS FLOAT) * CAST(table3.Currency_Rate AS FLOAT)
            ELSE CAST(table2.Balance_Amount AS FLOAT) * CAST(table3.Currency_Rate AS FLOAT)
        END) OVER (PARTITION BY table1.Customer_Id, table1.Date_Period) AS Monthly_Min_Amount,
        -- 给每组重复行分配唯一序号，仅保留第一行
        ROW_NUMBER() OVER (PARTITION BY table1.Customer_Id, table1.Date_Period ORDER BY 1) AS rn
    FROM table3
    INNER JOIN table1 
        ON table1.date_period = table3.date_period
    FULL JOIN supporttable1 
        ON table1.Criterion1 = supporttable1.Criterion1
    INNER JOIN table2 
        ON table1.customer_id = table2.customer_id
) t
WHERE rn = 1
ORDER BY Date_Period;

关键说明 #

1. 分组维度正确性：Date_Period是月末日期，直接按Customer_Id + Currency + Date_Period分组即可对应“每月”维度，避免跨年份的月份重复问题；
2. 关联顺序优化：提前INNER JOIN table1减少后续关联的数据量，提升查询性能；
3. 规避嵌套聚合：通过子查询先计算每日余额，外层再做聚合，符合Teradata的聚合规则。

现有重复结果 #

期望结果 #

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