ejemplos - over partition sql

Partición Función COUNT() OVER posible utilizando DISTINCT

Question

Intento escribir lo siguiente para obtener un total acumulado de NumUsers distintos, así:

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

El estudio de administración no parece muy feliz con esto. El error desaparece cuando DISTINCT palabra clave DISTINCT , pero luego no será un recuento distinto.

DISTINCT no parece ser posible dentro de las funciones de partición. ¿Cómo hago para encontrar el conteo distinto? ¿Utilizo un método más tradicional , como una subconsulta correlacionada?

Al analizar esto un poco más, quizás estas funciones OVER funcionen de manera diferente a Oracle en la forma en que no se pueden usar en SQL-Server para calcular los totales acumulados.

He agregado un ejemplo en vivo aquí en SQLfiddle donde intento usar una función de partición para calcular un total SQLfiddle .

Answer 1

Creo que la única forma de hacerlo en SQL-Server 2008R2 es usar una subconsulta correlacionada o una aplicación externa:

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;

Esto se puede hacer en SQL-Server 2012 usando la sintaxis que ha sugerido:

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

Sin embargo, el uso de DISTINCT aún no está permitido, por lo que si se requiere DISTINCT y / o si la actualización no es una opción, entonces creo que OUTER APPLY es su mejor opción.

Answer 2

Hay una solución muy simple usando dense_rank()

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

Esto le dará exactamente lo que estaba solicitando: la cantidad de UserAccountKeys distintas dentro de cada mes.

Answer 3

Nigromancia:

Es relativamente simple emular un COUNT DISTINCT sobre PARTITION BY con MAX a través de 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

Answer 4

Utilizo una solución que es similar a la de anterior, pero con un giro adicional si algunas filas se deben excluir del recuento. Esto supone que [UserAccountKey] nunca es nulo.

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

Aquí se puede encontrar un SQL Fiddle con un ejemplo extendido.