如何计算窗口函数中不同的值

Question

编辑我添加了另一个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 group_id ORDER BY ccy ) as dense_rank_ccy
from cte
)

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

Answer 1

如果您只想报告每笔交易的不同货币数量，那么您应该进行汇总，而不是使用窗口函数:

SELECT tx_id, COUNT(DISTINCT ccy) AS cnt
FROM cte
GROUP BY tx_id;

Answer 2

您可以在标量子查询中使用count(distinct ccy):

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)
)
select id, tx_id, ccy,
(
select count(distinct D.ccy)
from cte D 
where D.tx_id = T.tx_id
) distinct_ccy
from cte T

或者您可以像下面这样使用dense_rank函数:

select id, tx_id, ccy,
dense_rank() over (partition by tx_id order by ccy) + 
dense_rank() over (partition by tx_id order by ccy desc) - 1 as distinct_ccy
from cte T
order by id

看到演示