MYSQL - 每 5 分钟获取一次数据，并对前几行求和以显示聚合数据

Question

我需要有关 MySQL 的帮助......我有以下查询每 5 分钟提取一次数据。但我需要随着时间的推移将收入汇总到前一小时，并在一天结束时给出最终收入。因此，每行收入将是该时间戳的收入总和+当前行中前几个小时的总收入。

SELECT o.creation_date,
       ROUND(SUM(((it.current_price*quantity)- IFNULL(it.total_adjustment,0))/100),0) AS reven,
       COUNT(o.order_id)
FROM order.order o
JOIN order.order_item it ON o.order_id=it.order_id
WHERE DATE(o.creation_date)='2014-09-16'
  AND o.order_status = "confirmed"
  AND it.line_item_type = 'item'
GROUP BY round(UNIX_TIMESTAMP(o.creation_date)/300);

Answer 1

给您一个示例来解决您的主要问题，假设您有一个包含收入的订单表，如下所示:

create table orders
(creation_date datetime,
  reven decimal(20,2))
;

insert into orders (creation_date,reven)values
(curdate()+ INTERVAL 1 MINUTE ,100),
(curdate()+ INTERVAL 2 MINUTE ,100),
(curdate()+ INTERVAL 60 MINUTE ,100),
(curdate()+ INTERVAL 61 MINUTE ,100),
(curdate()+ INTERVAL 62 MINUTE ,100),
(curdate()+ INTERVAL 120 MINUTE ,100),
(curdate()+ INTERVAL 121 MINUTE ,100),
(curdate()+ INTERVAL 122 MINUTE ,100)

第一步是在五分钟内获取所有领先日期 (lead_date)。然后将订单表连接到每个 lead_date 的一小时内。

select o1.lead_date,sum(reven) as reven
  from (select distinct FROM_UNIXTIME(round(UNIX_TIMESTAMP(creation_date)/300)*300) as lead_date
          from orders) o1
 inner join orders o2
    on o2.creation_date >= o1.lead_date
   and o2.creation_date < (o1.lead_date + INTERVAL 1 HOUR)
 group by o1.lead_date

结果如下:

LEAD_DATE                         REVEN
September, 18 2014 00:00:00+0000    200
September, 18 2014 01:00:00+0000    300
September, 18 2014 02:00:00+0000    300

我认为您可以将此方法应用于您的案例，将 revnen 替换为 (it.current_price*quantity)- IFNULL(it.total_adjustment,0) ，加入您的order 和 order_item 表作为子查询来替换 o2。