时间戳

首页 > 解决方案 > 在sql中按月统计新用户

mysql - 在sql中按月统计新用户

问题描述

我正在尝试计算今年每个月记录的新用户。喜欢

每月需要数据，按月新用户注册

JAn  Feb  Mar  April May ..... Dec

1     2   4     2     5 .....  1

通过 created_at 日期和用户 ID。

这是 user_table

id     created_at
1      2020-01-09 22:38:55
2      2020-02-09 22:38:55
3      2020-02-09 22:38:55
4      2020-03-09 22:38:55
5      2020-03-09 22:38:55
6      2020-03-09 22:38:55
7      2020-04-09 22:38:55
8      2020-04-09 22:38:55
9      2020-05-09 22:38:55

我正在尝试这个查询

SELECT  ut.id, Month(FROM_UNIXTIME(ut.created_at)), Count(*) 
from  $userTable ut 
where FROM_UNIXTIME(ut.created_at) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Month(FROM_UNIXTIME(ut.created_at));

标签： mysqlsql

解决方案

您可以按年分组并按月求和，如下所示：

 select YEAR(created_at) as year,
  sum(case when Month(created_at) = 1 then 1 else 0 end) AS Jan,
  sum(case when Month(created_at) = 2 then 1 else 0 end) AS Feb,
  sum(case when Month(created_at) = 3 then 1 else 0 end) AS Mar,
  sum(case when Month(created_at) = 4 then 1 else 0 end) AS Apr,
  sum(case when Month(created_at) = 5 then 1 else 0 end) AS May,
  sum(case when Month(created_at) = 6 then 1 else 0 end) AS Jun,
  sum(case when Month(created_at) = 7 then 1 else 0 end) AS Jul,
  sum(case when Month(created_at) = 8 then 1 else 0 end) AS Aug,
  sum(case when Month(created_at) = 9 then 1 else 0 end) AS Sep,
  sum(case when Month(created_at) = 10 then 1 else 0 end) AS Oct,
  sum(case when Month(created_at) = 11 then 1 else 0 end) AS Nov,
  sum(case when Month(created_at) = 12 then 1 else 0 end) AS Dec from ut group by YEAR(created_at)

推荐阅读