Geek Answers Handbook

Calculating an increase or decrease in the trend over time in MySQL

I have a table store_visitswith the following structure:

store_visits:
 store_name: string
 visit_count: integer
 visit_date: date

My goal is to create a query that for each repository and a given date range will calculate:

  • Average visits in date range (currently in use AVG(visit_count))
  • Shopping visits are increasing or decreasing.
  • Relative increase / decrease rate (scale from 1 to 4, where 1 = low speed, 4 = high speed)

The relative rate of increase / decrease in visits is intended only for a directed goal. It will always be linear.

I spent the whole day trying to build a MySQL query to do this, and just can't get around it.

Any help would be greatly appreciated.

Thanks, Scott

+5
1

, , , 40 , 2 , .

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;

, , 1-4 , , , . , .

: ((endVisits + startVisits)/40) ((endVisits + startVisits)/2). avg 2, .

+4

More articles:


All Articles