检查连续 x 天 - 给定数据库中的时间戳

一尘不染

检查连续 x 天 - 给定数据库中的时间戳

sql

任何人都可以给我一个想法或提示如何在存储登录名（用户 ID、时间戳）的数据库表（MySQL）中检查连续 X 天？

Stackoverflow 做到了（例如像 Enthusiast 这样的徽章——如果你连续登录 30 天左右......）。您必须使用哪些功能或如何使用它的想法是什么？

像SELECT 1 FROM login_dates WHERE ...什么？

阅读 483

2021-07-01

共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天或以上的记录*ANYTIME*过去。

这个查询的首当其冲的是在第一个子选择中。让我们仔细看看，以便更好地理解它是如何工作的：

使用以下示例数据集：

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 天。对于与前一天不连续的每一天，NULLLEFT JOIN 会生成一个值。

现在，我们知道其中非连续的日子中，我们可以使用一个变量来区分各组通过检测转移表的行是否是连续数日NULL。如果是NULL，则日期不连续，因此只需增加变量即可。如果是NOT NULL，则不要增加变量：

在我们用递增变量区分每组连续天数之后，只需按每个“组”（如consec_set列中定义）进行分组并使用HAVING过滤掉任何少于指定连续天数的组（在您的示例中为 30）：

最后，我们包装*那个*查询并简单地计算连续 30 天或更多天的集合数。如果有这些集合中的一个或多个，则返回1，否则返回0。

2021-07-01