如何修改SQL语句实现每日所有取消类型的计数（含0值）

问题描述 #

我有一张订阅取消表，表结构包含USER_ID、CANCEL_TYPE、OCCURRED_AT三个字段。其中CANCEL_TYPE的可选值固定为：“Cancelled Free Trial”、“Cancelled Paying”、“Set Cancellation”。

需求是按OCCURRED_AT的日期和CANCEL_TYPE分组，统计每日每种取消类型的用户取消次数。当前使用的SQL语句如下：

select
    extract(date from occurred_at) as date, cancel_type, COUNT(customer_id) as total
from TABLE
group by date, cancel_type
order by date, cancel_type

遇到的问题：部分日期没有任何取消记录，或者某日期下部分取消类型没有数据，导致这些组合不会出现在结果中。需要修改语句，让所有日期的所有取消类型都返回，无数据时显示0。

解决方案 #

核心思路是先构建完整的日期范围 + 所有取消类型的组合维度，再左连接原表进行统计，确保所有可能的分组都被覆盖。

1. 定义所有取消类型 #

因为取消类型是固定的3种，用UNION ALL生成临时数据集：

SELECT 'Cancelled Free Trial' AS cancel_type UNION ALL
SELECT 'Cancelled Paying' AS cancel_type UNION ALL
SELECT 'Set Cancellation' AS cancel_type

2. 生成目标日期范围 #

不同数据库生成连续日期的语法不同，以下是主流数据库的实现方式：

PostgreSQL

使用generate_series快速生成连续日期：

SELECT generate_series(
    (SELECT MIN(extract(date from occurred_at)) FROM 你的表名),
    (SELECT MAX(extract(date from occurred_at)) FROM 你的表名),
    INTERVAL '1 day'
)::date AS date

MySQL 8.0+

用递归CTE生成日期序列：

WITH RECURSIVE date_range AS (
    SELECT MIN(DATE(occurred_at)) AS date FROM 你的表名
    UNION ALL
    SELECT date + INTERVAL 1 DAY FROM date_range
    WHERE date + INTERVAL 1 DAY <= (SELECT MAX(DATE(occurred_at)) FROM 你的表名)
)
SELECT date FROM date_range

SQL Server

用递归CTE生成日期，需开启最大递归次数：

WITH date_range AS (
    SELECT CAST(MIN(occurred_at) AS DATE) AS date FROM 你的表名
    UNION ALL
    SELECT DATEADD(DAY, 1, date) FROM date_range
    WHERE DATEADD(DAY, 1, date) <= (SELECT CAST(MAX(occurred_at) AS DATE) FROM 你的表名)
)
SELECT date FROM date_range OPTION (MAXRECURSION 0)

3. 完整SQL示例（以PostgreSQL为例） #

将日期范围和取消类型做笛卡尔积，左连接原表统计，用COALESCE把空值转为0：

WITH all_cancel_types AS (
    SELECT 'Cancelled Free Trial' AS cancel_type UNION ALL
    SELECT 'Cancelled Paying' AS cancel_type UNION ALL
    SELECT 'Set Cancellation' AS cancel_type
),
date_range AS (
    SELECT generate_series(
        (SELECT MIN(extract(date from occurred_at)) FROM 你的表名),
        (SELECT MAX(extract(date from occurred_at)) FROM 你的表名),
        INTERVAL '1 day'
    )::date AS date
)
SELECT
    dr.date,
    ct.cancel_type,
    COALESCE(COUNT(t.user_id), 0) AS total
FROM date_range dr
CROSS JOIN all_cancel_types ct
LEFT JOIN 你的表名 t
    ON dr.date = extract(date from t.occurred_at)
    AND ct.cancel_type = t.cancel_type
GROUP BY dr.date, ct.cancel_type
ORDER BY dr.date, ct.cancel_type;

关键提示 #

• CROSS JOIN生成日期和取消类型的所有组合，确保每个日期都有3种类型的记录
• LEFT JOIN保留所有组合，原表无对应数据时COUNT(t.user_id)返回NULL，COALESCE将其转为0
• 如果需要固定日期范围（比如近30天），可把date_range里的MIN/MAX换成具体日期，例如'2024-05-01'和'2024-05-30'

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