Reputation: 683
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
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