如何查询含零注册量的课程月度注册统计及累计注册数？

我的PieCloudDB数据库中有courses和enrollments两张表，表结构及数据如下：

courses表 #

enrollments表 #

我需要查询每门课程的月度注册人数及累计注册人数，当前使用的查询语句：

SELECT 
    EXTRACT(YEAR FROM e.enrollment_date) AS year, 
    EXTRACT(MONTH FROM e.enrollment_date) AS month, 
    c.course_name, 
    COUNT(DISTINCT e.customer_id) AS monthly_registrations, 
    SUM(COUNT(DISTINCT e.customer_id)) OVER (PARTITION BY e.course_id ORDER BY EXTRACT(YEAR FROM e.enrollment_date), EXTRACT(MONTH FROM e.enrollment_date)) AS cumulative_registrations 
FROM enrollments e 
JOIN courses c ON e.course_id = c.course_id 
GROUP BY e.course_id, c.course_name, year, month 
ORDER BY year, month, c.course_name;

得到的结果：

现在需要补充课程月度注册人数为0的记录，期望结果如下：

请用PostgreSQL语法给出解决方案（PieCloudDB兼容PostgreSQL）。

要实现包含0注册数的记录，核心是生成所有课程与所有统计月份的笛卡尔积，再关联实际注册数据，具体步骤如下：

1. 生成所有需要统计的年月范围：从注册记录的最小年份月份到最大年份月份，确保覆盖所有存在注册的月份。
2. 将生成的年月与所有课程做交叉连接（CROSS JOIN），得到每个课程在每个年月的空记录框架。
3. 左连接实际的注册统计数据，将空值转为0，再计算累计注册数。

完整SQL语句：

WITH date_range AS (
    -- 生成所有需要统计的年月
    SELECT 
        generate_series(
            DATE_TRUNC('month', MIN(enrollment_date))::DATE,
            DATE_TRUNC('month', MAX(enrollment_date))::DATE,
            INTERVAL '1 month'
        ) AS month_start
    FROM enrollments
),
course_month_frames AS (
    -- 生成所有课程+所有年月的组合框架
    SELECT
        EXTRACT(YEAR FROM dr.month_start) AS year,
        EXTRACT(MONTH FROM dr.month_start) AS month,
        c.course_id,
        c.course_name
    FROM date_range dr
    CROSS JOIN courses c
),
monthly_reg AS (
    -- 预先统计每门课程每个月的实际注册人数
    SELECT
        EXTRACT(YEAR FROM e.enrollment_date) AS year,
        EXTRACT(MONTH FROM e.enrollment_date) AS month,
        e.course_id,
        COUNT(DISTINCT e.customer_id) AS monthly_registrations
    FROM enrollments e
    GROUP BY e.course_id, year, month
)
-- 关联框架和实际统计，计算累计值
SELECT
    cmf.year,
    cmf.month,
    cmf.course_name,
    COALESCE(mr.monthly_registrations, 0) AS monthly_registrations,
    SUM(COALESCE(mr.monthly_registrations, 0)) OVER (
        PARTITION BY cmf.course_id 
        ORDER BY cmf.year, cmf.month
    ) AS cumulative_registrations
FROM course_month_frames cmf
LEFT JOIN monthly_reg mr 
    ON cmf.course_id = mr.course_id 
    AND cmf.year = mr.year 
    AND cmf.month = mr.month
ORDER BY cmf.year, cmf.month, cmf.course_name;

语句说明 #

• date_range CTE：通过generate_series生成从最早注册月份到最晚注册月份的所有月份起始日期，确保覆盖所有需要统计的年月。
• course_month_frames CTE：将所有年月与所有课程交叉连接，生成完整的记录框架，保证每个课程每个月都有一条记录。
• monthly_reg CTE：预先统计每门课程每个月的实际注册人数，避免重复计算。
• 最后关联框架和实际统计，用COALESCE把空值转为0，再通过窗口函数计算累计注册数，累计时会自动包含之前月份的0值，得到正确的累计结果。

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