
[code]

    I,T,V
    A,1,0
    A,2,0
    A,3,1
    A,4,1
    A,5,1
    A,6,0
    A,7,1
    A,8,1
    A,9,0
    A,10,0
    
[/code]

こんなデータがあった時以下のようなデータをSQLで生成するにはどうすればいいのでしょうか？

①  
Iのグルーピング,V=1が連続するグループの数  
A,2

②  
I毎のVが1が連続するグループのIDのグルーピング,I毎のV=1が連続するグループの最初のT,I毎のV=1が連続するグループの最後のT  
A3,3,5  
A7,7,8


CC BY-SA 4.0
2020-04-06T23:18:40.580
65415
2022-01-22T08:06:05.720
2020-04-07T01:48:46.403
20706
20706
question
sql
時系列データで連続する値をグルーピングするSQL


英語版で参考になる質問を見つけて自己解決しました。

[Grouping contiguous table data - Stack
Overflow](https://stackoverflow.com/q/48411640)

[code]

    WITH step1 AS(
    SELECT
    I,T,V,
    ROW_NUMBER() OVER (ORDER BY I,T) AS A,
    ROW_NUMBER() OVER (PARTITION BY I,V ORDER BY T) AS B
    FROM table
    ORDER BY
    I,T ),
    step2 AS(
    SELECT
    I,T,V,A,B,
    A - B AS RN
    FROM STEP1
    ORDER BY I,T ),
    step3 AS(
    SELECT
    I,
    CONCAT(I,MIN(T)) AS I_T1,
    MIN(T) T1,
    MAX(T) T2,
    V
    FROM
    step2
    GROUP BY I,V,RN
    ORDER BY T1 )
    --①
    --SELECT I,V,count(V) count_V FROM step3 Group by I,V
    --②
    SELECT * FROM step3 WHERE V =1
    
[/code]


CC BY-SA 4.0
2020-04-07T01:44:15.337
65421
2020-04-07T05:06:43.503
2020-04-07T05:06:43.503
20706
20706
65415
answer


この回答ではテーブル名を`X`とします。

> Iのグルーピング,V=1が連続するグループの数

値が連続するものをグループ化し、そのグループ数の数える方法ですが、`T`と`T+1`を比較し存在しない場合をグループの先頭を検出できます。

[code]

    SELECT T
    FROM X
    WHERE T NOT IN (SELECT T+1 FROM X)
    
[/code]

これをベースに、グループ化や条件を付けていけば組み立てることができます。最終的にはこんな感じでしょうか。

[code]

    SELECT I, COUNT(*)
    FROM X AS X1
    WHERE V=1 AND T NOT IN (SELECT T+1 FROM X AS X2 WHERE V=1 AND X1.I=X2.I)
    GROUP BY I
    
[/code]

> I毎のVが1が連続するグループのIDのグルーピング,I毎のV=1が連続するグループの最初のT,I毎のV=1が連続するグループの最後のT

こちらは自己回答されているように`ROW_NUMBER()`等が必要になってきます。


CC BY-SA 4.0
2020-04-07T03:18:10.087
65427
2020-04-07T03:18:10.087
4236
65415
answer