pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWindow function frame clause
3+ messages / 2 participants
[nested] [flat]
* Window function frame clause
@ 2012-02-17 01:01 vpapavas <vicky.papavas@gmail.com>
2012-02-17 01:33 ` Re: Window function frame clause David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: vpapavas @ 2012-02-17 01:01 UTC (permalink / raw)
To: pgsql-sql
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
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Window-function-frame-clause-tp5491171p5491171.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Window function frame clause
2012-02-17 01:01 Window function frame clause vpapavas <vicky.papavas@gmail.com>
@ 2012-02-17 01:33 ` David Johnston <polobo@yahoo.com>
2012-02-18 02:52 ` Re: Window function frame clause vpapavas <vicky.papavas@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Johnston @ 2012-02-17 01:33 UTC (permalink / raw)
To: vpapavas <vicky.papavas@gmail.com>; +Cc: pgsql-sql
On Feb 16, 2012, at 20:01, vpapavas <vicky.papavas@gmail.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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Window function frame clause
2012-02-17 01:01 Window function frame clause vpapavas <vicky.papavas@gmail.com>
2012-02-17 01:33 ` Re: Window function frame clause David Johnston <polobo@yahoo.com>
@ 2012-02-18 02:52 ` vpapavas <vicky.papavas@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: vpapavas @ 2012-02-18 02:52 UTC (permalink / raw)
To: pgsql-sql
Thank you David for your fast response.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Window-function-frame-clause-tp5491171p5494599.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-02-18 02:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-17 01:01 Window function frame clause vpapavas <vicky.papavas@gmail.com>
2012-02-17 01:33 ` David Johnston <polobo@yahoo.com>
2012-02-18 02:52 ` vpapavas <vicky.papavas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox