分析函数可以引用窗口内的多行吗？
给定一个表：
ID VALUE
-- -----
1 1
2 2
3 3
4 4
我想计算如下：
ID VALUE SUM
-- ----- ---
1 1 40 -- (2-1)*2 + (3-1)*3 + (4-1)*4 + (5-1)*5
2 2 26 -- (3-2)*3 + (4-2)*4 + (5-2)*5
3 3 14 -- (4-3)*4 + (5-3)*5
4 4 5 -- (5-4)*5
5 5 0 -- 0
其中每行的 SUM 是每个后续行的值之和乘以后续行与当前行的值之间的差。
我可以从这样的事情开始：
CREATE TABLE x(id int, value int);
INSERT INTO x VALUES(1, 1);
INSERT INTO x VALUES(2, 2);
INSERT INTO x VALUES(3, 3);
INSERT INTO x VALUES(4, 4);
INSERT INTO x VALUES(5, 5);
SELECT id, value
,SUM(value) OVER(ORDER BY id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS sum
FROM x;
id | value | sum
----+-------+-----
1 | 1 | 14
2 | 2 | 12
3 | 3 | 9
4 | 4 | 5
5 | 5 |
(5 rows)
每行都有所有后续行的总和。但更进一步来说，我真的想要这样的伪代码：
SELECT id, value
,SUM( (value - FIRST_ROW(value)) * value )
OVER(ORDER BY id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS sum
FROM x;
但这是无效的。这就是问题的关键：有没有办法在分析函数的窗口中引用多行？或者有不同的方法来解决这个问题？上面的例子是人为的。我实际上正在玩另一篇文章中的一个有趣的谜题 Rollup查询导致我遇到了这个问题。我正在 Postgresql 9.1 中尝试这个，但不受限于此。
Given a table with:
ID VALUE
-- -----
1 1
2 2
3 3
4 4
I would like to compute something like this:
ID VALUE SUM
-- ----- ---
1 1 40 -- (2-1)*2 + (3-1)*3 + (4-1)*4 + (5-1)*5
2 2 26 -- (3-2)*3 + (4-2)*4 + (5-2)*5
3 3 14 -- (4-3)*4 + (5-3)*5
4 4 5 -- (5-4)*5
5 5 0 -- 0
Where the SUM on each row is the sum of the values of each subsequent row multiplied by the difference between the value of the subsequent row and the current row.
I could start with something like this:
CREATE TABLE x(id int, value int);
INSERT INTO x VALUES(1, 1);
INSERT INTO x VALUES(2, 2);
INSERT INTO x VALUES(3, 3);
INSERT INTO x VALUES(4, 4);
INSERT INTO x VALUES(5, 5);
SELECT id, value
,SUM(value) OVER(ORDER BY id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS sum
FROM x;
id | value | sum
----+-------+-----
1 | 1 | 14
2 | 2 | 12
3 | 3 | 9
4 | 4 | 5
5 | 5 |
(5 rows)
where each row has the sum of all subsequent rows. But to take it further, I would really want something like this pseudo code:
SELECT id, value
,SUM( (value - FIRST_ROW(value)) * value )
OVER(ORDER BY id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS sum
FROM x;
But this is not valid. And that is the crux of the question: is there a way to reference multiple rows in the window of an analytic function? Or a different way to approach this? The example above is contrived. I was actually playing with an interesting puzzle from another post Rollup Query which led me to this problem. I am trying this in Postgresql 9.1, but not bound to that.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不太确定我是否完全理解了您的要求，但您想要的查询类似于
希望有帮助。
Not quite sure if I've understood your requirement exactly here, but the query that you want is something like
Hope that helps.