如何在Redshift SQL中计算按市场、产品分区的滚动12个月移动年度总计

我尝试新建一列计算按Market、Product分区的滚动求和（即移动年度总计/滚动12个月求和），为简化说明示例仅包含唯一的Market和Product，我期望实现的效果如下：

遇到的难点 #

• 数据中存在同一个日期对应的重复行，见黄色高亮标注部分；
• 已尝试编写的SQL逻辑无法正常运行，代码如下：

SELECT   ,
 TO_DATE(A.DATE
          || '01','YYYYMMDD') AS DATE_PERIOD ,
 EXTRACT(YEAR FROM (TO_DATE(A.DATE
          || '01','YYYYMMDD'))) AS YEARS ,
 EXTRACT(MONTH FROM (TO_DATE(A.DATE
          || '01','YYYYMMDD'))) AS MONTHS ,
 A.PRODUCT                      AS PRODUCT ,
 A.MARKET                       AS MKT ,
 SUM(A.LOCAL_CURRENCY_VAL) OVER (PARTITION BY EXTRACT(YEAR FROM (TO_DATE(A.DATE
          || '01','YYYYMMDD'))),A.MARKET, A.PRODUCT ORDER BY EXTRACT(MONTH FROM (TO_DATE(A.DATE
          || '01','YYYYMMDD'))) ROWS BETWEEN 11 PRECEDING AND    CURRENT ROW ) AS ROLLING_12     
FROM MYTABLE AS A
GROUP BY ,
     A.PRODUCT ,
     A.LOCAL_CURRENCY_VAL ,
     EXTRACT(YEAR FROM (TO_DATE(A.DATA_PERIOD
              || '01','YYYYMMDD'))) ,
     EXTRACT(MONTH FROM (TO_DATE(A.DATA_PERIOD
              || '01','YYYYMMDD'))) ,
     A.DATA_PERIOD

问题修正方案 #

原有代码问题梳理 #

1. 语法错误：SELECT和GROUP BY后多了冗余逗号，同时代码中混用了DATE和DATA_PERIOD两个字段名
2. 分区逻辑错误：开窗函数的分区条件加入了年份，会导致跨年时无法统计上一年度的数据，只能在同一年内滚动计算
3. 去重逻辑错误：GROUP BY中加入了LOCAL_CURRENCY_VAL，无法实现同日期下的金额聚合，也没有解决重复行的问题
4. 开窗范围错误：使用ROWS BETWEEN按行偏移计算，遇到同月份多条数据时，滚动范围会少于12个自然月

修正后代码 #

WITH month_agg AS (
    -- 先按月份、市场、产品聚合，去除同月份重复行，计算单月总金额
    SELECT
        TO_DATE(A.DATA_PERIOD || '01','YYYYMMDD') AS DATE_PERIOD,
        A.PRODUCT,
        A.MARKET AS MKT,
        SUM(A.LOCAL_CURRENCY_VAL) AS MONTH_TOTAL
    FROM MYTABLE AS A
    -- 按唯一分组维度聚合，解决重复行问题
    GROUP BY A.DATA_PERIOD, A.PRODUCT, A.MARKET
)
SELECT
    DATE_PERIOD,
    EXTRACT(YEAR FROM DATE_PERIOD) AS YEARS,
    EXTRACT(MONTH FROM DATE_PERIOD) AS MONTHS,
    PRODUCT,
    MKT,
    -- 开窗按市场、产品分区，按日期排序，取近12个月的金额总和
    SUM(MONTH_TOTAL) OVER (
        PARTITION BY MKT, PRODUCT 
        ORDER BY DATE_PERIOD 
        RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW
    ) AS ROLLING_12
FROM month_agg
ORDER BY MKT, PRODUCT, DATE_PERIOD;

如果你的数据库不支持RANGE搭配间隔的写法，可以替换为下面的兼容版本：

WITH month_agg AS (
    SELECT
        TO_DATE(A.DATA_PERIOD || '01','YYYYMMDD') AS DATE_PERIOD,
        A.PRODUCT,
        A.MARKET AS MKT,
        SUM(A.LOCAL_CURRENCY_VAL) AS MONTH_TOTAL
    FROM MYTABLE AS A
    GROUP BY A.DATA_PERIOD, A.PRODUCT, A.MARKET
)
SELECT
    DATE_PERIOD,
    EXTRACT(YEAR FROM DATE_PERIOD) AS YEARS,
    EXTRACT(MONTH FROM DATE_PERIOD) AS MONTHS,
    PRODUCT,
    MKT,
    SUM(MONTH_TOTAL) OVER (
        PARTITION BY MKT, PRODUCT 
        ORDER BY EXTRACT(YEAR FROM DATE_PERIOD)*100 + EXTRACT(MONTH FROM DATE_PERIOD)
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS ROLLING_12
FROM month_agg
ORDER BY MKT, PRODUCT, DATE_PERIOD;

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