SQL实现：统计指定条件行数并显示计数为0的日期行

原始数据表 #

|users_id  | request_at | status    | banned |
| -------- | ---------- | --------  | ------ |
| 2        | 2013-10-01 | cancelled | No     |
| 3        | 2013-10-01 | cancelled | Yes    |
| 4        | 2013-10-01 | cancelled | No     |
| 5        | 2013-10-02 | confirmed | No     |
| 6        | 2013-10-03 | cancelled | No     |
| 7        | 2013-10-03 | cancelled | No     |

需求说明 #

统计每天中满足status != 'confirmed'且banned = 'No'的行数，同时在条件不匹配的日期显示计数为0。

当前SQL及问题 #

当前使用的SQL语句：

select 
    distinct (COUNT(users_id) OVER (PARTITION BY request_at)) AS count_ban,
    request_at
    from Alll 
    where banned='No' and status != 'confirmed'

执行结果：

| count_ban | request_at |
| --------- | ---------- |
| 2         | 2013-10-03 |
| 3         | 2013-10-01 |

结果计数值正确，但缺少2013-10-02对应的行（该日期计数应为0）。

最简单的解决方法 #

方法1：条件聚合（推荐，代码更简洁） #

直接按日期分组，用条件判断统计符合要求的行数：

SELECT 
    request_at,
    SUM(CASE WHEN banned = 'No' AND status != 'confirmed' THEN 1 ELSE 0 END) AS count_ban
FROM Alll
GROUP BY request_at
ORDER BY request_at;

执行结果会包含所有日期，其中2013-10-02的count_ban为0，完全符合需求。

方法2：基于日期集合的左连接 #

如果需要更灵活的日期范围（比如包含表中没有的日期），可以先提取所有唯一日期再左连接统计：

WITH all_dates AS (
    SELECT DISTINCT request_at FROM Alll
)
SELECT 
    ad.request_at,
    COALESCE(COUNT(a.users_id), 0) AS count_ban
FROM all_dates ad
LEFT JOIN Alll a 
    ON ad.request_at = a.request_at 
    AND a.banned = 'No' 
    AND a.status != 'confirmed'
GROUP BY ad.request_at
ORDER BY ad.request_at;

COALESCE函数会把左连接后没有匹配到的NULL值转为0，确保所有日期都有计数显示。

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