mysql - 迭代列以显示时间之间的计数

Question

我正在尝试从 MySQL 生成一个结果集，该结果集将显示一天中开始和结束之间的时间间隔的行数，这样我就可以计算出房间利用率，例如从 8.30 开始的一个小时截止至 21.30。

准备此操作的源数据保存在格式如下的表格中(即具有 session 开始和结束时间)

我可以编写一些 PHP 来多次迭代来完成此操作，尝试确定是否可以直接在 MySQL 中完成。

源表(示例数据)

id  start_time            end_time               hostname          room
1   2018-10-19 16:28:37   2018-10-22 11:44:43    DESKTOP-FMVT2MJ   Room A

所需输出示例:

          8.30 9.30 10.30 11.30 ...
 Room A   0    3    4     2
 Room B   0    0    0     5

Answer 1

在 MySQL 中进行数据透视很困难，因为您必须为列的每个实例创建存储桶，这使得它变得相当长且重复。但如果你真的想在 MySQL 而不是 php 中执行此操作，那么这可以工作:

测试表结构:

id  start_time          end_time            hostname room
1   2019-01-01 06:30:00 2019-01-01 07:30:00 test1    Room A
2   2019-01-01 07:00:00 2019-01-01 07:30:00 test2    Room A
3   2019-01-01 08:00:00 2019-01-01 09:00:00 test1    Room B
4   2019-01-01 06:00:00 2019-01-01 09:00:00 test3    Room C
5   2019-01-01 06:45:00 2019-01-01 07:45:00 test2    Room B
6   2019-01-01 07:15:00 2019-01-01 08:55:00 test1    Room B
7   2019-01-01 08:15:00 2019-01-01 09:30:00 test2    Room C
8   2019-01-01 06:00:00 2019-01-01 18:30:00 test3    Room A

查询:

SELECT
    room,
    SUM(IF(6 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '6Hr',
    SUM(IF(7 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '7Hr',
    SUM(IF(8 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '8Hr',
    SUM(IF(9 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '9Hr',
    SUM(IF(10 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '10Hr',
    SUM(IF(11 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '11Hr',
    SUM(IF(12 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '12Hr',
    SUM(IF(13 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '13Hr',
    SUM(IF(14 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '14Hr',
    SUM(IF(15 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '15Hr',
    SUM(IF(16 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '16Hr',
    SUM(IF(17 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '17Hr',
    SUM(IF(18 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '18Hr',
    SUM(IF(19 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '19Hr',
    SUM(IF(20 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '20Hr',
    SUM(IF(21 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '21Hr',
    SUM(IF(22 BETWEEN DATE_FORMAT(start_time,'%H') AND DATE_FORMAT(DATE_SUB(end_time,INTERVAL 1 SECOND),'%H'),1,0)) AS '22Hr'
FROM
    TEST
WHERE
    DATE(start_time) = '2019-01-01'
AND DATE(end_time) = '2019-01-01'
GROUP BY room

结果:

room    6Hr 7Hr 8Hr 9Hr 10Hr 11Hr 12Hr 13Hr 14Hr 15Hr 16Hr 17Hr 18Hr 19Hr 20Hr 21Hr 22Hr
Room A  2   3   1   1   1    1    1    1    1    1    1    1    1    0    0    0    0
Room B  1   2   2   0   0    0    0    0    0    0    0    0    0    0    0    0    0
Room C  1   1   2   1   0    0    0    0    0    0    0    0    0    0    0    0    0

这假设每个房间 start_time 在开始的同一天都有一个 end_time。

我使用了 IF 语句来确定特定 session 行是否在BETWEEN 特定时间(每个时段每个小时)之间。对于 end_time，如果它在 09:00:00 结束，您不希望将其计入 9Hr 存储桶(因为 BETWEEN 包括结束范围)，所以我用 DATE_SUB 将其删除一秒钟，以便它保留在 8Hr 存储桶中。由于这些必须非常严格，如果一行在 09:00:01 结束，它将计入 9Hr 存储桶。可以修改此逻辑以满足您的偏好。