gpt4 book ai didi

mysql - 从MySQL中获取连续3天的数据

转载 作者:行者123 更新时间:2023-11-29 20:47:48 25 4
gpt4 key购买 nike

我正在尝试使用此问题 Check for x consecutive days - given timestamps in database 中的查询计算用户提交事件的连续天数,即 3 天、5 天、7 天等。

查询是:

SELECT IF(COUNT(1) > 0, 1, 0) AS has_consec
FROM
(
SELECT *
FROM
(
SELECT IF(b.dateAdded IS NULL, @val:=@val+1, @val) AS consec_set
FROM activity a
CROSS JOIN (SELECT @val:=0) var_init
LEFT JOIN activity b ON
a.userID = b.userID AND
a.dateAdded = b.dateAdded + INTERVAL 1 DAY
WHERE a.userID = 1
) a
GROUP BY a.consec_set
HAVING COUNT(1) >= 3
) a

当日期字段不是 dateTime 时,代码效果很好,但是我如何修改代码以忽略 dateTime 的时间部分?我尝试过使用 DATE(dateAdded) 但没有成功。

我的数据如下:

userID  dateAdded
1 2016-07-01 17:01:56
1 2016-07-02 12:45:49
1 2016-07-03 13:06:27
1 2016-07-04 12:51:10
1 2016-07-05 15:51:10
2 2016-07-06 16:51:10
2 2016-07-07 11:51:10
1 2016-07-08 11:26:38

谢谢

最佳答案

dateAdded 字段转换为 Date

请尝试一下,如果可以解决问题请告诉我:

SELECT IF(COUNT(1) > 0, 1, 0) AS has_consec
FROM
(
SELECT *
FROM
(
SELECT IF(b.dateAdded IS NULL, @val:=@val+1, @val) AS consec_set
FROM activity a
CROSS JOIN (SELECT @val:=0) var_init
LEFT JOIN activity b ON
a.userID = b.userID AND
DATE(a.dateAdded) = DATE(b.dateAdded) + INTERVAL 1 DAY
WHERE a.userID = 1
) a
GROUP BY a.consec_set
HAVING COUNT(1) >= 3
) a;

注意:仅当它们具有相同的时间(hh:mm:ss)时,使用timestamp才会返回正确的输出

关于mysql - 从MySQL中获取连续3天的数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38319544/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com