SQL 统计过去 12 个月内，按月统计记录条数，基于 created_at 字段

例如，我想统计过去一年内，每个月发布的博客数量。
即，查询 MySQL 表中过去 12 个月内，按月统计记录条数，基于 created_at 字段。

MySQL SQL 语句

SELECT
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    COUNT(*) AS count
FROM
    article
WHERE
    created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY
    YEAR(created_at),
    MONTH(created_at)
ORDER BY
    year DESC,
    month DESC;

输出结果

+------+-------+-------+
| year | month | count |
+------+-------+-------+
| 2024 |     9 |    16 |
| 2024 |     8 |    15 |
| 2024 |     7 |    21 |
| 2024 |     6 |    33 |
| 2024 |     5 |    22 |
| 2024 |     4 |    11 |
| 2024 |     3 |    13 |
| 2024 |     2 |    16 |
| 2024 |     1 |    15 |
| 2023 |    12 |     7 |
| 2023 |    11 |    15 |
| 2023 |    10 |    18 |
| 2023 |     9 |     3 |
+------+-------+-------+
13 rows in set (0.01 sec)

MS SQL Server SQL 语句

MS SQL 由于没有 DATE_SUB 函数，需要替换为：

SELECT
	YEAR(CreateDate) AS year,
	MONTH(CreateDate) AS month,
	COUNT(*) AS count
FROM
	[SomeDB].[dbo].[SomeTable]
WHERE
	CreateDate >= DATEADD(MONTH, -12, GETDATE())
GROUP BY
	YEAR(CreateDate),
	MONTH(CreateDate)
ORDER BY
	year DESC,
	month DESC;

golang 实现

先定义一个结构体，用来解析 SQL 查询返回

type CountGroupByMonth struct {
	Year  int `json:"year"`
	Month int `json:"month"`
	Count int `json:"count"`
}

例如，查询过去一年内的新增项目数，执行 SQL：

// 统计过去 12 个月内，按月统计新增项目记录条数，基于 created_at 字段
var projectCountGroupByMonth []CountGroupByMonth
models.DB.Raw(`
		SELECT
			YEAR(created_at) AS year,
			MONTH(created_at) AS month,
			COUNT(*) AS count
		FROM
			project
		WHERE
			created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
		GROUP BY
			YEAR(created_at),
			MONTH(created_at)
		ORDER BY
			year DESC,
			month DESC;
`).Scan(&projectCountGroupByMonth)

返回结果：

[
    {
      "year": 2024,
      "month": 9,
      "count": 1
    },
    {
      "year": 2024,
      "month": 8,
      "count": 5
    }
]

关于作者 🌱

我是来自山东烟台的一名开发者，有感兴趣的话题，或者软件开发需求，欢迎加微信 zhongwei 聊聊，或者关注我的个人公众号“大象工具”， 查看更多联系方式