Search Postgresql Archives

convert a query to use window functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux