Início / dba / Perguntas / 333554
Accepted
Leonard
Asked: 2023-11-29 18:13:56 +0800 CST

Compreendendo o impacto de ORDER BY em funções de janela em outras partições no PostgreSQL

  • 772

Eu tenho uma tabela simples odd_numscom colunas nume odd:

create table odd_nums
(
    num numeric,
    odd numeric
);

INSERT INTO odd_nums VALUES (0, 0);
INSERT INTO odd_nums VALUES (1, 1);
INSERT INTO odd_nums VALUES (2, 0);
INSERT INTO odd_nums VALUES (3, 1);

Estou brincando com funções de janela. Esta consulta produz os resultados esperados:

SELECT num,
       FIRST_VALUE(num) OVER (PARTITION BY odd) a,
       FIRST_VALUE(num) OVER (PARTITION BY odd) b
FROM odd_nums;
número a b
0 0 0
2 0 0
1 1 1
3 1 1

Quando ordeno a partição em coluna, bos resultados mudam assim:

SELECT NUM,
       FIRST_VALUE(num) OVER (PARTITION BY odd)                   a,
       FIRST_VALUE(num) OVER (PARTITION BY odd ORDER BY num DESC) b
FROM odd_nums;
número a b
2 2 2
0 2 2
3 3 3
1 3 3

Coluna bé o que eu esperaria, mas por que a coluna atambém está mudando?

DB violino

Alguém pode me ajudar a entender por que adicionar a ORDER BY num DESCcláusula na função da segunda janela causa uma alteração na função da primeira janela? Existe algum detalhe específico de implementação ou otimização do banco de dados que possa estar afetando o resultado?

Examinei a documentação do PostgreSQL, mas sem sucesso.

postgresql

2 respostas

  1. Best Answer

    Pense nos seus dados como uma pilha de roupas, onde a oddcoluna armazena o id de um tipo de roupa (camisa, calça, meia, etc). A PARTITIONcláusula on oddequivale a organizar as roupas em pilhas separadas por cada tipo. Portanto, uma pilha de roupas é composta apenas de camisas, outra pilha é composta apenas de meias. Então, a FIRST_VALUE()função da janela em si é como se alguém lhe dissesse: dê-me a primeira peça de roupa de cada pilha.

    Bem, uma pilha de roupas está desorganizada, então qual peça de roupa é a primeira em cada pilha? Você pode pegar uma camisa azul e meias vermelhas, aleatoriamente, na primeira vez. Se você colocar essas roupas de volta nas respectivas pilhas e fizer isso de novo, poderá acabar com uma camisa verde e meias roxas.

    Estes são seus dados. Não há ordem até que você especifique explicitamente uma em uma consulta que está buscando esses dados. A maioria das funções de janela não faz sentido sem uma ORDER BYcláusula. Sem ele, os dados retornados são semi-aleatórios e conhecidos como não determinísticos .

    Quando você adiciona a ORDER BYcláusula à sua consulta, é como pegar suas pilhas de roupas e dispor as peças em uma linha, ordenadas, digamos, por tamanho. Agora, toda vez que alguém lhe pedir a primeira peça de roupa de cada linha, você sempre puxará a menor camisa, as menores meias, etc., repetidamente. Isso é conhecido como determinístico. O mesmo vale para seus dados, certifique-se de ordená-los explicitamente ao usar funções de janela que dependem de ordenação (por exemplo, FIRST_VALUE()).

    E por que a acoluna acabou seguindo a mesma ordem da bcoluna quando a função de janela da acoluna não usa uma ORDER BYcláusula: os sistemas de banco de dados são preguiçosos, também conhecidos como eficientes. Isso irá variar um pouco de um sistema de banco de dados para outro, e não há garantia de que você sempre verá o mesmo comportamento com base em um grande número de fatores de como funciona o mecanismo SQL do sistema de banco de dados específico. Mas, como observou jjanes, para ser mais eficiente, quando não viu nenhuma ordem específica fornecida para a acoluna, provavelmente reutilizou a lista de dados já classificada da bcoluna para ser mais eficiente. Classificar algo uma vez é mais eficiente do que classificar a mesma coisa de duas maneiras diferentes.

    • 3

  2. Sem ORDER BY, a expressão é subdeterminada. Existem múltiplas respostas possíveis, das quais retorna a mais conveniente para calcular. Depois que uma expressão ganha um ORDER BY, torna-se mais conveniente aplicar a mesma ordem também à outra expressão.

    • 2

relate perguntas