使用SQL实现为所有用户输出全季度数据 缺失季度活动单位补0

完全不需要额外创建实体季度表，直接通过SQL生成连续季度序列即可实现需求，以下是可直接运行的实现逻辑：

实现思路 #

1. 用递归CTE生成你需要覆盖的所有连续季度（示例范围为2020-Q1到2021-Q3，可按需调整）
2. 将生成的季度全集和用户表做笛卡尔积，得到每个用户对应所有季度的全量基础组合
3. 左关联活动数据表B，未匹配到的活动数据用COALESCE函数替换为0即可

示例SQL代码（兼容MySQL 8.0+、PostgreSQL、SQL Server等主流支持CTE的数据库） #

WITH RECURSIVE all_quarters AS (
    -- 定义起始季度
    SELECT '2020-Q1' AS quarter
    UNION ALL
    -- 递归生成下一个季度，直到结束季度2021-Q3
    SELECT 
        CONCAT(
            IF(RIGHT(quarter,1)='4', LEFT(quarter,4)+1, LEFT(quarter,4)),
            '-Q',
            IF(RIGHT(quarter,1)='4', 1, RIGHT(quarter,1)+1)
        ) AS quarter
    FROM all_quarters
    WHERE quarter < '2021-Q3'
)
SELECT 
    a.USER_NAME,
    aq.quarter AS QUARTER,
    COALESCE(b.UNITS, 0) AS UNITS
FROM all_quarters aq
-- 笛卡尔关联所有用户，得到每个用户的全季度组合
CROSS JOIN 表A a
-- 左关联活动数据
LEFT JOIN 表B b 
ON aq.quarter = b.QUARTER AND a.USER_ID = b.USER_ID
-- 如果只需要查有活动的用户，可加如下过滤条件
-- WHERE EXISTS (SELECT 1 FROM 表B b2 WHERE b2.USER_ID = a.USER_ID)
ORDER BY a.USER_ID, aq.quarter;

动态季度范围优化 #

如果不想硬编码季度起止范围，可以直接从表B中取最小和最大季度作为递归边界，修改CTE部分即可：

WITH RECURSIVE quarter_range AS (
    SELECT MIN(QUARTER) AS min_q, MAX(QUARTER) AS max_q FROM 表B
),
all_quarters AS (
    SELECT min_q AS quarter FROM quarter_range
    UNION ALL
    SELECT 
        CONCAT(
            IF(RIGHT(quarter,1)='4', LEFT(quarter,4)+1, LEFT(quarter,4)),
            '-Q',
            IF(RIGHT(quarter,1)='4', 1, RIGHT(quarter,1)+1)
        ) AS quarter
    FROM all_quarters, quarter_range
    WHERE quarter < quarter_range.max_q
)
-- 后续查询逻辑和上面一致

运行上述SQL即可得到你需要的输出结果，缺失季度的UNITS字段会自动填充为0。

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