MySQL Mysql查询以获取每月计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15774280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql query to get count per months
提问by David R
I have a table for my users that have a field named "created" that have the registration date.
我有一个表供我的用户使用，其中有一个名为“created”的字段，其中包含注册日期。
How can i get a list that contains a count for the registrations number per month in last 12 months? Like this:
如何获取包含过去 12 个月内每月注册数量计数的列表？像这样：
Month Count
1 1232
2 2222
3 122
4 4653
... ...
12 7654
I'm not used to working with mysql, so until now i just know how to count the number of registrations in last year, not how to group that count by last 12 months. Thanks in advance!
我不习惯使用 mysql，所以直到现在我只知道如何计算去年的注册数量，而不是如何将过去 12 个月的数量分组。提前致谢！
UPDATE
更新
Now I'm getting this, using @fthiella solution:
现在我得到了这个，使用@fthiella 解决方案：
+------------------------------+-------------------------------+----------+
| Year(FROM_UNIXTIME(created)) | Month(FROM_UNIXTIME(created)) | Count(*) |
+------------------------------+-------------------------------+----------+
| 2012 | 4 | 9927 |
| 2012 | 5 | 5595 |
| 2012 | 6 | 4431 |
| 2012 | 7 | 3299 |
| 2012 | 8 | 429 |
| 2012 | 10 | 3698 |
| 2012 | 11 | 6208 |
| 2012 | 12 | 5142 |
| 2013 | 1 | 1196 |
| 2013 | 2 | 10 |
+------------------------------+-------------------------------+----------+
How can i force query to give me the months with count = 0?
如何强制查询给我计数 = 0 的月份？
Solution by @fthiella (thanks a lot!):
@fthiella 的解决方案（非常感谢！）：
SELECT y, m, Count(users.created)
FROM (
SELECT y, m
FROM
(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
LEFT JOIN users
ON ym.y = YEAR(FROM_UNIXTIME(users.created))
AND ym.m = MONTH(FROM_UNIXTIME(users.created))
WHERE
(y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
OR
(y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
GROUP BY y, m;
And the results:
结果：
+------+----+----------------------+
| y | m | Count(users.created) |
+------+----+----------------------+
| 2012 | 5 | 5595 |
| 2012 | 6 | 4431 |
| 2012 | 7 | 3299 |
| 2012 | 8 | 429 |
| 2012 | 9 | 0 |
| 2012 | 10 | 3698 |
| 2012 | 11 | 6208 |
| 2012 | 12 | 5142 |
| 2013 | 1 | 1196 |
| 2013 | 2 | 10 |
| 2013 | 3 | 0 |
| 2013 | 4 | 0 |
+------+----+----------------------+
回答by fthiella
If created is an INT field, you should use FROM_UNIXTIMEfunction to convert it to a date field, and then MONTHfunction to extract the month:
如果 created 是一个 INT 字段，则应使用FROM_UNIXTIME函数将其转换为日期字段，然后使用MONTH函数提取月份：
SELECT Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Month(FROM_UNIXTIME(created))
this will count all the rows that have been created in the last 12 months. Please notice that it's probably better to also group by the YEAR:
这将计算过去 12 个月内创建的所有行。请注意，最好也按年份分组：
SELECT Year(FROM_UNIXTIME(created)), Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Year(FROM_UNIXTIME(created)), Month(FROM_UNIXTIME(created))
If you need to count the registration numbers instead of the rows, you could use something like
如果您需要计算注册号而不是行数，您可以使用类似
COUNT(registration_number)
to skip null values, or
跳过空值，或
COUNT(DISTINCT registration_number)
to count only distinct ones.
只计算不同的。
Edit
编辑
If you also need to show months that have count=0, I would use a query like this that returns all of the months for the current and for the previous year:
如果您还需要显示 count=0 的月份，我将使用这样的查询，返回当前和上一年的所有月份：
SELECT y, m
FROM
(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months;
And then I'd use a LEFT JOIN, that returns all of the rows of the first query, and only the rows of the second query that matches:
然后我会使用 LEFT JOIN，它返回第一个查询的所有行，并且只返回匹配的第二个查询的行：
SELECT y, m, Count(yourtable.created)
FROM (
SELECT y, m
FROM
(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
LEFT JOIN yourtable
ON ym.y = YEAR(FROM_UNIXTIME(yourtable.created))
AND ym.m = MONTH(FROM_UNIXTIME(yourtable.created))
WHERE
(y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
OR
(y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
GROUP BY y, m
(please notice that here I am considering just the last 12 months, so if we are in the middle April 2013 it will count rows in the interval May 2012 - April 13, if this is not the correct behaviour please let me know)
（请注意，这里我只考虑过去 12 个月，所以如果我们在 2013 年 4 月中旬，它将计算 2012 年 5 月 - 4 月 13 日之间的行数，如果这不是正确的行为，请告诉我）
回答by Vatev
SELECT MONTH(reg_date) , COUNT(reg_date)
FROM your_table
WHERE reg_date >= NOW() - INTERVAL 1 YEAR
GROUP BY MONTH(reg_date)
回答by David M
In case this helps anyone else, I additionally wanted this query but for a given time interval that was not necessarily the last 12 months (also with a timestamp for the date):
如果这对其他人有帮助，我还想要这个查询，但对于给定的时间间隔，不一定是过去 12 个月（也有日期的时间戳）：
set @start='2013-05-01 00:00:00';
set @end='2015-03-31 00:00:00';
SELECT YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_date)), COUNT(yourtable.created_date)
FROM yourtable
WHERE created_date BETWEEN UNIX_TIMESTAMP( DATE(@start) ) AND UNIX_TIMESTAMP( DATE(@end) )
GROUP BY YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_date))
ORDER BY YEAR(FROM_UNIXTIME(yourtable.created_date)), MONTH(FROM_UNIXTIME(yourtable.created_date));
