Функция разделения COUNT() ДОЛЖНА использовать DISTINCT

Я пытаюсь написать следующее, чтобы получить общее количество различных NumUsers, например:

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

Студия управления, похоже, не слишком рада этому. Ошибка исчезает, когда я удаляю ключевое слово DISTINCT, но тогда это не будет отдельный счет.

DISTINCT не представляется возможным в рамках функций раздела. Как мне найти отчетный счет? Использую ли я более традиционный метод, такой как коррелированный подзапрос?

Посмотрев на это немного, возможно, эти функции OVER работают по-разному с Oracle в том виде, в котором они не могут использоваться в SQL-Server для вычисления текущих итогов.

Я добавил живой пример здесь, на SQLfiddle, где я пытаюсь использовать функцию раздела для вычисления текущей суммы.

Ответ 1

Существует очень простое решение, использующее dense_rank()

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

Это даст вам именно то, о чем вы просили: количество отдельных UserAccountKeys в течение каждого месяца.

Ответ 2

Я думаю, что единственный способ сделать это в 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 - ваш лучший вариант

Ответ 3

Я использую решение, аналогичное решению David выше, но с дополнительным завихрением, если некоторые строки должны быть исключены из подсчета. Это предполагает, что [UserAccountKey] никогда не является нулевым.

-- 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 с расширенным примером можно найти здесь.

Ответ 4

Necromancing:

Релятивистски просто подражать СЧЕТЧЕСКОЙ ОТЛИЧЕСТИ над PARTITION BY с помощью MAX через 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