Używanie DISTINCT w funkcji okna z OVER

Próbuję migrować zapytanie z Oracle do SQL Server 2014.

Oto moje zapytanie, które działa świetnie w Oracle:

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

Oto błąd, który wystąpił po próbie uruchomienia tego zapytania w programie SQL Server 2014.

Use of DISTINCT is not allowed with the OVER clause

Czy ktoś wie na czym polega problem? Czy takie zapytanie jest możliwe w SQL Server? Proszę doradź.

Czy ktoś wie na czym polega problem? Czy takie zapytanie jest możliwe w SQL Server?

Nie, to nie jest obecnie zaimplementowane. Zobacz następujące żądanie elementu połączenia.

Żądanie rozszerzenia klauzuli OVER - klauzula DISTINCT dla funkcji agregujących

Innym możliwym wariantem byłoby

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) 

cast to NUMERICjest, aby uniknąć podziału na liczby całkowite. Powód klauzuli dołączania jest wyjaśniony tutaj .

W razie potrzeby można go zastąpić ON M.B = T.B OR (M.B IS NULL AND T.B IS NULL)(lub po prostu, ON M.B = T.Bjeśli Bkolumna nie ma wartości zerowej).

Daje to odrębną liczbę (*) dla A podzielonego przez B:

dense_rank() over (partition by B order by A) 
+ dense_rank() over (partition by B order by A desc) 
- 1

Możesz przyjąć maksymalną wartość, dense_rank()aby uzyskać odrębną liczbę A podzieloną przez B.

Aby zająć się przypadkiem, w którym A może mieć wartości zerowe, możesz użyć, first_valueaby dowiedzieć się, czy w partycji występuje wartość zerowa, a następnie odjąć 1, jeśli jest to sugerowane przez Martina Smitha w komentarzu.

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

Spróbuj wykonać podkwerendę, pogrupując według A, B i uwzględniając liczbę. Następnie w zewnętrznym zapytaniu liczba (odrębna) staje się liczbą regularną, a liczba (*) staje się sumą (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;