greenplum 分析函数
开窗函数（每一行返回结果）区分于聚合函数，聚合函数统计的是汇总后的结果，没有明细数据，如果统既要计结果又要明细，开窗函数就比较简单
tutorial=> select * from test002 order by name;
id | name
----+------
1 | gh
1 | gh2
2 | gh2
4 | gh2
2 | gh2
3 | gh3
3 | gh4
(7 rows)
tutorial=> select count(1),name from test002 group by name;
count | name
-------+------
1 | gh4
1 | gh
4 | gh2
1 | gh3
(4 rows)
按照姓名排序,rank():相同者为相同数据，row_number():相同者顺序也是不同
tutorial=> select id,name ,rank() over ( partition by name order by name desc), row_number() over (
partition by name order by name desc) from test002; id | name | rank | row_number
----+------+------+------------
3 | gh3 | 1 | 1
1 | gh | 1 | 1
2 | gh2 | 1 | 1
4 | gh2 | 1 | 2
2 | gh2 | 1 | 3
1 | gh2 | 1 | 4
3 | gh4 | 1 | 1
(7 rows)
tutorial=> select id, name , sum(id) over () sum1,
tutorial-> sum(id) over (order by id) sum2,
tutorial-> sum(id) over ( partition by name) sum3,
tutorial-> sum(id) over ( partition by name order by name desc) sum4
tutorial-> from test002;
id | name | sum1 | sum2 | sum3 | sum4
----+------+------+------+------+------
1 | gh2 | 16 | 2 | 9 | 9
1 | gh | 16 | 2 | 1 | 1
2 | gh2 | 16 | 6 | 9 | 9
2 | gh2 | 16 | 6 | 9 | 9
3 | gh4 | 16 | 12 | 3 | 3
3 | gh3 | 16 | 12 | 3 | 3
4 | gh2 | 16 | 16 | 9 | 9
(7 rows)
sum1为所有之和，
sum2不能理解
sum3为按照姓名排序的结果只
sum4..
grouping sets对于组合字段的排序,暂时了解一下
