FW: SQL rolling window without aggregation
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@postgresql.org'
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
https://www.linkedin.com/in/ajw0100
On Sun, Dec 7, 2014 at 3:13 PM, Huang, Suya <Suya.Huang@au.experian.com>
wrote:
Show quoted text
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@postgresql.org'
*Subject:* [SQL] rolling window without aggregationHi 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
Huang, Suya wrote
It seems like it's not been sent to the SQL group, so I'm trying with this
group.
Asked and answered...online archives follow
http://postgresql.nabble.com/rolling-window-without-aggregation-td5829344.html#a5829345
/messages/by-id/1417764928965-5829345.post@n5.nabble.com
David J.
--
View this message in context: http://postgresql.nabble.com/FW-GENERAL-SQL-rolling-window-without-aggregation-tp5829528p5829564.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G Johnston
Sent: Monday, December 08, 2014 1:18 PM
To: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] SQL rolling window without aggregation
Huang, Suya wrote
It seems like it's not been sent to the SQL group, so I'm trying with
this group.
Asked and answered...online archives follow
http://postgresql.nabble.com/rolling-window-without-aggregation-td5829344.html#a5829345
/messages/by-id/1417764928965-5829345.post@n5.nabble.com
David J.
============================================================================================================
Thanks Dave. I was trying to avoid UNION...
--
View this message in context: http://postgresql.nabble.com/FW-GENERAL-SQL-rolling-window-without-aggregation-tp5829528p5829564.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Monday, December 8, 2014, Huang, Suya <Suya.Huang@au.experian.com> wrote:
-----Original Message-----
From: pgsql-general-owner@postgresql.org <javascript:;> [mailto:
pgsql-general-owner@postgresql.org <javascript:;>] On Behalf Of David G
Johnston
Sent: Monday, December 08, 2014 1:18 PM
To: pgsql-general@postgresql.org <javascript:;>
Subject: Re: FW: [GENERAL] SQL rolling window without aggregationHuang, Suya wrote
It seems like it's not been sent to the SQL group, so I'm trying with
this group.Asked and answered...online archives follow
http://postgresql.nabble.com/rolling-window-without-aggregation-td5829344.html#a5829345
/messages/by-id/1417764928965-5829345.post@n5.nabble.com
David J.
============================================================================================================
Thanks Dave. I was trying to avoid UNION...
Instead of avoiding things get something that works then ask whether there
is a better way to do things. At least that way you can provide a working
query that others can look at and know exactly what you need.
In this case you have a single table and want the result to have more rows
than the input - I'm not sure how anything but a union will accomplish that
goal. You have to generate those rows somehow. There may be better ways
of looking at your problem but you only gave us a toy model to play with.
David J.