Início / dba / Perguntas / 89031
Accepted
Omri
Asked: 2015-01-12 01:21:19 +0800 CST

Usando DISTINCT na função de janela com OVER

  • 772

Estou tentando migrar uma consulta do Oracle para o SQL Server 2014.

Aqui está minha consulta que funciona muito bem no Oracle:

select
count(distinct A) over (partition by B) / count(*) over() as A_B
from MyTable

Aqui está o erro que recebi depois de tentar executar esta consulta no SQL Server 2014.

Use of DISTINCT is not allowed with the OVER clause

Alguém sabe qual é o problema? Esse tipo de consulta é possível no SQL Server? Por favor informar.

sql-server sql-server-2014

5 respostas

  1. Isso fornece a contagem distinta (*) para A particionada por B:

    dense_rank() over (partition by B order by A) 
+ dense_rank() over (partition by B order by A desc) 
- 1
    • 21
  2. Best Answer

    Alguém sabe qual é o problema? Esse tipo de consulta é possível no SQL Server?

    Não, não está implementado no momento. Consulte a seguinte solicitação de item de conexão.

    Solicitação de aprimoramento da cláusula OVER - cláusula DISTINCT para funções agregadas

    Outra variante possível seria

    SELECT M.A,
       M.B,
       T.A_B
FROM   MyTable M
       JOIN (SELECT CAST(COUNT(DISTINCT A) AS NUMERIC(18,8)) / SUM(COUNT(*)) OVER() AS A_B,
                    B
             FROM   MyTable
             GROUP  BY B) T
         ON EXISTS (SELECT M.B INTERSECT SELECT T.B)

    a conversão para NUMERICestá lá para evitar a divisão de inteiros. A razão para a cláusula join é explicada aqui .

    Ele pode ser substituído por ON M.B = T.B OR (M.B IS NULL AND T.B IS NULL)se preferir (ou simplesmente ON M.B = T.Bse a Bcoluna não for anulável).

    • 13

  3. Você pode pegar o valor máximo de dense_rank()para obter a contagem distinta de A particionada por B.

    Para cuidar do caso em que A pode ter valores nulos, você pode usar first_valuepara descobrir se um nulo está presente na partição ou não e, em seguida, subtrair 1 se for sugerido por Martin Smith no comentário.

    select (max(T.DenseRankA) over(partition by T.B) - 
          cast(iif(T.FirstA is null, 1, 0) as numeric(18, 8))) / T.TotalCount as A_B
from (
     select dense_rank() over(partition by T.B order by T.A) DenseRankA,
            first_value(T.A) over(partition by T.B order by T.A) as FirstA,
            count(*) over() as TotalCount,
            T.A,
            T.B
     from MyTable as T
     ) as T
    • 8

  4. Tente fazer uma subconsulta, agrupando por A, B e incluindo a contagem. Então, em sua consulta externa, sua contagem(distinta) se torna uma contagem regular e sua contagem(*) se torna uma soma(cnt).

    select
count(A) over (partition by B) * 1.0 / 
    sum(cnt) over() as A_B
from
(select A, B, count(*) as cnt
 from MyTable
 group by A, B) as partial;
    • 5

  5. O SQL Server por enquanto não permite o uso Distinctcom funções em janela.

    Mas uma vez que você se lembre de como as funções em janela funcionam (ou seja: elas são aplicadas ao conjunto de resultados da consulta), você pode contornar isso:

    select B,
min(count(distinct A)) over (partition by B) / max(count(*)) over() as A_B
from MyTable
group by B
    • 0

relate perguntas