在缺少数据的时间范围内取平均值查询细分：

php date mysql average sql

2022-08-31 01:13:30

假设有一个表，例如：

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

我想从下午2点到下午3点获得每个房间的平均用户计数（1，2，3，4）。问题是，有时房间可能不会在15分钟的间隔时间“签到”，因此必须假设以前最后一次已知的用户计数仍然有效。

例如，4号房间的入住手续从未办理过入住手续，所以必须假设4号房间有3个用户，因为这就是它所拥有的。2012-08-03 14:15:002012-08-03 14:15:002012-08-03 14:00:00

这遵循，因此我正在寻找的平均用户计数如下：

房间 1：（2 + 3 + 6 + 3） / 4 = 3.5
房间 2：（3 + 4 + + 7） / 4 = 4.5
房间 3：（1 + 1 + + + 8） / 4 = 2.75
房间 4：（3 + + + 4） / 4 = 3.254133

其中是基于以前已知签入的假定数字。#

我想知道是否有可能仅使用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;
    }

}

答案 1

您的困难（最昂贵的步骤）将是填补空白。如果无法在源数据中“填写空白”，则可能需要一个要联接的模板，然后使用相关子查询查找与该模板关联的数据。

这通常最适合真实表，但这里有一个使用硬编码的行内视图的示例...

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 会创建模板，以确保您始终拥有每个房间的每个签入插槽的记录。

- 相关子查询将按时间回溯搜索，以查找该房间当时的最新签入。

答案 2

您可以使用此解决方案：

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 414:15:0014: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 步：

然后，一旦我们有了这些关联，我们就将结果连接回主表（），条件是主表的时间部分在其字段小于每个的笛卡尔连接时间。这将对每个 -> 关联执行的操作，它将显示在该时间或之前发生的所有条目。tblDatetimeUIDUIDintrvlintrvl

例如，由于没有 intrvl 的条目，因此只有两个条目将与该 intrvl 连接：on 和因为它们都发生在 intrvl 时间上或之前。Room 314:30:0014:15:0014: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

呈现（不包括“名称”列）：

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 步：

我们的下一步是采用上面的结果集，并仅为每个 intrvl 拉取最近联接的结果。我们可以通过与聚合函数结合使用来实现此目的。DatetimeGROUP BYMAX()

不幸的是，由于行为方式，我们也无法正确地拉取每个选定值。UsersDatetimeGROUP 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

现在，我们必须获取每个值的值，以便我们可以获取这些值的平均值。为此，我们将最后一步的查询包装为子句中的子选择，然后再次联接到主表，条件是对于每个匹配的 -> 关联，获取值。Userslast_dateFROMUIDlast_dateUsers

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