20160511 GreenPlum6 分析函数之开窗函数、聚合函数与grouping sets用法

1 开窗函数

   测试数据为：

   部门            number           工资

    发展部        8                      6000
    发展部        10                    5200
    销售部        1                      5000
    销售部        3                      4800
    发展部        7                      4200
    销售部        4                      4800
    发展部        9                      4500
    私立部        5                      3500
    私立部        2                      3900
    发展部       11                     5200

 

   用rank()函数，同样工资排名一样，但是排名号会往后窜：
    #select 部门,number,工资,rank() over(partition by 部门 order by 工资 desc) from lianxi;

    发展部       8                       6000                 1
    发展部      10                      5200                 2
    发展部      11                      5200                 2
    发展部       9                       4500                 4
    发展部       7                       4200                 5
    私立部       2                       3900                 1
    私立部       5                       3500                 2
    销售部       1                       5000                 1
    销售部       3                       4800                 2
    销售部       4                       4800                 2

    用row_number()函数，同样工资排名会不同：

    #select 部门,number,工资,row_number() over(partition by 部门 order by 工资 desc) from lianxi;

    销售部      1                       5000                 1
    销售部      3                       4800                 2
    销售部      4                       4800                 3
    发展部      8                       6000                 1
    发展部      11                     5200                 2
    发展部      10                     5200                 3
    发展部      9                       4500                 4
    发展部      7                       4200                 5
    私立部      2                       3900                 1
    私立部      5                       3500                 2

2 聚合函数

    所有人工资的总和：这里工资的类型要为numeric

    #select *,sum(工资) over() sum1 from lianxi;

    发展部        10          5200        47100
    私立部         5           3500        47100
    发展部         9           4500        47100
    销售部         1           5000        47100
    发展部         7           4200        47100
    发展部        11          5200        47100
    发展部        8            6000        47100
    销售部        4            4800        47100
    销售部        3            4800        47100
    私立部        2            3900        47100

   

    所有人工资的累加，先排序，然后第一个工资不动，下一个一次累加：

    # select *,sum(工资) over(order by 工资) sum2 from lianxi;

    私立部         5          3500        3500
    私立部         2          3900        7400
    发展部         7          4200        11600
    发展部         9          4500        16100
    销售部         3          4800         25700
    销售部         4          4800         25700
    销售部         1          5000         30700
    发展部         11        5200         41100
    发展部         10        5200         41100
    发展部         8          6000         47100

 

     每个部门的工资总和：

     # select *,sum(工资) over(partition by 部门) sum3 from lianxi;

     发展部        9           4500         25100
     发展部        8           6000         25100
     发展部      10           5200         25100
     发展部      11           5200         25100
     发展部        7           4200         25100
     销售部        3           4800         14600
     销售部        1           5000         14600
     销售部        4           4800         14600
     私立部        2           3900          7400
     私立部        5           3500          7400

 

     部门内排序后递增，但是这里没有明白为什么工资相同的值不进行累加：

     #select *,sum(工资) over(partition by 部门 order by 工资) sum4 from lianxi;

    私立部        5             3500            3500
    私立部        2             3900            7400
    销售部        3             4800            9600
    销售部        4             4800            9600
    销售部        1             5000            14600
    发展部        7             4200            4200
    发展部        9             4500            8700
    发展部        10           5200           19100
    发展部        11           5200           19100
    发展部        8             6000           25100

3 grouping sets

   对于grouping sets 的理解，看书时没看懂，但是自己用数据跑完一遍的时候我明白了这个用法的强大，它先以工资进行分组，再以部门进行分组，很方便：

    #select 部门,工资,count(1) from lianxi group by grouping sets(部门,(部门,工资)) order by 工资;

     私立部        3500           1
     私立部        3900           1
     发展部        4200           1
     发展部        4500           1
     销售部        4800           2
      销售部       5000           1
      发展部       5200           2
      发展部       6000           1
      私立部       2
      销售部       3
      发展部       5