php - 按发生的时间对时间间隔进行分组

Question

假设我有 startTime 和 endTime 给定，它们之间有半小时的间隔。例如 startTime=8:00, endTime=12:00 所以可能的时间是 8:00, 8:30, 9:00, 9:30, 10:00、10:30、11:00、11:30、12:00

现在我有几行操作，其中包含我的操作发生的时间段。每行都有 rowStartTime 和 rowEndTime。 它们的类型是 TIME

我想要实现的是按时间返回 GROUPed 结果以及在特定时间发生的行数。

演示数据

name    rowStarTime  rowEndTime
--------------------------------
action1  6:00        10:00
action2  9:00        13:00
action3  10:00       11:30
action4  12:00       13:00
action5  11:30       15:00

预期结果

Time      Action count    (actions in that time, this is just comment)
---------------------------------------------------------------------
8:00      1               //1
8:30      1               //1
9:00      2               //1, 2
9:30      2               //1, 2
10:00     3               //1, 2, 3
10:30     2               //2, 3
11:00     2               //2, 3
11:30     3               //2, 3, 5
12:00     4               //2, 4, 5

我想在数据库级别(使用 SQL)完成大部分工作。可行吗？还是我必须得到一些 PHP 的帮助(我正在使用 Doctrine DQL，但我可以使用很多特殊的 SQL 函数 https://github.com/orocrm/doctrine-extensions & https://github.com/beberlei/DoctrineExtensions )？

我只能在 rowStartTime 期间实现这一点，而不是整个期间:

SELECT `rowStartTime` AS sclr_0, COUNT(o.id) AS sclr_1 
FROM orders o 
GROUP BY sclr_0

(如果只在startTime和endTime之间选择时间会比较麻烦，不是核心，这个条件可以省略)

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(name    VARCHAR(12) NOT NULL PRIMARY KEY
,start_time TIME NOT NULL
,end_time TIME NOT NULL
);

INSERT INTO my_table VALUES
('action1', '6:00:00','10:00:00'),
('action2', '9:00:00','13:00:00'),
('action3','10:00:00','11:30:00'),
('action4','12:00:00','13:00:00'),
('action5','11:30:00','15:00:00');

SELECT * FROM my_table;
+---------+------------+----------+
| name    | start_time | end_time |
+---------+------------+----------+
| action1 | 06:00:00   | 10:00:00 |
| action2 | 09:00:00   | 13:00:00 |
| action3 | 10:00:00   | 11:30:00 |
| action4 | 12:00:00   | 13:00:00 |
| action5 | 11:30:00   | 15:00:00 |
+---------+------------+----------+

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT SEC_TO_TIME(1800*(i2.i*10+i1.i)) time 
     , COUNT(*) total
     , GROUP_CONCAT(x.name) actions
  FROM ints i1
 CROSS  
  JOIN ints i2 
  LEFT
  JOIN my_table x
    ON SEC_TO_TIME(1800*(i2.i*10+i1.i)) BETWEEN x.start_time AND x.end_time
 WHERE SEC_TO_TIME(1800*(i2.i*10+i1.i)) BETWEEN '08:00:00' AND '12:00:00'
 GROUP 
    BY time;
+----------+-------+-------------------------+
| time     | total | actions                 |
+----------+-------+-------------------------+
| 08:00:00 |     1 | action1                 |
| 08:30:00 |     1 | action1                 |
| 09:00:00 |     2 | action1,action2         |
| 09:30:00 |     2 | action1,action2         |
| 10:00:00 |     3 | action1,action2,action3 |
| 10:30:00 |     2 | action2,action3         |
| 11:00:00 |     2 | action2,action3         |
| 11:30:00 |     3 | action2,action3,action5 |
| 12:00:00 |     3 | action2,action4,action5 |
+----------+-------+-------------------------+