Как использовать кольцевую структуру данных в оконных функциях

Question

У меня есть данные, которые расположены в кольцевой структуре (или кольцевом буфере), то есть они могут быть выражены в виде последовательностей, которые циклически изменяются: ...-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();

Answer 1

• использование COALESCE как @Justin при условии.

• С first_value() / last_value() вам нужно добавить ORDER BY предложение к определению окна или порядок не определен. Вам просто повезло в этом примере, потому что строки оказались в порядке сразу после создания фиктивной таблицы.
  Как только вы добавите ORDER BY оконная рама по умолчанию заканчивается в текущей строке, и вам нужно last_value() вызовите - или измените порядок сортировки в рамке окна, как показано в моем первом примере.

• При многократном использовании определения окна явное WINDOW Предложение значительно упрощает синтаксис:

SELECT ring, part, ARRAY[
          coalesce(
             lag(part) OVER w
            ,first_value(part) OVER (PARTITION BY ring ORDER BY part DESC))
         ,part
         ,coalesce(
             lead(part) OVER w
            ,first_value(part) OVER w)
         ] AS neighbours
FROM   rp
WINDOW w AS (PARTITION BY ring ORDER BY part);

А еще лучше, используйте одно и то же определение окна, чтобы Postgres мог рассчитать все значения за одно сканирование. Чтобы это работало, нам нужно определить пользовательскую рамку окна:

SELECT ring, part, ARRAY[
          coalesce(
             lag(part) OVER w
            ,last_value(part) OVER w)
         ,part
         ,coalesce(
             lead(part) OVER w
            ,first_value(part) OVER w)
         ] AS neighbours
FROM   rp
WINDOW w AS (PARTITION BY ring
             ORDER BY part
             RANGE BETWEEN UNBOUNDED PRECEDING
                       AND UNBOUNDED FOLLOWING)
ORDER  BY 1,2;

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

SELECT ring, part, ARRAY[
          coalesce(
             lag(part) OVER w
            ,last_value(part) OVER (w RANGE BETWEEN CURRENT ROW
                                                AND UNBOUNDED FOLLOWING))
         ,part
         ,coalesce(
             lead(part) OVER w
            ,first_value(part) OVER w)
         ] AS neighbours
FROM   rp
WINDOW w AS (PARTITION BY ring ORDER BY part)
ORDER  BY 1,2;

Может быть быстрее для колец со многими частями. Вам придется проверить.

SQL Fiddle демонстрирует все три с улучшенным контрольным примером. Рассмотрим планы запросов.

Подробнее об определениях оконных рам:

• В руководстве.
• Оконная функция PostgreSQL: разделение по сравнению
• Запрос PostgreSQL с максимальной и минимальной датой плюс связанный идентификатор на строку

Answer 2

Запрос:

SQLFIDDLEExample

SELECT ring, part, array[
    coalesce(lag(part, 1, NULL) over (partition by ring), 
             max(part) over (partition by ring)),
    part,
    lead(part, 1, 1) over (partition by ring)
    ] AS neighbours
FROM rp;

Результат:

| RING | PART | NEIGHBOURS |
|------|------|------------|
|    1 |    1 |      5,1,2 |
|    1 |    2 |      1,2,3 |
|    1 |    3 |      2,3,4 |
|    1 |    4 |      3,4,5 |
|    1 |    5 |      4,5,1 |
|    2 |    1 |      7,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 |