如何用单条SQL按状态及对应日期列统计日维度用户数？

我需要按日期统计不同状态的用户数量，数据表结构及数据如下：

topic_id| user_id | status |  created_at| updated_at | completed_at |
176     | 1       | 0      |2022-11-20  | NULL       | NULL         |
176     | 2       | 1      |2022-11-20  | 2022-11-21 | 2022-11-21   |
176     | 3       | 0      |2022-11-20  | NULL       | NULL         |
176     | 4       | 2      |2022-11-20  | 2022-11-21 | NULL         |
176     | 5       | 2      |2022-11-20  | 2022-11-21 | NULL         |
176     | 6       | 1      |2022-11-20  | 2022-11-22 | 2022-11-22   |

统计规则：

• Total_Inactive：基于status=0，以created_at的日期作为统计日期
• Total_Scheduled：基于status=2，以updated_at的日期作为统计日期
• Total_Active：基于status=1，以completed_at的日期作为统计日期

期望输出格式：

Date       | Total_Inactive | Total_Scheduled | Total_Active
2022-11-20 | 2              | 0               | 0
2022-11-21 | 0              | 2               | 1 
2022-11-22 | 0              | 0               | 1

实现SQL语句 #

可以通过UNION ALL先整合三类状态对应的日期与标识，再按日期分组聚合数量，单条SQL语句如下：

SELECT
    date AS Date,
    COUNT(CASE WHEN type = 'inactive' THEN 1 END) AS Total_Inactive,
    COUNT(CASE WHEN type = 'scheduled' THEN 1 END) AS Total_Scheduled,
    COUNT(CASE WHEN type = 'active' THEN 1 END) AS Total_Active
FROM (
    -- 提取Inactive状态记录，以created_at为统计日期
    SELECT created_at AS date, 'inactive' AS type
    FROM your_table_name
    WHERE status = 0
    UNION ALL
    -- 提取Scheduled状态记录，以updated_at为统计日期
    SELECT updated_at AS date, 'scheduled' AS type
    FROM your_table_name
    WHERE status = 2
    UNION ALL
    -- 提取Active状态记录，以completed_at为统计日期
    SELECT completed_at AS date, 'active' AS type
    FROM your_table_name
    WHERE status = 1
) AS combined_data
GROUP BY date
ORDER BY date;

说明 #

1. 子查询通过UNION ALL将三类状态的日期与类型标识合并为统一数据集
2. 外层查询按日期分组，用CASE WHEN分别统计每个日期下各状态的用户数
3. 替换语句中的your_table_name为实际数据表名称即可执行

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