I have a yearly list of sales of an item for the past 20 years.
the data is like this.
date ; sales value 2001-01-01 ; 423 2001-01-02 ; 152 2001-01-03 ; 162 2001-01-04 ; 172 . . .
I have a behavioral problem. I must find the five consecutive days where the sum of sales is maximum in a year, for each year, for the past 20 years. Then using the result i must analyse the spending pattern.
how can i get the 5 consecutive days whose sum is maximum in a year?
I must get it for all years with dates and sum of sales in those 5 days total value. Can anyone help me in my assignment, please?
TIA
Advertisement
Answer
Well, in MySQL 8+, you can use window functions. In earlier versions, a correlated subquery. That looks like:
select year(t.date),
max(next_5_days_sales),
substring_index(group_concat(date order by next_5_days_sales desc), ',', 1) as date_at_max
from (select t.*,
(select sum(t2.sales)
from t t2
where t2.date >= t.date and t2.date < t.date + interval 5 day
) as next_5_days_sales
from t
) t
group by year(t.date);
Notes:
- You will need to reset the group_concat_max_len, because 1024 is probably not long enough for the intermediate result.
- This allows the periods to span year boundaries.
In MySQL 8, use window functions!
select t.*
from (select t.*,
row_number() over (partition by year(date) order by next_5_days_sales) as seqnum
from (select t.*,
sum(t2.sales) over (order by date range between current row and 4 following) as as next_5_days_sales
from t
) t
) t
where seqnum = 1;