分区函数 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()

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