Extracting rows with a consecutive pattern that exceeds a threshold

user_10576430 0
Viewed 22

I currently have this set of data.

Id pattern
1 1
2 2
3 1
4 2
5 3
6 4
7 1
8 2
9 1
10 2
11 3

I have ids sorted ascending, and a column called pattern. The goal is to retrieve only the rows with a consecutive pattern >=3 down the table.

So the result would be:

Id pattern
3 1
4 2
5 3
6 4
9 1
10 2
11 3

Since the pattern for Ids 3-6 have consecutively reached >=3, we will return these 4 rows. Same goes for Ids 9-11.

Id 1-2 did not reach the threshold of 3. Same for Id 7-8.

Would appreciate any help!

correlated-subquery window-functions mysql
1 Answers

Use SUM() window function to create the groups of the sequences and with MAX() window function check the max pattern in each group:

SELECT Id, pattern
FROM (
  SELECT *, MAX(pattern) OVER (PARTITION BY grp) max_pattern
  FROM (
    SELECT *, SUM(pattern = 1) OVER (ORDER BY Id) grp
    FROM tablename
  ) t
) t
WHERE max_pattern >= 3;

Or, with a correlated subquery, for versions of MySql that do not support window functions:

SELECT t1.*
FROM tablename t1
WHERE (SELECT t2.pattern FROM tablename t2 WHERE t2.Id = t1.Id + (3 - t1.pattern)) = 3;

See the demo.

user_104988280
Related