Início / dba / Perguntas / 230328
Accepted
youcantryreachingme
Asked: 2019-02-21 14:52:41 +0800 CST

Explicar uma cláusula OVER

  • 772

Eu vi uma instrução TSQL concisa que efetivamente divide uma string em seus caracteres constituintes, um por linha, com o objetivo de avaliar o asciivalor de cada caractere.

Se estou lendo a consulta corretamente, efetivamente, 3 CTEs estão sendo usados ​​para preparar uma tabela de 1 coluna contendo 10.000 linhas, cada uma com o valor '0'.

Um quarto CTE é definido da seguinte forma:

cteTally(n) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n 
    FROM E4
)

Posteriormente, este CTE é associado a uma tabela contendo uma coluna com as strings de interesse, com o seguinte select:

SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))

Ou seja, o número da linha n, o enésimo caractere em LastName e o valor ascii desse caractere.

Minhas dúvidas se referem à overcláusula do CTE acima.

Essencialmente, o que exatamente ele está fazendo?

Se estamos consultando row_number de 10.000 linhas idênticas, por que precisamos de uma order bycláusula? Por que o put é order bycolocado em uma overcláusula e não como uma order bycláusula para a selectinstrução - especialmente porque a overcláusula nem especifica nenhuma partição? (Presumo que isso significa que a janela sobre a qual row_numberopera são as 10.000 linhas completas?) E o que significa ordenar select null?

sql-server t-sql

2 respostas

  1. Best Answer

    ROW_NUMBER() é uma função de janela de classificação, e as funções de janela de classificação requerem uma cláusula ORDER BY obrigatória. Se você tentar escrevê-lo sem o ORDER BY, receberá um erro de sintaxe.

    SELECT ROW_NUMBER() OVER()
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 4112, Level 15, State 1, Line 1 
-- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

    O truque com a subconsulta foi descoberto por alguém que blogou sobre isso, como uma otimização de desempenho. O SQL Server sempre executa uma operação de classificação, pois as constantes não são permitidas para a cláusula ORDER BY:

    SELECT ROW_NUMBER() OVER(ORDER BY NULL)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5309, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions 
-- do not support constants as ORDER BY clause expressions.

    E nem são inteiros que são tratados como índices:

    SELECT ROW_NUMBER() OVER(ORDER BY 1)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5308, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions 
-- do not support integer indices as ORDER BY clause expressions.

    Acontece que devido a alguma falha no código, você pode contornar essa limitação usando uma subconsulta, que por algum motivo é permitida, e elimina o operador de classificação.

    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);

    Nenhum operador de classificação

    Você pode usar qualquer constante na subconsulta, o NULL é provavelmente uma reminiscência do hábito de usar SELECT NULL dentro de predicados EXISTS, que no início da história do SQL Server teve um impacto no desempenho em oposição a * ou qualquer outra expressão de coluna, como o otimizador não foi inteligente o suficiente para ignorá-lo.

    HTH

    UPDATE @Erik-Darling comentou que você também pode contornar isso usando expressões computadas:

    You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);
    • 8

  2. A cláusula OVER define a ordenação (e particionamento se PARTITION BY estiver incluído) do conjunto de linhas antes de aplicar a função de janela selecionada. Como você pode usar várias funções do Windows em uma única consulta, cada uma requer seu próprio particionamento e ordenação para garantir que os dados sejam retornados conforme desejado.

    No seu exemplo, ROW_NUMBER() está sendo usado para gerar um número de linha sequencial para cada linha no CTE. SELECT NULL é usado porque não há nenhuma ordem específica necessária, mas uma cláusula ORDER BY é necessária para uma função de janela.

    Outra maneira de conseguir a mesma coisa seria usar uma coluna IDENTITY, no entanto, que tem outras implicações e requer alterações em uma tabela existente ou a criação de uma tabela temporária. A função de janela ROW_NUMBER em um CTE permite que essa identidade seja gerada em tempo real.

    Para responder à sua pergunta específica:

    Essencialmente, o que exatamente ele está fazendo?

    Ele está ordenando as linhas em E4 'aleatoriamente' antes de aplicar a função de janela ROW_NUMBER() a esse conjunto de resultados para produzir uma lista de números de linha igual ao número de linhas em E4. OVER pode ser traduzido como "Busque-me um conjunto de resultados com esta ordenação e particionamento e aplique (OVER) esta função de janela a esse conjunto de resultados independente da ordenação na instrução SELECT principal".

    Mais informações: Cláusula OVER

    • 4

relate perguntas