partition函数在oracle和presto查询中都能使用,mysql中是不能用的。。
dt | category | cnt |
0606 | aa | 2 |
0606 | bb | 3 |
0607 | aa | 6 |
现在求类别占比,
select a.dt,a.category,a.cnt,sum(a.cnt) over (partition by a.dt) as cnt_sum,
case when sum(a.cnt) over (partition by a.dt) >0 then round(a.cnt/(sum(a.cnt) over (partition by a.dt))*100,2) else 0 end as pnt
from (
select dt,category,cnt from table
) a