select a.month_id, sum(b.aa) aa, sum(b.bb) bb, sum(b.cc) cc, '今年往月新增订购' as A from ( select month_id, sum(aa) aa,sum(bb) bb,sum(cc) cc from ( select month_id , count(distinct case when offer_type_name='主套餐' then offer_name end) aa, count(distinct case when offer_type_name='促销' then offer_name end) bb, count(distinct case when offer_type_name='可选包' then offer_name end) cc, from dwd_sum_zhuliuxxsc where substring(crt_version,1,4)='2023' group by month_id ) ptuser group by month_id ) a join ( select month_id,sum(aa) aa,sum(bb) bb,sum(cc) cc from ( select month_id , count(distinct case when offer_type_name='主套餐' then offer_name end) aa, count(distinct case when offer_type_name='促销' then offer_name end) bb, count(distinct case when offer_type_name='可选包' then offer_name end) cc, from dwd_sum_zhuliuxxsc where substring(crt_version,1,4)='2023' group by month_id ) ptuser group by month_id ) b on a.month_id >= b.month_id group by a.month_id order by a.month_id;(如何每个月都算前几个月的统计值)

要每个月都计算前几个月的统计值，可以使用窗口函数来实现。具体做法是，在子查询中使用窗口函数，计算出每个月的累计值，然后在外层查询中使用SUM()函数来计算前几个月的累计值。

以下是一个示例代码，用于计算每个月的累计值以及前3个月的累计值：

SELECT 
    a.month_id, 
    SUM(b.aa) aa, 
    SUM(b.bb) bb, 
    SUM(b.cc) cc, 
    '今年往月新增订购' AS A,
    SUM(SUM(b.aa)) OVER (ORDER BY a.month_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS aa_cumulative,
    SUM(SUM(b.bb)) OVER (ORDER BY a.month_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS bb_cumulative,
    SUM(SUM(b.cc)) OVER (ORDER BY a.month_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cc_cumulative,
    SUM(SUM(b.aa)) OVER (ORDER BY a.month_id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS aa_cumulative_3m,
    SUM(SUM(b.bb)) OVER (ORDER BY a.month_id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS bb_cumulative_3m,
    SUM(SUM(b.cc)) OVER (ORDER BY a.month_id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cc_cumulative_3m
FROM (
    SELECT 
        month_id, 
        SUM(aa) aa, 
        SUM(bb) bb, 
        SUM(cc) cc 
    FROM (
        SELECT 
            month_id, 
            COUNT(DISTINCT CASE WHEN offer_type_name='主套餐' THEN offer_name END) aa,
            COUNT(DISTINCT CASE WHEN offer_type_name='促销' THEN offer_name END) bb,
            COUNT(DISTINCT CASE WHEN offer_type_name='可选包' THEN offer_name END) cc
        FROM dwd_sum_zhuliuxxsc
        WHERE SUBSTRING(crt_version,1,4)='2023'
        GROUP BY month_id
    ) ptuser
    GROUP BY month_id
) a
JOIN (
    SELECT 
        month_id,
        SUM(aa) aa,
        SUM(bb) bb,
        SUM(cc) cc
    FROM (
        SELECT 
            month_id,
            COUNT(DISTINCT CASE WHEN offer_type_name='主套餐' THEN offer_name END) aa,
            COUNT(DISTINCT CASE WHEN offer_type_name='促销' THEN offer_name END) bb,
            COUNT(DISTINCT CASE WHEN offer_type_name='可选包' THEN offer_name END) cc
        FROM dwd_sum_zhuliuxxsc
        WHERE SUBSTRING(crt_version,1,4)='2023'
        GROUP BY month_id
    ) ptuser
    GROUP BY month_id
) b ON a.month_id >= b.month_id
GROUP BY a.month_id
ORDER BY a.month_id;

在这个示例代码中，我们使用了窗口函数SUM()和OVER()来计算累计值，其中ROWS BETWEEN子句定义了窗口的范围。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从窗口的开始到当前行，而ROWS BETWEEN 3 PRECEDING AND CURRENT ROW表示从当前行往前数3行到当前行。