-- 角色活越等级分布,并累加大于(含当前等级)的角色数量
select server_id,lv, os,rid,chn,num , sum(num) over(PARTITION BY server_id,os,rid,chn order by (lv)::int desc ) lv_sum
from(
SELECT server_id,cont->>'lv' lv,cont->>'platform' os,cont->>'rid' rid,cont->>'channel' chn,count(*) num
FROM "convert"."convert_data_info"
where file='player'
and (cont->>'login')::int>=1521734400
GROUP BY server_id,lv,os,rid,chn
) as t1
order by (lv)::int
https://www.postgresql.org/docs/9.1/static/tutorial-window.html
例子,求当天活跃角色等级分布情况,并求出当前等级之上(含当前等级)的角色数量报表
sum(num) over()求和
PARTITION BY 根据分区的条件
order by XX 从这里开始求和
部分结果...
23 1 editor 1 uc 6 12
23 1 win 1 uc 2 4
119 1 win 1 uc 4 7
4 1 editor 1 uc 1 3
18 1 editor 1 uc 1 8
119 1 unkown 1 uc 1 1
119 1 editor 1 uc 4 35
8 1 editor 1 uc 13 113
9 1 android 2 uc 1 1
8 1 editor 3 uc 4 17
9 1 win 3 uc 1 1
23 1 editor 2 uc 1 4
23 1 win 3 uc 3 3
1 1 win 2 uc 2 2
8 1 win 1 uc 7 9
23 1 editor 3 uc 1 3
23 1 win 2 uc 2 3
8 1 unkown 1 uc 1 1
9 1 editor 1 uc 8 50
9 1 editor 2 uc 1 14
9 1 editor 3 uc 2 3
8 1 android 1 uc 1 2
9 1 unkown 3 uc 1 1
24 1 win 3 uc 2 3
8 1 editor 2 uc 3 16
24 1 win 1 uc 5 31
24 1 unkown 1 uc 2 2
24 1 editor 3 uc 1 2
119 1 win 2 uc 2 2
9 1 win 1 uc 3 25
9 1 win 2 uc 1 11
24 1 editor 1 uc 9 33
8 2 editor 4 uc 1 2
24 2 editor 1 uc 2 24
9 2 win 1 uc 1 22
9 2 win 2 uc 2 10
23 2 win 2 uc 1 1
8 2 editor 2 uc 1 13
8 2 editor 1 uc 9 100
24 2 win 2 uc 1 9
8 3 editor 1 uc 1 91
24 4 win 1 uc 1 26
8 4 editor 1 uc 6 90
8 5 editor 1 uc 3 84
24 5 editor 3 uc 1 1
8 6 editor 2 uc 1 12
8 7 editor 1 uc 3 81
8 7 editor 2 uc 1 11
....
9 225 editor 2 uc 1 4
8 227 editor 1 uc 1 9
119 230 editor 1 uc 1 3
4 230 editor 2 uc 1 1
4 230 win 1 uc 1 1
8 245 editor 1 uc 2 8
9 250 editor 1 uc 7 8
119 250 editor 1 uc 2 2
9 250 editor 2 uc 2 3
24 250 win 1 uc 1 1
119 250 editor 2 uc 2 3
4 250 editor 1 uc 1 1
119 250 win 1 uc 1 1
8 250 editor 1 uc 3 6
9 250 win 1 uc 1 1
9 251 editor 2 uc 1 1
8 252 editor 1 test 1 1
24 260 editor 1 uc 1 1
8 260 win 1 uc 1 1
8 270 editor 1 uc 2 3
9 300 editor 1 uc 1 1
119 300 editor 2 uc 1 1
8 300 editor 1 uc 1 1