在时间范围内进行平均计算时出现数据缺失

Question

在时间范围内进行平均计算时出现数据缺失

16

Column1	Column2
Value1	Value2

How can I select all cells in the table except the first row?

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在2012-08-03 14:15:00拥有3个用户，因为它在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解决方案，而不仅是粗暴的数学计算，就像我快速而不准确的伪代码一样：

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

}

- ParoX

1

SQL具有SUM()、CNT()和AVG()聚合函数。 - wildplasser

平均用户计数的计算有点令人困惑。如果您有签入和签出时间，那么我们可以很容易地找出平均用户计数。我怀疑如果我们只使用签入时间是否正确。 - Joe G Joseph

我认为这不是登记/签出时间，而只是观察。缺失的观察值必须由同一房间最近的先前值填充。此外，他需要一个日历表（或generate_series函数）来提供“刻度”。 - wildplasser

4个回答

5

您可以使用此解决方案：

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演示

第二步：

然后，一旦我们有了这些关联，我们将结果与主表(tbl)再次连接，条件是主表的Datetime字段的时间部分小于每个UID的笛卡尔积时间。这样做的作用是，对于每个UID -> intrvl关联，它将显示在intrvl时间之前或同时发生的所有条目。

例如，由于Room 3没有14:30:00时间段的条目，因此只有两个条目会与该时间段连接：14:15:00和14:00:00，因为它们都是在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

渲染（不包括 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演示

第三步：

我们接下来的步骤是从上面的结果集中提取每个intrvl的最新加入的Datetime。我们可以使用GROUP BY与MAX()聚合函数相结合来实现这一点。

不幸的是，由于GROUP BY的行为方式，我们无法正确地提取每个所选Datetime的Users值。

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

第四步

现在我们需要获取每个last_date下Users的值，以便我们可以对这些值取平均值。我们通过将上一步中的查询作为子查询包装到FROM子句中，并再次根据每个匹配的UID -> last_date关联条件连接至主表格，并获取Users的值来完成此操作。

SQLFiddle演示

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演示

第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 最终结果的演示

- Zane Bien

请注意，步骤2是半笛卡尔积。这意味着 TIME(b.intrvl) >= TIME(c.Datetime) 可以匹配到很多不必要的记录；例如，在14:00之前有56个15分钟间隔。这可能会通过使用类似 AND TIME(c.Datetime) >= '14:00' 的方式缓解（但不能完全避免）。同时，通过使用TIME()函数，这将匹配以前日期的记录，因此需要像 AND c.Datetime >= '2012-08-03' AND c.DateTime < '2012-08-04' 这样的条件。最后，使用TIME()也会防止在该连接中使用索引。 - MatBailie

2

我刚刚尝试了一下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）。

仍然有两个限制：给定时间段的第一条和最后一条记录必须存在。如果不存在，平均值将在可用记录上“结束”。

- Shi

1

我认为这个方案可以很好地适应所有时间框架，即使签到间隔不均匀。此外，我认为您的示例中存在错误；在加权平均值中，房间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;

- Sako73

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MatBailie · Accepted Answer

你最困难的一步（最耗费时间的步骤）将是填写空白处。如果在源数据中无法“填写空白”，您可能需要一个模板来进行连接，然后使用相关子查询来查找与该模板相关联的数据。

这通常最好使用实际表格，但以下示例使用硬编码的内联视图...

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 会创建一个模板，以确保每个房间的每个签到时间都有记录。 - 相关子查询 会回溯时间，找到该房间在该时间的最近签到记录。