SQL-英雄出场排名top3的出场次数及出场率

2022-12-07 约 115 字预计阅读 1 分钟

求英雄的出场排名 top3 的出场次数及出场率。names 代表英雄名字。

id	names
1	aa,bb,cc,dd,ee
2	aa,bb,ff,ww,qq
3	aa,cc,rr,yy
4	aa,bb,dd,oo,pp

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


select
    name,
    cnt,
    cast(cnt/all_cnt as decimal(3,1)) as ratio
from 
(
    select
        name,
        cnt,
        sum(cnt) over () as all_cnt,
        dense_rank() over (order by cnt desc) as rn
    from 
    (
        select
            name,
            count(1) as cnt
        from tmp_2022120501 lateral view explode(split(my_str, ',')) t as name
        group by 1
    ) a 
) a 
where rn <= 3;