From: Josh Berkus <josh@agliodbs.com>
To: Scott Gerhardt <scott@g-it.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregate query for multiple records
Date: Fri, 27 Aug 2004 11:59:26 -0700
Message-ID: <200408271159.26445.josh@agliodbs.com> (raw)
In-Reply-To: <AF64E983-F851-11D8-A763-000393801C60@g-it.ca>
References: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
<200408270948.00680.josh@agliodbs.com>
<AF64E983-F851-11D8-A763-000393801C60@g-it.ca>
Scott,
> >> I tried your query but it doesn't seem to work. The interpreter
> >> expects prd2.date to be grouped but this doesn't make sence, see
> >> below:
> >
> > Oh, yeah, darn it.
> >
> > Hmmm ... maybe you could explain the purpose of selecting just 6?
> > This query
> > is going to get a *lot* uglier if there isn't another way to
> > accomplish it.
>
> The purpose of selecting the first 6 is we need sum values for just the
> first 3, 6 and 12 months oil/water production for every oil well in the
> database to perform statistical calculations. Optionally, we also need
> the _last_ 6 months of oil and water production.
> The prd_data table has total oil for each month but some wells have 500
> months of data, we just want the first 6.
Well, here's my suggestion, to make this query both easier and faster:
1) Add a column called "month_prod", integer, to the table. This is the
"number of months in production".
2) Populate this query with a calculated difference on your "date" column
against the first appearance of that WID (I'm assuming that each increment of
"date" = 1 month)
UPDATE prd_data SET months_prod = prd_data."date" - prd2."date" + 1
FROM prd_data prd2
WHERE prd_data.wid = prd2.wid
AND prd2."date" = ( SELECT "date" FROM prd_data prd3
WHERE prd3.wid = prd2.wid
ORDER BY "date" LIMIT 1 );
(warning: the above will take a long time and floor your server. Make sure to
do VACUUM FULL ANALYZE prd_data afterwards.)
(if you can't get the above to complete, which is possible depending on your
hardware, you could do it procedurally in a programmming language)
3) Then running your aggregate becomes very easy/fast:
SELECT wid, sum(oil) as tot_oil, sum(hours) as tot_hours
FROM prd_data
WHERE months_prod < 7
GROUP BY wid
ORDER BY 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: <200408271159.26445.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