Q

Maximum of 5-day running totals

I have a table of dates and number of events per date. I need to find a 5-day range where the most number of events happened. I just couldn't figure out a way to sum up the values of events-per-date for every 5 rows. This means all possible 5 consecutive rows, but dates are not necessarily consecutive (days where no events occurred are not kept).

I have a table of dates and number of events per date. I need to find a 5-day range where the most number of events happened. I just couldn't figure out a way to sum up the values of events-per-date for every 5 rows. This means all possible 5 consecutive rows, but dates are not necessarily consecutive (days where no events occurred are not kept).

eventdate  numevents
2006-06-16    3
2006-06-17    3
2006-06-18    3
2006-06-19    3
2006-06-20    4
2006-06-21    4
2006-06-22    4
2006-06-23    4
2006-06-24    3
2006-06-25    2
2006-06-26    2
2006-06-27    2
2006-06-30    2
2006-07-01    2
2006-07-04    1

This is only a very small section of the table.

What an interesting problem. Thanks for sending it in.

The solution requires a self-join. What we need to do is join the table to itself, with a special condition: each row must be joined only to itself and the immediately preceeding four rows. In other words, we join each row to the top 5 rows that have an eventdate that is equal to or less than that row. Please make sure you understand this statement of the problem before looking at the following SQL:

select t1.eventdate   
     , t1.numevents 
     , t2.eventdate   
     , t2.numevents 
  from events as t1
inner
  join events as t2
    on t2.eventdate <= t1.eventdate 
   and 5 >=
     ( select count(*) 
         from events  
        where eventdate 
              between t2.eventdate 
                  and t1.eventdate )  
order 
    by t1.eventdate
     , t2.eventdate

The above query produces the following results:

t1             t2
2006-06-16  3  2006-06-16  3

2006-06-17  3  2006-06-16  3
2006-06-17  3  2006-06-17  3

2006-06-18  3  2006-06-16  3
2006-06-18  3  2006-06-17  3
2006-06-18  3  2006-06-18  3

2006-06-19  3  2006-06-16  3
2006-06-19  3  2006-06-17  3
2006-06-19  3  2006-06-18  3
2006-06-19  3  2006-06-19  3

2006-06-20  4  2006-06-16  3
2006-06-20  4  2006-06-17  3
2006-06-20  4  2006-06-18  3
2006-06-20  4  2006-06-19  3
2006-06-20  4  2006-06-20  4

2006-06-21  4  2006-06-17  3
2006-06-21  4  2006-06-18  3
2006-06-21  4  2006-06-19  3
2006-06-21  4  2006-06-20  4
2006-06-21  4  2006-06-21  4

and so on

Notice that the first four t1 dates do not have five preceeding t2 dates. We'll come back to that in a moment.

Now that we know we're looking at the right data, we can do the next step, which is to add up the numevents values for the joined t2 rows. We do this by changing the query into a GROUP BY query:

select t1.eventdate   
     , count(*)          as rows
     , sum(t2.numevents) as total
  from events as t1
inner
  join events as t2
    on t2.eventdate <= t1.eventdate 
   and 5 >=
     ( select count(*) 
         from events  
        where eventdate 
              between t2.eventdate 
                  and t1.eventdate )  
group 
    by t1.eventdate

The above query produces the following results:

eventdate  rows total
2006-06-16  1     3
2006-06-17  2     6
2006-06-18  3     9
2006-06-19  4    12
2006-06-20  5    16
2006-06-21  5    17
2006-06-22  5    18
2006-06-23  5    19
2006-06-24  5    19
2006-06-25  5    17
2006-06-26  5    15
2006-06-27  5    13
2006-06-30  5    11
2006-07-01  5    10
2006-07-04  5     9 

Notice that we can add HAVING COUNT(*)=5 to this query if we wish to eliminate the first four dates.

The final step is to take the maximum of the total column, and there are a number of ways to do it, which we don't really have room to discuss. The easiest is to add ORDER BY total DESC to the above query, and, by inspection, take the first result row. Caution: look for ties for the maximum, as there are in this case for the 23rd and 24th.

This was first published in October 2006
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close