From: Josh Berkus <josh@agliodbs.com>
To: Scott Gerhardt <scott@g-it.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Aggregate query for multiple records
Date: Thu, 26 Aug 2004 21:55:42 -0700
Message-ID: <200408262155.42636.josh@agliodbs.com> (raw)
In-Reply-To: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
References: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
Scott,
> Hello, I am new to the list, my apology if this question is beyond the
> scope or charter of this list.
We have a charter? Why didn't anyone tell me?
> My questions is:
> What is the best method to perform an aggregate query to calculate
> sum() values for each distinct wid as in the example below, but except
> for all wid's (not just WHERE wid='01/1-6-1-30w1/0').
> SELECT
> SUM(oil) as sumoil, SUM(hours) as sumhours,
> FROM
> (SELECT oil, hours prd_data WHERE wid='01/1-6-1-30w1/0'
> ORDER BY date LIMIT 6) subtable
Your problem here is the "limit 6" There isn't any good+fast way to take only
6 of each thing and total them. Also the above query is missing a FROM
clause, so I;ve had to guess at your table name below. Oh, and the word
"date" is a reserved word, better to quote it.
This is valid, but it won't be fast:
SELECT wid,
(SELECT SUM(oil) FROM prd_data pd2
WHERE pd2.wid = prd_data.wid ORDER BY "date" LIMIT 6) as tot_oil,
(SELECT SUM(hours) FROM prd_data pd2
WHERE pd2.wid = prd_data.wid ORDER BY "date" LIMIT 6) as tot_hours
FROM prd_data
ORDER BY wid;
However, that will be querying the prd_data table about (distinct wid)*2+1
times. Don't hold your breath.
> Also, performance wise, would it be better to build a function for this
> query. The table has 9 million records and these aggregate queries
> take hours.
It might. Not for the summaries themselves (which are fastest as aggregates),
but to build a table that has only 6 records per WID.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, scott@g-it.ca
Subject: Re: Aggregate query for multiple records
In-Reply-To: <200408262155.42636.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox