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
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 |