Average over a timeframe with missing data

Posted by BHare on Stack Overflow See other posts from Stack Overflow or by BHare
Published on 2012-08-04T11:47:26Z Indexed on 2012/08/27 21:38 UTC
Read the original article Hit count: 211

Filed under:
|
|
|
|

Assuming a table such as:

UID     Name        Datetime                Users
4       Room 4      2012-08-03 14:00:00     3
2       Room 2      2012-08-03 14:00:00     3
3       Room 3      2012-08-03 14:00:00     1
1       Room 1      2012-08-03 14:00:00     2

3       Room 3      2012-08-03 14:15:00     1
2       Room 2      2012-08-03 14:15:00     4
1       Room 1      2012-08-03 14:15:00     3

1       Room 1      2012-08-03 14:30:00     6

1       Room 1      2012-08-03 14:45:00     3
2       Room 2      2012-08-03 14:45:00     7
3       Room 3      2012-08-03 14:45:00     8
4       Room 4      2012-08-03 14:45:00     4

I wanted to get the average user count of each room (1,2,3,4) from the time 2PM to 3PM. The problem is that sometimes the room may not "check in" at the 15 minute interval time, so the assumption has to be made that the previous last known user count is still valid.

For example the check-in's for 2012-08-03 14:15:00 room 4 never checked in, so it must be assumed that room 4 had 3 users at 2012-08-03 14:15:00 because that is what it had at 2012-08-03 14:00:00

This follows on through so that the average user count I am looking for is as follows:

Room 1: (2 + 3 + 6 + 3) / 4 = 3.5
Room 2: (3 + 4 + 4 + 7) / 4 = 4.5
Room 3: (1 + 1 + 1 + 8) / 4 = 2.75
Room 4: (3 + 3 + 3 + 4) / 4 = 3.25

where # is the assumed number based on the previous known check-in.

I am wondering if it's possible to so this with SQL alone? if not I am curious of a ingenious PHP solution that isn't just bruteforce math, as such as my quick inaccurate pseudo code:

foreach ($rooms_id_array as $room_id) {
    $SQL = "SELECT * FROM `table` WHERE (`UID` == $room_id && `Datetime` >= 2012-08-03 14:00:00 && `Datetime` <= 2012-08-03 15:00:00)";
    $result = query($SQL);
    if ( count($result) < 4 ) {
        // go through each date and find what is missing, and then go to previous date and use that instead
    } else {
        foreach ($result)
            $sum += $result;
        $avg = $sum / 4;
    }

}

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql