postgres文档(
http://www.postgresql.org/docs/9.1/static/tutorial-window.html)讨论了窗口函数.
在他们的例子中：
SELECT salary,sum(salary) OVER (ORDER BY salary) FROM empsalary;
重复处理如下：
salary | sum --------+------- 3500 | 3500 3900 | 7400 4200 | 11600 4500 | 16100 4800 | 25700 <-- notice duplicate rows have the same value 4800 | 25700 <-- SAME AS ABOVE 5000 | 30700 5200 | 41100 <-- same as next line 5200 | 41100 <-- 6000 | 47100 (10 rows)
你如何做同样的事情,以便重复的行没有给出相同的值？换句话说,我希望这个表看起来如下：
salary | sum --------+------- 3500 | 3500 3900 | 7400 4200 | 11600 4500 | 16100 4800 | 20900 <-- not the same as the next line 4800 | 25700 <-- 5000 | 30700 5200 | 35900 <-- not the same as the next line 5200 | 41100 <-- 6000 | 47100 (10 rows)
使用frame子句中的行而不是默认范围
select
salary,sum(salary) over (
order by salary
rows unbounded preceding
)
from empsalary
;
salary | sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 20900
4800 | 25700
5000 | 30700
5200 | 35900
5200 | 41100
6000 | 47100
http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
