[sql] DISTINCT를 사용하여 파티션 함수 COUNT () OVER 가능

Program / 글쓴이

다음과 같이 고유 한 NumUsers의 누적 합계를 얻기 위해 다음을 작성하려고합니다.

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

관리 스튜디오는 이것에 대해 너무 행복해 보이지 않습니다. DISTINCT키워드를 제거하면 오류가 사라지 지만 고유 카운트는 아닙니다.

DISTINCT파티션 기능 내에서 가능하지 않은 것 같습니다. 고유 개수를 찾는 방법은 무엇입니까? 상관 하위 쿼리와 같은 보다 전통적인 방법을 사용 합니까?

좀 더 자세히 살펴보면 이러한 OVER함수는 SQL-Server누계 계산에 사용할 수 없다는 점에서 Oracle과 다르게 작동 할 수 있습니다 .

여기 SQLfiddle 에 실제 예제를 추가했습니다. 여기서 분할 함수를 사용하여 누적 합계를 계산하려고합니다.

답변

사용하는 매우 간단한 솔루션이 있습니다. dense_rank()

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

이것은 당신이 요구했던 것을 정확하게 제공 할 것입니다 : 매월 고유 한 UserAccountKey의 수.

답변

네크 로맨싱 :

DENSE_RANK를 통해 MAX로 PARTITION BY보다 COUNT DISTINCT를 에뮬레이션하는 것은 비교적 간단합니다.

;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

참고 :
이는 해당 필드가 NON 허용 필드가 아니라고 가정합니다.
필드에 하나 이상의 NULL 항목이있는 경우 1을 빼야합니다.

답변

위 의 David 와 유사한 솔루션을 사용 하지만 일부 행을 개수에서 제외해야하는 경우 추가 비틀기가 필요합니다. 이것은 [UserAccountKey]가 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은 여기에서 찾을 수 있습니다.

답변

SQL-Server 2008R2에서이 작업을 수행하는 유일한 방법은 상관 하위 쿼리 또는 외부 적용을 사용하는 것입니다.

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;

이는 제안한 구문을 사용하여 SQL-Server 2012 에서 수행 할 수 있습니다 .

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

그러나 사용 DISTINCT은 여전히 ​​허용되지 않으므로 DISTINCT가 필요하거나 업그레이드가 옵션이 아닌 경우 OUTER APPLY최선의 선택 이라고 생각 합니다.

답변