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
