按Status月份统计Started与Completed数量的SQL查询问题

原始数据 #

Started     Completed  Status
----------  ---------- ----------
2000-01-30  2000-01-31 2000-01-31
2000-01-30  2000-02-01 2000-02-01

需求 #

按Status的月份分组，统计各月份对应的：

• Started：所有记录中Started日期属于该月份的总数
• Completed：记录中Completed日期和Status日期均属于该月份的总数

期望结果：

Started Completed  Status
------- ---------  ------
2       1          Jan-2000
0       1          Feb-2000

现有SQL及错误结果 #

编写的SQL：

SELECT DATE_TRUNC('month', Status) AS Status,
       SUM(IF(DATE_TRUNC('month', Started) = DATE_TRUNC('month', Status), 1, 0)) AS Started,
       SUM(IF(DATE_TRUNC('month', Completed) = DATE_TRUNC('month', Status), 1, 0)) AS Completed
FROM table
GROUP BY Status

执行后错误结果：

Started Completed  Status
------- ---------  ------
1       1          Jan-2000
0       1          Feb-2000

问题原因 #

原SQL的逻辑是：在每个Status月份分组内，仅统计当前分组记录中Started月份与Status月份一致的条目。但第二条记录的Status是2月，它的Started是1月，这条记录被分到了2月的分组中，不会被计入1月分组的Started统计，导致1月的Started计数少了1。

解决方案 #

需要先提取所有存在的Status月份，再针对每个月份单独统计Started的总数（不受Status月份限制），以及符合条件的Completed数量。可以用以下两种方式实现：

方法1：子查询方式 #

SELECT
    TO_CHAR(s.status_month, 'Mon-YYYY') AS Status,
    (SELECT COUNT(*) FROM table WHERE DATE_TRUNC('month', Started) = s.status_month) AS Started,
    COUNT(CASE WHEN DATE_TRUNC('month', Completed) = s.status_month THEN 1 END) AS Completed
FROM (
    SELECT DISTINCT DATE_TRUNC('month', Status) AS status_month
    FROM table
) s
LEFT JOIN table t ON DATE_TRUNC('month', t.Status) = s.status_month
GROUP BY s.status_month
ORDER BY s.status_month

方法2：CTE方式（更易读） #

WITH status_months AS (
    SELECT DISTINCT DATE_TRUNC('month', Status) AS month
    FROM table
)
SELECT
    TO_CHAR(s.month, 'Mon-YYYY') AS Status,
    (SELECT COUNT(*) FROM table WHERE DATE_TRUNC('month', Started) = s.month) AS Started,
    SUM(CASE WHEN DATE_TRUNC('month', Completed) = s.month THEN 1 ELSE 0 END) AS Completed
FROM status_months s
LEFT JOIN table t ON DATE_TRUNC('month', t.Status) = s.month
GROUP BY s.month
ORDER BY s.month

逻辑说明 #

1. 先通过子查询/CTE获取所有出现过的Status月份，确保每个需要统计的月份都出现在结果中。
2. 对每个Status月份，用子查询统计所有记录中Started属于该月份的总数，这样就能把所有Started在1月的记录都计入1月的统计，得到数值2。
3. Completed的统计保留原逻辑：仅统计Status属于当前月份且Completed也属于当前月份的记录数，符合需求。

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