From: | AJ Welch <awelch0100(at)gmail(dot)com> |
---|---|
To: | "Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: FW: SQL rolling window without aggregation |
Date: | 2014-12-08 00:42:08 |
Message-ID: | CAO-RzRK6E-y0aPiFb-kpdM+DmHVCLeB49gEcWZyqwbe9HZsjDg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)Huang(at)au(dot)experian(dot)com>
wrote:
> 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(at)postgresql(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2014-12-08 01:05:15 | Re: Strange behavior in generate_series(date, date, interval) with DST |
Previous Message | Huang, Suya | 2014-12-07 23:13:35 | FW: SQL rolling window without aggregation |