postgresql 窗口函数排序实例

经常遇到一种应用场景，将部分行的内容进行汇总、比较、排序。

比如数据表名称test.test2

select num,province from test.test2

得到结果：

1828;"黑龙江"
137;"黑龙江"
184;"黑龙江"
183;"福建"
125;"福建"
143;"福建"
119;"海南"
109;"海南"
132;"海南"

那么我希望将内容按照省份来排序，那么需要：

select 
  province, 
  num,
  sum(num) over (partition by province order by num desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as all_num
from test.test2

得到结果：

"海南";132;132
"海南";119;251
"海南";109;360
"福建";183;183
"福建";143;326
"福建";125;451
"黑龙江";1828;1828
"黑龙江";184;2012
"黑龙江";137;2149

如果还要看每行占整个省份的百分比，那么需要

with tmp as(
select 
  province, 
  num,
  sum(num) over (partition by province order by num desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as curr_num,
  sum(num) over (partition by province ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as all_num
from test.test2
)
select 
   province, 
  num,
  all_num,
  curr_num/all_num
from tmp

结果如下

"海南";132;360;0.36666666666666666667
"海南";119;360;0.69722222222222222222
"海南";109;360;1.00000000000000000000
"福建";183;451;0.40576496674057649667
"福建";143;451;0.72283813747228381375
"福建";125;451;1.00000000000000000000
"黑龙江";1828;2149;0.85062819916240111680
"黑龙江";184;2149;0.93624941833410888785
"黑龙江";137;2149;1.00000000000000000000