如何在Hive SQL中去除UNION ALL，实现按年月统计流量的紧凑查询？

核心逻辑 #

先生成从2020-01-01开始的所有月份第一天的日期序列，再将这个日期表和你的业务表关联，筛选出满足start_date <= 当月第一天且end_date >= 当月第一天的记录，最后按年月分组求和即可，彻底替代冗长的UNION ALL拼接。

以下是主流数据库的实现代码：

MySQL 8.0+/MariaDB 10.2+（递归CTE生成日期序列）

WITH RECURSIVE date_dim AS (
    SELECT '2020-01-01' AS month_start
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM date_dim
    -- 这里可以替换成你需要的截止月份，比如'2024-12-01'
    WHERE month_start <= CURDATE()
)
SELECT
    DATE_FORMAT(dd.month_start, '%Y-%m') AS year_month,
    -- 用COALESCE确保无数据时返回0，不需要可以去掉
    COALESCE(SUM(t.traffic), 0) AS total_traffic
FROM date_dim dd
LEFT JOIN your_table t
    ON t.start_date <= dd.month_start
    AND t.end_date >= dd.month_start
GROUP BY dd.month_start, year_month
ORDER BY dd.month_start;

PostgreSQL（generate_series生成日期序列）

WITH date_dim AS (
    SELECT generate_series(
        '2020-01-01'::date,
        CURRENT_DATE, -- 替换为目标截止月份
        '1 month'::interval
    )::date AS month_start
)
SELECT
    TO_CHAR(dd.month_start, 'YYYY-MM') AS year_month,
    COALESCE(SUM(t.traffic), 0) AS total_traffic
FROM date_dim dd
LEFT JOIN your_table t
    ON t.start_date <= dd.month_start
    AND t.end_date >= dd.month_start
GROUP BY dd.month_start, year_month
ORDER BY dd.month_start;

SQL Server（递归CTE生成日期序列）

WITH date_dim AS (
    SELECT CAST('2020-01-01' AS DATE) AS month_start
    UNION ALL
    SELECT DATEADD(MONTH, 1, month_start)
    FROM date_dim
    -- 替换为你的截止月份
    WHERE month_start <= GETDATE()
)
SELECT
    FORMAT(dd.month_start, 'yyyy-MM') AS year_month,
    ISNULL(SUM(t.traffic), 0) AS total_traffic
FROM date_dim dd
LEFT JOIN your_table t
    ON t.start_date <= dd.month_start
    AND t.end_date >= dd.month_start
GROUP BY dd.month_start, year_month
ORDER BY dd.month_start
-- 如果生成的月份超过100个，必须加这个选项
OPTION (MAXRECURSION 0);

注意事项 #

• 把your_table替换成你的实际表名
• 可以修改日期序列的截止条件，改成固定月份比用当前日期更可控
• 如果不需要显示无数据的年月，把LEFT JOIN改成INNER JOIN即可

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