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
If anyone has any sort of suggestions I would be very greatful...
cheers Jake
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 timeIf it is correct, then it should run a lot faster, as it is simpler.
For added clarity, I made the SQL keywords uppercased in my version and your identifiers lowercased, as I understood them.
Personally, I wouldn't give my table columns names that looked like key words, like 'time' or 'date' as it appears you did.
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.
-- Darren Duncan
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
