Technology gossip | mysql rate calculation

I haven’t updated it for a long time. I have been too busy recently, dealing with SQL almost every day. Happened to encounter a problem today, here to share with you, is to use SQL to calculate the growth rate of the problem.

The raw data in my database looks like this:

But what we need to show on the front end is a discount, which shows the growth rate, or year-over-year growth, which you probably know, and the formula is as follows: (last year’s data – previous year’s data)/previous year’s data. This really puzzled me at the beginning, but later I found a solution on the Internet. After understanding it, I felt very classic and shared it with everyone.

So let’s just look at the formula, let’s say that this year’s number is T1, and last year’s number was T2, so the growth rate is t1 minus T2 over T2, which is the same thing as T1 over T2 minus 1, which should make a lot of sense. So since we want to use SQL to achieve him, the top priority, even if how to get this year and last year’s data in our SQL, if we can get, we can directly apply the above formula.

How do we get the data of two adjacent years? In fact, we can use two tables that are identical and identical to each other, but instead of having them be equal, we can have them be malaligned. SQL > alter table name test; alter table name year; alter table name value.

select * from test t1 left join test t2 on t1.year = t2.year+1
Copy the code

We associate the same table with a left JOIN (t1.year = t2.year+1). When t1.year=2018, t2. Year = 2017, so that T1 and T2 represent two adjacent years, with two adjacent years, we can calculate the growth rate.

select t1.year, t1.value/t2.value- 1 rate from 
test t1 left join test t2 on t1.year = t2.year +1
order by t1.year 
Copy the code

That’s how you calculate the growth rate. So what? You got it.