Как проранжировать числа в столбце на группы?

Question

[olraccoon]

Добрый день.

Есть столбец, в котором находятся числа вот в такой структуре:

id | num
_________
1 | 0
2 | 9
3 | 0
4 | 0
5 | 5
6 | 0
7 | 5
8 | 3
9 | 0

Как с помощью MS SQL проранжировать числа на группы, чтобы получилось вот так:

id | num | group
_________________
1 | 0 | 0
2 | 9 | 1
3 | 0 | 0
4 | 0 | 0
5 | 5 | 2
6 | 0 | 0
7 | 5 | 3
8 | 3 | 3
9 | 0 | 0

т.е. если между числами в рамках столбца нет 0, то это одна группа, а если появился 0, то группа закончилась.

Была попытка применить оконные функции, но не к чему привязать партицию. Можно посмотреть LAG и LEAD и сравнить с 0, но не совсем понятно, как вставить счетчик групп?

Comments

[Akina]

А почему для id=2 значение num изменилось с 0 на 9?

[olraccoon]

Это мой косяк, пока писала вопрос... Исправила, спасибо))

Answer 1

[Akina]

Ну если совсем влоб, то

WITH cte AS (
  SELECT id, 
         num, 
         LAG(num) OVER (ORDER BY id) lag_num
  FROM test
)
SELECT id, 
       num,
       CASE WHEN num <> 0
            THEN SUM(CASE WHEN num <> 0 AND lag_num = 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) 
            ELSE 0
            END [group]
FROM cte

Сработает неправильно, если самая первая запись ненулевая... ну это как-нить сам поправь. Мне лень.

Comments

[olraccoon]

СПАСИБО!!!

Answer 2

[Olteya]

Альтернативное решение.
Суть в том, чтобы в Ranked посчитать количество предшествующих нулей. Это число и образует группы. Следующим шагом (D_Rank) присваиваем ранг каждой группе.
Должно работать независимо от наличия или отсутствия лидирующих строк с нулями:

WITH Ranked AS (
    -- Создание ранжированной таблицы с группами
    SELECT
        id,
        num,
        CASE
            WHEN num = 0 THEN 0
            ELSE SUM(CASE WHEN num = 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) + 1 --добавляем + 1, чтобы в случаях если нет первой строки с нулевым значением скрипт корректно работал. Иначе присваивается группа 0 и некорректно обрабатывается следующим блоком.
        END as grp
    FROM
        t -- имя вашей таблицы
)
, D_Rank AS (
    -- Присвоение ранга для каждой группы, исключая группу 0
    SELECT
        id,
        num,
        grp,
        DENSE_RANK() OVER (ORDER BY grp) as gr
    FROM Ranked
    WHERE grp <> 0
)

-- Основной запрос, объединяющий таблицу 't' с ранжированной таблицей 'D_Rank'
SELECT
    t.id,
    t.num,
    ISNULL(D_Rank.gr, 0) as group -- Замена NULL на 0 для группы
FROM
    t -- имя вашей таблицы
LEFT JOIN D_Rank ON
    t.id = D_Rank.id 
ORDER BY
    t.id;