Mysql查询以获取每月计数

2021-12-27 00:00:00 group-by count mysql

我的用户有一个表，其中有一个名为created"的字段，其中包含注册日期.

I have a table for my users that have a field named "created" that have the registration date.

如何获取包含过去 12 个月内每月注册数量计数的列表?像这样:

How can i get a list that contains a count for the registrations number per month in last 12 months? Like this:

Month   Count
1        1232
2        2222
3         122
4        4653
...       ...
12       7654

我不习惯使用 mysql，所以直到现在我只知道如何计算去年的注册数量，而不是如何按过去 12 个月对计数进行分组.提前致谢！

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!

更新

现在我得到了这个，使用@fthiella 解决方案:

Now I'm getting this, using @fthiella solution:

+------------------------------+-------------------------------+----------+
| 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 |
+------------------------------+-------------------------------+----------+

如何强制查询为我提供 count = 0 的月份?

How can i force query to give me the months with count = 0?

@fthiella 的解决方案(非常感谢！):

Solution by @fthiella (thanks a lot!):

     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;

结果:

+------+----+----------------------+
| 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 |
+------+----+----------------------+

推荐答案

如果 created 是一个 INT 字段，你应该使用 FROM_UNIXTIME 函数将其转换为日期字段，然后 MONTH 提取月份的函数:

If created is an INT field, you should use FROM_UNIXTIME function to convert it to a date field, and then MONTH function to extract the month:

SELECT Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Month(FROM_UNIXTIME(created))

这将计算过去 12 个月内创建的所有行.请注意，最好也按年份分组:

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:

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)

跳过空值，或

COUNT(DISTINCT registration_number)

只计算不同的.

编辑

如果您还需要显示 count=0 的月份，我会使用这样的查询来返回当前和上一年的所有月份:

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:

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;

然后我会使用一个LEFT JOIN，它返回第一个查询的所有行，并且只返回匹配的第二个查询的行:

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:

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

(请注意，这里我只考虑过去 12 个月，所以如果我们在 2013 年 4 月中旬，它将计算 2012 年 5 月 - 4 月 13 日之间的行数，如果这不是正确的行为，请告诉我)

(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)

相关文章