带有 postgres 窗口函数的重复行 [英] Duplicate lines with postgres window functions
本文介绍了带有 postgres 窗口函数的重复行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
postgres 文档(http://www.postgresql.org/docs/9.1/static/tutorial-window.html) 讨论窗口函数.
The postgres documentation (http://www.postgresql.org/docs/9.1/static/tutorial-window.html) talks about window functions.
在他们的例子中:
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)
你如何做同样的事情,使重复的行不会被赋予相同的值?换句话说,我希望这张表如下所示:
How do you do the same thing making it so that duplicate rows are NOT given the same value? In other words, I would like this table to look as follows:
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)
推荐答案
在框架子句中使用 rows
而不是默认的 range
Use rows
in the frame clause in instead of the default range
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
这篇关于带有 postgres 窗口函数的重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文