检查连续X天 - 在数据库中给出的时间戳(Check for x consecutive days

可能有人给我一个想法或提示你如何能在一个数据库表来检查连续X天（MySQL的），其中登录（用户ID，时间戳）存储？

计算器做它（例如徽章爱好者一样 - 如果你登录了连续30天左右...）。 你将有什么样的功能使用或什么是如何做到这一点的想法？

喜欢的东西SELECT 1 FROM login_dates WHERE ... ？

Answer 1:

您可以通过完成这个移位自外连接与变量结合。 看到这个解决方案：

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

这将返回一个1或0基于用户是否已经登录了随时在过去连续30天以上。

此查询的首当其冲是真正的第一子查询中。 让我们来仔细看看，所以我们可以更好地理解它是如何工作的：

用下面的例子的数据集：

CREATE TABLE tbl (
  user_id INT,
  login_date DATE
);

INSERT INTO tbl VALUES
(1, '2012-04-01'),  (2, '2012-04-02'),
(1, '2012-04-25'),  (2, '2012-04-03'),
(1, '2012-05-03'),  (2, '2012-04-04'),
(1, '2012-05-04'),  (2, '2012-05-04'),
(1, '2012-05-05'),  (2, '2012-05-06'),
(1, '2012-05-06'),  (2, '2012-05-08'),
(1, '2012-05-07'),  (2, '2012-05-09'),
(1, '2012-05-09'),  (2, '2012-05-11'),
(1, '2012-05-10'),  (2, '2012-05-17'),
(1, '2012-05-11'),  (2, '2012-05-18'),
(1, '2012-05-12'),  (2, '2012-05-19'),
(1, '2012-05-16'),  (2, '2012-05-20'),
(1, '2012-05-19'),  (2, '2012-05-21'),
(1, '2012-05-20'),  (2, '2012-05-22'),
(1, '2012-05-21'),  (2, '2012-05-25'),
(1, '2012-05-22'),  (2, '2012-05-26'),
(1, '2012-05-25'),  (2, '2012-05-27'),
                    (2, '2012-05-28'),
                    (2, '2012-05-29'),
                    (2, '2012-05-30'),
                    (2, '2012-05-31'),
                    (2, '2012-06-01'),
                    (2, '2012-06-02');

这个查询：

SELECT a.*, b.*, IF(b.login_date IS NULL, @val:=@val+1, @val) AS consec_set
FROM tbl a
CROSS JOIN (SELECT @val:=0) var_init
LEFT JOIN tbl b ON 
    a.user_id = b.user_id AND
    a.login_date = b.login_date + INTERVAL 1 DAY
WHERE a.user_id = 1

会产生：

正如你所看到的，我们所做的是通过1天换挡连接表。 每天不是连续的与前一天，一个NULL由LEFT生成值JOIN。

现在我们知道了不连续的日子中，我们可以使用一个变量通过检测移表的行是否是区分每一组连续几天NULL 。 如果它们是NULL ，天是不连续的，所以只是增加变量。 如果它们是NOT NULL ，则不要增加变量：

我们区分每一套连续几天后与递增的变量，它然后通过每个“设置”分组的只是一件简单的事（如定义consec_set列），并使用HAVING筛选出具有小于指定任何一组连续几天（在你的榜样30）：

于是最后，我们结束该查询，并且只计算集，曾连续30天或以上的数量。 如果有一个或多个这些集合的，则返回1 ，否则返回0 。

---

看到SQLFiddle一步一步演示

Answer 2:

您可以添加X到时间戳的日期和再检查一下，如果不同（日期）在此日期范围是== X：

的这30天，每一天至少一次：

SELECT distinct 1 
FROM 
   login_dates l1 
inner join
   login_dates l2
      on l1.user = l2.user and 
         l2.timestamp between l1.timestamp and  
                              date_add( l1.timestamp, Interval X day )
where l1.user = some_user
group by 
   DATE(l1.timestamp)
having 
   count( distinct DATE(l1.timestamp) ) = X

（你不speack对性能要求...;））

*编辑*只有最后X天查询：的这30天，每天一次东

SELECT distinct 1 
FROM 
   login_dates l1 
where l1.user = some_user
      and l1.timestamp >  date_add( CURDATE() , Interval -X day )
group by
    l1.user
having 
   count( distinct DATE(l1.timestamp) ) = X

Answer 3:

这是单独的SQL来解决一个困难的问题。

问题的核心是，你需要动态结果集在一个查询中相互比较。 例如，你需要得到所有登录/会话ID为一个日期，然后用列表JOIN或UNION他们登录之日起（）（你可以使用DATE_ADD确定）的分组。 你可以为N个连续的日期做到这一点。 如果你已经离开了任何行，那么这些会话已被记录在该段期间。

假设如下表：

会话ID INT，创建日期

该查询返回所有具有对过去两天行sessionids：

select t1.sessionid from logins t1 
  join logins t2 on t1.sessionid=t2.sessionid 
  where t1.created = DATE(date_sub(now(), interval 2 day)) 
    AND t2.created = DATE(date_sub(now(), interval 1 day));

正如你所看到的，SQL会得到粗糙30天。 有一个脚本生成它。 :-D

这进一步假定每天登录表与会话更新。

我不知道这实际上解决您的问题，但我希望我已经帮助框架的问题。

祝好运。

Answer 4:

那岂不是更简单地在login_dates表一个额外的列consecutive_days使用默认值1。这表明在这一天结束连续日期的长度。

在您检查是否有前一天的条目您创建login_dates触发后插入。

如果没有，那么字段将有一个新的序列，在那一天开始的默认值1的含义。

如果这里是前一天的条目，那么你从默认1更改days_logged_in值为1大于以前的日子。

例如：

| date       | consecutive_days |
|------------|------------------|
| 2013-11-13 | 5                |
| 2013-11-14 | 6                |
| 2013-11-16 | 1                |
| 2013-11-17 | 2                |
| 2013-11-18 | 3                |

文章来源: Check for x consecutive days - given timestamps in database