MySQL是否可以按持续时间交叉点分组？

cnh2zyt3 于 2023-11-16 发布在 Mysql

关注(0)|答案(1)|浏览(120)

我有一组电话呼叫数据，其中包含start和end时间戳，我需要计算出一个月内的最多并发呼叫，因此呼叫“持续时间“需要重叠，不幸的是，我不认为我可以使用“滑动窗口”，因为它只适用于单个字段。
eg.

start, end
...
2023-09-04 11:14:12, 2023-09-04 11:24:27
2023-09-04 11:20:17, 2023-09-04 11:34:37
...

字符串
而不是将整个集合提取到代码中，并循环通过每个时间块（每30秒）来计算重叠，如下面的简化版本的代码，（* 是的，有空间来优化，进一步分区的数据到天，甚至小时，所以时间块不会循环通过不必要的数据 *）

all_time_blocks = {}
    time_block = date_from
    while time_block <= date_to:
        counter = all_time_blocks.setdefault(time_block, 0)
        for call in calls:
            if call[0] <= time_block <= call[1]:
                counter += 1
        all_time_blocks[time_block] = counter
        if counter > high_watermark:
            high_watermark = counter
        # advance the time block
        time_block = time_block + relativedelta.relativedelta(seconds=30)

型
是否可以生成查询中的30秒块，并按生成的与呼叫持续时间相交的周期块进行分组？
像

group_by, count
2023-09-01 00:00:00 2023-09-01 00:00:30, 1
2023-09-01 00:00:30 2023-09-01 00:01:00, 3
2023-09-01 00:01:00 2023-09-01 00:01:30, 7
...
2023-09-30 23:59:30 2023-10-01 00:00:00, 0

型
我能找到的最接近的答案是这个，但是分组逻辑有点不同，因为它是按生成的期间键分组的，但是我需要按与实际开始和结束相交的期间分组。
Group by half hour interval

mysql

来源：https://stackoverflow.com/questions/77356497/mysql-is-it-possible-to-group-by-duration-intersection

1条答案

按热度按时间

jhkqcmku1#

您可以使用递归CTE生成一系列时间戳值，然后使用start和end列将其连接到表。
它看起来像这样：

with recursive cte as (   
  select '2023-09-01 00:00:00' as timestamp   
  union   
  select timestamp + interval 30 second   
  from cte   
  where timestamp < '2023-09-30 23:59:30'
) 
select * from cte 
join mytable on cte.timestamp between mytable.start and mytable.end;

字符串
不幸的是，MySQL的递归CTE的实现对迭代次数的限制相当低。
错误3636（HY 000）：递归查询在1001次迭代后中止。请尝试将@@cte_max_recursion_depth增加到更大的值。
你可以试着增加这个数字，但我认为把它增加到86,400是不明智的，这是你30天所需要的30秒间隔的数量。
因此，更优化的方法是创建一个临时表，并用时间戳值填充它，然后将该表连接到您的表。在任何脚本语言中，使用循环来完成这一点都很简单。

select c.timestamp, c.count
from (
    select t.timestamp, count(*) as count
    from all_timestamps as t
    join mytable on t.timestamp between mytable.start and mytable.end
    group by t.timestamp
) as c
order by count desc
limit 1;

型
即使在只读示例上，您也可以仅使用SELECT权限来使用CTE解决方案。
要创建临时表，您必须具有CREATE TEMPORARY TABLE权限。

赞(0）回复(0）举报 2023-11-16

我来回答

MySQL是否可以按持续时间交叉点分组？

1条答案

相关问题

热门标签

最新问答