如何用单窗口函数统计每个tx_id的不同货币数量？

编辑说明：新增id列以说明无法仅用简单GROUP BY，需使用窗口函数。

我需要统计每个tx_id对应的货币种类数量。目前我可以通过以下代码实现，但感觉过于复杂。我认为应该可以用单个窗口函数实现，但无法确定正确语法。

原实现代码（已修正group_id笔误，应为tx_id）：

-- test data
WITH cte AS (
    SELECT * FROM (
        VALUES
            (1,123, 'GBP'), -- 2 ccys
            (2,123, 'USD'),
            (3,123, 'USD'),
            (4,124, 'GBP'), -- 1 ccys
            (5,124, 'GBP'),
            (6,125, 'EUR'), -- 3 ccys
            (7,125, 'EUR'),
            (8,125, 'JPY'),
            (9,125, 'USD'),
            (10,125, 'EUR')
) AS a (id, tx_id, ccy)
)

,ccy_count as (
    select id, tx_id, ccy,
        dense_rank() over (PARTITION BY tx_id ORDER BY ccy) as dense_rank_ccy
    from cte
)
    
select id, 
        tx_id, 
        ccy, 
        max(dense_rank_ccy) over (PARTITION BY tx_id) as ccy_count
from ccy_count
order by tx_id, ccy

简化实现方案 #

方案1：支持窗口函数COUNT(DISTINCT)的数据库（如PostgreSQL 9.4+、SQL Server 2017+）

直接用单个窗口函数完成，写法最简洁：

WITH cte AS (
    SELECT * FROM (
        VALUES
            (1,123, 'GBP'),
            (2,123, 'USD'),
            (3,123, 'USD'),
            (4,124, 'GBP'),
            (5,124, 'GBP'),
            (6,125, 'EUR'),
            (7,125, 'EUR'),
            (8,125, 'JPY'),
            (9,125, 'USD'),
            (10,125, 'EUR')
    ) AS a (id, tx_id, ccy)
)
SELECT 
    id,
    tx_id,
    ccy,
    COUNT(DISTINCT ccy) OVER (PARTITION BY tx_id) AS ccy_count
FROM cte
ORDER BY tx_id, ccy;

方案2：不支持窗口函数COUNT(DISTINCT)的数据库

嵌套使用DENSE_RANK和MAX窗口函数，省去中间CTE：

WITH cte AS (
    SELECT * FROM (
        VALUES
            (1,123, 'GBP'),
            (2,123, 'USD'),
            (3,123, 'USD'),
            (4,124, 'GBP'),
            (5,124, 'GBP'),
            (6,125, 'EUR'),
            (7,125, 'EUR'),
            (8,125, 'JPY'),
            (9,125, 'USD'),
            (10,125, 'EUR')
    ) AS a (id, tx_id, ccy)
)
SELECT 
    id,
    tx_id,
    ccy,
    MAX(DENSE_RANK() OVER (PARTITION BY tx_id ORDER BY ccy)) 
        OVER (PARTITION BY tx_id) AS ccy_count
FROM cte
ORDER BY tx_id, ccy;

关键说明 #

• 原代码中的group_id是笔误，必须替换为tx_id才能按交易ID正确分区统计。
• 方案1利用COUNT(DISTINCT)直接统计每个交易下的唯一货币种类数，效率和可读性最优。
• 方案2通过DENSE_RANK给每个交易下的货币排重编号，再用MAX取编号最大值得到种类数，兼容更多数据库版本。

内容的提问来源于stack exchange，提问作者Laurence Hook