Darren Duncan wrote:
At 6:10 PM +1030 3/19/04, Jake Skinner wrote:
The following query is very slow. I don't know how to improve my
query, and I'm sure I'm doing this just about the slowest way
possible!! :(
begin sql query
======================
select t1.time, t1.units_processed,
round((t1.units_processed+t2.units_processed+t3.units_processed+t4.units_processed)/4)
as ave
from location_stats as t1,
location_stats as t2,
location_stats as t3,
location_stats as t4
where t1.time=t2.time
and t3.time=t4.time
and t4.time=t2.time
and t1.date=date("2004-02-24",'-7 days')
and t2.date=date("2004-02-24",'-14 days')
and t3.date=date("2004-02-24",'-28 days')
and t4.date=date("2004-02-24",'-35 days')
=======================
end sql query
I'm not quite sure if this is what you want, but it should be in the
right ball park:
SELECT time, AVG(units_processed) AS ave
FROM location_stats
WHERE date=DATE("2004-02-24",'-7 DAYS')
OR date=DATE("2004-02-24",'-14 DAYS')
OR date=DATE("2004-02-24",'-28 DAYS')
OR date=DATE("2004-02-24",'-35 DAYS')
GROUP BY time
If it is correct, then it should run a lot faster, as it is simpler.
If you were wanting to compare your number of units just this last
week to the average of the previous several weeks, then you may have
to take the whole expression I wrote and use it as a sub-query in the
FROM clause of a larger one that joins the results with a simpler
select just fetching the newer day's numbers.
Thanks for the help, the revised select runs about 10 times faster.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
