Início / dba / Perguntas / 316783
Accepted
AndreKR
Asked: 2022-09-12 16:43:40 +0800 CST

Gerar números sequenciais em uma junção

  • 772

Para cada linha do lado direito em um JOIN, gostaria de criar um número sequencial, começando em 1 (ou 0) para cada linha do lado esquerdo.

Exemplo:

create table persons (person_id int, person_name text);
create table places (place_id int, person_id int, place_name text);
insert into persons values (10, 'Aulus Agerius'), (20, 'Numerius Negidius');
insert into places values (10, 10, 'Anytown'), (20, 10, 'Timbuktu'), (30, 20, 'Podunk');

select person_name, place_name
from persons join places using (person_id)
order by person_id, place_id;

violino

Resultado desejado:

nome_pessoa coloque o nome place_seq
Aulo Agerio Qualquer cidade 1
Aulo Agerio Timbuctu 2
Numerius Negidius Podunk 1

Então, por exemplo, um 2 in place_seqsignificaria "este é o 2º lugar encontrado para esta pessoa".

Como criar a place_seqcoluna?

postgresql

1 respostas

  1. Best Answer

    Você precisa da função de janela ROW_NUMBER()( manual ) da seguinte forma (o código abaixo é mostrado no violino aqui - baseado em seu próprio violino +1 para isso!):

    SELECT 
  person_id,
  place_id,
  person_name, 
  place_name, 
  ROW_NUMBER() 
    OVER (PARTITION BY person_id ORDER BY place_id) AS rn
FROM persons 
  JOIN places using (person_id)
ORDER BY person_id, place_id;

    Isso é baseado na suposição de que person_id& place_idsão pelo menos UNIQUEou (melhor) os PRIMARY KEYs de suas respectivas tabelas. É concebível que duas pessoas possam ter o mesmo nome - eu conheci dois conjuntos de três pessoas (independentes - não a mesma família) com o mesmo nome. Quanto aos lugares, as duplicatas são ainda mais comuns.

    Resultado:

    person_name       place_name    rn
Aulus Agerius        Anytown     1
Aulus Agerius       Timbuktu     2
Numerius Negidius     Podunk     1

    Para garantir a obtenção de resultados consistentes em diferentes execuções, ORDER BYé necessária uma cláusula determinística na função de janela. Em geral, uma instrução SQL deve conter um ORDER BY, assim como as funções de janela, se desejarmos resultados em ordem determinística.

    Para funções de janela, veja também aqui e aqui . Essas funções são muito poderosas e compensarão qualquer esforço gasto em aprendê-las muitas vezes. Existem artigos em toda a web sobre eles - eu recomendo que você dê uma olhada e pratique até ter (algum grau de) domínio deles.

    Como primeira escala, recomendo vivamente a apresentação de Bruce Momjian no YouTube aqui (slides aqui ). Bruce Momjian é VP e "Postgres Evangelist" para EnterpriseDB - a principal empresa de PostgreSQL do mundo. Ele também é um excelente palestrante e explica muito bem a funcionalidade do PostgreSQL!

    • 5

relate perguntas