SQL Server Output the rows which are occurring more than two times consecutively

gijlo24d 于 11个月前发布在其他

关注(0)|答案(3)|浏览(106)

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;

sql-server

来源：https://stackoverflow.com/questions/77157916/output-the-rows-which-are-occurring-more-than-two-times-consecutively

3条答案

按热度按时间

a0x5cqrl1#

You can use the below query to output only the values from the num column that occur more than two times consecutively.

WITH CTE AS (
    SELECT num, ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) grp
    FROM Logs
)
SELECT  num AS ConsecutiveNums
FROM CTE
GROUP BY num, grp
HAVING COUNT(*) > 2;

This query uses the ROW_NUMBER() function to generate a unique number for each row in the table. Again, It uses the ROW_NUMBER() function to generate unique number for each id within the each num 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 the num and grp columns and uses the HAVING clause to select any groups that have more than two rows (count>2).

db<>fiddle

赞(0）回复(0）举报 12个月前

uinbv5nw2#

This is a classic Gaps-and-Islands problem

Example

Select num
 From  (
        Select *
              ,Grp = row_number() over (order by id)
                    -row_number() over (partition by num order by id)
         From  YourTable
       ) A
 Group by Grp,num
 Having count(*)>2

Results

num
1

赞(0）回复(0）举报 12个月前

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.

WITH sampleData AS  (
    SELECT id = 1, num = 1
    UNION ALL
    SELECT id = 2, num = 1
    UNION ALL
    SELECT id = 3, num = 1
    UNION ALL
    SELECT id = 4, num = 2
    UNION ALL
    SELECT id = 5, num = 1
    UNION ALL
    SELECT id = 6, num = 1
    UNION ALL
    SELECT id = 7, num = 2
)
SELECT DISTINCT cur.num
FROM sampleData cur
JOIN sampleData pre
    ON pre.Id = cur.Id-1 AND pre.num = cur.num
JOIN sampleData post
    ON post.Id = cur.Id+1 AND post.num = cur.num

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,

DECLARE @n INT = 2;
WITH sampleData AS  (
    SELECT id = 1, num = 1
    -- ... other sample data
)
SELECT DISTINCT cur.num
FROM sampleData cur
WHERE ( 
        SELECT COUNT(*)
        FROM sampleData other
        WHERE other.id BETWEEN cur.id+1 AND cur.id+@n
        AND other.num = cur.num
    ) = @n

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Output the rows which are occurring more than two times consecutively

3条答案

solution using join clause

generic solution

相关问题

热门标签

最新问答