Пробелы и острова запрашивают / сбрасывают количество строк на основе последовательности столбцов

Я знаю, что есть много примеров этого, но до сих пор не могу найти точное решение.

Я хочу сбросить количество строк на основе последовательностей 1 и 0.

DECLARE @TestTable TABLE (category INT, ts INT,window int)
INSERT INTO @TestTable (category,ts,window)
VALUES  (1,1,1),(1,2,1),(1,3,0),(1,4,0),(1,5,1),(1,6,1),(1,7,1),(2,1,0),(2,2,1),(2,3,1),(2,4,1),(2,5,0),(2,6,0),(2,7,1),(2,8,1),(2,9,1),(2,10,1),(2,11,1)

введите здесь описание изображения

В приведенной выше таблице мне нужен столбец с номером строки, который увеличивается на 1, разделенный по категориям, но счетчик должен сбрасываться каждый раз при изменении окна.

Лучшее, что у меня есть до сих пор, это:

SELECT
    x.category,
    ts,
    window, 
    is_group, 
    SUM( is_group ) OVER (PARTITION BY x.category ORDER BY ts ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING ) * is_group
FROM
    (
    SELECT 
        *, 
        CASE WHEN LAG(window) OVER(PARTITION BY category ORDER BY ts  ) = window THEN 1 ELSE 0 END is_group
    FROM @TestTable 
    ) x
ORDER BY x.category,ts

Это почти работает, но для последнего отрезка он не увеличивает номер строки больше, чем на 2:

введите здесь описание изображения


sql sql-server window-functions gaps-and-islands
person Paul Grimshaw    schedule 20.04.2016    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Что-то вроде этого ?

DECLARE @TestTable TABLE (category INT, ts INT,window int)
INSERT INTO @TestTable (category,ts,window)
VALUES  (1,1,1),(1,2,1),(1,3,0),(1,4,0),(1,5,1),(1,6,1),(1,7,1),(2,1,0),(2,2,1),(2,3,1),(2,4,1),(2,5,0),(2,6,0),(2,7,1),(2,8,1),(2,9,1),(2,10,1),(2,11,1);


with ctex
as
(
SELECT
x.category,
ts,
window, 
is_group,
sum(is_group) over (partition by category order by ts) as groupstot


FROM
(
SELECT 
    *, 
    CASE WHEN LAG(window) OVER(PARTITION BY category ORDER BY ts  ) = window THEN 0 ELSE 1 END  is_group
FROM @TestTable 
) x

)
Select * , row_number() over(partition by category,groupstot order by ts) from ctex
ORDER BY category,ts
person DaveBally    schedule 20.04.2016
comment
Отлично, это сделало это. Спасибо - person Paul Grimshaw; 20.04.2016