Запрос: 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
Вам нужно сначала заказать, чтобы сохранить этот порядок...
SQL не знает, какой порядок используется, см.: Каков порядок записей по умолчанию (что относится к MySQL, но также применимо к PostgreSQL)
Привет всем, я изменил вопрос, и вы, ребята, правы, у него есть столбец последовательности, по которому можно упорядочить
Возможным подходом является использование кумулятивной SUM
агрегации:
SELECT
SUM( -- 2
CASE WHEN id IS NULL THEN 1 ELSE 0 END -- 1
) OVER (ORDER BY seq) as grp,
id
FROM mytable
NULL
, то сделайте его 1
, иначе 0
. Теперь у вас есть куча нулей, разделенных 1 в каждой NULL
записи. Если суммировать эти значения кумулятивно, то при каждой записи NULL
сумма будет увеличиваться.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/…Отличная идея! Я забыл эту опцию!
"а также сохранить тот же порядок строк" - порядка нет. Вы не указали предложение
ORDER BY
, система может возвращать строки в любом порядке, который ей наиболее удобен в данный момент. И нет очевидного способа добавитьORDER BY
, учитывая только образцы данных, которые вы в настоящее время предоставили.