Mysql 高效的 SQL 查询，用于计算半小时时间序列中已发生的行的一部分

发布于01月18日

我有一张桌子，看起来像这样:

id	slot	total
1	2022-12-01T12:00	100
2	2022-12-01T12:30	150
3	2022-12-01T13:00	200

槽上已经有索引了.该表有大约100mil行(以及这里没有显示的更多列)

我想把总数加起来，算到(EDIT: WASN'T CLEAR INITIALLY, I WILL PROVIDE A LOWER SLOT BOUND, SO THE SUM WILL BE OVER SOME NUMBER OF DAYS/WEEKS, NOT OVER FULL TABLE)的当前时刻.假设现在的时间是2022-12-01T12:45.如果我跑select * from my_table where slot < CURRENT_TIMESTAMP()，然后我拿回了1和2的记录.

然而，在我的数据中，这些记录代表了一个时间段内的预测销售额.我想要找到截至2022-12-01T12:45的预测，因此我想要找到记录2的半小时时段已经过go 的比例，并返回总数的该比例.

截至2022-12-01T12:45(假设为分钟粒度)，第2行中的50%行已经过go ，因此我预计总数将返回150 / 2 = 75.

我当前的查询可以工作，但速度很慢.我有什么方法可以优化这一点，或者我可以采取其他方法？

另外，我们如何将这一解决方案推广到任意区间频率？也许明天我们会改变我们的预测模型，数据会零星地出现.在这种情况下，硬编码的30将不起作用.

select sum(fraction * total) as t from
 select total,
        LEAST(
   timestampdiff(
   minute,
   datetime,
   current_timestamp()
    ),
   30
) / 30 as fraction

  from my_table
  where slot <= current_timestamp()

SET @current_time = CURRENT_TIMESTAMP(); WITH cte AS ( SELECT slot, SUM(total) OVER(ORDER BY slot) AS total, total AS rowtotal FROM my_table WHERE slot < @current_time ORDER BY slot DESC LIMIT 1 ) SELECT slot, total - (30 - TIMESTAMPDIFF(MINUTE, slot, @current_time)) /30 * rowtotal AS total FROM cte

Mysql 高效的 SQL 查询，用于计算半小时时间序列中已发生的行的一部分

推荐答案

Mysql相关问答推荐

MySQL INSERT INSERT ON DIPLICATE KEY UPDATE WITH COMPAY PRIMARY KEY失败

Laravel DB：：Transaction()

如何在sql中包含0作为计数？

从连接表中try 按唯一ID计算行数 MySQL

模拟Mysql Cursor类的fetchone()方法，并将其返回值设置为None

如何使用mysql更新列中的json数据

Mysql 中相同列的相关 2 查询

如何将结果列中的不同值按其他列sql中的值带入单行

如何查询由另一个查询创建的表？

Select 分组产品的数量和每个产品的最便宜的价格

MySql，如何从列中替换某种格式的字符串

按长度过滤 varbinary 字段

用于国际和多语言目的的数据库建模

PHP 判断 NULL

如何使用 SQL 数据库中的经纬度查找最近的位置？

如何在 MySQL 的日期时间字段中存储 NULL 值？

Ubuntu 中的 MySQL JDBC jar 文件在哪里？

从 MySQL 中的日、月、年字段创建日期

如何在 MySQL 中删除此索引？

在 MySQL 中签名或未签名