Postgresql window function 第二篇

    上一篇很好的解释了什么是窗口函数，并且举了一个最简单的例子，帮助理解什么是窗口函数。接下来我们来更深入的理解postgresql的窗口函数。还是借用上一篇新建的表来讲解。
   

点击(此处)折叠或打开

1. postgres=# \\d empsalary
   
2.         资料表 \"public.empsalary\"
   
3.   栏位 | 型别 | 修饰词
   
4. ---------+-----------------------+--------
   
5.  depname | character varying(20) |
   
6.  empno | character varying(20) |
   
7.  salary | integer |

   EXAMPLE 2：
   function name over ( partition by column)   这里的partition by 子句用于对行进行分组的。
  

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, sum(salary) over(partition by depname)  from empsalary;
2.  depname | empno | salary | sum
   
3. ----------+-------+--------+-------
   
4.  develop | 11 | 5200 | 13900
   
5.  develop | 7 | 4200 | 13900
   
6.  develop | 9 | 4500 | 13900
   
7.  personel | 5 | 3500 | 25400
   
8.  personel | 6 | 6500 | 25400
   
9.  personel | 12 | 6500 | 25400
   
10.  personel | 15 | 8900 | 25400
    
11. (7 行记录)

    由于这次对窗口进行了限制，每一行只能看见自己的分组，所以develop组的sum都是一样的，而personel组的sum是一样的。

   EXAMPLE3：
   function name over (order by column)
   

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, sum(salary) over(order by salary) from
   
2.  empsalary;
   
3.  depname | empno | salary | sum
   
4. ----------+-------+--------+-------
   
5.  personel | 5 | 3500 | 3500
   
6.  develop | 7 | 4200 | 7700
   
7.  develop | 9 | 4500 | 12200
   
8.  develop | 11 | 5200 | 17400
   
9.  personel | 6 | 6500 | 30400
   
10.  personel | 12 | 6500 | 30400
    
11.  personel | 15 | 8900 | 39300
    
12. (7 行记录)

    上面的结果是按照salary的正序排列的，但是sum列显得很奇怪，好像是累积的加法，好像不是。这就取决于，到底这里的每一行从窗口看看到了什么，我们可以用以下的语句看一下。

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary,array_agg(salary) over(order by salary)
   
2.  ,sum(salary) over(order by salary) from empsalary;
   
3.  depname | empno | salary | array_agg                            | sum
   
4. ----------+-------+--------+--------------------------------------+-------
   
5.  personel | 5  | 3500     | {3500}                               | 3500
   
6.  develop  | 7  | 4200     | {3500,4200}                          | 7700
   
7.  develop  | 9  | 4500     | {3500,4200,4500}                     | 12200
   
8.  develop  | 11 | 5200     | {3500,4200,4500,5200}                | 17400
   
9.  personel | 6  | 6500     | {3500,4200,4500,5200,6500,6500}      | 30400
   
10.  personel | 12 | 6500     | {3500,4200,4500,5200,6500,6500}      | 30400
    
11.  personel | 15 | 8900     | {3500,4200,4500,5200,6500,6500,8900} | 39300
    
12. (7 行记录)

   每一行能看见的结果都在array_agg列当中可以看出，这就很容易理解为什么sum的值会变成现在像上面一样奇怪的结果吧。其实这里的

   EXAMPLE 3:
    function name over （order by column [rows | range ] between framestart and frameend）;
   rows between 子句无法独自和over一起使用，这个子句的作用也是决定了那些行可以被每一行看到。
   

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary,sum(salary) over(order by salary rows between unbounded preceding and current row) from empsalary;
2.  depname | empno | salary | sum
   
3. ----------+-------+--------+-------
   
4.  personel | 5 | 3500 | 3500
   
5.  develop | 7 | 4200 | 7700
   
6.  develop | 9 | 4500 | 12200
   
7.  develop | 11 | 5200 | 17400
   
8.  personel | 6 | 6500 | 23900
   
9.  personel | 12 | 6500 | 30400
   
10.  personel | 15 | 8900 | 39300
    
11. (7 行记录)

   现在的结果是安salary从小到大排列的，sum刚好就是累积的加法运算。这里的framestart和frameend只能从固定的三个值当中取： unbounded preceding ， current row， unbounded following。第一个表示第一行，第二个表示当前行，最后一个表示最后一行，而且他们顺序也不能调换。这能是 unbounded preceding and current row 或者是current row and unbounded following 再或者就是  unbounded preceding and  unbounded following。
 至此所有的部分都已经讲完了，在复杂的用法就是把他们结合起来使用。相信大家能很好的使用窗口函数。 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30010989/viewspace-1345154/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30010989/viewspace-1345154/