月度动态数据筛选与近12月聚合报表优化需求问询

首先，你的核心痛点是硬编码每个月的SQL并通过UNION拼接，这种方式维护成本极高，而且无法自动适配时间推移。下面是更优雅的实现思路，核心是动态生成过去12个月的月度日期范围，再关联业务表进行聚合，彻底告别手动拼接UNION的繁琐操作。

核心思路 #

1. 动态生成包含过去12个月每个月最后一天的日期列表（无需手动指定年月）
2. 将这个日期列表与业务表（crm_account）关联，按每个月的最后一天筛选符合条件的记录
3. 按月份维度聚合数据，得到每个月的统计结果

分数据库实现示例 #

1. PostgreSQL 实现 #

PostgreSQL可以用generate_series轻松生成日期序列，结合date_trunc计算月末：

WITH monthly_periods AS (
    SELECT
        -- 生成过去12个月的每个月最后一天
        (date_trunc('month', CURRENT_DATE - INTERVAL 'n months') + INTERVAL '1 month - 1 day')::DATE AS month_end,
        -- 生成格式为'YYYY_MM'的周期标识
        TO_CHAR(date_trunc('month', CURRENT_DATE - INTERVAL 'n months'), 'YYYY_MM') AS period
    FROM generate_series(0, 11) AS n -- 0代表当前月，11代表11个月前，覆盖过去12个月
)
SELECT
    mp.period,
    COUNT(ca.AccountId) AS account_count -- 替换为你需要的聚合逻辑
FROM monthly_periods mp
LEFT JOIN crm_account ca
    ON ca.ntw_StartedOnBoardingDate <= mp.month_end
    AND (ca.ntw_ChangedToLiveOn > mp.month_end OR ca.ntw_ChangedToLiveOn IS NULL)
    AND (ca.ntw_DisabledOn > mp.month_end OR ca.ntw_DisabledOn IS NULL)
GROUP BY mp.period, mp.month_end
ORDER BY mp.month_end DESC;

2. MySQL 实现 #

MySQL用递归CTE生成日期序列，配合LAST_DAY函数获取月末：

WITH RECURSIVE monthly_periods AS (
    SELECT
        LAST_DAY(CURRENT_DATE - INTERVAL 11 MONTH) AS month_end,
        DATE_FORMAT(LAST_DAY(CURRENT_DATE - INTERVAL 11 MONTH), '%Y_%m') AS period
    UNION ALL
    SELECT
        LAST_DAY(month_end + INTERVAL 1 MONTH),
        DATE_FORMAT(LAST_DAY(month_end + INTERVAL 1 MONTH), '%Y_%m')
    FROM monthly_periods
    WHERE month_end < LAST_DAY(CURRENT_DATE)
)
SELECT
    mp.period,
    COUNT(ca.AccountId) AS account_count
FROM monthly_periods mp
LEFT JOIN crm_account ca
    ON ca.ntw_StartedOnBoardingDate <= mp.month_end
    AND (ca.ntw_ChangedToLiveOn > mp.month_end OR ca.ntw_ChangedToLiveOn IS NULL)
    AND (ca.ntw_DisabledOn > mp.month_end OR ca.ntw_DisabledOn IS NULL)
GROUP BY mp.period, mp.month_end
ORDER BY mp.month_end DESC;

3. SQL Server 实现 #

SQL Server用递归CTE结合EOMONTH函数生成月度序列：

WITH monthly_periods AS (
    SELECT
        EOMONTH(DATEADD(MONTH, -11, GETDATE())) AS month_end,
        FORMAT(EOMONTH(DATEADD(MONTH, -11, GETDATE())), 'yyyy_MM') AS period
    UNION ALL
    SELECT
        EOMONTH(DATEADD(MONTH, 1, month_end)),
        FORMAT(EOMONTH(DATEADD(MONTH, 1, month_end)), 'yyyy_MM')
    FROM monthly_periods
    WHERE month_end < EOMONTH(GETDATE())
)
SELECT
    mp.period,
    COUNT(ca.AccountId) AS account_count
FROM monthly_periods mp
LEFT JOIN crm_account ca
    ON ca.ntw_StartedOnBoardingDate <= mp.month_end
    AND (ca.ntw_ChangedToLiveOn > mp.month_end OR ca.ntw_ChangedToLiveOn IS NULL)
    AND (ca.ntw_DisabledOn > mp.month_end OR ca.ntw_DisabledOn IS NULL)
GROUP BY mp.period, mp.month_end
ORDER BY mp.month_end DESC
OPTION (MAXRECURSION 12); -- 限制递归次数为12，刚好覆盖12个月

方案优势 #

• 动态性：无需手动修改SQL，每月运行都会自动包含最新的12个月数据
• 可维护性：筛选条件只需要定义一次，修改时无需更新多个UNION分支
• 性能优化：避免了多个UNION带来的重复解析开销，数据库能更好地优化查询计划

如果业务表数据量较大，建议给ntw_StartedOnBoardingDate、ntw_ChangedToLiveOn、ntw_DisabledOn这些字段建立复合索引，进一步提升查询效率。

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