高效SQL查询优化：计算时间序列已流逝时段占比及通用方案

问题背景 #

现有数据表结构如下：

该表已为slot字段建立索引，数据量约1亿行（包含未展示字段）。需求为：计算指定起始时间到当前时间点（示例为2022-12-01T12:45）的累计预测销售额，其中当前时间所在时段的记录需按已流逝时长占该时段的比例计算（如示例中12:30时段已流逝50%，对应销售额为150/2=75）。

当前使用的SQL查询执行速度较慢：

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()

性能优化核心思路 #

原查询慢的核心原因是扫描范围过大（未限制起始时间，全表扫描所有slot <= 当前时间的记录），同时存在子查询冗余、硬编码时段间隔的问题。优化方向如下：

• 精准缩小数据扫描范围，利用slot索引快速定位目标数据
• 简化查询逻辑，减少嵌套层级
• 实现动态计算时段间隔的通用方案，摆脱硬编码限制

优化后的通用SQL实现（适配任意时段间隔） #

SELECT SUM(
    CASE
        -- 已完全结束的时段，全额计入销售额
        WHEN next_slot <= CURRENT_TIMESTAMP() THEN total
        -- 当前正在进行的时段，按流逝时长比例计算
        WHEN slot <= CURRENT_TIMESTAMP() THEN 
            total * TIMESTAMPDIFF(MINUTE, slot, CURRENT_TIMESTAMP()) 
            / TIMESTAMPDIFF(MINUTE, slot, next_slot)
        -- 未开始的时段，不计入
        ELSE 0
    END
) AS cumulative_sales
FROM (
    SELECT 
        slot,
        total,
        -- 获取下一个时段的起始时间，最后一条记录默认加30分钟（可根据业务调整）
        LEAD(slot, 1, slot + INTERVAL '30' MINUTE) OVER (ORDER BY slot) AS next_slot
    FROM my_table
    -- 精准过滤时间范围：指定起始时间到当前时段（避免遗漏当前时段记录）
    WHERE slot >= '指定起始时间' 
      AND slot <= CURRENT_TIMESTAMP() + INTERVAL '1' MINUTE
) t

固定时段间隔的专属优化（如统一30分钟） #

如果业务中时段间隔是固定值，可直接用固定间隔计算，无需窗口函数，性能更优：

SELECT SUM(
    CASE
        -- 已完全结束的时段，全额计算
        WHEN slot + INTERVAL '30' MINUTE <= CURRENT_TIMESTAMP() THEN total
        -- 当前时段按比例计算
        ELSE total * TIMESTAMPDIFF(MINUTE, slot, CURRENT_TIMESTAMP()) / 30
    END
) AS cumulative_sales
FROM my_table
WHERE slot >= '指定起始时间' 
  AND slot <= CURRENT_TIMESTAMP()

额外性能提升建议 #

• 创建覆盖索引：为slot和total创建联合索引（或包含total的覆盖索引），避免查询时回表读取数据，大幅提升速度：

  -- MySQL语法
  CREATE INDEX idx_slot_total ON my_table(slot, total);
  -- PostgreSQL语法
  CREATE INDEX idx_slot_total ON my_table(slot) INCLUDE (total);
  

• 数据分区：对大表按时间分区（如按天/小时），进一步缩小查询时的扫描范围。
• 预计算缓存：如果查询频率高，可将历史时段的累计值预计算存入缓存，仅实时计算当前时段的比例值，减少数据库压力。

内容的提问来源于stack exchange，提问作者Alan