分区函数COUNT（）OVER可能使用DISTINCT分区函数COUNT（）OVER可能使用DIST

我想写以下，以获得一个运行总计不同NUMUSERS的，就像这样：

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

管理工作室似乎并没有太高兴这一点。 当我删除错误消失DISTINCT关键字，但随后它不会是一个重复计数。

DISTINCT不会出现在分区函数中是可能的。 我该如何去寻找重复计数？ 难道我用的是比较传统的方法，如相关子查询？

展望这个远一点，也许这些OVER的功能，它们不能被使用的方式不同的方式工作到Oracle SQL-Server计算运行总和。

我就在这里增加了一个活生生的例子SQLfiddle ，我尝试使用分区函数来计算运行总和。

Answer 1:

有使用非常简单的解决方案dense_rank()

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

这会给你你问什么了：每月内的不同UserAccountKeys的数量。

Answer 2:

我想在SQL-服务器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是您最好的选择

Answer 3:

我用一个解决方案，类似于的大卫以上，但有一个附加的扭曲，如果某些行应该从计数排除。 这假定[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拨弄扩展的例子可以在这里找到。

Answer 4:

Necromancing：

这是relativiely简单通过DENSE_RANK效仿DISTINCT在分区COUNT BY与MAX：

;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

文章来源: Partition Function COUNT() OVER possible using DISTINCT