CODEWITHSUNDEEP
sqloptimizationquery-optimizationaverage
user438199
user438199

Reputation: 63

SQL averaging multiple time periods of the same dataset

I have the following query:

SELECT AVG(val) from floatTable
WHERE tagindex IN(1,2,3,4)
AND DateAndTime > '$first_of_year'

It returns the average value for all the values measured for those four tags for the year to date. Since I'm already retrieving this data, how can I get the data since the first of the month, since the first of the week and since midnight? I already have those dates calculated as $first_of_month, $first_of_week and $midnight. I'm trying to minimize the query and was hoping someone could help me whip up some SQL magic to return this data in a single query or an optimized set of queries. This query takes on average 300 seconds, so I want to do it as little as possible.

Thank you in advance.

Upvotes: 2

Views: 2142

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

SELECT AVG(case when DateAndTime > '$first_of_year' then val end) as FirstOfYear,
       AVG(case when DateAndTime > '$first_of_month' then val end) as FirstOfMonth,
       AVG(case when DateAndTime > '$first_of_week' then val end) as FirstOfWeek,
       AVG(case when DateAndTime > '$midnight' then val end) as Midnight
from floatTable 
WHERE tagindex IN(1,2,3,4) 
    and DateAndTime > '$first_of_year'

To improve performance, make sure you have indexes on columns DateAndTime and tagIndex.

Upvotes: 5

Related Questions