Row_number(), но увеличивает значение только после определенного значения в столбце

Запрос: SELECT (row_number() OVER ()) as grp, * from tbl

Обновлено: приведенные ниже строки возвращаются функцией кратчайшего пути pgrouting, и у нее есть последовательность.

seq grp   id
1    1      8
2    2      3
3    3      2
4    4      null
5    5      324
6    6      82
7    7      89
8    8      null
9    9      1
10   10     2
11   11     90
12   12     null

Как мне сделать так, чтобы столбец grp увеличивался только после значения null на id, а также сохранял тот же порядок строк

seq grp   id 
1    1      8
2    1      3
3    1      2
4    1      null
5    2      324
6    2      82
7    2      89
8    2      null
9    3      1
10   3      2
11   3      90
12   3      null

"а также сохранить тот же порядок строк" - порядка нет. Вы не указали предложение ORDER BY, система может возвращать строки в любом порядке, который ей наиболее удобен в данный момент. И нет очевидного способа добавить ORDER BY, учитывая только образцы данных, которые вы в настоящее время предоставили.

Damien_The_Unbeliever 04.04.2022 09:27

Вам нужно сначала заказать, чтобы сохранить этот порядок...

Andronicus 04.04.2022 09:28

SQL не знает, какой порядок используется, см.: Каков порядок записей по умолчанию (что относится к MySQL, но также применимо к PostgreSQL)

Luuk 04.04.2022 09:29

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

xyzit 04.04.2022 09:41
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
4
37
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

демо: БД <> рабочий пример

Возможным подходом является использование кумулятивной SUM агрегации:

SELECT
    SUM(                                       -- 2
       CASE WHEN id IS NULL THEN 1 ELSE 0 END  -- 1
    ) OVER (ORDER BY seq) as grp,
    id
FROM mytable
  1. Если текущее (упорядоченное!) значение равно NULL, то сделайте его 1, иначе 0. Теперь у вас есть куча нулей, разделенных 1 в каждой NULL записи. Если суммировать эти значения кумулятивно, то при каждой записи NULL сумма будет увеличиваться.
  2. Выполнение кумулятивного SUM() с помощью оконные функции

Это дает:

0   8
0   3
0   2
1   null
1   324
1   82
1   89
2   null
2   1
2   2
2   90
3   null

Как видите, группы начинаются с записей NULL, но вы ожидаете, что они закончатся.

Этого можно добиться, добавив еще одну оконную функцию: LAG(), которая перемещает записи в следующую строку:

SELECT
    SUM(
        CASE WHEN next_id IS NULL THEN 1 ELSE 0 END
    ) OVER (ORDER BY seq) as grp,
    id
FROM (
    SELECT
        LAG(id) OVER (ORDER BY seq) as next_id,
        seq,
        id
    FROM mytable
) s

Результат ожидаемый:

1   8
1   3
1   2
1   null
2   324
2   82
2   89
2   null
3   1
3   2
3   90
3   null
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING было бы лучше, чем использовать отставание. Затем вам также нужно будет добавить 1, если вы хотите, чтобы идентификатор начинался с 1. dbfiddle.uk/…
MatBailie 04.04.2022 10:05

Отличная идея! Я забыл эту опцию!

S-Man 04.04.2022 10:15

Другие вопросы по теме