CODEWITHSUNDEEP
mysqlsql
VBJ
VBJ

Reputation: 683

MYSQL - Get data for every 5 mins and sum value from previous rows to show an aggregate data

I need help on MySQL....I have the following query to pull data every 5 mins. But I need the revenue to be summed up to the previous hour as time progress and give final revenue at the end of the day. So each row revenue would be sum of the revenue for that timestamp+previous hours total in the current row. 

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

Upvotes: 0

Views: 329

Answers (1)

Jaugar Chang
Jaugar Chang

Reputation: 3196

Give you an sample to solve your main problem, assume you have an orders table with revenue in it like this:

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)

First step is to get all dates lead in five minutes(lead_date). Then join the orders table to within one hour of every 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

Here's the result:

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

I think you could apply this approach to your case, replace revnen to (it.current_price*quantity)- IFNULL(it.total_adjustment,0) , join your order and order_item tables as an subquery to replace o2.

Upvotes: 1

Related Questions