如何基于聚合表汇总事件日期，生成每日启用/禁用统计报表？

原始数据 #

目标报表 #

原SQL的问题 #

你当前的SQL使用了隐式内连接，仅保留了同时存在启用和禁用记录的日期，导致丢失了只有单一事件的日期（比如02/01、03/01等）。

完整解决方案 #

要生成包含所有日期的报表，需要先获取所有出现过的日期（禁用日期+启用日期），再分别关联每日的启用、禁用统计数据，用LEFT JOIN确保所有日期都被保留，空值用0填充。

实现SQL #

WITH all_dates AS (
    -- 获取所有唯一的日期（禁用日期和启用日期的合集）
    SELECT "禁用日期（Disabled Date)" AS date FROM your_table
    UNION
    SELECT "启用日期（Enabled Date)" AS date FROM your_table
),
daily_disables AS (
    -- 按日期统计每日禁用总数
    SELECT 
        "禁用日期（Disabled Date)" AS date,
        SUM("数量（Count）") AS disables_count
    FROM your_table
    GROUP BY "禁用日期（Disabled Date)"
),
daily_enables AS (
    -- 按日期统计每日启用总数
    SELECT 
        "启用日期（Enabled Date)" AS date,
        SUM("数量（Count）") AS enables_count
    FROM your_table
    GROUP BY "启用日期（Enabled Date)"
)
SELECT
    ad.date AS "日期",
    COALESCE(de.enables_count, 0) AS "启用数（Enables）",
    COALESCE(dd.disables_count, 0) AS "禁用数（Disables）"
FROM all_dates ad
LEFT JOIN daily_enables de ON ad.date = de.date
LEFT JOIN daily_disables dd ON ad.date = dd.date
ORDER BY ad.date;

关键说明 #

• all_dates CTE：通过UNION获取所有出现过的日期，确保没有遗漏任何有事件的日期
• daily_disables和daily_enables：分别统计每日的禁用、启用总数
• LEFT JOIN：确保all_dates中的每一个日期都被保留，即使对应的启用/禁用统计不存在
• COALESCE：将空值（无对应统计的日期）替换为0，符合目标报表格式

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