大家好,借此篇文章,我想和你们分享一种使用 SQL 找出在连续三天内登录系统的用户的方法。这可能在一些场景下非常有用,例如预测用户留存、评估活跃程度或者识别潜在的高价值用户。
首先,假设我们有一个 user_login
表,它记录了所有用户的登录信息,包含两个字段:user_id
以及日期时间字段 login_date
。
CREATE TABLE user_login (
user_id int,
login_date date
);
然后,我们插入一些模拟数据:
INSERT INTO user_login (user_id, login_date)
VALUES
(1, '2022-01-01'),
(1, '2022-01-02'),
(1, '2022-01-03'),
(2, '2022-01-02'),
(2, '2022-01-04'),
(3, '2022-01-01'),
(3, '2022-01-05');
这里我们可以看到用户1在’2022-01-01′, ‘2022-01-02’ 和 ‘2022-01-03’ 都有登录,所以满足连续三天登录的条件。
下面就是主题部分,怎么使用 SQL 查询找出连续登录三天的用户呢?
方法一:
SELECT a.user_id,EXTRACT(DAY FROM a.next_2_login - a.login_date::timestamp )
FROM (
SELECT
user_id,
login_date,
LEAD(login_date,2) OVER (PARTITION BY user_id ORDER BY login_date) as next_2_login
FROM
dw.user_login
) a
WHERE
EXTRACT(DAY FROM a.next_2_login - a.login_date::timestamp ) =2
在这条 SQL 语句中,我们使用了 LEAD()
函数,这是一个窗口函数,它可以返回在当前行之后的指定偏移量的值。因此,LEAD(login_date, 2)
就表示的是每个用户登录日期后面第二个的登录日期。结果如下:

然后我们用这个登录日期减去当前的登录日期,如果结果等于2,就意味着用户在这三天内都有登录记录。需要注意的是,窗口函数按照 PARTITION BY
子句中列出的字段对结果进行分区,将它们作为分组,并按照ORDER BY
子句中列出的字段对每个合格组进行排序。结果如下:

值得一提的是,我们假设了 login_date
字段的数据类型是 DATE
,只包含日期部分,没有时间部分。因此,我们使用 EXTRACT()
函数减去的是两个日期,而不是具体的时间戳。如果你的数据包含具体时间戳,可能需要在查询语句中进行些许改动以满足你的需求。
方法二:
SELECT user_id,login_date,login_date - CAST(row_num AS int)
FROM (
SELECT user_id, login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS row_num
FROM dw.user_login
) AS subquery
GROUP BY user_id, (login_date - CAST(row_num AS int) )
HAVING COUNT(*) >= 3;
在这个查询中,我们首先使用窗口函数ROW_NUMBER()对每个用户根据登录日期进行排序,并为每一行分配一个序号(row_num)。结果如下:

接下来,我们根据user_id和登录日期减去序号来进行分组。最后,通过HAVING COUNT(*) >= 3条件筛选出连续登录三天及以上的用户。结果如下:

请注意,这个查询仍然使用了窗口函数,但是在分组和聚合阶段使用了”current_date – 1″来计算登录日期减去序号的值。根据不同的数据库管理系统,请调整语法以适应特定的日期减法操作。
方法三:
SELECT DISTINCT t1.user_id
FROM dw.user_login t1
JOIN dw.user_login t2 ON t1.user_id = t2.user_id AND EXTRACT(DAY FROM t1.login_date::timestamp - t2.login_date::timestamp ) = 1
JOIN dw.user_login t3 ON t1.user_id = t3.user_id AND EXTRACT(DAY FROM t1.login_date::timestamp - t3.login_date::timestamp ) = 2;
在这个查询中,我们对user_login表进行了两次自连接,每次比较前一天和后一天的日期差是否为1,从而确定登录是否连续三天。通过这种方式,我们可以得到那些拥有连续登录三天行为的用户。结果如下:

请注意,具体的语法可能因数据库管理系统而异,请根据您使用的数据库调整语法。
方法四:
SELECT DISTINCT user_id
FROM (
SELECT user_id, login_date,
COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS consecutive_days
FROM dw.user_login
) AS subquery
WHERE consecutive_days = 3;
在这个查询中,我们使用COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)来计算每个用户在当前行之前的3行是否存在,该窗口函数为每个用户分配了一个连续登录天数的计数值。结果如下:

然后我们将这个子查询作为内部表,并根据consecutive_days为3进行筛选,得到所有连续登录三天的用户。结果如下:

注意:具体的语法可能因数据库管理系统而异,请根据您使用的数据库调整语法。
请创建你所关心的表,运行上述 SQL 命令,并尝试在不同场景下测试它。看看它是否按照你的预期返回了连续三天登录的用户。
需要明确的是,这段 SQL 只能够检测到那些至少连续登录3天的用户。如果你的需求更为复杂,比如想找到连续多日、或者非连续多日登录的用户,可能需要使用其他更为复杂的方法。
同时,以上的 SQL 查询适用于大多数支持窗口函数(如 LEAD)和日期计算函数(如 DATEDIFF)的数据库系统,例如 MySQL, PostgreSQL, Oracle 和 SQL Server。
最后,希望你在 SQL 的学习之路上取得进步,并找到这种解决特定问题的实用方法。如果遇到任何问题,或有什么想要了解的 SQL 技巧,欢迎随时向我发问,我会尽快回复你的。
SQL 是一门非常强大且灵活的语言,越是精通它,就越能洞察数据隐藏的秘密,完成更多以前看似无法解决的问题。所以继续学习、实践和探索吧!记得关注我们哟,获取更多小知识!希望你每天都能有新的收获!
参考资料:
-
PostgreSQL 文档:https://www.postgresql.org/docs/ -
MySQL 文档:https://dev.mysql.com/doc/
原文始发于微信公众号(运维小九九):SQl技巧:如何获取连续登录三天的用户
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之音整理,本文链接:https://www.bmabk.com/index.php/post/218604.html