Sum by window function

Question

I`m trying to count sum by window function of PostgresQL.

INSERT INTO t VALUES
('2012-06-21 05:20:00',12),
('2012-06-21 05:21:00',14),
('2012-06-21 05:22:00',10),
('2012-06-21 05:23:00',20),
('2012-06-21 05:24:00',25),
('2012-06-21 05:25:00',30),
('2012-06-21 05:26:00',10);

SELECT dt,
cnt,
sum(cnt) OVER (order by dt ROWS BETWEEN 3 preceding AND 0 preceding)
FROM t
ORDER BY dt;

I want to count for each 3 rows and expect to get such a result and i get this

2012-06-21 05:21:00 14  26
2012-06-21 05:22:00 10  36
2012-06-21 05:23:00 20  56
2012-06-21 05:24:00 25  69
2012-06-21 05:25:00 30  85
2012-06-21 05:26:00 10  85

Tell me, please, it is possible to get result like

2012-06-21 05:20:00 12  0
2012-06-21 05:21:00 14  0
2012-06-21 05:22:00 10  36
2012-06-21 05:23:00 20  0
2012-06-21 05:24:00 25  0
2012-06-21 05:25:00 30  75
2012-06-21 05:26:00 10  0

by window function? Thanks!

Gordon Linoff · Accepted Answer · 2019-12-13 13:44:36Z

1

Just use conditional logic:

SELECT dt, cnt,
       (case when row_number() over (order by dt) % 3 = 0
             then sum(cnt) OVER (order by dt ROWS BETWEEN 3 preceding AND 0 preceding)
        else 0
       end)
FROM t
ORDER BY dt;

answered Dec 13, 2019 at 13:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frederic Over a year ago

@gordon-linoff, dont need to use the between, only " ROWS 3 preceding " can do the job. dbfiddle.uk/…

Gordon Linoff Over a year ago

@Frederic . . . I am aware of that shortcut. I prefer to be explicit about window frames.

Collectives™ on Stack Overflow

Sum by window function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related