Início / dba / Perguntas / 290034
Accepted
Pantea
Asked: 2021-04-19 22:34:13 +0800 CST

Por que alterar a coluna na seção ORDER BY da função de janela "MAX() OVER()" afeta o resultado final?

  • 772

Eu tenho uma tabela com a estrutura abaixo e seus dados:

create table test_table
(
Item_index   int,
Item_name    varchar(50)
)

insert into test_table (Item_index,Item_name) values (0,'A')
insert into test_table (Item_index,Item_name) values (1,'B')
insert into test_table (Item_index,Item_name) values (0,'C')
insert into test_table (Item_index,Item_name) values (1,'D')
insert into test_table (Item_index,Item_name) values (0,'E')

Eu quero saber porque alterar a coluna na order byseção da consulta, altera o resultado? Em QUERY-1, usei item_indexe na coluna QUERY-2usei na ordem por seção. item_nameEu pensei que ambas as consultas devem gerar o mesmo resultado because I used item_index in both queries for partitioning!Estou completamente confuso agora! por que a ordem por coluna deve afetar o resultado final?

PERGUNTA-1:

select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_index) new_column
from test_table t;

RESULTADO:

Item_index  Item_name     new_column
----------- --------------------------
0           A                E
0           C                E
0           E                E
1           D                D
1           B                D

QUERY-2:

select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_name) new_column
from test_table t;

RESULTADO:

Item_index  Item_name  new_column
----------- -----------------------
0           A             A
0           C             C
0           E             E
1           B             B
1           D             D

Alguém pode explicar como exatamente essas duas consultas estão sendo executadas e por que cada uma delas gera resultados diferentes?

desde já, obrigado

sql-server t-sql

4 respostas

  1. A explicação para os diferentes resultados é dada na documentação do SQL Server sobre funções de janela , a ORDER BYseção:

    ORDER BY

    Define a ordem lógica das linhas em cada partição do conjunto de resultados. Ou seja, especifica a ordem lógica na qual o cálculo da função de janela é executado.

    Se for especificado e a ROWS/RANGEnão for especificado, o padrão RANGE UNBOUNDED PRECEDING AND CURRENT ROWserá usado como padrão para o quadro de janela pelas funções que podem aceitar ROWS/RANGE especificação opcional (por exemplo minou max).

    Observe que MIN()e MAX()os agregados de janela aceitam um opcional ROWSou RANGEespecificação.

    Quando não há tal especificação, eles calculam o MIN e MAX em toda a partição. Quando houver, eles calculam o MIN ou MAX no intervalo especificado. Como suas duas consultas especificam ordens/intervalos diferentes, elas produzem resultados diferentes.

    Se você queria o MAX em toda a partição, remova o ORDER BYintervalo:

    Se não for especificado, a ordem padrão é ASC e a função de janela usará todas as linhas na partição .

    • 5
  2. Best Answer

    max(t.Item_name)over(partition by t.item_index order by item_index) new_column

    Vamos pegar um grupo onde t.item_index= 0. É

    Item_index Nome do item
    0 UMA
    0 C
    0 E

    Quando order by item_indexé aplicado, todas as linhas têm o mesmo valor, portanto, todas elas são incluídas no quadro e todos os valores das linhas são usados ​​para a seleção MAX(). Portanto, o valor 'E'é retornado para todas as linhas.

    max(t.Item_name)over(partition by t.item_index order by item_name)

    Vamos pegar o mesmo grupo.

    Item_index Nome do item
    0 UMA
    0 C
    0 E

    Agora, a chave de classificação é diferente e, quando a janela RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWé aplicada, diferentes linhas são incluídas no quadro a ser investigado.

    Apenas para a 1ª linha, esta linha é incluída no quadro e 'A'é o único valor no quadro, portanto, é retornado.

    Para a 2ª linha, as primeiras 2 linhas são incluídas no quadro, os valores 'A'e 'C'são comparados e 'C'retornados como valor máximo no quadro.

    Para a 3ª linha, todas as 3 linhas são incluídas no quadro, os valores 'A'e 'C'são 'E'comparados e 'E'retornados como valor máximo no quadro.

    • 3

  3. A cláusula de janela que você usa (neste caso, o padrão de "intervalo entre a linha anterior e a atual ilimitada") opera de acordo com o que você ordena. Há alguma lógica nisso: para saber o que está "anteriormente" ou "seguindo" você tem que falar sobre dados ordenados.

    Na query-1 você ordena por item_index, que também é o que você particiona. Então vamos olhar para a partição com item_index = 0. Cada linha na partição terá um item_index 0. Isso significa que para cada linha a janela será efetivamente "Todas as linhas com um item_index <= 0". Então, basicamente todas as linhas na partição. E o max(item_name) disso é 'E'.

    Na query-2 você ordena por item_name. Para essa mesma partição, isso significa que a janela para a linha 'A' será "Todas as linhas com um item_name <= 'A'" Isso é apenas a linha 'A', então o max(item_name) é 'A'. A janela para a linha 'C' será "Todas as linhas com um item_name <= 'C'" Essa é a linha 'A' e 'C', então o max(item_name) é 'C' . A janela para a linha 'E' será "Todas as linhas com um item_name <= 'E'" Essa é a linha 'A', 'C' e 'E', então o max(item_name) é 'E' .

    Espero que isso ajude a visualizar o que acontece.

    • 3

  4. Há algo que você precisa ter em mente para funções de janela: elas são executadas em todas as linhas. Assim, funções de janela como lag e lead se aplicam às linhas anteriores e seguintes. Ao adicionar um order by, você está basicamente dizendo “neste agrupamento, até e incluindo a linha atual, qual é o valor máximo para a coluna item_name”.

    Isso não faz sentido ao usar max na mesma coluna em que você está ordenando (assumindo asc), pois sempre será o mesmo que a coluna atual, mas faz sentido para min e avg. E fazer qualquer outra coisa para max seria muito complicado.

    • 1

relate perguntas