Funkcja partycji COUNT () OVER jest możliwa przy użyciu DISTINCT

Próbuję napisać co następuje, aby uzyskać bieżącą liczbę różnych NumUserów, na przykład:

NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth])

Studio zarządzania nie wydaje się z tego powodu zbytnio zadowolone. Błąd znika po usunięciu DISTINCTsłowa kluczowego, ale wtedy nie będzie to wyraźna liczba.

DISTINCTnie wydaje się być możliwe w ramach funkcji partycji. Jak mam znaleźć odrębną liczbę? Czy używam bardziej tradycyjnej metody, takiej jak skorelowane podzapytanie?

Patrząc nieco dalej, być może te OVERfunkcje działają inaczej niż Oracle, ponieważ nie można ich używać SQL-Serverdo obliczania sum bieżących.

Dodałem tutaj przykład na żywo w SQLfiddle, w którym próbuję użyć funkcji partycji do obliczenia bieżącej sumy.

Jest bardzo proste rozwiązanie przy użyciu dense_rank()

dense_rank() over (partition by [Mth] order by [UserAccountKey]) 
+ dense_rank() over (partition by [Mth] order by [UserAccountKey] desc) 
- 1

W ten sposób uzyskasz dokładnie to, o co prosiłeś: liczbę różnych kluczy UserAccountKeys w każdym miesiącu.

Nekromancja:

Emulowanie COUNT DISTINCT przez PARTITION BY z MAX przez DENSE_RANK jest stosunkowo proste:

;WITH baseTable AS
(
    SELECT 'RM1' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM1' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR2' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR2' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR3' AS ADR
    UNION ALL SELECT 'RM3' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM2' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM3' AS RM, 'ADR1' AS ADR
    UNION ALL SELECT 'RM3' AS RM, 'ADR2' AS ADR
)
,CTE AS
(
    SELECT RM, ADR, DENSE_RANK() OVER(PARTITION BY RM ORDER BY ADR) AS dr 
    FROM baseTable
)
SELECT
     RM
    ,ADR

    ,COUNT(CTE.ADR) OVER (PARTITION BY CTE.RM ORDER BY ADR) AS cnt1 
    ,COUNT(CTE.ADR) OVER (PARTITION BY CTE.RM) AS cnt2 
    -- Not supported
    --,COUNT(DISTINCT CTE.ADR) OVER (PARTITION BY CTE.RM ORDER BY CTE.ADR) AS cntDist
    ,MAX(CTE.dr) OVER (PARTITION BY CTE.RM ORDER BY CTE.RM) AS cntDistEmu 
FROM CTE

Uwaga: przy
założeniu, że dane pola są polami, które nie dopuszczają wartości null.
Jeśli w polach znajduje się jeden lub więcej wpisów NULL, należy odjąć 1.

Używam rozwiązania podobnego do rozwiązania Davida powyżej, ale z dodatkowym skrętem, jeśli niektóre wiersze powinny zostać wyłączone z liczenia. Zakłada się, że [UserAccountKey] nigdy nie jest null.

-- subtract an extra 1 if null was ranked within the partition,
-- which only happens if there were rows where [Include] <> 'Y'
dense_rank() over (
  partition by [Mth] 
  order by case when [Include] = 'Y' then [UserAccountKey] else null end asc
) 
+ dense_rank() over (
  partition by [Mth] 
  order by case when [Include] = 'Y' then [UserAccountKey] else null end desc
)
- max(case when [Include] = 'Y' then 0 else 1 end) over (partition by [Mth])
- 1

SQL Fiddle z rozszerzonym przykładem można znaleźć tutaj.

Myślę, że jedynym sposobem na zrobienie tego w SQL-Server 2008R2 jest użycie skorelowanego podzapytania lub zastosowania zewnętrznego:

SELECT  datekey,
        COALESCE(RunningTotal, 0) AS RunningTotal,
        COALESCE(RunningCount, 0) AS RunningCount,
        COALESCE(RunningDistinctCount, 0) AS RunningDistinctCount
FROM    document
        OUTER APPLY
        (   SELECT  SUM(Amount) AS RunningTotal,
                    COUNT(1) AS RunningCount,
                    COUNT(DISTINCT d2.dateKey) AS RunningDistinctCount
            FROM    Document d2
            WHERE   d2.DateKey <= document.DateKey
        ) rt;

Można to zrobić w SQL-Server 2012 przy użyciu sugerowanej składni:

SELECT  datekey,
        SUM(Amount) OVER(ORDER BY DateKey) AS RunningTotal
FROM    document

Jednak użycie DISTINCTnadal jest niedozwolone, więc jeśli DISTINCT jest wymagany i / lub jeśli aktualizacja nie jest opcją, myślę, że OUTER APPLYjest to najlepsza opcja