From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: rolling window without aggregation |
Date: | 2014-12-05 07:35:28 |
Message-ID: | 1417764928965-5829345.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Huang, Suya wrote
> 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
Use the lead() function to create a second column. Then write a UNION ALL
query to covert the two columns into one.
David J.
--
View this message in context: http://postgresql.nabble.com/rolling-window-without-aggregation-tp5829344p5829345.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Dudgeon | 2014-12-07 22:59:38 | querying with index on jsonb slower than standard column. Why? |
Previous Message | Huang, Suya | 2014-12-05 07:25:17 | rolling window without aggregation |