填补开始结束时间戳之间的空白

Question

我正在尝试找到一个查询来填补开始和结束时间戳之间的空白。间隙/间隔应为分钟。我的目标是获取一个时间序列，以根据结果创建图表，以可视化每条记录的时间段。

样品日期

create table tbl(
id int, value int, dateStart timestamp, dateEnd timestamp
);
insert into tbl values
(1, 4, '2020-01-04 16:29:00', '2020-01-04 16:33:00'),
(2, 3, '2020-01-04 18:29:00', '2020-01-04 18:33:00'),
(3, 3, '2020-07-16 09:10:00', '2020-07-16 09:12:00');

| id  | value    | dateStart           | dateEnd             |
| --- | -------- | ------------------- | ------------------- |
| 1   | 4        | 2020-01-04 16:29:00 | 2020-01-04 16:33:00 |
| 2   | 3        | 2020-01-04 18:29:00 | 2020-01-04 18:30:00 |
| 3   | 3        | 2020-07-16 09:10:00 | 2020-07-16 09:12:00 |
| .   | .        | .                   | .                   |
| .   | .        | .                   | .                   |
| .   | .        | .                   | .                   |

预期输出

| id  | value    | date                |
| --- | -------- | ------------------- | 
| 1   | 4        | 2020-01-04 16:29:00 |
| 2   | 4        | 2020-01-04 16:30:00 |
| 3   | 4        | 2020-01-04 16:31:00 |
| 4   | 4        | 2020-01-04 16:32:00 |
| 5   | 4        | 2020-01-04 16:33:00 |
| 6   | 3        | 2020-01-04 18:29:00 |
| 7   | 3        | 2020-01-04 18:30:00 |
| 8   | 3        | 2020-07-16 09:10:00 |
| 9   | 3        | 2020-07-16 09:11:00 |
| 10  | 3        | 2020-07-16 09:12:00 |

我还不太熟悉 CTE 和光标。 任何建议/帮助如何最好地解决它？

Answer 1

这是一种(相当低效的(方法是使用递归查询：

with recursive cte as (
select id, value, dateStart, dateEnd from tbl
union all
select id, value, dateStart + interval 1 minute, dateEnd 
from cte
where dateStart < dateEnd
)
select row_number() over(order by id) id, value, dateStart date 
from cte
order by id

DB小提琴上的演示：

ID | 值 | 日期               -: |----: |:------------------  1 |    4 |2020-01-04 16:29:00  2 |    4 |2020-01-04 16:30:00  3 |    4 |2020-01-04 16:31:00  4 |    4 |2020-01-04 16:32:00  5 |    4 |2020-01-04 16:33:00  6 |    3 |2020-01-04 18:29:00  7 |    3 |2020-01-04 18:30:00  8 |    3 |2020-07-16 09:10:00  9 |    3 |2020-07-16 09:11:00 10 |    3 |2020-07-16 09：12：00

Answer 2

更有效的是使用 MariaDB 的 "seq" 伪表：

( SELECT '2000-01-01' + INTERVAL seq DAY AS dy FROM seq_0_to_36524 )

将在 3 毫秒内生成一个世纪的价值日期。 是的，毫秒。

SELECT s.dy
FROM ( SELECT '2020-01-04 18:29:00' + INTERVAL seq MINUTE AS dy
FROM seq_0_to_1440 ) AS s
WHERE s.dy <= '2020-01-04 18:33:00';
+---------------------+
| dy                  |
+---------------------+
| 2020-01-04 18:29:00 |
| 2020-01-04 18:30:00 |
| 2020-01-04 18:31:00 |
| 2020-01-04 18:32:00 |
| 2020-01-04 18:33:00 |
+---------------------+
5 rows in set (0.00 sec)

这将生成一天的日期(以 1 毫秒为单位(，然后限制为所需的范围。 在您的情况下，您将LEFT JOIN稀疏表。