It seems like it’s not been sent to the SQL group, so I’m trying with this group.
Thanks,
Suya
From: Huang, Suya
Sent: Friday, December 05, 2014 6:25 PM
To: 'pgsql-sql@xxxxxxxxxxxxxx'
Subject: [SQL] rolling window without aggregation
Hi SQL experts,
I’ve got a question here, is that possible to implement a window function without aggregation? Any SQL could get below desired result?
For example:
Table input
date | id
------------+--------
2014-04-26 | A
2014-05-03 | B
2014-05-10 | C
2014-05-17 | D
2014-05-24 | E
2014-05-31 | F
Expected output, use 2 week roll up as an example:
date | id
------------+--------
2014-04-26 | A
2014-05-03 | A
2014-05-03 | B
2014-05-10 | B
2014-05-10 | C
2014-05-17 | C
2014-05-17 | D
2014-05-24 | D
2014-05-24 | E
2014-05-31 | E
2014-05-31 | F
Thanks,
Suya
I believe this can be accomplished with lead() and union: http://sqlfiddle.com/#!15/521d5/7
Thanks,
AJ
On Sun, Dec 7, 2014 at 3:13 PM, Huang, Suya <Suya.Huang@xxxxxxxxxxxxxxx> wrote:
