一个日历年的MySQL Fetch Month明智的总注册用户数

Question

我试图在一个日历年中获取明智的总注册用户数，例如2016年。该查询需要考虑到2015年12月之前的注册用户以及将其添加到注册用户每月在2016年。

以下是我尝试获取总注册用户，但它有两个问题： 1.它跳过没有新注册用户的行 2.它计算总用户数时也不计算以前注册的用户数。

SELECT tots.*, @var := @var + tots.`monthlyUsers` as totalUsers 
FROM (
SELECT m.monthNum, 
    YEAR(from_unixtime(u.createdDate)) AS `year`, 
    MONTH(from_unixtime(u.createdDate)) AS `month`, 
    COUNT(*) AS `monthlyUsers` 
FROM user u 
GROUP BY `year`, `month`) AS tots, (SELECT @var := 0) AS inc 

这样做的结果是一样的东西

year month monthlyUsers totalUsers 
------------------------------------ 
2015 1  2    2 
2016 1  3    5 
2016 10  1    6 
2016 11  2    8 

我如何克服这两个问题？

编辑 我设法取上一年的记录，并把它添加到当前年数据

SELECT tots.*, @var := @var + tots.`monthlyUsers` as totalUsers FROM (
SELECT 
    YEAR(from_unixtime(u.createdDate)) AS `year`, 
    MONTH(from_unixtime(u.createdDate)) AS `month`, 
    COUNT(*) AS `monthlyUsers` 
FROM user u 
where YEAR(from_unixtime(u.createdDate)) ='2016' 
GROUP BY `year`, `month`) AS tots, (SELECT @var from (select @var :=count(u.id) from user u where year(from_unixtime(u.createdDate)) < '2016') as var) AS inc 

这将会给我拿

year month monthlyUsers totalUsers 
------------------------------------ 
2016 1  3    5 
2016 10  1    6 
2016 11  2    8 

但我期望的结果是

year month monthlyUsers totalUsers 
------------------------------------ 
2016 1  3    5 
2016 2  0    5 
2016 3  0    5 
2016 4  0    5 
2016 5  0    5 
2016 6  0    5 
2016 7  0    5 
2016 8  0    5 
2016 9  0    5 
2016 10  1    6 
2016 11  2    8 
2016 12  0    8 

Answer 1

从不在FROM子句中使用逗号。 总是使用明确的，适当的JOIN语法。至少有一个问题是，你需要一个LEFT JOIN：

SELECT um.*, 
     (@var := @var + COALESCE(um.monthlyUsers, 0)) as totalUsers 
FROM months m LEFT JOIN 
    (SELECT m.monthNum, 
      YEAR(from_unixtime(u.createdDate)) AS `year`, 
      MONTH(from_unixtime(u.createdDate)) AS `month`, 
      COUNT(*) AS `monthlyUsers` 
     FROM user u 
     GROUP BY `year`, `month` 
    ) um 
    ON m.monthNum = um.monthNum CROSS JOIN 
    (SELECT @var := 0) AS inc 
ORDER BY `year`, `month`; 

我不知道如何在用户的其余部分补充。这个查询似乎没有过滤任何东西，尽管JOIN条件也应该包括一年。