PostgreSQL 的 Window Functions (八)

这节来介绍PostgreSQL的一个特性，叫"Window Functions"，这个功能有点类似于"group by"，它很强大，能够实现意想不到的功能。而且这个功能不是所有数据库系统都有的，例如MySQL就没有。它结合统计图来用更为强大。

它的官方定义是这样的："A window function performs a calculation across a set of table rows that are somehow related to the current row. "。

说那么多没什么用，直接看例子。

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

假如把上面的 sql 语句中的"OVER (PARTITION BY depname)"改成"GROUP BY depname"的话，结果就是只有三条记录，它会根据 depname(develop、personnel、sales) 合并成三条的。

depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
(10 rows)

在某些场合下，这种肯定没有"Window Functions"，因为 salary 和 empno 只有一个了。假如我们需要输出 salary 和 empno 的话，只能再查一次，然后用程序循环出来，只能这样组合了。在实际的开发中，是遇到过这种问题的。而PostgreSQL默认就提供了"Window Function"机制来解决这一问题，很方便。

还支持排序。

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

 depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

在Rails中可以这样用

Article.find_by_sql("SELECT *, rank() OVER (ORDER BY group_id DESC) FROM articles")

结合一些图表统计的库，比如 hightcharts，可以实现类似这样的效果。