解析函数可以引用窗口中的多行吗？ [英] Can multiple rows within a window be referenced by an analytic function?

问题描述

给出一个带有以下内容的表：

Given a table with:

    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是每个后继行的值的总和乘以下一行与当前行的值之差。

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.

我可以从

    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;

但这是无效的。这就是问题的症结所在：是否有一种方法可以引用分析函数窗口中的多行？还是采用其他方式来解决这个问题？上面的示例是人为设计的。我实际上正在玩另一篇文章 Rollup查询导致了这个问题。我正在Postgresql 9.1中尝试此操作，但不限于此。

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.

推荐答案

不确定我是否完全理解您的要求在这里，但是您想要的查询类似于

Not quite sure if I've understood your requirement exactly here, but the query that you want is something like

select a.id, a.value, sum(( b.value - a.value ) * b.value )
from x a, x b
where a.id < b.id
group by a.id, a.value

希望有帮助。

这篇关于解析函数可以引用窗口中的多行吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！