[SQL] 어떻게 열 값을 기준으로 연속 행을 찾는 방법은?

어떻게 열 값을 기준으로 연속 행을 찾는 방법은?

나는 일부 데이터를 가지고있다. 나는 그들 데이터 컬럼의 값에 따라 그룹으로합니다. (10)보다 큰 데이터가 3 개 이상 연속 행이 경우, 해당 행은 내가 원하는 무엇인가.

이 데이터 그래서 :

use tempdb;
go
set nocount on;

if object_id('t', 'U') is not null
drop table t;
go

create table t
(
    id int primary key identity,
    [when] datetime,
    data int
)
go

insert into t([when], data) values ('20130801', 1);
insert into t([when], data) values ('20130802', 121);
insert into t([when], data) values ('20130803', 132);
insert into t([when], data) values ('20130804', 15);
insert into t([when], data) values ('20130805', 9);
insert into t([when], data) values ('20130806', 1435);
insert into t([when], data) values ('20130807', 143);
insert into t([when], data) values ('20130808', 18);
insert into t([when], data) values ('20130809', 19);
insert into t([when], data) values ('20130810', 1);
insert into t([when], data) values ('20130811', 1234);
insert into t([when], data) values ('20130812', 124);
insert into t([when], data) values ('20130813', 6);

select * from t;

내가 원하는 것입니다 :

id          when                    data       
----------- ----------------------- -----------
2           2013-08-02 00:00:00.000 121        
3           2013-08-03 00:00:00.000 132        
4           2013-08-04 00:00:00.000 15         
6           2013-08-06 00:00:00.000 1435       
7           2013-08-07 00:00:00.000 143        
8           2013-08-08 00:00:00.000 18         
9           2013-08-09 00:00:00.000 19    

그렇게하는 방법?

해결법

1. ==============================

   1.이 시도

   이 시도

   WITH cte
   AS
   (
       SELECT *,COUNT(1) OVER(PARTITION BY cnt) pt  FROM
       (
           SELECT tt.*
              ,(SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS cnt
           FROM  t tt
           WHERE data > 10
       ) t1
   )
   
   SELECT id, [when], data FROM cte WHERE pt >= 3
   

   SQL 뿐인 DEMO

   산출

   id  when                    data
   2   2013-08-02 00:00:00.000 121
   3   2013-08-03 00:00:00.000 132
   4   2013-08-04 00:00:00.000 15
   6   2013-08-06 00:00:00.000 1435
   7   2013-08-07 00:00:00.000 143
   8   2013-08-08 00:00:00.000 18
   9   2013-08-09 00:00:00.000 19
   

   편집하다

   먼저 내부 쿼리는 레코드의 수를 계산없는 경우 데이터 <= 10

   SELECT tt.*
        ,(SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS cnt
   FROM  t tt
   

   산출

   id  when                    data   cnt
   1   2013-08-01 00:00:00.000 1       1
   2   2013-08-02 00:00:00.000 121     1
   3   2013-08-03 00:00:00.000 132     1
   4   2013-08-04 00:00:00.000 15      1
   5   2013-08-05 00:00:00.000 9       2
   6   2013-08-06 00:00:00.000 1435    2
   7   2013-08-07 00:00:00.000 143     2
   8   2013-08-08 00:00:00.000 18      2
   9   2013-08-09 00:00:00.000 19      2
   10  2013-08-10 00:00:00.000 1       3
   11  2013-08-11 00:00:00.000 1234    3
   12  2013-08-12 00:00:00.000 124     3
   13  2013-08-13 00:00:00.000 6       4
   

   그런 다음 데이터> (10)로 레코드를 필터링

   WHERE data > 10
   

   이제 우리는 탄소 나노 튜브 열을 분할하여 기록을 계산

   SELECT *,COUNT(1) OVER(PARTITION BY cnt) pt  FROM
   (
       SELECT tt.*
           ,(SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS cnt
       FROM  t tt
       WHERE data > 10
   ) t1
   

   산출

   id  when    data                   cnt  pt
   2   2013-08-02 00:00:00.000 121     1   3
   3   2013-08-03 00:00:00.000 132     1   3
   4   2013-08-04 00:00:00.000 15      1   3
   6   2013-08-06 00:00:00.000 1435    2   4
   7   2013-08-07 00:00:00.000 143     2   4
   8   2013-08-08 00:00:00.000 18      2   4
   9   2013-08-09 00:00:00.000 19      2   4
   11  2013-08-11 00:00:00.000 1234    3   2
   12  2013-08-12 00:00:00.000 124     3   2
   

   위의 쿼리는 단지 임시 테이블처럼 CTE에 넣어

   이제 연속 카운트> = 3가있는 레코드를 선택

   SELECT id, [when], data FROM cte WHERE pt >= 3
   

   또 다른 해결책

   ;WITH partitioned AS (
     SELECT *, id - ROW_NUMBER() OVER (ORDER BY id) AS grp
     FROM t
     WHERE data > 10
   ),
   counted AS (
     SELECT *, COUNT(*) OVER (PARTITION BY grp) AS cnt
     FROM partitioned
   )
   
   SELECT id, [when], data
   FROM counted
   WHERE cnt >= 3
   

   참조 URL

   SQL 뿐인 DEMO

2. ==============================

   2.첫째, 우리는 10 이하의 값이있는 행 할인 :

   첫째, 우리는 10 이하의 값이있는 행 할인 :

   WITH t10 AS (SELECT * FROM t WHERE data > 10),
   

   다음으로, 그의 전임자도 10을 초과 행을 얻을 :

   okleft AS (SELECT t10.*, pred.id AS predid FROM
      t10
      INNER JOIN t pred ON 
           pred.[when] < t10.[when]
           AND pred.[when] >= ALL (SELECT [when] FROM t t2 WHERE t2.[when] < t10.[when])
      WHERE pred.data > 10
   ),
   

   또한 그 즉시 후임 개 이상의 10 행을 얻을 :

   okright as (SELECT t10.*, succ.id AS succid FROM
      t10
      INNER JOIN t succ ON
           succ.[when] > t10.[when] 
           AND succ.[when] <= ALL (SELECT [when] FROM t t2 WHERE t2.[when] > t10.[when])
      WHERE succ.data > 10
   ),
   

   마지막으로,이 중, (3)의 시퀀스를 시작 하나, 또는 단 하나의 중간에있는 임의의 행을 선택

   그 유효한 우측 또한 유효한 우측을 가진 행은 적어도 3의 시퀀스를 시작한다 :

   starts3 AS (SELECT id, [when], data FROM okright r1 WHERE EXISTS(
   SELECT NULL FROM okright r2 WHERE r2.id = r1.succid)),
   

   그의 선행 및 후속 모두 유효 행은 적어도 3의 중간이다 :

   mid3 AS (SELECT id, [when], data FROM okleft l WHERE EXISTS(
   SELECT NULL FROM okright r WHERE r.id = l.id)),
   

   그 유효한 좌측 또한 유효한 좌측을 가진 행은 적어도 3의 시퀀스를 종료한다 :

   ends3 AS (SELECT id, [when], data FROM okleft l1 WHERE EXISTS(
   SELECT NULL FROM okleft l2 WHERE l2.id = l1.predid))
   

   UNION과 함께 제거 중복에 그들 모두를 가입 :

   SELECT * FROM starts3
   UNION SELECT * FROM mid3
   UNION SELECT * FROM ends3
   

   SQL 피들러 : http://sqlfiddle.com/#!3/12f3a/9

   훨씬 더 우아하고 나보다 BVR의 대답과 같은 I : 편집.

from https://stackoverflow.com/questions/18436749/how-to-find-consecutive-rows-based-on-the-value-of-a-column by cc-by-sa and MIT license