Window running function except current row

Question

I have a theoretical question, so I'm not interested in alternative solutions. Sorry.

Q: Is it possible to get the window running function values for all previous rows, except current?

For example:

with
  t(i,x,y) as (
    values
      (1,1,1),(2,1,3),(3,1,2),
      (4,2,4),(5,2,2),(6,2,8)
    )
select
  t.*,
  sum(y) over (partition by x order by i) - y as sum,
  max(y) over (partition by x order by i) as max,
  count(*) filter (where y > 2) over (partition by x order by i) as cnt
from
  t;

Actual result is

 i | x | y | sum | max | cnt 
---+---+---+-----+-----+-----
 1 | 1 | 1 |   0 |   1 |   0
 2 | 1 | 3 |   1 |   3 |   1
 3 | 1 | 2 |   4 |   3 |   1
 4 | 2 | 4 |   0 |   4 |   1
 5 | 2 | 2 |   4 |   4 |   1
 6 | 2 | 8 |   6 |   8 |   2
(6 rows)

I want to have max and cnt columns behavior like sum column, so, result should be:

 i | x | y | sum | max | cnt 
---+---+---+-----+-----+-----
 1 | 1 | 1 |   0 |     |   0
 2 | 1 | 3 |   1 |   1 |   0
 3 | 1 | 2 |   4 |   3 |   1
 4 | 2 | 4 |   0 |     |   0
 5 | 2 | 2 |   4 |   4 |   1
 6 | 2 | 8 |   6 |   4 |   1
(6 rows)

It can be achieved using simple subquery like

select t.*, lag(y,1) over (partition by x order by i) as yy from t

but is it possible using only window function syntax, without subqueries?

Answer 1

Yes, you can. This does the trick:

with
  t(i,x,y) as (
    values
      (1,1,1),(2,1,3),(3,1,2),
      (4,2,4),(5,2,2),(6,2,8)
    )
select
  t.*,
  sum(y) over w as sum,
  max(y) over w as max,
  count(*) filter (where y > 2) over w as cnt
from t
window w as (partition by x order by i
             rows between unbounded preceding and 1 preceding);

The frame_clause selects just those rows from the window frame that you are interested in.

Note that in the sum column you'll get null rather than 0 because of the frame clause: the first row in the frame has no row before it. You can coalesce() this away if needed.

SQLFiddle