Skip site navigation (1) Skip section navigation (2)

Re: Window function frame clause

From: David Johnston <polobo(at)yahoo(dot)com>
To: vpapavas <vicky(dot)papavas(at)gmail(dot)com>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Window function frame clause
Date: 2012-02-17 01:33:16
Message-ID: 4DA2930E-CB3F-4925-8DD2-7D541E5087F0@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Feb 16, 2012, at 20:01, vpapavas <vicky(dot)papavas(at)gmail(dot)com> wrote:

> Hello all, 
> 
> I am trying to use this query in a toy database with customers and orders in
> order to understand the capabilities of partitioning. In plain english what
> I want to do is to select the orders of each customer and return only 3 of
> those orders. 
> 
> The query I am using is this: 
> select c_custkey, o_orderkey, o_orderpriority, id from (
> select c_custkey, o_orderkey, o_orderpriority, o_totalprice, row_number() 
> over(PARTITION BY c_custkey ROWS between UNBOUNDED PRECEDING and 3
> FOLLOWING) as id 
> from customers left outer join orders on c_custkey = o_custkey) as temp
> 
> Although I am using the frame clause ROWS between UNBOUNDED PRECEDING and 3
> FOLLOWING which in my understanding should return the first row of the
> partition and the three following, this query returns all rows in the
> partition. Am I doing something wrong? Or have I understood wrong the
> semantics of the frame clause? I am using Postgresql v9.1
> 
> I rewrote the query like this in order to make it work:
> select c_custkey, o_orderkey, o_orderpriority, id from (
> select c_custkey, o_orderkey, o_orderpriority,  row_number()
> over(PARTITION BY c_custkey) as id 
> from customers left outer join orders on c_custkey = o_custkey ) as temp
> where id <= 3
> 
> but the problem is that I would like to not have to compute the entire join
> since I am interested in only 3 orders for each customer. 
> 
> Thank you,
> Vicky
> 
> 

Put the window function on the order table, perform the where-limit, then join customer to the result.

Also, you are numbering rows but not imposing any kind of order before doing so.

Row_number doesn't make sense with a frame clause...frame is more useful for stuff like calculating rolling sums/averages and the like - where you evaluate fields in the surrounding frame as part of the aggregate.

Window functions do not affect the number of rows returned.

David J.


In response to

Responses

pgsql-sql by date

Next:From: Amila JayasooriyaDate: 2012-02-17 07:10:44
Subject: cast type bytea to double precision
Previous:From: vpapavasDate: 2012-02-17 01:01:43
Subject: Window function frame clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group