Netezza SQL实现按4月起始年度分组的月度累计求和

现有Netezza数据表my_table，结构与数据如下：

type var1 var2     date_1  date_2
     a    5    0 2010-01-01 2009-2010
     a   10    1 2010-01-15 2009-2010
     a    1    0 2010-01-29 2009-2010
     a    5    0 2010-05-15 2010-2011
     a   10    1 2010-05-25 2010-2011
     b    2    0 2011-01-01 2010-2011
     b    4    0 2011-01-15 2010-2011
     b    6    1 2011-01-29 2010-2011
     b    1    1 2011-05-15 2011-2012
     b    5    0 2011-05-15 2011-2012

其中date_2是date_1所属的“4月至次年4月”年度（例如date_1=2010-01-01属于2009年4月1日至2010年4月1日，故date_2=2009-2010）。

需求：

• 针对每个type和date_2的组合，计算var1和var2的月度累计和，累计和随date_2变更重置；
• 年度第一个月为4月，需计算month_position（4月为1，5月为2……3月为12）。

预期结果：

type month_position    date_2 cumsum_var1 cumsum_var2
1    a             10 2009-2010          16           1
2    a              2 2010-2011          15           1
3    b             10 2009-2010          12           1
4    b              2 2010-2011           6           1

以下是完整的Netezza SQL语句：

WITH monthly_agg AS (
    SELECT
        type,
        date_2,
        -- 计算month_position：4月为1，依次递推至次年3月为12
        CASE
            WHEN MONTH(date_1) >= 4 THEN MONTH(date_1) - 3
            ELSE MONTH(date_1) + 9
        END AS month_position,
        SUM(var1) AS monthly_var1,
        SUM(var2) AS monthly_var2
    FROM my_table
    GROUP BY type, date_2, CASE WHEN MONTH(date_1) >=4 THEN MONTH(date_1)-3 ELSE MONTH(date_1)+9 END
),
cumulative_calc AS (
    SELECT
        type,
        month_position,
        date_2,
        -- 按type和date_2分区，按month_position排序计算累计和
        SUM(monthly_var1) OVER (PARTITION BY type, date_2 ORDER BY month_position ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumsum_var1,
        SUM(monthly_var2) OVER (PARTITION BY type, date_2 ORDER BY month_position ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumsum_var2
    FROM monthly_agg
)
SELECT * FROM cumulative_calc ORDER BY type, date_2;

逻辑说明 #

1. 月度聚合（monthly_agg）：

   • 用简化的CASE语句计算month_position：4月及以后的月份直接减3（4-3=1，5-3=2…12-3=9），1-3月加9（1+9=10，2+9=11，3+9=12）；
   • 按type、date_2、month_position分组，计算每个月的var1和var2总和。

2. 累计求和（cumulative_calc）：

   • 以type和date_2为分区，按month_position从小到大排序，用窗口函数计算累计和，确保不同date_2的累计会自动重置。

3. 最终输出：

   • 从累计结果中取出所需字段，按type和date_2排序，匹配预期结果格式。

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