查询垃圾用户

Question

大致的表结构为
userId， loginDate

Answer 1

-- A
SELECT userid, COUNT(*) AS LoginDays, SUM(DailyCount)/COUNT(*) DailyAverage 
FROM 
(SELECT userid, CONVERT(varchar(10),logindate,120) AS LoginDate, COUNT(*) AS DailyCount
FROM tb 
GROUP BY userid, CONVERT(varchar(10),logindate,120)) S 
WHERE s.LoginDate >= DATEADD(year,-1,GETDATE())
GROUP BY userid 
HAVING COUNT(*) > 180 -- B
SELECT userid, COUNT(*) AS LoginDays, SUM(DailyCount)/COUNT(*) DailyAverage 
FROM 
(SELECT userid, CONVERT(varchar(10),logindate,120) AS LoginDate, COUNT(*) AS DailyCount
FROM tb 
GROUP BY userid, CONVERT(varchar(10),logindate,120)) S 
WHERE s.LoginDate >= DATEADD(year,-1,GETDATE())
GROUP BY userid 
HAVING COUNT(*) < 24 AND SUM(DailyCount)/COUNT(*) <= 2-- C
SELECT userid, COUNT(*) AS LoginDays, SUM(DailyCount)/COUNT(*) DailyAverage 
FROM 
(SELECT userid, CONVERT(varchar(10),logindate,120) AS LoginDate, COUNT(*) AS DailyCount
FROM tb 
GROUP BY userid, CONVERT(varchar(10),logindate,120)) S 
WHERE s.LoginDate >= DATEADD(year,-1,GETDATE())
GROUP BY userid 
HAVING COUNT(*) < 24 AND SUM(DailyCount)/COUNT(*) >= 5   -- 具体叁数自己视情况调整一下。

Answer 2

A类是按天维度统计的比例是1的, 那B就可以做成按月维度的计算比例大于1同时不属于A的用户

Answer 3

最早和最近登录相隔小于10天的用户SELECT userId
FROM table
GROUP BY userId
HAVING datadiff(day,min(loginDate),max(loginDate))<10