月到月增长率

Question

我有一张有以下细节的桌子

year    month   entry
------------------------
2017    March   1
2017    April   1
2017    May     2
2017    June    9

预期输出

year |  month |entry |  growth rate
-----------------------------------
2017 |  March | 1    |    0 %
-----------------------------------
2017 |  April | 1    |    0 %
-------------------------------------
2017 |  May   | 2    |    50 %
-------------------------------------
2017 |  June  | 9    |    77 %
------------------------------------

请发布一个MySQL查询以查看上面的内容。

我的增长率公式是：

(current month entry - previous month entry) / (current month entry) * 100

Answer 1

您可以使用一个变量来模拟延迟()函数，因为我不知道每个月我使用了一个组的记录有多少，只是为了确定。

select year, month, entry, 
       if(@last_entry = 0, 0, ((entry - @last_entry) / entry) * 100) "growth rate",
       @last_entry := entry                  
from
      (select @last_entry := 0) x,
      (select year, month, sum(entry) entry
       from   mytable
       group by year, month) y;

其结果是：

| year | month | entry | growth rate      |
|------|-------|-------|------------------|
| 2017 | 3     | 1     | 0                |
| 2017 | 4     | 1     | 0                |
| 2017 | 5     | 2     | 50               |
| 2017 | 6     | 9     | 77,7777777777778 |

rextester 这里