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

Mysql – How to select the start date and end date based on consecutive occurrence in rows

I am using MySQL and facing a question. I want to count the consecutive occurrence of type based on each ID and select out only the IDs with more than 3 times of error-type.

Additionaly, based on the above result, I want to include the start timestamp and end timestamp in the result.

I’ve checked other posts but had no clues how to solve it.

Appreciate for any suggestions. Thank you in advance and welcome any advice.

IDs with action type

The result I’d like to show is as the screenshot.

ID A: has 4 times consecutive errors, the start_at is the timestamp at the beginning of sequence
ID C: has 3 times consecutive errors, the start_at is the timestamp at the beginning of sequence

result

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you, p3consulting. It works and I will dive into the gap and island questions more. Here is what I've edited to get the result with dummy data.

    WITH DATA AS (
SELECT 'A' AS id, cast('2023-04-01 5:00:00' as datetime) AS dt, 'Success' AS type UNION ALL
SELECT 'A', cast('2023-04-01 5:30:00' as datetime), 'Error' UNION ALL
SELECT 'A', cast('2023-04-01 5:33:00' as datetime), 'Error' UNION ALL
SELECT 'A', cast('2023-04-01 5:34:00' as datetime), 'Error' UNION ALL
SELECT 'A', cast('2023-04-01 5:40:00' as datetime), 'Error' UNION ALL
SELECT 'B', cast('2023-04-01 4:00:00' as datetime), 'Success' UNION ALL
SELECT 'B', cast('2023-04-01 4:20:00' as datetime), 'Error' UNION ALL
SELECT 'B', cast('2023-04-01 4:45:00' as datetime), 'Error' UNION ALL
SELECT 'B', cast('2023-04-01 4:50:00' as datetime), 'Error' ),

base as (
  SELECT 
    d.*,
    row_number() OVER(PARTITION BY id ORDER BY dt) as rn1,
    row_number() OVER(PARTITION BY id, type ORDER BY dt) as rn2,
    row_number() OVER(PARTITION BY id ORDER BY dt) - row_number() OVER(PARTITION BY id, type ORDER BY dt) AS grp
FROM DATA d)

SELECT 
base.id,
base.type,
MIN(dt) AS start_dt, 
MAX(dt) AS end_dt,
COUNT(base.id) AS activities
FROM base
WHERE base.type = 'Error'
GROUP BY base.id, base.grp, base.type
HAVING(COUNT(base.id) >= 3 )
ORDER BY base.id;

  2. 0

    Try

    SELECT d.id, MIN(dt) AS start_dt, MAX(dt) AS end_dt
FROM 
(
    SELECT 
        d.*, 
        row_number() OVER(PARTITION BY id ORDER BY dt) - row_number() OVER(PARTITION BY id, type ORDER BY dt) AS grp
    FROM DATA d
) d
WHERE d.type = 'Error'
GROUP BY d.id, d.grp HAVING( COUNT(d.id) >= 3 )
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search