Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group