PARTITION BY 只考虑两个特定的列进行聚合?
我的表有以下数据:
| REF_NO | PRD_GRP | ACC_NO |
|---|---|---|
| ABC | 12 | 1234 |
| ABC | 9C | 1234 |
| DEF | AB | 7890 |
| DEF | TY | 9891 |
我正在尝试构建一个汇总每个客户帐户数量的查询 - 产品组与此目的无关，因此我的预期结果是:
I'm trying to build a query that summarises the number of accounts per customer - the product group is irrelevant for this purpose so my expected result is:
| REF_NO | PRD_GRP | ACC_NO | NO_OF_ACC |
|---|---|---|---|
| ABC | 12 | 1234 | 1 |
| ABC | 9C | 1234 | 1 |
| DEF | AB | 7890 | 2 |
| DEF | TY | 9891 | 2 |
我尝试使用窗口函数来做到这一点:
I tried doing this using a window function:
SELECT
T.REF_NO,
T.PRD_GRP,
T.ACC_NO,
COUNT(T.ACC_NO) OVER (PARTITION BY T.REF_NO) AS NUM_OF_ACC
FROM TABLE T
但是，返回的 NUM_OF_ACC 值是 2，而不是上面示例中第一个客户 (ABC) 的 1.该查询似乎只是计算每个客户的唯一行数，而不是根据需要识别帐户数.
However, the NUM_OF_ACC value returned is 2 and not 1 in the above example for the first customer (ABC). It seems that the query is simply counting the number of unique rows for each customer, rather than identifying the number of accounts as desired.
我该如何解决这个错误?
How can I fix this error?
Fiddle 链接 - https://dbfiddle.uk/?rdbms19&fiddle=83344cbe95fb46d4a1640caf0bb6d0b2"=83344cbe95fb46d4a1640caf0bb6d0b2
Link to Fiddle - https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=83344cbe95fb46d4a1640caf0bb6d0b2
推荐答案
您需要 COUNT(DISTINCT，遗憾的是 SQL Server 不支持将其作为窗口函数.
You need COUNT(DISTINCT, which is unfortunately not supported by SQL Server as a window function.
但是你可以用 DENSE_RANK 和 MAX
SELECT
T.REF_NO,
T.PRD_GRP,
T.ACC_NO,
MAX(T.rn) OVER (PARTITION BY T.REF_NO) AS NUM_OF_ACC
FROM (
SELECT *,
DENSE_RANK() OVER (PARTITION BY T.REF_NO ORDER BY T.ACC_NO) AS rn
FROM [TABLE] T
) T;
DENSE_RANK 将按 ACC_NO 排序的行进行计数，但忽略关系，因此 MAX 将是不同值的数量.
DENSE_RANK will count up rows ordered by ACC_NO, but ignoring ties, therefore the MAX of that will be the number of distinct values.
db<>fiddle.uk
相关文章