sql - 两个时间戳和分类到 1 分钟存储桶之间的 SQL 差异

Question

我正在开发一个示例项目，其中我使用了一个名为 TDV（TIBCO 数据虚拟化）的工具，它同时支持 oracle 和 Postgres SQL。我有两个带有一些值的时间戳列 startDate 和 endDate，我正在尝试将数据扩展到 1 分钟，但我无法以所需的格式获取它。startdate 和 enddate 之间的差异不是恒定的，有时是 3 分钟或 5 或 7 分钟。我做了一些研究，但找不到正确的解决方案。

样本数据

+--------------------+--------------------+--------+
|     StartDate      |      EndDate       | Val1   |
+--------------------+--------------------+--------+
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 |     10 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 |     22 |
+--------------------+--------------------+--------+

期望的结果

+--------------------+--------------------+--------------------+--------+
|     StartDate      |      EndDate       |     Time_1Min      | Val1   |
+--------------------+--------------------+--------------------+--------+
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:07:00 |     10 |
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:08:00 |     10 |
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:09:00 |     10 |
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:10:00 |     10 |
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:11:00 |     10 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:21:00 |     22 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:22:00 |     22 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:23:00 |     22 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:24:00 |     22 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:25:00 |     22 |
+--------------------+--------------------+--------------------+--------+

标签： sqloracle

Answer 1

您可以使用递归 CTE：

with recursive cte as (startdate, enddate, time_1min, value) as
      select startdate, enddate, trunc(startdate) + interval '1 minute', value
      from t
      union all
      select startdate, enddate, time_1min + interval '1 minute', value
      from cte
      where time_1min < enddate
     )
select *
from cte;