running sum with window function

Question

I have the following data in a table:

col1
---
1
2
5
9
10

I want to update col2 in the table with the running sum of the difference between col1 and the previous value of col1 minus 1

col2 = col2.prev + col1 - col1.prev - 1

The result would be:

col1  |  col2
--------------
  1   |  0
  2   |  0
  5   |  2
  9   |  5
 10   |  5

I tried using a window function:

SELECT sum(col1 - lag(col1) OVER (ORDER BY col1) - 1) AS col2 FROM table1

But this is not allowed - ERROR: aggregate function calls cannot contain window function calls

Is there another way I can accomplish this? I know I could easily write a function to loop through the rows but I get the impression from what I've read that this method is not efficient and discouraged in most cases. Please correct me if I have the wrong impression.

Answer 1

ERROR: aggregate function calls cannot contain window function calls

This error message is displayed because it is not permitted to apply an aggregate function on a column generated through a windowed expression. Nor is it permitted to apply a second windowed expression. The solution is to simply wrap the result in a cte & apply the second windowed expression in a subsequent select statement.

WITH mytable(col1) AS (
VALUES (1), (2), (5), (9), (10)
)
, lagdiff AS (
  SELECT
    col1
  , COALESCE(col1 - lag(col1) OVER (ORDER BY col1) - 1, 0) col2_
  FROM mytable
)
SELECT
  col1
, SUM(col2_) OVER (ORDER BY col1) col2
FROM lagdiff

Produces Output:

 col1 | col2
------+------
    1 |    0
    2 |    0
    5 |    2
    9 |    5
   10 |    5
(5 rows)