wake-up-neo.com

Partitionsfunktion COUNT () OVER mit DISTINCT möglich

Ich versuche, Folgendes zu schreiben, um eine fortlaufende Anzahl unterschiedlicher NumUsers zu erhalten:

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

Das Management Studio scheint sich darüber nicht allzu sehr zu freuen. Der Fehler verschwindet, wenn ich das Schlüsselwort DISTINCT entferne, aber dann wird es nicht eindeutig gezählt.

DISTINCT scheint in den Partitionsfunktionen nicht möglich zu sein. Wie finde ich die eindeutige Zählung? Verwende ich eine traditionellere Methode wie eine korrelierte Unterabfrage?

Wenn Sie dies etwas genauer betrachten, funktionieren diese OVER - Funktionen möglicherweise anders als Oracle, da sie in SQL-Server Nicht zur Berechnung der laufenden Summen verwendet werden können.

Ich habe hier ein Live-Beispiel für SQLfiddle hinzugefügt, in dem ich versuche, mithilfe einer Partitionsfunktion eine laufende Summe zu berechnen.

sqlsql-servertsqlsql-server-2008-r2sql-server-2014
67
whytheq

Es gibt eine sehr einfache Lösung mit dense_rank()

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

Dadurch erhalten Sie genau das, wonach Sie gefragt haben: Die Anzahl der unterschiedlichen UserAccountKeys innerhalb eines Monats.

139
David

Ich denke, die einzige Möglichkeit, dies in SQL Server 2008R2 zu tun, besteht darin, eine korrelierte Unterabfrage oder eine äußere Anwendung zu verwenden:

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;

Dies kann in SQL-Server 2012 mit der von Ihnen vorgeschlagenen Syntax erfolgen:

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

Die Verwendung von DISTINCT ist jedoch weiterhin nicht zulässig. Wenn also DISTINCT erforderlich ist und/oder ein Upgrade nicht möglich ist, denke ich OUTER APPLY ist die beste Option

5
GarethD

Ich verwende eine Lösung, die der von David oben ähnlich ist, aber mit einer zusätzlichen Wendung, wenn einige Zeilen von der Zählung ausgeschlossen werden sollen. Dies setzt voraus, dass [UserAccountKey] niemals null ist.

-- 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

Ein SQL Fiddle mit einem erweiterten Beispiel finden Sie hier.

4
Lars Rönnbäck

Nekromanzierung:

Es ist relativ einfach, COUNT DISTINCT über PARTITION BY mit MAX über DENSE_RANK zu emulieren:

;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
3
Stefan Steiger