I have a table store_visits
with the following structure:
store_visits:
store_name: string
visit_count: integer
visit_date: date
My goal is to create a query that for each store and a given date range, will calculate:
- Average Number of Visits over the date range (currently using
AVG(visit_count)
) - Whether store visits are increasing or decreasing
- The relative rate of increase/decrease (1 to 4 scale where 1 = low rate, 4 = high rate)
The relative rate开发者_如何学JAVA of increase/decrease in visits is for directional purpose only. It will always be a linear scale.
I've spent a day trying to construct the MySQL query to do this, and just can't get my head around it.
Any help would be greatly appreciated.
Thanks, -Scott
Assuming you just want to compare the store visits in the first half of the date range to the second half, here's an example that spans the last 40 days using 2 sub-queries to get the counts for each range.
select
((endVisits + startVisits)/40) average,
(endVisits > startVisits) increasing,
((endVisits - startVisits)/(startVisits) * 100) percentChange
from
(select sum(visit_count) startVisits
from store_visit
where
visit_date > current_date - 40
and visit_date <= current_date - 20) startRange,
(select sum(visit_count) endVisits
from store_visit
where
visit_date > current_date - 20) endRange;
Notes
I don't know where the how you want to calculate your 1-4 increase amount, so I just made it a percentage and you can modify that to whatever logic you want. Also, you'll need to update the date ranges in the sub-queries as needed.
Edit: Just updated the average to ((endVisits + startVisits)/40) instead of ((endVisits + startVisits)/2). You could also use the avg function in your sub-queries and divide the sum of those by 2 to get the average over the whole period.
精彩评论