如何在MySQL中统计事件连续每日发生的天数？

表结构与测试数据 #

CREATE TABLE MrDataConverter (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  num INT,
  controller_id INT,
  event_type INT,
  date VARCHAR(255),
  `Desired output` INT
);
INSERT INTO MrDataConverter 
  (num,controller_id,event_type,date,`Desired output`) 
VALUES 
  (1,42260,12,'2/27/2023 13:09',8),
  (2,42260,12,'2/26/2023 13:09',7),
  (3,42260,12,'2/25/2023 13:09',6),
  (4,42260,12,'2/24/2023 13:08',5),
  (5,42260,12,'2/23/2023 13:08',4),
  (6,42260,12,'2/22/2023 13:06',3),
  (7,42260,12,'2/21/2023 13:09',2),
  (8,42260,12,'2/20/2023 13:06',1),
  (9,42260,12,'2/19/2023 10:27',0),
  (10,42260,12,'2/19/2023 3:11',0),
  (11,42260,12,'2/17/2023 16:08',0),
  (12,42260,12,'2/17/2023 15:36',0),
  (13,42260,12,'2/17/2023 14:20',0),
  (14,42260,12,'2/17/2023 13:14',9),
  (15,42260,12,'2/16/2023 13:05',8),
  (16,42260,12,'2/15/2023 13:04',7),
  (17,42260,12,'2/14/2023 13:04',6),
  (18,42260,12,'2/13/2023 13:05',5),
  (19,42260,12,'2/12/2023 13:05',4),
  (20,42260,12,'2/11/2023 13:05',3),
  (21,42260,12,'2/10/2023 13:04',2),
  (22,42260,12,'2/9/2023 13:04',1),
  (23,42260,12,'2/8/2023 13:04',0);

用户思路 #

考虑使用datediff（计算与上一行的日期差）和ROW_NUMBER()函数，尝试了以下SQL：

SELECT row_number() over ( PARTITION BY controller_id ORDER BY date DESC) num, controller_id, date
FROM events 
WHERE controller_id = 42260
Order By date desc
LIMIT 200 

需求说明 #

需根据上述表数据，计算每条记录对应的事件连续每日发生的天数，匹配Desired output列的结果。

解决方案 #

要实现匹配目标列的结果，核心是先按日期去重（同一天多条记录仅算1天），再识别连续日期的分组，最后为每条记录计算对应的连续天数序号。完整SQL如下：

SELECT 
    m.id,
    m.num,
    m.controller_id,
    m.event_type,
    m.date,
    CASE 
        WHEN grp_days.rn = 1 THEN 0
        ELSE grp_days.rn - 1
    END AS calculated_output,
    m.`Desired output`
FROM MrDataConverter m
JOIN (
    SELECT 
        controller_id,
        event_type,
        dt,
        ROW_NUMBER() OVER (PARTITION BY controller_id, event_type, grp ORDER BY dt DESC) AS rn
    FROM (
        SELECT 
            controller_id,
            event_type,
            DATE(STR_TO_DATE(date, '%m/%d/%Y %H:%i')) AS dt,
            DATE_SUB(DATE(STR_TO_DATE(date, '%m/%d/%Y %H:%i')), INTERVAL ROW_NUMBER() OVER (PARTITION BY controller_id, event_type ORDER BY DATE(STR_TO_DATE(date, '%m/%d/%Y %H:%i'))) DAY) AS grp
        FROM MrDataConverter
        GROUP BY controller_id, event_type, DATE(STR_TO_DATE(date, '%m/%d/%Y %H:%i'))
    ) AS date_groups
) AS grp_days ON m.controller_id = grp_days.controller_id 
             AND m.event_type = grp_days.event_type
             AND DATE(STR_TO_DATE(m.date, '%m/%d/%Y %H:%i')) = grp_days.dt
ORDER BY m.id;

代码解释 #

1. 日期转换与去重：内层子查询先将字符串类型的date转换为日期格式，提取日期部分并按controller_id、event_type和日期去重，确保同一天的事件只统计一次。
2. 连续日期分组：通过DATE_SUB(日期, INTERVAL 行号 DAY)生成分组标识grp，连续的日期会得到相同的grp值，以此区分不同的连续日期段。
3. 计算连续天数序号：对每个分组内的日期按倒序排序，用ROW_NUMBER()生成序号，序号减1后就是对应记录的连续天数（序号为1时对应0，符合目标列规则）。
4. 关联回原表：将分组计算的结果关联回原表，为每条记录匹配对应的连续天数。

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