Как использовать кольцевую структуру данных в оконных функциях
У меня есть данные, которые расположены в структуре кольца (иликольцевой буфер), то есть это можно выразить в виде последовательностей, которые циклически повторяются: ...- 1-2-3-4-5-1-2-3 -.... Смотрите эту картинку, чтобы получить представление о кольце из 5 частей :
Я хотел бы создать оконный запрос, который может объединить элементы отставания и отведения в массив из трех точек, но я не могу понять это. Например, в части 1 кольца из 5 частей последовательность отставания / отведения составляет 5-1-2, или в части 4 - 3-4-5.
Вот пример таблицы из двух колец с разным количеством частей (всегда больше, чем три на кольцо):
create table rp (ring int, part int);
insert into rp(ring, part) values(1, generate_series(1, 5));
insert into rp(ring, part) values(2, generate_series(1, 7));
Вот почти успешный запрос:
SELECT ring, part, array[
lag(part, 1, NULL) over (partition by ring),
part,
lead(part, 1, 1) over (partition by ring)
] AS neighbours
FROM rp;
ring | part | neighbours
------+------+------------
1 | 1 | {NULL,1,2}
1 | 2 | {1,2,3}
1 | 3 | {2,3,4}
1 | 4 | {3,4,5}
1 | 5 | {4,5,1}
2 | 1 | {NULL,1,2}
2 | 2 | {1,2,3}
2 | 3 | {2,3,4}
2 | 4 | {3,4,5}
2 | 5 | {4,5,6}
2 | 6 | {5,6,7}
2 | 7 | {6,7,1}
(12 rows)
Единственное, что мне нужно сделать, это заменитьNULL
с конечной точкой каждого кольца, которое является последним значением. Теперь вместе сlag
а такжеlead
оконные функции, естьlast_value
функция который был бы идеальным. Однако они не могут быть вложенными:
SELECT ring, part, array[
lag(part, 1, last_value(part) over (partition by ring)) over (partition by ring),
part,
lead(part, 1, 1) over (partition by ring)
] AS neighbours
FROM rp;
ERROR: window function calls cannot be nested
LINE 2: lag(part, 1, last_value(part) over (partition by ring)) ...
Обновить, Благодаря предложению @ Джастина использоватьcoalesce
чтобы избежать вложенности оконных функций. Кроме того, многие отмечают, что первым / последним значениям требуется явноеorder by
на кольцевой последовательности, которая оказываетсяpart
для этого примера. Итак, немного рандомизирую входные данные:
create table rp (ring int, part int);
insert into rp(ring, part) select 1, generate_series(1, 5) order by random();
insert into rp(ring, part) select 2, generate_series(1, 7) order by random();