SQL 函数为 Group By 中的多个列返回 "most common value"

Question

我正在寻找最简单的方法来返回分组的 select 语句的多列结果中最常见的值。我在网上找到的所有内容都指向选择中单个项目的 RANK 或在 GROUP BY 之外单独处理每一列。

示例数据:

SELECT 100 as "auser", 
'A' as "instance1", 'M' as "instance2" 
union all select 100, 'B', 'M' 
union all select 100,'C', 'N' 
union all select 100, 'B', 'O'
union all select 200,'D', 'P' 
union all select 200, 'E', 'P' 
union all select 200,'F', 'P' 
union all select 200, 'F', 'Q'

示例数据结果:

auser   instance1   instance2
100     A           M
100     B           M
100     C           N
100     B           O
200     D           P
200     E           P
200     F           P
200     F           Q

查询逻辑(我在脑海中如何看待它):

SELECT auser, most_common(instance1), most_common(instance2)
FROM datasample
GROUP BY auser;

期望的结果:

100     B           M
200     F           P

Answer 1

解决此问题的方法是使用嵌套窗口函数。最里面的子查询计算每列的计数。下一个子查询对这些进行排名(使用 row_number() )。外部查询然后使用条件聚合来获取您想要的结果:

select auser, MAX(case when seqnum1 = 1 then instance1 end),
       MAX(case when seqnum2 = 1 then instance2 end)
from (select t.*,
             ROW_NUMBER() over (partition by auser order by cnt1 desc) as seqnum1,
             ROW_NUMBER() over (partition by auser order by cnt2 desc) as seqnum2
      from (select t.*,
                   count(*) over (partition by auser, instance1) as cnt1,
                   COUNT(*) over (partition by auser, instance2) as cnt2
            from t
           ) t
     ) t
group by auser