Среднее по таймфрейму с отсутствующими данными

Предполагая таблицу, такую как:

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

Я хотел получить среднее количество пользователей каждой комнаты (1,2,3,4) с момента времени 2 вечера до 3 вечера. Проблема в том, что иногда комната не может "заходить" на 15-минутный интервал времени, поэтому необходимо сделать предположение о том, что предыдущий последний пользовательский счет все еще действителен.

Например, регистрация для комнаты 2012-08-03 14:15:00 номер 4 никогда не проверялась, поэтому следует предположить, что в комнате 4 было 3 пользователя в 2012-08-03 14:15:00, потому что это то, что у него было при 2012-08-03 14:00:00

Это следует за тем, чтобы средний счетчик пользователей, который я искал, выглядит следующим образом:

Комната 1: (2 + 3 + 6 + 3)/4 = 3,5
Номер 2: (3 + 4 + 4 + 7)/4 = 4,5
Комната 3: (1 + 1 + 1 + 8)/4 = 2,75
Комната 4: (3 + 3 + 3 + 4)/4 = 3,25

где # - это предполагаемое число, основанное на предыдущей известной регистрации.

Мне интересно, возможно ли это с помощью SQL? если нет, мне любопытно гениальное PHP-решение, которое не просто математическое выражение bruteforce, как, например, мой быстрый неточный псевдо-код:

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;
    }

}

Ответы

Ответ 1

Вы можете использовать это решение:

SELECT   b.Name, 
         AVG(b.Users) avg_users
FROM     (
         SELECT     a.UID, 
                    MAX(c.Datetime) last_date
         FROM       (SELECT DISTINCT UID FROM tbl) a
         CROSS JOIN (
                    SELECT '14:00:00' intrvl UNION ALL
                    SELECT '14:15:00'        UNION ALL
                    SELECT '14:30:00'        UNION ALL
                    SELECT '14:45:00'
                    ) b
         JOIN       tbl c ON a.UID           = c.UID
                         AND TIME(b.intrvl) >= TIME(c.Datetime)
         GROUP BY   a.UID,
                    b.intrvl
         ) a
JOIN     tbl b ON a.UID       = b.UID
              AND a.last_date = b.Datetime
GROUP BY b.UID,
         b.Name

Прорыв запроса:

Шаг 1:

Первое, что нам нужно сделать, это связать каждую комнату с каждым интервалом времени. Например, в ваших данных примера Room 4 не имеет ассоциации с интервалами 14:15:00 и 14:30:00, но нам все равно нужно каким-то образом представлять эти ассоциации.

Мы достигаем этого, создавая декартово произведение каждой отдельной комнаты с соответствующими временными интервалами:

SELECT     a.UID, 
           b.intrvl
FROM       (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
           SELECT '14:00:00' intrvl UNION ALL
           SELECT '14:15:00'        UNION ALL
           SELECT '14:30:00'        UNION ALL
           SELECT '14:45:00'
           ) b
ORDER BY   b.intrvl, a.UID DESC --Ordering for display purposes

Визуализирует:

UID | intrvl
--------------
4   | 14:00:00
3   | 14:00:00
2   | 14:00:00
1   | 14:00:00
4   | 14:15:00
3   | 14:15:00
2   | 14:15:00
1   | 14:15:00
4   | 14:30:00
3   | 14:30:00
2   | 14:30:00
1   | 14:30:00
4   | 14:45:00
3   | 14:45:00
2   | 14:45:00
1   | 14:45:00

SQLFiddle Demo

Шаг 2:

Затем, когда у нас есть эти ассоциации, мы присоединяем результат обратно к основной таблице (tbl) при условии, что основная часть таблицы времени своего поля Datetime меньше, чем декартово-связанное время для каждого UID. Что это будет сделано для каждой ассоциации UID → intrvl, она покажет все записи, которые произошли во время или до intrvl.

Так, например, поскольку Room 3 не имеет записи для 14:30:00 intrvl, к этому intrvl присоединяются только две записи: те, что указаны в 14:15:00 и 14:00:00, поскольку оба они произошли либо в до времени вторжения.

Теперь вы можете увидеть, с чем это связано. Результат этого шага даст нам доступ к самой последней записи для каждого входа.

SELECT     a.UID, 
           b.intrvl,
           c.*
FROM       (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
           SELECT '14:00:00' intrvl UNION ALL
           SELECT '14:15:00'        UNION ALL
           SELECT '14:30:00'        UNION ALL
           SELECT '14:45:00'
           ) b
JOIN       tbl c ON a.UID           = c.UID
                AND TIME(b.intrvl) >= TIME(c.Datetime)
ORDER BY   b.intrvl, a.UID DESC, c.Datetime --Ordering for display purposes

Renders (исключая столбец Name):

UID |  intrvl    |  Datetime             |  Users
---------------- --------------------------------
4   |  14:00:00  |  2012-08-03 14:00:00  |  3   <-- Most recent entry up until 14:00:00
3   |  14:00:00  |  2012-08-03 14:00:00  |  1   <-- Most recent entry up until 14:00:00
2   |  14:00:00  |  2012-08-03 14:00:00  |  3   <-- Most recent entry up until 14:00:00
1   |  14:00:00  |  2012-08-03 14:00:00  |  2   <-- Most recent entry up until 14:00:00
4   |  14:15:00  |  2012-08-03 14:00:00  |  3   <-- Most recent entry up until 14:15:00
3   |  14:15:00  |  2012-08-03 14:00:00  |  1
3   |  14:15:00  |  2012-08-03 14:15:00  |  1   <-- Most recent entry up until 14:15:00
2   |  14:15:00  |  2012-08-03 14:00:00  |  3
2   |  14:15:00  |  2012-08-03 14:15:00  |  4   <-- Most recent entry up until 14:15:00
1   |  14:15:00  |  2012-08-03 14:00:00  |  2
1   |  14:15:00  |  2012-08-03 14:15:00  |  3   <-- Most recent entry up until 14:15:00
4   |  14:30:00  |  2012-08-03 14:00:00  |  3   <-- Most recent entry up until 14:30:00
3   |  14:30:00  |  2012-08-03 14:00:00  |  1   
3   |  14:30:00  |  2012-08-03 14:15:00  |  1   <-- Most recent entry up until 14:30:00
2   |  14:30:00  |  2012-08-03 14:00:00  |  3
2   |  14:30:00  |  2012-08-03 14:15:00  |  4   <-- Most recent entry up until 14:30:00
1   |  14:30:00  |  2012-08-03 14:00:00  |  2
1   |  14:30:00  |  2012-08-03 14:15:00  |  3
1   |  14:30:00  |  2012-08-03 14:30:00  |  6   <-- Most recent entry up until 14:30:00
4   |  14:45:00  |  2012-08-03 14:00:00  |  3
4   |  14:45:00  |  2012-08-03 14:45:00  |  4   <-- Most recent entry up until 14:45:00
3   |  14:45:00  |  2012-08-03 14:00:00  |  1
3   |  14:45:00  |  2012-08-03 14:15:00  |  1
3   |  14:45:00  |  2012-08-03 14:45:00  |  8   <-- Most recent entry up until 14:45:00
2   |  14:45:00  |  2012-08-03 14:00:00  |  3
2   |  14:45:00  |  2012-08-03 14:15:00  |  4
2   |  14:45:00  |  2012-08-03 14:45:00  |  7   <-- Most recent entry up until 14:45:00
1   |  14:45:00  |  2012-08-03 14:00:00  |  2
1   |  14:45:00  |  2012-08-03 14:15:00  |  3
1   |  14:45:00  |  2012-08-03 14:30:00  |  6
1   |  14:45:00  |  2012-08-03 14:45:00  |  3   <-- Most recent entry up until 14:45:00

SQLFiddle Demo

Шаг 3:

Наш следующий шаг - взять вышеприведенный результат и вытащить только самые свежие объединенные Datetime для каждого intrvl. Мы можем выполнить это, используя GROUP BY в сочетании с агрегированной функцией MAX().

К сожалению, мы также не можем правильно вывести значение Users вместе с каждым из выбранных Datetime из-за того, как ведет себя GROUP BY.

SELECT     a.UID, 
           b.intrvl,
           MAX(c.Datetime) last_date
FROM       (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
           SELECT '14:00:00' intrvl UNION ALL
           SELECT '14:15:00'        UNION ALL
           SELECT '14:30:00'        UNION ALL
           SELECT '14:45:00'
           ) b
JOIN       tbl c ON a.UID           = c.UID
                AND TIME(b.intrvl) >= TIME(c.Datetime)
GROUP BY   a.UID,
           b.intrvl
ORDER BY   b.intrvl, a.UID DESC --Again, for display purposes

Визуализирует:

UID |  intrvl    |  last_date
---------------------------------------
4   |  14:00:00  |  2012-08-03 14:00:00
3   |  14:00:00  |  2012-08-03 14:00:00
2   |  14:00:00  |  2012-08-03 14:00:00
1   |  14:00:00  |  2012-08-03 14:00:00
4   |  14:15:00  |  2012-08-03 14:00:00
3   |  14:15:00  |  2012-08-03 14:15:00
2   |  14:15:00  |  2012-08-03 14:15:00
1   |  14:15:00  |  2012-08-03 14:15:00
4   |  14:30:00  |  2012-08-03 14:00:00
3   |  14:30:00  |  2012-08-03 14:15:00
2   |  14:30:00  |  2012-08-03 14:15:00
1   |  14:30:00  |  2012-08-03 14:30:00
4   |  14:45:00  |  2012-08-03 14:45:00
3   |  14:45:00  |  2012-08-03 14:45:00
2   |  14:45:00  |  2012-08-03 14:45:00
1   |  14:45:00  |  2012-08-03 14:45:00

SQLFiddle Demo

Шаг 4

Теперь нам нужно взять значение Users для каждого last_date, чтобы мы могли взять среднее значение этих значений. Мы делаем это, обертывая наш запрос на последнем шаге как подсегмент внутри предложения FROM и снова присоединяемся к основной таблице при условии, что для каждой сопоставления UID → last_date возьмите значение Users.

SELECT   a.UID,
         a.last_date,
         b.Users
FROM     (
         SELECT     a.UID, 
                    MAX(c.Datetime) last_date
         FROM       (SELECT DISTINCT UID FROM tbl) a
         CROSS JOIN (
                    SELECT '14:00:00' intrvl UNION ALL
                    SELECT '14:15:00'        UNION ALL
                    SELECT '14:30:00'        UNION ALL
                    SELECT '14:45:00'
                    ) b
         JOIN       tbl c ON a.UID           = c.UID
                         AND TIME(b.intrvl) >= TIME(c.Datetime)
         GROUP BY   a.UID,
                    b.intrvl
         ) a
JOIN     tbl b ON a.UID       = b.UID
              AND a.last_date = b.Datetime
ORDER BY a.UID DESC --Display purposes again

Визуализирует:

UID | last_date           | Users
---------------------------------
4   | 2012-08-03 14:00:00 | 3
4   | 2012-08-03 14:00:00 | 3
4   | 2012-08-03 14:00:00 | 3
4   | 2012-08-03 14:45:00 | 4
3   | 2012-08-03 14:00:00 | 1
3   | 2012-08-03 14:15:00 | 1
3   | 2012-08-03 14:15:00 | 1
3   | 2012-08-03 14:45:00 | 8
2   | 2012-08-03 14:00:00 | 3
2   | 2012-08-03 14:15:00 | 4
2   | 2012-08-03 14:15:00 | 4
2   | 2012-08-03 14:45:00 | 7
1   | 2012-08-03 14:00:00 | 2
1   | 2012-08-03 14:15:00 | 3
1   | 2012-08-03 14:30:00 | 6
1   | 2012-08-03 14:45:00 | 3

SQLFiddle Demo

Шаг 5

Теперь это просто вопрос группировки в каждой комнате и усреднение столбца Users:

SELECT   b.Name, 
         AVG(b.Users) avg_users
FROM     (
         SELECT     a.UID, 
                    MAX(c.Datetime) last_date
         FROM       (SELECT DISTINCT UID FROM tbl) a
         CROSS JOIN (
                    SELECT '14:00:00' intrvl UNION ALL
                    SELECT '14:15:00'        UNION ALL
                    SELECT '14:30:00'        UNION ALL
                    SELECT '14:45:00'
                    ) b
         JOIN       tbl c ON a.UID           = c.UID
                         AND TIME(b.intrvl) >= TIME(c.Datetime)
         GROUP BY   a.UID,
                    b.intrvl
         ) a
JOIN     tbl b ON a.UID       = b.UID
              AND a.last_date = b.Datetime
GROUP BY b.UID,
         b.Name

Визуализирует:

Name   | avg_users
------------------
Room 1 | 3.5
Room 2 | 4.5
Room 3 | 2.75
Room 4 | 3.25

SQLFiddle Демо окончательного результата

Ответ 2

Ваша трудность (самый дорогостоящий шаг) будет заключаться в заполнении пробелов. Если невозможно "заполнить пробелы" в ваших исходных данных, вы, вероятно, захотите включить шаблон для присоединения, а затем используйте коррелированные подзапросы, чтобы найти данные, связанные с этим шаблоном.

Это часто бывает лучше с реальными таблицами, но вот пример с жестко закодированными встраиваемыми представлениями...

SELECT
  `room`.`uid`           `uid` ,
  AVG(`data`.`users`)    `average_users`
FROM
  (SELECT 1 `UID`  UNION ALL
   SELECT 2 `UID`  UNION ALL
   SELECT 3 `UID`  UNION ALL
   SELECT 4 `UID`)                                     `room`
CROSS JOIN
  (SELECT '2012-08-03 14:00:00' `datetime`  UNION ALL
   SELECT '2012-08-03 14:15:00' `datetime`  UNION ALL
   SELECT '2012-08-03 14:30:00' `datetime`  UNION ALL
   SELECT '2012-08-03 14:45:00' `datetime`)            `checkin`
LEFT JOIN
  data
    ON  `data`.`uid`      = `room`.`uid`
    AND `data`.`datetime` = (SELECT MAX(`datetime`)
                               FROM `data`
                              WHERE `uid`       = `room`.`uid`
                                AND `datetime` <= `checkin`.`datetime`)
GROUP BY
  `room`.`uid`

- CROSS JOIN создает шаблон, гарантирующий, что у вас всегда есть запись для каждого слота для каждой комнаты.

- correlated sub-query выполняет поиск по времени, чтобы найти самую последнюю проверку для этой комнаты в это время.

Ответ 3

Я немного поиграл с переменными MySQL и придумал следующую идею:

Просто вычислите (дискретный) интеграл пользователей с течением времени, а затем разделите на общее время.

SET @avgSum := @lastValue := @lastTime := @firstTime := 0;
SELECT
  *,
  @firstTime := IF(@firstTime = 0, UNIX_TIMESTAMP(`DateTime`), @firstTime),
  @avgSum := @avgSum + (UNIX_TIMESTAMP(`DateTime`) - @lastTime) * @lastValue,
  @lastValue,
  @lastTime,
  @lastValue := `Users`,
  @lastTime := UNIX_TIMESTAMP(`DateTime`),
  @avgSum / (UNIX_TIMESTAMP(`DateTime`) - @firstTime) AS `average`
FROM
  `table`
WHERE
  `UID` = 1 AND
  UNIX_TIMESTAMP(`DateTime`) >= … AND
  UNIX_TIMESTAMP(`DateTime`) < …
ORDER BY
  UNIX_TIMESTAMP(`DateTime`) ASC;

@firstTime - это метка первой записи пользователя, @avgSum сумма пользователей с течением времени (интеграл). @lastValue и @lastTime - это значение и время предыдущей записи. Столбец average - общая сумма пользователей, делящихся на весь интервал (не обращайте внимания на NULL из-за деления на ноль для первой записи).

Все еще существуют два ограничения: должна присутствовать первая и последняя запись для данного интервала. Без, средний "заканчивается" на последней доступной записи.

Ответ 4

Я думаю, что это очень хорошая работа по размещению всех временных рамок, даже если интервалы регистрации не совпадают. Кроме того, я думаю, что у вас есть ошибка в вашем примере; в ваших средневзвешенных значениях, комната 2 имеет "4" вместо "7" для последнего значения.

Настройка:

if object_id(N'avgTbl', N'U') is not null
drop table avgTbl;

create table avgTbl (
    UserId int not null,
    RoomName nvarchar(10) not null,
    CheckInTime datetime not null,
    UserCount int not null,

    constraint pk_avgTbl primary key (UserId, RoomName, CheckInTime)
);

insert into avgTbl (UserId, RoomName, CheckInTime, UserCount) values
(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);

Запрос:

/* 
* You just need to enter the start and end times below.  
* They can be any intervals, as long as the start time is 
* before the end time.
*/
declare 
    @startTime datetime = '2012-08-03 14:00:00',
    @endTime datetime = '2012-08-03 15:00:00';

declare     
    @totalTime numeric(18,1) = datediff(MINUTE, @startTime, @endTime);

    /*
    * This orders the observations, and assigns a sequential number so we can 
    *join on it later.
    */
with diffs as (
    select 
        row_number() over (order by RoomName, CheckInTime) as RowNum,
        CheckInTime,
        UserCount,
        RoomName
    from avgTbl
),
/*
* Get the time periods, 
* calc the number of minutes, 
* divide by the total minutes in the period, 
* multiply by the UserCount to get the weighted value, 
* sum the weighted values to get the weighted avg.
*/
mins as (
    select 
        cur.RoomName,
        /*
        * If we do not have an observation for a given room, use "0" instead
        * of "null", so it does not affect calculations later.
        */
        case 
            when prv.UserCount is null then 0
            else prv.UserCount
            end as UserCount, 
        /* The current observation time. */            
        cur.CheckInTime as CurrentT,
        /* The prior observation time. */
        prv.CheckInTime as PrevT,
        /*
        * The difference in minutes between the current, and previous qbservation
        * times.  If it is the first observation, then use the @startTime as the
        * previous observation time.  If the current time is null, then use the
        * end time.
        */
        datediff(MINUTE, 
            case 
                when prv.CheckInTime is null then @startTime 
                else prv.CheckInTime 
                end, 
            case 
                when cur.CheckInTime is null then @endTime 
                else cur.CheckInTime 
                end) as Mins 
    from diffs as cur
        /*
        * Join the observations based on the row numbers.  This gets the current,
        * and previous observations together in the same record, so we can 
        * perform our calculations.
        */
        left outer join diffs as prv on cur.RowNum = prv.RowNum + 1
            and cur.RoomName = prv.RoomName
    union
    /*
    * Add the end date as a period end, assume that the user count is the same 
    * as the last observation.
    */
    select 
        d.RoomName, 
        d.UserCount, 
        @endTime,
        d.CheckInTime, -- The last recorded observation time.
        datediff(MINUTE, d.CheckInTime, @endTime) as Mins
    from diffs as d 
    where d.RowNum in (
        select MAX(d2.RowNum)
        from diffs as d2
        where d2.RoomName = d.RoomName
        )
    group by d.RoomName, d.CheckInTime, d.UserCount
)
/* Now we just need to get our weighted average calculations. */
select 
    m.RoomName, 
    count(1) - 1 as NumOfObservations,
    /*
    * m.Min = minutes during which "UserCount" is the active number.
    * @totalTime = total minutes between start and end.
    * m.Min / @totalTime = the % of the total time.
    * (m.Min / @totalTime) * UserCount = The weighted value.
    * sum(..above..) = The total weighted average across the observations.
    */
    sum((m.Mins/@totalTime) * m.UserCount) as WgtAvg
from mins as m
group by m.RoomName
order by m.RoomName;