EXPLAIN EXTENDED

How to create fast database queries

Archive for March 15th, 2009

Aggregated hours

Comments enabled. I *really* need your comment

From Stack Overflow:

I would like to know the sum of all web services completions per hour of day.

Obviously, this can be easily done with SUM() and GROUP BY:

SELECT hour, SUM(calls) FROM sample s GROUP BY hour;  
hour SUM(calls)
0 634
1 642
2 633
3 624
4 420
5 479
6 428
7 424
8 473
9 434
10 485
11 567
12 526
13 513
14 555
15 679
16 624
17 796
18 752
19 843
20 827
21 774
22 647
23 533

My problem is that in old sets, the web service calls in the hours [00-11] were already summed up.

The simple statement as listed above would therefore lead to

SELECT hour, SUM(calls) FROM sample s GROUP BY hour;  
hour SUM(calls)
0 6234
12 526
13 513
14 555
15 679
16 624
17 796
18 752
19 843
20 827
21 774
22 647
23 533

This is an undesirable result. To make the old sets [00,12,...,23] comparable to the new sets [00,01,...,23], I would like to have one statement that averages the value of [00] and distributes it over the missing hours, e.g.:

hour SUM(calls)
0 6243/12
1 6243/12
12 526
23 533

I can easily do this using temporary tables or views, but i don't know how to accomplish this without them.

In this rowset, we have a row that needs to be split into 12 another rows.
Read the rest of this entry »

Written by Quassnoi

March 15th, 2009 at 11:00 pm

Posted in MySQL