Início / dba / Perguntas / 77639
Accepted
Pavel Nefyodov
Asked: 2014-09-26 05:48:49 +0800 CST

RANK() e DENSE_RANK() são determinísticos ou não determinísticos?

  • 772

De acordo com o oficial da Microsoft, BOL DENSE_RANK não é determinístico ( RANK() ). Mas de acordo com Ranking Functions de Itzik Ben-Gan "... as funções RANK() e DENSE_RANK() são sempre determinísticas". Quem está certo?

O que descobri até agora: Definição da Microsoft "As funções determinísticas sempre retornam o mesmo resultado sempre que são chamadas com um conjunto específico de valores de entrada e recebem o mesmo estado do banco de dados."

Assim, nas tabelas de teoria dos conjuntos Empregados

Employee            Salary
Sue Right            1.00
Robin Page           1.00
Phil Factor          1.00

e Empregados2

Employee            Salary
Phil Factor          1.00
Sue Right            1.00
Robin Page           1.00

são os mesmos. Mas as funções de classificação retornam valores diferentes:

    CREATE TABLE [dbo].[Employees](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Employees2](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[Employees]
([Employee] ,[Salary])
VALUES
('Sue Right', 1)
, ('Robin Page', 1)
,('Phil Factor', 1 )
GO
INSERT INTO [dbo].[Employees2]
([Employee] ,[Salary])
VALUES
('Phil Factor', 1 )
,('Sue Right', 1)
,('Robin Page', 1)
GO
SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees

SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees2

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees2
sql-server

3 respostas

  1. Best Answer

    De acordo com o oficial da Microsoft, BOL DENSE_RANK é não determinístico (RANK()). Mas de acordo com Ranking Functions de Itzik Ben-Gan "... as funções RANK() e DENSE_RANK() são sempre determinísticas". Quem está certo?

    Ambos estão certos, porque estão usando diferentes sentidos da palavra "determinista".

    Do ponto de vista do otimizador do SQL Server, "determinístico" tem um significado muito preciso; um significado que existia antes das funções de janela e classificação serem adicionadas ao produto. Para o otimizador, a propriedade "determinística" define se uma função pode ser duplicada livremente em suas estruturas internas de árvore durante a otimização. Isso não é legal para uma função não determinística.

    Determinístico aqui significa: a instância exata da função sempre retorna a mesma saída para a mesma entrada, não importa quantas vezes ela seja chamada. Isso nunca é verdade para funções de janelamento, por definição, porque como uma função escalar (de linha única), elas não retornam o mesmo resultado em uma linha ou entre linhas. Simplificando, usando ROW_NUMBERcomo exemplo:

    A ROW_NUMBERfunção retorna valores diferentes para linhas diferentes (por definição!), portanto, para fins de otimização, ela não é determinística

    Este é o sentido que o BOL está usando.

    Itzik está fazendo uma observação diferente sobre o determinismo do resultado como um todo. Sobre um conjunto de entrada ordenado (com desempate adequado) a saída é uma sequência "determinística". Essa é uma observação válida, mas não é a qualidade "determinística" que é importante durante a otimização da consulta.

    • 23

  2. NTILE()é um caso interessante; parece aplicar-se após a classificação (que, no caso de empate, é deixada para os próprios dispositivos do SQL Server, e isso geralmente é conduzido pela escolha mais eficiente de índice para fins de classificação). Você pode tornar isso determinístico não forçando o SQL Server a fazer uma escolha arbitrária aqui - adicione um ou mais desempate à OVER()cláusula:

    OVER (ORDER BY Salary, Employee)

    Essencialmente, você precisa tornar a classificação exclusiva. Se você tiver funcionários com o mesmo nome, talvez seja necessário escolher uma coluna de desempate diferente ou continuar adicionando colunas até que realmente não haja empates.

    Para RANK()e DENSE_RANK(), os empates são, na verdade, uma razão crucial pela qual você não pode obter valores diferentes. Tente não confundir determinismo da saída da função com determinismo da ordem dos resultados. Se suas consultas não tiverem ORDER BY, o que não é determinístico nisso?

    1   1   Sue Right
1   1   Robin Page
1   1   Phil Factor

1   1   Phil Factor
1   1   Sue Right
1   1   Robin Page

    RANK()e DENSE_RANK()aplicou os mesmos valores em ambos os casos, o SQL Server apenas retornou os resultados para você em uma ordem diferente. Isso não tem nada a ver com esperar a mesma saída RANK()ou DENSE_RANK()receber a mesma entrada - trata-se apenas de assumir ou esperar alguma ordem determinística quando você disse ao SQL Server (omitindo uma ORDER BYcláusula) que não se importa com a ordem de os resultados. Veja o nº 3 aqui:

    • 11

  3. Sintaxe:

    WindowFunction() OVER (PARTITION BY <some expressions>        -- partition list
                       ORDER BY <some other expressions>)     -- order list

    Ambas as funções, RANK()e DENSE_RANK(), por suas definições, têm a garantia de produzir os mesmos resultados, desde que as próprias expressões na OVERcláusula sejam determinísticas. E é isso que Itzik Ben-Gun quis dizer em seu artigo. Essas listas geralmente são apenas colunas das tabelas envolvidas.

    Assim, embora as funções sejam gerais não sejam determinísticas, sua implementação poderia ter tido o cuidado de distinguir os dois casos e considerá-las determinísticas ou não, ao examinar as listas de partição e ordem.

    Meu palpite é que os desenvolvedores do SQL-Server decidiram que era mais fácil implementá-los como sempre "não determinísticos", apesar de isso contradizer de certa forma sua definição de funções determinísticas. Portanto, eles são declarados como não determinísticos no MSDN porque, na implementação atual, o mecanismo os considera sempre como não determinísticos.

    Mais um argumento é que as outras duas funções de janela, ROW_NUMBER()e NTILE(), são ainda mais complicadas porque, para elas terem saída idêntica, a expressão na partição e a ordem por listas não apenas precisam ser determinísticas, mas também únicas. Portanto, implementar todos esses detalhes está longe de ser trivial.

    Não vou comentar sobre a ordem dos conjuntos de resultados, pois isso não tem nada a ver com o determinismo, como Aaron Bertrand explicou claramente em sua resposta.

    • 7

relate perguntas