有没有办法计算上一年的价值(年环比%)

Question

我目前正在使用mySQL，并且很难弄清楚这个看似简单的查询。我试图按月跟踪注册情况，通过一个同比比较，选择给定日期的注册数量，同一日期但一年前的注册数量。然后再增加一列，显示两者之间的百分比变化。

到目前为止，我有以下专栏没有问题：

Date | User Registrations
__________

然而，我正在努力获得：

Date | User Registrations | Previous Year # | % Change
_______

这是我当前的查询。

SELECT DATE_FORMAT(created_at,'%M %Y') AS "Month", count(*) AS "New User Registrations", 
from users
WHERE `created_at` > "2018-01-01 00:00:00"
INNER JOIN
GROUP BY YEAR(created_at), MONTH(created_at)

---

如果有人知道如何获取一年前的匹配值，我将不胜感激。

Answer 1

假设每个月都有数据，则使用lag():

SELECT DATE_FORMAT(created_at, '%M %Y') AS "Month",
COUNT(*) AS "New User Registrations", 
LAG(COUNT(*)) OVER (ORDER BY MIN(created_at)) as prev_year,
( COUNT(*) / LAG(COUNT(*)) OVER (ORDER BY MIN(created_at)) ) - 1 as change
FROM users u
WHERE `created_at` > '2018-01-01'
GROUP BY DATE_FORMAT(created_at, '%M %Y')