問題描述

我的表有以下數(shù)據(jù):

我正在嘗試構建一個匯總每個客戶帳戶數(shù)量的查詢 - 產品組與此目的無關，因此我的預期結果是:

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:

我嘗試使用窗口函數(shù)來做到這一點:

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.該查詢似乎只是計算每個客戶的唯一行數(shù)，而不是根據(jù)需要識別帳戶數(shù).

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 不支持將其作為窗口函數(shù).

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 排序的行進行計數(shù)，但忽略關系，因此 MAX 將是不同值的數(shù)量.

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 只考慮兩個特定的列進行聚合?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！