convert a query to use window functions

From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: convert a query to use window functions
Date: 2008-11-27 19:25:20
Message-ID: 2f4958ff0811271125m7ae9c6afre4e317ab813ddeae@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey,
I am struggling a bit here with a query, that I would like to rewrite to use
window functions, if they ever get to 8.4 (and I hope they will).
But since I never used them before, I have trouble even figuring out how to
approach it.

a table:

Table "public.stats"
Column | Type | Modifiers | Storage |
Description
-----------+--------------------------------+--------------------+---------+-------------
size_to | bigint | not null default 0 | plain
|
size_from | bigint | not null default 0 | plain
|
mac | integer | not null | plain
|
data | timestamp(0) without time zone | not null | plain
|
Indexes:
"stats_test_pkey" PRIMARY KEY, btree (mac, data)
"stats_test_data_idx" btree (data)
"stats_test_mac_idx" btree (mac)
Has OIDs: no

and current query:

select x, coalesce(f,0), coalesce(t,0), 88.162244898 from (
select width_bucket( extract( epoch from data )::numeric, 1227711060,
1227797460, 980 ) as dupa,
(max(size_from*8))/60 as f,
(max(size_to*8))/60 as t from stats where stats.mac=19721 group by dupa
) as y
right join generate_series(1,980) as x on y.dupa=x ;

this one is parametrized , so I can actually test it.
What that query is used for, for given time scale, it has to return data
that is going to be used to plot a graph of traffic for certain macaddr.

so it has to come up with nr of rows , each one for Y, where row nr is X. So
far, I've been adviced by some smart folks from #postgresql to use
width_bucket with it, but it made me wonder - whether the same could be
achieved with window functions, and if so - that would be a great test for
it, me thinks.

--
GJ

Browse pgsql-general by date

  From Date Subject
Next Message Bill Todd 2008-11-27 20:52:40 COPY with a variable path
Previous Message Andrus 2008-11-27 19:20:16 Expected password response, got message type 88