PostgreSQL window functions

Often encounter an application scenario that summarizes, compares, and sorts the contents of some rows.

such as the data table name Test.test2

Select  from Test.test2

Get results:

1828;"Heilongjiang"137;"Heilongjiang"184;"Heilongjiang"183;"Fujian" the;"Fujian"143;"Fujian"119;"Hainan"109;"Hainan" the;"Hainan"

So I want to sort the content by province, so I need to:

Select    Province,   num,  sumover byorderbyDesc  between and all_num from Test.test2

Get results:

"Hainan"; the; the"Hainan";119;251"Hainan";109; the"Fujian";183;183"Fujian";143;326"Fujian"; the;451"Heilongjiang";1828;1828"Heilongjiang";184; -"Heilongjiang";137;2149

If you want to see the percentage of the entire province in each row, you need

 withTmp as(Selectprovince, Num,sum(num) Over(Partition byProvinceOrder  byNumdescROWSbetweenunbounded preceding and  CurrentROW) asCurr_num,sum(num) Over(Partition byProvince ROWSbetweenunbounded preceding andUnbounded following) asAll_num fromtest.test2)Selectprovince, Num, All_num, Curr_num/All_num fromTmp

The results are as follows

"Hainan"; the; the;0.36666666666666666667"Hainan";119; the;0.69722222222222222222"Hainan";109; the;1.00000000000000000000"Fujian";183;451;0.40576496674057649667"Fujian";143;451;0.72283813747228381375"Fujian"; the;451;1.00000000000000000000"Heilongjiang";1828;2149;0.85062819916240111680"Heilongjiang";184;2149;0.93624941833410888785"Heilongjiang";137;2149;1.00000000000000000000

PostgreSQL window functions