Question 21412 in Mysql Question posted in
The official MySQL documentation can be found here.

Calculating consecutive occurences in MySQL

I have a quick question in relation to windowing in MySQL

SELECT 
Client,
User,
Date,
Flag,
lag(Date) over (partition by Client,User order by Date asc) as last_date,
lag(Flag) over (partition by Client,User order by Date  asc) as last_flag,
case when Flag = 1 and last_flag = 1 then 1 else 0 end as consecutive
FROM db.tbl

This query returns something like the below. I am trying to work out the number of consecutive times that the Flag column was 1 for each user most recently, if they had 11110000111 then we should take the final three occurences of 1 to determine that they had a consecutive flag of 3 times.

I need to extract the start and end date for the consecutive flag.

How would I go about doing this, can anyone help me 🙂

enter image description here

If we use the example of 11110000111 then we should extract only 111 and therefore the 3 most recent dates for that customer. So in the below, we would need to take 10.01.2023 as the first date and 24.01.2023 as the last date. The consecutive count should be 3

enter image description here

Output:

enter image description here

Tags:

2

Answers


  1. 0

    Use aggregation and string functions:

    WITH cte AS (
  SELECT Client, User,
         GROUP_CONCAT(CASE WHEN Flag THEN Date END ORDER BY Date) AS dates,
         CHAR_LENGTH(SUBSTRING_INDEX(GROUP_CONCAT(Flag ORDER BY Date SEPARATOR ''), '0', '-1')) AS consecutive
  FROM tablename
  GROUP BY Client, User
)
SELECT Client, User,
       NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(dates, ',', -consecutive), ',', 1), '') AS first_date,
       CASE WHEN consecutive > 0 THEN SUBSTRING_INDEX(dates, ',', -1) END AS last_date,
       consecutive 
FROM cte;

    Another solution with window functions and conditional aggregation:

    WITH 
  cte1 AS (SELECT *, SUM(NOT Flag) OVER (PARTITION BY Client, User ORDER BY Date) AS grp FROM tablename),
  cte2 AS (SELECT *, MAX(grp) OVER (PARTITION BY Client, User) AS max_grp FROM cte1)
SELECT Client, User,
       MIN(CASE WHEN Flag THEN Date END) AS first_date,
       MAX(CASE WHEN Flag THEN Date END) AS last_date,
       SUM(Flag) AS consecutive
FROM cte2 
WHERE grp = max_grp
GROUP BY Client, User;

    See the demo.

    Login or Signup to reply.
  2. 0

    Made an attempt to get the result with more simpler queries and here is my approach taking advantage of lastDate and lastFlag column too.

    Run here

    WITH eTT 
AS 
( SELECT Client, User, NULLIF(MAX(Date), 
  (SELECT MAX(Date) FROM tt t2 WHERE t1.Client=t2.Client AND t1.User=t2.User)) as endDate 
  FROM tt t1 WHERE LastFlag=0 OR LastFlag IS NULL GROUP BY Client, User
)
SELECT Client, User, 
(CASE WHEN MAX(endDate) IS NULL THEN NULL ELSE MIN(Date) END) as first_date, 
(CASE WHEN MAX(endDate) IS NULL THEN NULL ELSE MAX(Date) END) as last_date, 
(CASE WHEN MAX(endDate) IS NULL THEN NULL ELSE COUNT(endDate) END) as consecutive 
FROM tt LEFT JOIN eTT USING (Client, User) 
WHERE Date >= endDate OR endDate IS null GROUP BY Client, User;

    EDIT

    The original table doesn’t have LastDate and LastFlag columns and were created using OP’s initial query.

    Since the method used is not apparantly supported but I get an impression that OP somehow manages to do that on their side.

    Hence another cte called tt can be added before eTT containing that query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search