agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: rolling window without aggregation
Date: Fri, 5 Dec 2014 00:35:28 -0700 (MST)
Message-ID: <1417764928965-5829345.post@n5.nabble.com> (raw)
In-Reply-To: <D83E55F5F4D99B4A9B4C4E259E6227CD1F66FC6A@AUX1EXC02.apac.experian.local>
References: <D83E55F5F4D99B4A9B4C4E259E6227CD1F66FC6A@AUX1EXC02.apac.experian.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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

view thread (2+ messages)

Message-ID: <1417764928965-5829345.post@n5.nabble.com>
Permalink:  ../1417764928965-5829345.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1417764928965-5829345.post@n5.nabble.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: david.g.johnston@gmail.com
  Subject: Re: rolling window without aggregation
  In-Reply-To: <1417764928965-5829345.post@n5.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox