SQL查询，将行分解为多个1hr间隔

Question

我有一个像下面这样的表，其中有不同间隔的开始和结束时间以及与这些间隔对应的数量。我需要有一个查询，它将计算基于时间间隔的数量从now()到5hrs, now()到10hrs, now()到15hrs

如:在下图中，现在时间是12:00 pm，那么现在()to 5hrs是12:00 to 5:00PM ->第3行至第6行和第7行部分符合条件，数量应如下:第三行->10点到12点30分，有4556个班次，需要2.30小时(150分钟)，但我需要半小时，因为现在()到12点30分是30分钟。所以我只需要30分钟的4556 Qty ->4556 * 30/1504、5、6行都完全符合->所以4905第七行->下午4时至6时-2小时的数量是3645。我需要到下午5点。1小时，即3645/2所以我现在到5小时的总需求量是7638

tbody> <<tr>245678

S No	interval start	interval end	Qty	time_diff_Min
1	11/23/2022 8:30	11/23/2022 9:45	3045	75
	11/23/2022 9:45	11/23/2022 10点	0	15
3	11/23/2022 10点	11/23/2022 12:30	4556	150
	11/23/2022 12:30	11/23/2022 13:00	0	30
	11/23/2022 13:00	11/23/2022 15分	4905	165
	11/23/2022 15分	11/23/2022 16:00时	0	15
	11/23/2022 16:00时	11/23/2022 18:00	3645	120
	11/23/2022 18:00	11/23/2022 18:45	0	45

1. 假设现在=2022-11-24 05:30:00，即距离图中最后一个interval_end15分钟
2. 生成一系列的分钟时间戳从现在一直回到最早的interval_start
3. 计算qty_per_minute=qty/time_diff_min(或使用timestampdiff()计算interval_start和interval_end之间的分钟数
4. 计算从现在开始的第n个5小时间隔，并汇总到每个5小时间隔。

set @now = '2022-11-24 05:30:00';
set @@cte_max_recursion_depth=10000;
with recursive cte(interval_ts) as (
select min(interval_start) as interval_ts from entries
union all
select timestampadd(minute, 1, interval_ts)
from cte
where interval_ts < timestampadd(minute, -1, @now)),
cte_entry_per_minute as (
select e.SNo,
e.interval_start,
e.interval_end,
e.qty,
(e.qty / timestampdiff(minute, e.interval_start, e.interval_end)) as qty_per_min,
c.interval_ts,
(timestampdiff(minute, c.interval_ts, @now)-1) div 300 as interval_5hr_from_now
from entries e,
lateral (select interval_ts from cte
where interval_ts >= e.interval_start
and interval_ts <  e.interval_end) as c)
select interval_5hr_from_now,
min(interval_ts)                          as interval_start,
max(timestampadd(minute, 1, interval_ts)) as interval_end,
sum(1)                                    as interval_minutes,
sum(qty_per_min)                          as total_qty
from cte_entry_per_minute
group by 1
order by 1 desc;

结果(qty舍入错误预期):

interval_5hr_from_now|interval_start     |interval_end       |interval_minutes|qty       |
---------------------+-------------------+-------------------+----------------+----------+
4|2022-11-23 08:30:00|2022-11-23 09:30:00|              60| 2436.0000|
3|2022-11-23 09:30:00|2022-11-23 14:30:00|             300| 7840.4520|
2|2022-11-23 14:30:00|2022-11-23 19:30:00|             300| 8074.6380|
1|2022-11-23 19:30:00|2022-11-24 00:30:00|             300|13564.9050|
0|2022-11-24 00:30:00|2022-11-24 05:15:00|             285| 8971.0080|

示例数据如下:

create table entries (
SNo     int,
interval_start  timestamp,
interval_end    timestamp,
qty             int,
time_diff_min   int);
truncate table entries;
insert into entries
values
( 1, '2022-11-23  8:30:00', '2022-11-23  9:45:00', 3045,  75),
( 2, '2022-11-23  9:45:00', '2022-11-23 10:00:00',    0,  15),
( 3, '2022-11-23 10:00:00', '2022-11-23 12:30:00', 4556, 150),
( 4, '2022-11-23 12:30:00', '2022-11-23 13:00:00',    0,  30),
( 5, '2022-11-23 13:00:00', '2022-11-23 15:45:00', 4905, 165),
( 6, '2022-11-23 15:45:00', '2022-11-23 16:00:00',    0,  15),
( 7, '2022-11-23 16:00:00', '2022-11-23 18:00:00', 3645, 120),
( 8, '2022-11-23 18:00:00', '2022-11-23 18:45:00',    0,  45),
( 9, '2022-11-23 18:45:00', '2022-11-23 21:30:00', 8067, 165),
(10, '2022-11-23 21:30:00', '2022-11-23 21:45:00',    0,  15),
(11, '2022-11-23 21:45:00', '2022-11-24 00:00:00', 7698, 135),
(12, '2022-11-24 00:00:00', '2022-11-24 00:30:00',    0,  30),
(13, '2022-11-24 00:30:00', '2022-11-24 03:45:00', 7253, 195),
(14, '2022-11-24 03:45:00', '2022-11-24 04:00:00',    0,  15),
(15, '2022-11-24 04:00:00', '2022-11-24 05:15:00', 1718,  75);