mysql - mysql中每个时间段内的计数器项目数

我有一个用户列表，其中每个用户注册时都为其分配了注册时间戳。

如何计算整个23个月的整个用户列表中每个月注册的用户数。

因此，输出应仅为2列，一列包含month + year，另一列显示该月有多少用户注册。

有人可以建议这样做吗？

示例数据

INSERT INTO `users` (`id`, `regdate`) VALUES
(861490, 1314021552),
(944392, 1317760487),
(674263, 1311119415),
(287512, 1305242049),
(125134, 1299310950),
(714619, 1311658393),
(777858, 1312508020),
(812091, 1312918459),
(979515, 1319524143),
(1009713, 1321038952),
(395950, 1307314421),
(552213, 1309435427),
(549979, 1309395329),
(919872, 1316468483),
(385920, 1307196494),
(825096, 1313085971),
(967657, 1318961937),
(136631, 1299825660),
(838405, 1313315222),
(984647, 1319803409),
(239280, 1303860435),
(917747, 1316368790),
(621945, 1310480971),
(1078781, 1324529892),
(897934, 1315556339),
(311070, 1305899412),
(830274, 1313157183),
(518156, 1308952471),
(524182, 1309037939),
(524142, 1309037521);

最佳答案

SELECT DATE_FORMAT(FROM_UNIXTIME(regdate), '%Y-%m') AS month,
       COUNT(*) AS registrations
FROM users
GROUP BY month