Eu tenho dados organizados em uma estrutura de anel (outampão circular), ou seja, pode ser expresso como sequências que alternam: ...- 1-2-3-4-5-1-2-3 -.... Veja esta figura para ter uma idéia de um anel de 5 partes :

Eu gostaria de criar uma consulta de janela que possa combinar os itens de atraso e lead em uma matriz de três pontos, mas não consigo descobrir. Por exemplo, na parte 1 de um anel de 5 partes, a sequência de atraso / avanço é 5-1-2, ou na parte 4 é 3-4-5.

Aqui está um exemplo de tabela de dois anéis com diferentes números de peças (sempre mais de três por anel):

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));

Aqui está uma consulta quase bem-sucedida:

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)

A única coisa que preciso fazer é substituir oNULL com o ponto final de cada anel, que é o último valor. Agora, junto comlag elead janela, existe umalast_value função o que seria ideal. No entanto, estes não podem ser aninhados:

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)) ...

Atualizar. Graças à sugestão de @ Justin para usarcoalesce para evitar aninhar funções da janela. Além disso, foi apontado por numerosas pessoas que o primeiro / o último valor precisa de uma explicação explícitaorder by na sequência de toques, que passa a serpart para este exemplo. Então, randomize os dados de entrada um pouco:

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();