2

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.

user438199
  • 63
  • 5
  • If you don't want to make too many round trips to the database, you could simply put those averages in subqueries in one huge-ass query. As far as my major in mathematics tell me averages can't be split up or combined so you really need to calculate them all over again over different periods of time. – Denis Valeev Sep 13 '10 at 18:17
  • @Denis, see RedFilter's answer. Sub-queries would not require additional trips to the database , but *would* require additional logical reads within the query - so the overall time for the query could be up to four times as long. As for averages, most versions of SQL completely omit NULL values from aggregate calculations - so the four different expressions in RedFilter's query would be calculated correctly (as values which do not satisfy the conditions would be evaluated as NULL). –  Sep 14 '10 at 13:33
  • 1
    @Mark Bannister, I silently admitted my faux pas the moment RedFilter posted his answer and immediately learned from it and I wanted to leave my comment for posterity to learn from my fast assumption. – Denis Valeev Sep 14 '10 at 14:38

1 Answers1

5
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.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • That works great... how come it works so much faster than my original query? – user438199 Sep 13 '10 at 18:20
  • 1
    It shouldn't be any faster than the query in your question, but it probably won't be any (much) slower either, despite adding in the extra columns that you need. When you refer to "original query", do you mean the one in your question or did you have one to get all of the other averages that you wanted? It could be an issue of caching - you've already run a query so SQL Server has that data in memory. – Tom H Sep 13 '10 at 18:38
  • @user438199 Please verify that you mean > I think you might mean >= '$first_of_year' – Nathan Koop Sep 13 '10 at 18:56
  • note that $first_of_week might be before $first_of_year in January, except this week will be considered as last week in December, because you only consider days after $first_of_yeear which actually might be January 1st. This depends on your cultural requirements though. – Alex Sep 13 '10 at 19:22
  • Thank you everyone. @Nathan Koop >= would be technically correct, although due to the large amount of data being returned, the effect is negligible. – user438199 Sep 13 '10 at 20:23
  • @user438199, if this is the correct answer, you should mark it as such. To do this click the checkmark. – Nathan Koop Sep 14 '10 at 14:38
  • @Vash: good point, this can be compensated for by changing last line of query to `and (DateAndTime > '$first_of_year' or DateAndTime > '$first_of_week')` – D'Arcy Rittich Sep 14 '10 at 14:50