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

h4cxqtbf 于 2023-01-20 发布在 Mysql

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

我有一个类似这样的表：
| 身份证|槽|总计|
| - ------|- ------|- ------|
| 1个|2022年12月1日12时|一百|
| 第二章|2022年12月1日12时30分|一百五十|
| 三个|2022年12月1日13时|二百|
slot上已经有一个索引了，这个表有大约1亿行（还有很多列没有显示）
我想将总数加到当前时刻**（编辑：最初不清楚，我将提供一个较低的插槽界限，因此总和将超过一定数量的天/周，而不是超过整个表）**。假设当前时间为2022-12-01T12:45。如果我运行select * from my_table where slot < CURRENT_TIMESTAMP()，则会返回记录1和2。
但是，在我的数据中，记录表示一个时段内的预测销售额。我希望查找截至2022-12-01T12:45的预测，因此我希望查找记录2的半小时时段中已经过去的部分，并返回该部分占总数的比例。
到2022-12-01T12:45为止（假设粒度为分钟），行2的50%已经过去，所以我希望返回的总数为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()

mysql

来源：https://stackoverflow.com/questions/75156848/efficient-sql-query-to-calculate-portion-of-a-row-in-half-hourly-time-series-tha

2条答案

按热度按时间

kd3sttzy1#

考虑先计算总和，然后去掉最后一个元素的部分总和，为了保持最后一个元素的总和，我更喜欢使用窗口函数而不是聚合，并将输出限制在最后一行。

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

检查here演示。