I have the below dataset of logs table where "ID" column is identity and "num" column represents a simple number and can have duplicates. I want to output only the values from the "num" column that occur more than two times consecutively. For example: value 1 occurs three times consecutively so the output has 1 as a result of first 3 rows. But values 1 and 2 neither repeated more than 2 times consecutively so they are not in the output:
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Output:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
I have tried the below query, but it returns the number of 1 as four times and not able to detect "consecutive" nature as required for the output:
with cte as(
select logno,grp = sum(case when logno = repeat_val then 0 else 1 end) over (order by sno) from
(
select sno,logno,
lag(logno) over(order by logno) as repeat_val
from
logprac l) s)
select logno as ConsecutiveNums , count(*)
from cte
group by grp,logno
having count(*)>2
order by grp;
3条答案
按热度按时间a0x5cqrl1#
You can use the below query to output only the values from the
num
column that occur more than two times consecutively.This query uses the
ROW_NUMBER()
function to generate a unique number for each row in the table. Again, It uses theROW_NUMBER()
function to generate unique number for each id within the eachnum
group. The difference between the row numbers is used to create a group number for each set of consecutive numbers. Finally, the query groups the rows by thenum
andgrp
columns and uses theHAVING
clause to select any groups that have more than two rows (count>2).db<>fiddle
uinbv5nw2#
This is a classic Gaps-and-Islands problem
Example
Results
irtuqstp3#
solution using join clause
If the requirements are exactly for 3 consecutive rows, then this solution could work, but is not intended for a generic number of rows.
The join for the previous and next value could be used to check if there is at least one row for each number that satisfies the requirements.
generic solution
This solution on the other side is generic about the repetition count. The WHERE conditions check that for a given row the following n rows contains the same value,