分区函数 COUNT() OVER 可能使用 DISTINCT [英] Partition Function COUNT() OVER possible using DISTINCT
问题描述
我正在尝试编写以下内容以获得不同 NumUsers 的运行总数,如下所示:
I'm trying to write the following in order to get a running total of distinct NumUsers, like so:
NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth])
管理工作室似乎对此不太满意.当我删除 DISTINCT
关键字时,错误消失了,但它不会是一个不同的计数.
Management studio doesn't seem too happy about this. The error disappears when I remove the DISTINCT
keyword, but then it won't be a distinct count.
DISTINCT
在分区函数中似乎是不可能的.我如何去寻找不同的计数?我是否使用更传统的方法,例如相关子查询?
DISTINCT
does not appear to be possible within the partition functions.
How do I go about finding the distinct count? Do I use a more traditional method such as a correlated subquery?
进一步研究一下,也许这些 OVER
函数与 Oracle 的工作方式不同,因为它们不能在 SQL-Server
中用于计算运行总数.
Looking into this a bit further, maybe these OVER
functions work differently to Oracle in the way that they cannot be used in SQL-Server
to calculate running totals.
我在 SQLfiddle 上添加了一个现场示例,我尝试使用用于计算运行总数的分区函数.
I've added a live example here on SQLfiddle where I attempt to use a partition function to calculate a running total.
推荐答案
dense_rank() over (partition by [Mth] order by [UserAccountKey])
+ dense_rank() over (partition by [Mth] order by [UserAccountKey] desc)
- 1
这将为您提供您所要求的内容:每个月内不同的 UserAccountKeys 的数量.
This will give you exactly what you were asking for: The number of distinct UserAccountKeys within each month.
这篇关于分区函数 COUNT() OVER 可能使用 DISTINCT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!