agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedrolling window without aggregation
2+ messages / 2 participants
[nested] [flat]
* rolling window without aggregation
@ 2014-12-05 07:25 Huang, Suya <Suya.Huang@au.experian.com>
2014-12-05 07:35 ` Re: rolling window without aggregation David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Huang, Suya @ 2014-12-05 07:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: rolling window without aggregation
2014-12-05 07:25 rolling window without aggregation Huang, Suya <Suya.Huang@au.experian.com>
@ 2014-12-05 07:35 ` David G Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G Johnston @ 2014-12-05 07:35 UTC (permalink / raw)
To: 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2014-12-05 07:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-12-05 07:25 rolling window without aggregation Huang, Suya <Suya.Huang@au.experian.com>
2014-12-05 07:35 ` David G Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox