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_numfrom 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_numfrom test.test2)select    province,   num,  all_num,  curr_num/all_numfrom 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

 

postgresql 視窗函數