如何高效返回属于出现频率Top N的元素的行

问题背景 #

给定如下表结构及数据：

CREATE TABLE test ( id INT, usr CHAR, age INT );
INSERT INTO test (id, usr, age) VALUES (1, 'a', 10); 
INSERT INTO test (id, usr, age) VALUES (2, 'a', 29); 
INSERT INTO test (id, usr, age) VALUES (3, 'a', 12); 
INSERT INTO test (id, usr, age) VALUES (4, 'b', 6); 
INSERT INTO test (id, usr, age) VALUES (5, 'b', 5); 
INSERT INTO test (id, usr, age) VALUES (6, 'b', 4); 
INSERT INTO test (id, usr, age) VALUES (7, 'c', 8); 
INSERT INTO test (id, usr, age) VALUES (8, 'c', 18); 
INSERT INTO test (id, usr, age) VALUES (9, 'd', 12);

需求：仅返回usr出现频率Top N的元素对应的所有行，当N=2时，预期结果为：

[(1, 'a', 10), (2, 'a', 29), (3, 'a', 12), (4, 'b', 6), (5, 'b', 5), (6, 'b', 4)]

要求结果以表形式返回，适配数据库：DuckDB、PostgreSQL、BigQuery。

现有方案的问题 #

原使用GROUP BY结合INNER JOIN的写法如下：

select *
from <MY TABLE> as t1
inner join (
    select usr, row_number() over() as usr_rank
    from (
        select 
            usr, 
            count(*) as usr_cnt
        from <MY TABLE>
        group by usr
        order by usr_cnt desc
        limit 10
    )
) as t2
on t1.usr = t2.usr

该写法需要多次扫描表并执行关联操作，效率较低。

尝试的窗口函数写法存在语法错误：

select
    *,
from (
    select
        *,
        row_number() over ( partition by usr order by count(*) desc) as usr_rank
    from <MY TABLE>
)
where usr_rank < N

系统报错原因：窗口函数的OVER子句中不能直接使用聚合函数count(*)，且逻辑上也无法按分区大小排名。

优化解决方案 #

利用嵌套窗口函数实现一次扫描表完成计算，效率更高，且结果直接为表格式：

SELECT id, usr, age
FROM (
    SELECT 
        *,
        -- 计算每个usr的出现次数
        COUNT(*) OVER (PARTITION BY usr) AS usr_cnt,
        -- 按出现次数从高到低排名，可根据需求选择排名函数
        DENSE_RANK() OVER (ORDER BY COUNT(*) OVER (PARTITION BY usr) DESC) AS usr_rank
    FROM test
) AS ranked
WHERE usr_rank <= 2; -- 替换为目标N值

关键说明 #

1. 窗口函数逻辑：

   • COUNT(*) OVER (PARTITION BY usr)：计算每个usr分组的总行数，即该用户的出现频率。
   • 排名函数选择：
     • DENSE_RANK()：如果多个用户出现频率相同，会获得相同排名，且后续排名不跳跃（如两个用户并列第1，下一个用户排名为2）。
     • RANK()：多个用户并列时，后续排名会跳跃（如两个用户并列第1，下一个用户排名为3）。
     • ROW_NUMBER()：强制给每个用户分配唯一排名，即使频率相同，适合严格取Top N不并列的场景。

2. 数据库兼容性：

   • DuckDB：完全支持该嵌套窗口函数写法。
   • PostgreSQL：原生支持嵌套窗口函数，无兼容问题。
   • BigQuery：同样支持该语法，可直接执行。

执行结果（N=2） #

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