mysql SQL查询将行细分为多个1小时间隔

我有一个如下所示的表，其中包含不同时间间隔的开始和结束时间以及与这些时间间隔对应的数量。我需要一个查询，该查询将根据从现在（）到5小时、从现在（）到10小时、从现在（）到15小时的时间间隔计算数量
例如：在下图中，现在时间是12：00 PM，因此现在（）到5小时是12：00到5：00 PM-〉行号（S编号）从3到6，部分7将符合条件，数量应如下所示：第3行-〉10至12：30，2.30小时（150分钟）的数量为4556，但我需要半个n小时，因为现在（）至12：30是30分钟。因此，我只需要30分钟的4556数量-〉4556*30/150 4、5、6行全部符合条件-〉因此，4905数量第7行-〉下午4：00至6：00-〉2小时，数量为3645。我需要到下午5：00，即，1小时，因此，3645/2因此，我现在（）至5小时的总数量为7638
| S编号|间隔开始|间隔结束|数量|最小时间差|
| - -|- -|- -|- -|- -|
| 一个|2022年11月23日8时30分|2022年11月23日9时45分|小行星3045|七十五|
| 2个|2022年11月23日9时45分|2022年11月23日上午10时|第0页|十五|
| 三个|2022年11月23日上午10时|2022年11月23日12时30分|小行星4556|一百五十个|
| 四个|2022年11月23日12时30分|2022年11月23日13时|第0页|三十|
| 五个|2022年11月23日13时|2022年11月23日15时45分|小行星4905|一百六十五|
| 六个|2022年11月23日15时45分|2022年11月23日16时|第0页|十五|
| 七个|2022年11月23日16时|2022年11月23日18时|小行星3645|一百二十|
| 八个|2022年11月23日18时|2022年11月23日18时45分|第0页|四十五|
如何实现这是mySQL使用SQL查询？
enter image description here

1.假设NOW = 2022-11-24 05:30:00，即15分钟过去了，最后interval_end显示在图像中。
1.生成一系列分钟时间戳，从NOW一直回溯到最早的interval_start
1.计算qty_per_minute = qty/time_diff_min（或使用timestampdiff()计算interval_start和interval_end之间的分钟数
1.计算从NOW开始的第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);

mysql SQL查询将行细分为多个1小时间隔

1条答案

相关问题

热门标签

最新问答