Fonction de partitionnement COUNT() SUR possible en utilisant DISTINCT

Question

J'essaie d'écrire ce qui suit afin d'obtenir un total courant de NumUsers distincts, comme ceci :

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

Le studio de gestion ne semble pas très heureux de cette situation. L'erreur disparaît lorsque je supprime le DISTINCT mais alors ce ne sera pas un compte distinct.

DISTINCT ne semble pas être possible dans les fonctions de partition. Comment dois-je m'y prendre pour trouver le compte distinct ? Dois-je utiliser une méthode plus traditionnel comme une sous-requête corrélée ?

En regardant un peu plus loin, peut-être que ces OVER fonctionnent différemment d'Oracle dans la mesure où elles ne peuvent pas être utilisées dans les fonctions SQL-Server pour calculer les totaux courants.

J'ai ajouté un exemple en direct ici sur SQLfiddle où j'essaie d'utiliser une fonction de partition pour calculer un total courant.

Answer 1

Il existe une solution très simple qui consiste à utiliser dense_rank()

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

Cela vous donnera exactement ce que vous demandiez : Le nombre de UserAccountKeys distincts dans chaque mois.

Answer 2

Nécromancie :

Il est relativement simple d'émuler un COUNT DISTINCT sur PARTITION BY avec MAX via DENSE_RANK :

;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

Note :
Cela suppose que les champs en question sont des champs NON nuls.
S'il y a une ou plusieurs entrées NULL dans les champs, vous devez soustraire 1.

Answer 3

Je pense que la seule façon de faire cela dans SQL-Server 2008R2 est d'utiliser une sous-requête corrélée, ou une application externe :

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;

Cela peut être fait dans SQL-Server 2012 en utilisant la syntaxe que vous avez suggérée :

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

Cependant, l'utilisation de DISTINCT n'est toujours pas autorisé, donc si DISTINCT est requis et/ou si la mise à niveau n'est pas une option, alors je pense que OUTER APPLY est votre meilleure option

Answer 4

J'utilise une solution qui est similaire à celle de David ci-dessus, mais avec une tournure supplémentaire si certaines lignes doivent être exclues du compte. Cela suppose que [UserAccountKey] n'est jamais nul.

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

Vous trouverez ici un exemple détaillé de SQL Fiddle.

Answer 5

Il existe une solution en SQL simple :

SELECT time, COUNT(DISTINCT user) OVER(ORDER BY time) AS users
FROM users

\=>

SELECT time, COUNT(*) OVER(ORDER BY time) AS users
FROM (
    SELECT user, MIN(time) AS time
    FROM users
    GROUP BY user
) t