查询以获取在过去30天内登录的所有用户

Question

我有一个MySQL数据库users表，它有一个名为lastLogin的列，这只是一个简单的时间戳，用于指示用户上次登录系统的时间。

例如

lastLogin1639572638>//tr>

	id	name	accountId
2	bob	4
3	tim	1639572638	4
3	ant	1639572638	5
	勒罗伊

SELECT accountId, 
FROM_UNIXTIME(MAX(lastlogin)) lastlogin,  -- not listed in desired output
-- but present in the query
SUM(lastlogin > UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY)) activeUsers 
FROM user
GROUP BY accountId

对于distinct id，使用

SELECT accountId, 
FROM_UNIXTIME(MAX(lastlogin)) lastlogin,
COUNT(DISTINCT CASE WHEN lastlogin > UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY) THEN id END) activeUsers  
FROM user
GROUP BY accountId

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f754e9ed49d872d0d68173a803f96126

Answer 1

试试这个：

with u as
(select accountId, count(distinct id) as activeUsers from user
group by accountId
having FROM_UNIXTIME(max(lastlogin)) > now() - INTERVAL 30 day),
v as
(select distinct accountId from user)
(select v.accountId, coalesce(u.activeUsers, 0) as activeUsers from v left join 
u on v.accountId = u.accountId)

Fiddle

Answer 2

好吧，我发现它有望帮助其他人-

SELECT accountId,count(distinct(id)) as activeUsers 
FROM user 
WHERE FROM_UNIXTIME(lastlogin) > now() - INTERVAL 30 day 
GROUP BY accountId;