查询每天每周每月用户数量代码,MYSQL代码库,德仔网

代码语言

代码分类

【MYSQL】查询每天每周每月用户数量

作者: / 发布于2016/12/2/ 794

查询每天每周每月用户数量

SELECT  * FROM login_history WHERE DAY(loginTime)=DAY(NOW())

SELECT * FROM `login_history` WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(`loginTime`)

SELECT * FROM `login_history` WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(`loginTime`)

SELECT * FROM login_history WHERE loginTime BETWEEN DATE_SUB(NOW(),INTERVAL 3 MONTH) AND NOW() ORDER BY loginTime ASC

SELECT * FROM login_history WHERE loginTime BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW() ORDER BY loginTime ASC

SELECT * FROM `login_history` WHERE YEAR(loginTime)=YEAR(NOW()) ORDER BY loginTime ASC



//查询每个用户每天登陆的次数
SELECT userId, DATE(loginTime) AS login_date, COUNT(userId) 
FROM login_history
WHERE loginTime>=CURRENT_DATE() AND loginTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY)
GROUP BY userid, login_date  

//查询每天所有用户登录的次数
SELECT  DATE(loginTime) AS login_date, COUNT(userId) 
FROM login_history
WHERE loginTime>=CURRENT_DATE() AND loginTime<DATE_ADD(CURDATE(),INTERVAL 1 DAY)
GROUP BY  login_date

//获取一周内每天登陆的次数
SELECT DATE(loginTime) AS login_date, COUNT(userId)  
FROM `login_history` WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(`loginTime`) 
GROUP BY login_date

获取年、月、日及本周是一年中的第几周
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAYOFYEAR(CURDATE()),WEEKOFYEAR(CURDATE())FROM login_history

//每周活跃率
SELECT WEEK(loginTime) AS login_week, COUNT(userId)  
FROM `login_history` 
WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= DATE(`loginTime`) 
GROUP BY login_week

//每月活跃率
SELECT MONTH(loginTime) AS login_month, COUNT(userId)   
FROM login_history 
WHERE loginTime BETWEEN DATE_SUB(NOW(),INTERVAL 12 MONTH) AND NOW() 
GROUP BY login_month ORDER BY loginTime

//一周新增用户

SELECT DATE(createTime) AS login_date, COUNT(id) as number
FROM account WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(createTime) 
GROUP BY login_date ORDER BY createTime

//一季度新怎用户
//一年新增用户


http://192.168.21.19/u2.0/Account/Register/sign

试试其它关键字

同语言下

可能有用的

贡献的其它代码Label