PARTITION BY 只考虑两个特定的列进行聚合? [英] PARTITION BY to consider only two specific columns for aggregation?

问题描述

我的表有以下数据:

<头>

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

这篇关于PARTITION BY 只考虑两个特定的列进行聚合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！