MySQL实现按当前时间后5/10/15小时区间计算数量的SQL查询

假设你的表名为interval_data，以下查询可以计算当前时间到未来5小时、10小时、15小时三个区间内的折算总数量，自动处理完全重叠、部分重叠和无重叠的情况：

SELECT
  '0-5小时' AS target_interval,
  SUM(
    CASE
      WHEN MAX(interval_start, NOW()) < MIN(interval_end, DATE_ADD(NOW(), INTERVAL 5 HOUR)) THEN
        Qty * TIMESTAMPDIFF(MINUTE, MAX(interval_start, NOW()), MIN(interval_end, DATE_ADD(NOW(), INTERVAL 5 HOUR))) / time_diff_Min
      ELSE 0
    END
  ) AS total_qty
FROM interval_data
UNION ALL
SELECT
  '5-10小时' AS target_interval,
  SUM(
    CASE
      WHEN MAX(interval_start, DATE_ADD(NOW(), INTERVAL 5 HOUR)) < MIN(interval_end, DATE_ADD(NOW(), INTERVAL 10 HOUR)) THEN
        Qty * TIMESTAMPDIFF(MINUTE, MAX(interval_start, DATE_ADD(NOW(), INTERVAL 5 HOUR)), MIN(interval_end, DATE_ADD(NOW(), INTERVAL 10 HOUR))) / time_diff_Min
      ELSE 0
    END
  ) AS total_qty
FROM interval_data
UNION ALL
SELECT
  '10-15小时' AS target_interval,
  SUM(
    CASE
      WHEN MAX(interval_start, DATE_ADD(NOW(), INTERVAL 10 HOUR)) < MIN(interval_end, DATE_ADD(NOW(), INTERVAL 15 HOUR)) THEN
        Qty * TIMESTAMPDIFF(MINUTE, MAX(interval_start, DATE_ADD(NOW(), INTERVAL 10 HOUR)), MIN(interval_end, DATE_ADD(NOW(), INTERVAL 15 HOUR))) / time_diff_Min
      ELSE 0
    END
  ) AS total_qty
FROM interval_data;

关键逻辑说明 #

• 重叠区间计算：用MAX(原区间开始时间, 目标区间开始时间)得到重叠部分的起始点，MIN(原区间结束时间, 目标区间结束时间)得到重叠部分的终点。只有当起始点早于终点时，才存在有效重叠。
• 折算数量：通过重叠分钟数 / 原区间总分钟数(time_diff_Min)得到占比，再乘以原数量Qty，得到该记录在目标区间的贡献值。
• 无重叠处理：如果原区间和目标区间没有重叠（起始点≥终点），则贡献值为0，不纳入总和。

示例验证 #

比如当前时间为12:00PM时：

• 对于原区间覆盖11:00-13:30（time_diff_Min=150）、Qty=4556的记录，目标区间12:00-17:00的重叠时间是30分钟，折算数量为4556*30/150=911.2
• 对于原区间覆盖16:00-18:00（time_diff_Min=120）、Qty=3645的记录，重叠时间是60分钟，折算数量为3645*60/120=1822.5
• 完全落在目标区间内的记录，折算数量为其全部Qty值

最终将所有有效贡献值求和，得到对应区间的总数量。

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