php - attribute - Promedio en un período de tiempo con datos faltantes

title tag html

Question

Asumiendo una tabla como:

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

Quería obtener el número promedio de usuarios de cada habitación (1,2,3,4) desde las 2 p.m. hasta las 3 p.m. El problema es que a veces la sala no puede "registrarse" en el intervalo de 15 minutos, por lo que se debe asumir que el último recuento de usuarios conocido todavía es válido.

Por ejemplo, el check-in para 2012-08-03 14:15:00 sala 4 nunca se registró, por lo que se debe asumir que la sala 4 tuvo 3 usuarios en 2012-08-03 14:15:00 porque eso es lo que tenido en 2012-08-03 14:00:00

Esto sigue hasta que el número de usuarios promedio que busco es el siguiente:

Habitación 1: (2 + 3 + 6 + 3) / 4 = 3.5
Habitación 2: (3 + 4 + 4 + 7) / 4 = 4.5
Sala 3: (1 + 1 + 1 + 8) / 4 = 2.75
Sala 4: (3 + 3 + 3 + 4) / 4 = 3.25

donde # es el número supuesto basado en el registro anterior conocido.

Me pregunto si es posible hacer esto solo con SQL? si no, tengo curiosidad por una ingeniosa solución de PHP que no sea solo matemática de fuerza bruta, como mi pseudo código inexacto rápido:

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

}

Answer 1

Creo que esto hace un buen trabajo de acomodar todos los marcos de tiempo, incluso si los intervalos de check-in no son iguales. Además, creo que tienes un error en tu ejemplo; en sus promedios ponderados, la sala 2 tiene un "4" en lugar de "7" para el último valor.

La puesta en marcha:

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

La consulta:

/* 
* 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;

Answer 2

Puedes usar esta solución:

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

Desglose de consultas:

Paso 1:

Lo primero que debemos hacer es asociar cada habitación con cada intervalo de tiempo. Por ejemplo, en los datos de su ejemplo, la Room 4 no tiene una asociación con los intervalos de 14:15:00 y 14:30:00 , pero todavía necesitamos representar esas asociaciones de alguna manera.

Esto lo logramos creando un producto cartesiano de cada habitación distinta con los intervalos de tiempo relevantes:

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

Renders:

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

Demostración de SQLFiddle

Paso 2:

Luego, una vez que tengamos esas asociaciones, uniremos el resultado nuevamente a la tabla principal ( tbl ) con la condición de que la parte de tiempo de la tabla principal de su campo Datetime hora sea menor que el tiempo de Datetime cartesiana para cada UID . Lo que esto hará es que para cada asociación UID -> intrvl , mostrará todas las entradas que hayan ocurrido en o antes de la hora intrvl .

Así, por ejemplo, dado que la Room 3 no tiene una entrada para la 14:30:00 14:30:00, solo se unirán dos entradas con esa intrvl: las de 14:15:00 y 14:00:00 ya que ambas ocurrieron En o antes de la hora de inicio.

Ahora puedes ver hacia dónde vamos con esto. El resultado de este paso nos dará acceso a la entrada más reciente para cada intrvl.

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 (excluyendo la columna 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

Demostración de SQLFiddle

Paso 3:

Nuestro siguiente paso es tomar el conjunto de resultados de arriba y extraer solo el Datetime unido más reciente para cada intrvl. Podemos lograr esto utilizando GROUP BY junto con la función de agregado MAX() .

Desafortunadamente, tampoco podemos extraer correctamente el valor de los Users junto con cada uno de los Datetime seleccionados debido a cómo se comporta 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

Renders:

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

Demostración de SQLFiddle

Etapa 4

Ahora tenemos que tomar el valor de los Users para cada last_date para que podamos tomar el promedio de esos valores. Hacemos esto envolviendo nuestra consulta en el último paso como una subselección dentro de la cláusula FROM y uniéndonos nuevamente a la tabla principal con la condición de que para cada asociación UID -> last_date , tome el valor de los 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

Renders:

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

Demostración de SQLFiddle

Paso 5

Ahora es solo una cuestión de agrupar en cada sala y promediar la columna 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

Renders:

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

Demostración de SQLFiddle del resultado final

Answer 3

Simplemente jugué un poco con las variables de MySQL y se me ocurrió la siguiente idea:

Simplemente calcule la integral (discreta) de los usuarios a lo largo del tiempo y luego divida por el tiempo total.

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 es la marca de tiempo del primer registro de usuario, @avgSum la suma de usuarios a lo largo del tiempo (la integral). @lastValue y @lastTime son valor y tiempo del registro anterior. El average columna es la suma total de los usuarios divididos por el intervalo completo (no importa el NULL debido a la división por cero para el primer registro).

Dos restricciones aún están presentes: el primer y el último registro para el intervalo dado deben estar presentes. Sin, el promedio "termina" en el último registro disponible.

Answer 4

Su dificultad (el paso más costoso) será completar los espacios en blanco. Si no es posible "completar los espacios en blanco" en los datos de origen, es probable que desee tener una plantilla para unirse, luego usar sub-consultas correlacionadas para encontrar los datos asociados con esa plantilla.

A menudo, esto es mejor con tablas reales, pero aquí hay un ejemplo con vistas en línea codificadas en su lugar ...

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 crea la plantilla para garantizar que siempre tenga un registro para cada ranura de registro en cada habitación.

- La correlated sub-query busca en el tiempo para encontrar el registro más reciente para esa habitación en ese momento.