Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M38014=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1XwnQR-0005Gw-Qs
 for pgsql-sql@arkaria.postgresql.org; Fri, 05 Dec 2014 07:35:39 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M38014=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1XwnQR-0007f8-Bb
 for pgsql-sql@arkaria.postgresql.org; Fri, 05 Dec 2014 07:35:39 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <david.g.johnston@gmail.com>)
 id 1XwnQQ-0007f1-Bo
 for pgsql-sql@postgresql.org; Fri, 05 Dec 2014 07:35:38 +0000
Received: from mwork.nabble.com ([162.253.133.43])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <david.g.johnston@gmail.com>) id 1XwnQM-0006X3-2D
 for pgsql-sql@postgresql.org; Fri, 05 Dec 2014 07:35:36 +0000
Received: from msam.nabble.com (unknown [162.253.133.85])
 by mwork.nabble.com (Postfix) with ESMTP id 1E63BCB08BC
 for <pgsql-sql@postgresql.org>; Thu,  4 Dec 2014 23:35:30 -0800 (PST)
Date: Fri, 5 Dec 2014 00:35:28 -0700 (MST)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1417764928965-5829345.post@n5.nabble.com>
In-Reply-To: 
 <D83E55F5F4D99B4A9B4C4E259E6227CD1F66FC6A@AUX1EXC02.apac.experian.local>
References: 
 <D83E55F5F4D99B4A9B4C4E259E6227CD1F66FC6A@AUX1EXC02.apac.experian.local>
Subject: Re: rolling window without aggregation
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -0.3 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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