0

find all numbers that appear at least three times consecutively: I am looking for a query to achieve this result using a window function rather than making a self join

Logs table:

+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result table:

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

here is my query:

select distinct l1.num as ConsecutiveNums from logs l1
inner join logs l2 on l1.id = l2.id - 1
inner join logs l3 on l1.id = l3.id + 1
where l1.num = l2.num and l2.num = l3.num

but I am looking to use a window function instead

sticky bit
  • 36,626
  • 12
  • 31
  • 42

3 Answers3

2

You can use lag() to get the value in the "previous" row and the row before that. If they're all equal to the value in the current row, you got a match. Consecutive equal numbers that are more than three would be matched more than one time like this. And a repetition of such a consecutive sequence would do so too. You can use DISTINCT to select them only once.

SELECT DISTINCT
       num
       FROM (SELECT num,
                    lag(num, 1) OVER (ORDER BY id) = num
                    AND lag(num, 2) OVER (ORDER BY id) = num c3
                    FROM logs) x
       WHERE c3;

db<>fiddle

sticky bit
  • 36,626
  • 12
  • 31
  • 42
0

You can also use the ROW_NUMBER() this way to group the consecutive numbers.

SELECT num as ConsecutiveNums 
FROM (
    SELECT *, 
           (row_number() OVER (ORDER BY id) - row_number() OVER (PARTITION BY num ORDER BY id)
            ) AS grp
    FROM logs
    ) SQ
GROUP BY num,grp
HAVING count(*) >= 3
Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
-1
SELECT num as ConsecutiveNums 
FROM (
    SELECT *, 
           row_number() OVER (ORDER BY id) - row_number() OVER (PARTITION BY num ORDER BY id as rw
    FROM table
    ) S
GROUP BY num,rw
HAVING count(*) >= 3
cigien
  • 57,834
  • 11
  • 73
  • 112
Likhith Gowda
  • 1