How to count the number of days in Mysql where an event has happened in a row consecutively daily?

Question

See table dataHow to count the number of days in MYsql where an event has happened in a row consecutively daily?

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);

I assume I need to do a datediff ( from the previous row ) and Row_number function?

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

This is a [tag:gaps-and-islands] problem. Islands specifically is what you're interested in. The solution will likely require using `LEAD()` or `LAG()` — Conrad Frix, Feb 27 '23 at 18:42
If I have database 8.0.23, how will my code look like? Thank you! — Ligia C, Feb 27 '23 at 20:09
Can you share you simple data as code not as image, and the expected data, so we can help — SelVazi, Feb 27 '23 at 20:15

user1191247 · Accepted Answer · 2023-02-27T22:17:53.340

With the date strings converted to dates:

UPDATE MrDataConverter
SET `date` = STR_TO_DATE(`date`, '%m/%d/%Y %H:%i');

This gets you most of the way there:

SELECT
    num, controller_id, event_type, date,
    COUNT(IF(DATE(`date`) = prev_date, NULL, `date`)) OVER (PARTITION BY controller_id, grp ORDER BY DATE(`date`)) AS `desired output`
FROM (
    SELECT *,
        DATE(`date`) - INTERVAL DENSE_RANK() OVER (PARTITION BY controller_id ORDER BY DATE(`date`)) DAY AS grp,
        LAG(DATE(`date`)) OVER (PARTITION BY controller_id ORDER BY DATE(`date`)) AS prev_date
    FROM MrDataConverter
) t
ORDER BY num ASC;

num	controller_id	event_type	date	desired output
1	42260	12	2023-02-27 13:09:00	9
2	42260	12	2023-02-26 13:09:00	8
3	42260	12	2023-02-25 13:09:00	7
4	42260	12	2023-02-24 13:08:00	6
5	42260	12	2023-02-23 13:08:00	5
6	42260	12	2023-02-22 13:06:00	4
7	42260	12	2023-02-21 13:09:00	3
8	42260	12	2023-02-20 13:06:00	2
9	42260	12	2023-02-19 10:27:00	1
10	42260	12	2023-02-19 03:11:00	1
11	42260	12	2023-02-17 16:08:00	10
12	42260	12	2023-02-17 15:36:00	10
13	42260	12	2023-02-17 14:20:00	10
14	42260	12	2023-02-17 13:14:00	10
15	42260	12	2023-02-16 13:05:00	9
16	42260	12	2023-02-15 13:04:00	8
17	42260	12	2023-02-14 13:04:00	7
18	42260	12	2023-02-13 13:05:00	6
19	42260	12	2023-02-12 13:05:00	5
20	42260	12	2023-02-11 13:05:00	4
21	42260	12	2023-02-10 13:04:00	3
22	42260	12	2023-02-09 13:04:00	2
23	42260	12	2023-02-08 13:04:00	1

I have used DENSE_RANK() instead of ROW_NUMBER() as we don't want it to increment for the same date, when building the group. I have also used LAG() so we can check against the previous date when incrementing the counter.

How to count the number of days in Mysql where an event has happened in a row consecutively daily?

1 Answers1