我有一个数据库表,我跟踪使用我的应用程序的人。每个用户每天可能有多个使用。因此,给定单个用户的示例记录集可能看起来像:
userId dateOfUsage
1 2012-12-01
1 2012-12-02
1 2012-12-02
1 2012-12-03
1 2012-12-06
1 2012-12-07
1 2012-12-07
1 2012-12-08
假设今天是2012-12-08。我想要得到今天和最后一个记录之间的天数,在三天或更长时间内没有记录。在上面的例子中,在2012-12-03和2012-12-06之间有一个休息时间,所以我想计算一下从2012-12-06到今天(2012-12-08)之间的天数。答案是6日到8日的"连续3天"。
是否可以在MySQL中这样做?我如何在MySQL中为给定的用户做到这一点?是否有可能一次为所有用户做到这一点?
我发现了关于条纹的其他问题,但没有任何运气来解决我的请求。谢谢!
您可以使用带有游标的存储过程来创建临时表。
或者你也可以这样写
set @dateOfUsage = NULL;
SELECT * FROM (
SELECT IF(@dateOfUsage IS NULL, 0, datediff(dateOfUsage, @dateOfUsage)) as gap, @dateOfUsage:=dateOfUsage AS dateOfUsage FROM (
SELECT * FROM `usage` WHERE userID = 1 ORDER BY dateOfUsage
) `usage`
) `t` WHERE gap >= 3;
http://sqlfiddle.com/!2/22228/2
请注意,您需要使用子查询,因为表中的行顺序不能保证。
我需要另一个子查询来过滤(虽然我不明白为什么HAVING
不会)。
如果你喜欢筛选用户
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,userId INT NOT NULL
,dateOfUsage DATE NOT NULL
);
INSERT INTO my_table (userid,dateofusage) VALUES
(1 ,'2012-12-01'),
(1 ,'2012-12-02'),
(1 ,'2012-12-02'),
(1 ,'2012-12-03'),
(2 ,'2012-12-04'),
(2 ,'2012-12-05'),
(1 ,'2012-12-06'),
(1 ,'2012-12-07'),
(1 ,'2012-12-07'),
(1 ,'2012-12-08');
SELECT DATEDIFF('2012-12-08',MAX(b.dateofusage))+1 streak
FROM ( SELECT x.*, COUNT(*) rank FROM my_table x JOIN my_table y ON y.userid = x.userid AND y.id <= x.id GROUP BY x.id) a
JOIN ( SELECT x.*, COUNT(*) rank FROM my_table x JOIN my_table y ON y.userid = x.userid AND y.id <= x.id GROUP BY x.id) b
ON b.dateofusage >= a.dateofusage + INTERVAL 3 DAY
AND b.rank = a.rank + 1
AND b.userid = a.userid
GROUP
BY a.userid;