sql - 分析函数可以引用窗口中的多行吗？

Question

给定一个表:

    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 Query这导致我遇到了这个问题。我正在 Postgresql 9.1 中尝试此操作，但不受此限制。

Answer 1

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

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

希望对您有所帮助。