SQL Grouping Continuous Values in Time Series Data

In this answer, the table name is X.

Grouping for I, number of groups contiguous with V=1

How to group consecutive values and count the number of groups, you can compare T with T+1 to detect the beginning of a group if it does not exist.

SELECTT
FROM X
WHERE NOT IN (SELECT + 1 FROM X)

Based on this, it can be assembled by grouping and conditioning.In the end, this is what it looks like.

SELECTI, COUNT(*)
FROM X AS X 1
WHERE V = 1 AND NOT IN (SELECT + 1 FROM X AS X 2 WHERE V = 1 AND X1.I = X2.I)
GROUP BY I

Grouping of IDs of groups where V for each I is continuous with 1, first T of groups where V=1 for each I is continuous, last T

You will need ROW_NUMBER() and so on as you have answered yourself.

I found a useful question in English and solved it myself.

Grouping continuous table data-Stack Overflow

 WITH step 1 AS (
SELECT
I,T,V,
ROW_NUMBER() OVER(ORDER BY I, T) ASA,
ROW_NUMBER() OVER(PARTMENT BY I, VORDER BY T) AS B
FROM table
ORDER BY
I,T),
step2 AS(
SELECT
I,T,V,A,B,
A-BAS RN
FROM STEP 1
ORDER BY I, T),
step3 AS(
SELECT
I,
CONCAT(I,MIN(T))ASI_T1,
MIN(T)T1,
MAX(T)T2,
V
from
step2
GROUP BY I, V, RN
ORDER BY T1)
--①
-- SELECTI, V, count(V) count_V FROM step 3 Group by I, V
--②
SELECT* FROM step3 WHERE V=1