如何在SQL中不同字段的组合中计算模式

Question

我有以下表：

Broker      Currency    Ticker  Rate
Barclays    USD         Apple    0.10 
Barclays    USD         IBM      0.10 
Barclays    USD         Amazon   1.00 
Barclays    CAD         Col      0.50 
Barclays    CAD         Lyft     0.50 
Merrill     USD         Apple    0.20 
Merrill     USD         IBM      0.20 
Merrill     USD         Amazon   0.20 
Merrill     USD         Tesla    0.30 
Merrill     MXN         ticker1  5.30 
Merrill     MXN         ticker2  5.30 
Merrill     MXN         ticker3  2.60 

我需要在每个经纪货币字段上计算模式。输出：

Broker      Currency    Mode
Barclays    USD         0.1
Barclays    CAD         0.5
Merrill     USD         0.2
Merrill     MXN         5.3

在下面尝试：

select Count(*) as Frequency, rate as mode, date, name as Broker , ccy
FROM t
group by mode, date, Broker , ccy
order by Frequency

我不确定一旦找到频率，如何找到该模式。

Answer 1

使用窗口函数：

select t.*
from (select name, ccy, rate,
             count(*) as frequency,
             row_number() over (partition by broker, currency order by count(*) desc) as seqnum
      from t
      group by name, ccy, rate
     ) t
where seqnum = 1;