MySQL按多日期列的%Y-%m分组统计ID数量

原表结构 #

id      idate               Client  Groupe  Des     Open_Date           Status  Update_Date         Install_Date
316161  05/03/2022 11:17    a       z       e       05/02/2022 18:04    aa      18/08/2022 18:40    NULL
316160  18/08/2022 16:19    b       y       f       17/08/2022 08:13    bb      NULL                30/09/2022 12:49
316159  25/09/2022 21:47    c       x       g       30/08/2022 23:56    aa      05/10/2022 09:24    06/10/2022 20:37
316158  02/10/2022 01:34    d       w       h       02/10/2022 00:04    dd      NULL                NULL

需求 #

按%Y-%m格式（包含NULL分组）统计各日期列的ID数量，输出格式如下：

date        idate_count     opdate_count    update_count    instdate_count
NULL        0               0               2               2
2022-02     0               1               0               0
2022-03     1               0               0               0
2022-08     1               2               1               0
2022-09     1               0               0               1
2022-10     1               1               1               1

解决方案 #

使用CTE收集所有需要的分组（含NULL），再通过条件聚合实现多列统计：

WITH all_dates AS (
    -- 收集所有可能的日期分组（包括各列的月份及NULL）
    SELECT DATE_FORMAT(idate, '%Y-%m') AS dt FROM Delivery
    UNION
    SELECT DATE_FORMAT(Open_Date, '%Y-%m') AS dt FROM Delivery
    UNION
    SELECT DATE_FORMAT(Update_Date, '%Y-%m') AS dt FROM Delivery
    UNION
    SELECT DATE_FORMAT(Install_Date, '%Y-%m') AS dt FROM Delivery
    UNION
    SELECT NULL AS dt
)
SELECT
    ad.dt AS date,
    -- 统计idate对应月份的ID数，NULL组为0
    COUNT(CASE WHEN DATE_FORMAT(d.idate, '%Y-%m') = ad.dt THEN d.id END) AS idate_count,
    -- 统计Open_Date：非NULL组按月份统计，NULL组统计该列值为NULL的数量
    COUNT(CASE 
        WHEN ad.dt IS NULL THEN CASE WHEN d.Open_Date IS NULL THEN d.id END
        ELSE CASE WHEN DATE_FORMAT(d.Open_Date, '%Y-%m') = ad.dt THEN d.id END
    END) AS opdate_count,
    -- 统计Update_Date：逻辑同上
    COUNT(CASE 
        WHEN ad.dt IS NULL THEN CASE WHEN d.Update_Date IS NULL THEN d.id END
        ELSE CASE WHEN DATE_FORMAT(d.Update_Date, '%Y-%m') = ad.dt THEN d.id END
    END) AS update_count,
    -- 统计Install_Date：逻辑同上
    COUNT(CASE 
        WHEN ad.dt IS NULL THEN CASE WHEN d.Install_Date IS NULL THEN d.id END
        ELSE CASE WHEN DATE_FORMAT(d.Install_Date, '%Y-%m') = ad.dt THEN d.id END
    END) AS instdate_count
FROM all_dates ad
LEFT JOIN Delivery d ON 
    ad.dt IS NOT NULL AND (
        DATE_FORMAT(d.idate, '%Y-%m') = ad.dt OR
        DATE_FORMAT(d.Open_Date, '%Y-%m') = ad.dt OR
        DATE_FORMAT(d.Update_Date, '%Y-%m') = ad.dt OR
        DATE_FORMAT(d.Install_Date, '%Y-%m') = ad.dt
    )
    OR (ad.dt IS NULL AND (d.Open_Date IS NULL OR d.Update_Date IS NULL OR d.Install_Date IS NULL))
GROUP BY ad.dt
ORDER BY 
    CASE WHEN ad.dt IS NULL THEN 0 ELSE STR_TO_DATE(ad.dt, '%Y-%m') END;

代码说明 #

1. CTE all_dates：通过UNION收集四个日期列的所有唯一月份值，再加入NULL，确保所有需要统计的分组都被覆盖。
2. 左连接匹配：区分两种场景：
   • 非NULL分组：匹配任意日期列格式化为该月份的记录；
   • NULL分组：匹配任意日期列为NULL的记录。
3. 条件聚合：使用COUNT(CASE ...)分别统计每个分组下各日期列的ID数量：
   • 非NULL月份：统计对应日期列符合该月份的ID数；
   • NULL分组：统计对应日期列值为NULL的ID数。
4. 排序：将NULL组放在最前面，其余分组按月份升序排列。

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