如何按year和product_code分组计算Products表的total_amt_by_prod等指标

问题修正 #

你当前编写的SQL存在一处笔误：total_num的计算逻辑里重复引用了num2，遗漏了num3，需要先修正为：NVL(num1, 0) + NVL(num2, 0) + NVL(num3, 0) total_num

提示：如果使用的是MySQL等不支持NVL函数的数据库，将语句中的NVL替换为IFNULL即可，逻辑完全一致。

实现方案 #

根据你是否需要保留原始行数据，有两种实现方式：

方案1：保留所有原始行，每行附带对应分组的汇总值

如果你需要保留Products表的全部原始字段、每行的total_amt和total_num，同时每行都显示所属year+product_code分组的汇总值，可以用窗口函数实现：

WITH prod_base AS (
    SELECT 
        *,
        NVL(amt1, 0) + NVL(amt2, 0) + NVL(amt3, 0) AS total_amt,
        NVL(num1, 0) + NVL(num2, 0) + NVL(num3, 0) AS total_num
    FROM Products
)
SELECT 
    *,
    SUM(total_amt) OVER (PARTITION BY year, product_code) AS total_amt_by_prod,
    SUM(total_num) OVER (PARTITION BY year, product_code) AS total_num_by_prod
FROM prod_base

方案2：仅输出分组后的汇总结果

如果你不需要原始行数据，只需要每个year+product_code分组的汇总值，直接用GROUP BY聚合即可：

WITH prod_base AS (
    SELECT 
        year,
        product_code,
        NVL(amt1, 0) + NVL(amt2, 0) + NVL(amt3, 0) AS total_amt,
        NVL(num1, 0) + NVL(num2, 0) + NVL(num3, 0) AS total_num
    FROM Products
)
SELECT 
    year,
    product_code,
    SUM(total_amt) AS total_amt_by_prod,
    SUM(total_num) AS total_num_by_prod
FROM prod_base
GROUP BY year, product_code

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