如何关联同表并按不同条件实现多列YTD统计结果展示？

问题场景 #

你有一张包含各国月度数据的表，结构如下：

你需要统计指定年份（示例为2019年）截至指定月份（3月）的年初至今（YTD）数量/金额，以及上一年同期的对应数据，期望结果：

之前尝试用表关联的方式编写SQL，但遇到了问题：如果当前年份缺失某个月份的记录，就无法统计到上一年同期该月份的数据；反过来以上一年表为主表，又会导致当前年份的统计不全。想要找到一种无需为每年单独编写查询的通用方法。

解决方案：使用条件聚合 #

其实不用复杂的表关联，咱们可以用条件聚合函数来实现，一次查询就能覆盖所有情况，不管哪一年有缺失月份都能正确统计。

-- 定义要统计的目标年份和截止月份
WITH params AS (
    SELECT 2019 AS target_year, 3 AS target_month
)
SELECT 
    Country_dsc AS Name,
    -- 统计目标年份年初至目标月份的Quantity总和
    SUM(CASE WHEN Year = p.target_year AND Month <= p.target_month THEN Quantity ELSE 0 END) AS YTD_Quantity_Y,
    -- 统计上一年同期的Quantity总和
    SUM(CASE WHEN Year = p.target_year - 1 AND Month <= p.target_month THEN Quantity ELSE 0 END) AS YTD_Quantity_LY,
    -- 统计目标年份年初至目标月份的Value总和
    SUM(CASE WHEN Year = p.target_year AND Month <= p.target_month THEN Value ELSE 0 END) AS YTD_Value_Y,
    -- 统计上一年同期的Value总和
    SUM(CASE WHEN Year = p.target_year - 1 AND Month <= p.target_month THEN Value ELSE 0 END) AS YTD_Value_LY
FROM example_table, params p
GROUP BY Country_dsc;

代码解释 #

1. 参数定义：用CTE（WITH params AS ...）定义目标年份和截止月份，后续要修改统计时间的话，只需要改这里的参数就行，非常灵活。
2. 条件聚合：每个SUM(CASE ...)都只对符合条件的行进行求和：
   • 对于当年YTD数据：只筛选Year = target_year且Month <= target_month的行，其他行的Quantity/Value按0处理
   • 对于上年同期数据：只筛选Year = target_year - 1且Month <= target_month的行，其他行按0处理
3. 无关联逻辑：不需要做表自关联，直接对整张表进行聚合，不管哪一年缺失月份，缺失的月份对应的数值会被视为0，不会影响最终的总和统计。

把你的示例数据代入这个查询，就能得到你想要的结果，而且不管是2019年缺1月，还是2018年缺某个月份，都能正确统计出对应YTD数值。

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