拿捏SQL：以“统计连续登录天数超过3天的用户“为例拿捏同类型SQL需求

最新推荐文章于 2024-08-27 11:13:57 发布
最新推荐文章于 2024-08-27 11:13:57 发布
阅读量4.5k 收藏 31
点赞数 5
分类专栏： 笔记 大数据架构 mysql 文章标签： sql 大数据 hive mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_49303490/article/details/130469205
版权
笔记 同时被 3 个专栏收录
48 篇文章 1 订阅
订阅专栏
大数据架构
16 篇文章 4 订阅
订阅专栏
mysql
3 篇文章 1 订阅
订阅专栏
文章介绍了两种统计用户连续登录天数超过3天的方法。方案1利用窗口函数ROW_NUMBER()和DATE_SUB()分组计算；方案2借助lag和lead函数检查日期差。两种方案对比，方案1实现简单，适合基础SQL操作，方案2需要更深入的窗口函数知识，但思路直观。
摘要由CSDN通过智能技术生成

文章目录

  • 作为大数据开发人员，绝不能丢下SQL能力。

一、介绍

在我们工作ETL或者在进行大数据面试的时候，总是常常会被手撕SQL，而常见的SQL有连续登录问题。一般的问法是“统计连续登录N天XX的XX”。
小编今天在这里给大家介绍两种方案轻松让你拿捏这类SQL问题。

mysql8.x和hive很多函数已经基本差不多都能满足了，为了执行效率和方便，这里以mysql为例，其他SQL类似，如果有不清楚的可以评论区留言。

案例：以"统计连续登录天数超过3天的用户"为需求。

数据准备

在mysql中执行以下代码，生成相应的数据表

-- ----------------------------
-- Table structure for user_activity
-- ----------------------------
DROP TABLE IF EXISTS `user_activity`;
CREATE TABLE `user_activity`  (
  `user_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `activity_date` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_activity
-- ----------------------------
INSERT INTO `user_activity` VALUES ('user1', '2023-03-01');
INSERT INTO `user_activity` VALUES ('user2', '2023-03-02');
INSERT INTO `user_activity` VALUES ('user3', '2023-03-03');
INSERT INTO `user_activity` VALUES ('user4', '2023-03-04');
INSERT INTO `user_activity` VALUES ('user1', '2023-03-08');
INSERT INTO `user_activity` VALUES ('user2', '2023-03-08');
INSERT INTO `user_activity` VALUES ('user5', '2023-03-08');
INSERT INTO `user_activity` VALUES ('user6', '2023-03-08');
INSERT INTO `user_activity` VALUES ('user3', '2023-03-09');
INSERT INTO `user_activity` VALUES ('user5', '2023-03-09');
INSERT INTO `user_activity` VALUES ('user6', '2023-03-09');
INSERT INTO `user_activity` VALUES ('user7', '2023-03-09');
INSERT INTO `user_activity` VALUES ('user3', '2023-03-10');
INSERT INTO `user_activity` VALUES ('user5', '2023-03-10');
INSERT INTO `user_activity` VALUES ('user6', '2023-03-10');
INSERT INTO `user_activity` VALUES ('user7', '2023-03-10');
INSERT INTO `user_activity` VALUES ('user5', '2023-03-11');
INSERT INTO `user_activity` VALUES ('user6', '2023-03-11');
INSERT INTO `user_activity` VALUES ('user7', '2023-03-11');
INSERT INTO `user_activity` VALUES ('user6', '2023-03-12');
INSERT INTO `user_activity` VALUES ('user7', '2023-03-12');
INSERT INTO `user_activity` VALUES ('user7', '2023-03-13');
INSERT INTO `user_activity` VALUES ('user8', '2023-03-13');
INSERT INTO `user_activity` VALUES ('user7', '2023-03-14');
INSERT INTO `user_activity` VALUES ('user8', '2023-03-14');
INSERT INTO `user_activity` VALUES ('user7', '2023-03-15');
INSERT INTO `user_activity` VALUES ('user8', '2023-03-15');
INSERT INTO `user_activity` VALUES ('user8', '2023-03-16');

SELECT * FROM `user_activity`

结果如下：

user1	2023-03-01
user2	2023-03-02
user3	2023-03-03
user4	2023-03-04
user1	2023-03-08
user2	2023-03-08
user5	2023-03-08
user6	2023-03-08
user3	2023-03-09
user5	2023-03-09
user6	2023-03-09
user7	2023-03-09
user3	2023-03-10
user5	2023-03-10
user6	2023-03-10
user7	2023-03-10
user5	2023-03-11
user6	2023-03-11
user7	2023-03-11
user6	2023-03-12
user7	2023-03-12
user7	2023-03-13
user8	2023-03-13
user7	2023-03-14
user8	2023-03-14
user7	2023-03-15
user8	2023-03-15
user8	2023-03-16

在这里插入图片描述

方案1：常规思路

  • 1、先对数据user_id分组，根据用户的活动日期排序
  • 2、用登录日期与rn求date_sub，得到的差值日期如果是相等的，则说明这两天肯定是连续的
    • 举例说，2023年1月1号、1月2号、1月3号；排名分别是1,2,3；现在用日期 - 排名 是不是都等于2022年12月31号
  • 3、根据user_id和日期差sub_date分组，登录次数即为分组后的count(1)
针对对数据user_id分组，根据用户的活动日期排序
select
			user_id,
			activity_date,
			ROW_NUMBER() over(partition by user_id order by activity_date) as rn
from user_activity

在这里插入图片描述

用登录日期与rn求date_sub，得到的差值日期如果是相等的，则说明这两天肯定是连续的
SELECT
		user_id,
		activity_date,
		DATE_SUB(activity_date,INTERVAL rn DAY) as sub_date
	from(
		select
			user_id,
			activity_date,
			ROW_NUMBER() over(partition by user_id order by activity_date) as rn
		from user_activity
	)t1

在这里插入图片描述

根据user_id和日期差sub_date分组，登录次数即为分组后的count(1)
SELECT
	user_id,
	min(activity_date) as min_date,
	max(activity_date)  as max_date,
	count(1) as  login_times
from(
	SELECT
		user_id,
		activity_date,
		DATE_SUB(activity_date,INTERVAL rn DAY) as sub_date
	from(
		select
			user_id,
			activity_date,
			ROW_NUMBER() over(partition by user_id order by activity_date) as rn
		from user_activity
	)t1
)t2
group by user_id,sub_date
having login_times>=3;

在这里插入图片描述

  • 从结果可以看出用户5,6,7,8存在连续登录3天及其以上的用户

方案2：使用lag和lead函数

  • 1、针对每个user_id,先使用lag和lead函数将当前日期的前一天和后后一天日期求出来
  • 2、针对每个用户，进行前一天和后一天的日期与当期日期相差值=1则属于连续登录。
    • 举例说，2023年1月1号、1月2号、1月3号；现在用日期2号 - 前后与它相差值2-1=1；3-2=1.是不是值都否为1呢。
  • 3、针对用户分组，datediff函数求出最大活动时间和最小活动时间的天数，求出>=3天的用户
使用LEAD和LAG函数求出前后1天日期
select
		user_id,
		LAG(activity_date,1,activity_date) over(partition by user_id order by activity_date) as lag_login_date,
		activity_date as current_login_date,
		LEAD(activity_date,1,activity_date) over(partition by user_id order by activity_date) as lead_login_date
	from user_activity

在这里插入图片描述

针对每个用户，进行前一天和后一天的日期与当期日期相差值=1则属于连续登录。
SELECT
	user_id,
	lag_login_date,
	current_login_date,
	lead_login_date
from(
	select
		user_id,
		LAG(activity_date,1,activity_date) over(partition by user_id order by activity_date) as lag_login_date,
		activity_date as current_login_date,
		LEAD(activity_date,1,activity_date) over(partition by user_id order by activity_date) as lead_login_date
	from user_activity
)t1
where datediff(current_login_date,lag_login_date)=1 
and datediff(lead_login_date,current_login_date)=1;

针对用户分组，datediff函数求出最大活动时间和最小活动时间的天数，求出>=3天的用户
SELECT
	user_id,
	min(activity_date) as min_date,
	max(activity_date)  as max_date,
	count(1) as  login_times
from(
	SELECT
		user_id,
		activity_date,
		DATE_SUB(activity_date,INTERVAL rn DAY) as sub_date
	from(
		select
			user_id,
			activity_date,
			ROW_NUMBER() over(partition by user_id order by activity_date) as rn
		from user_activity
	)t1
)t2
group by user_id,sub_date
having login_times>=3;

在这里插入图片描述

对比方案1和方案2

方案1，想法很简单，更容易实现，简单了解开窗排序函数和基础SQL能力即可完成。难度中
,
方案2，想法简单，实现难度更大一些，需要对开窗函数有一定的掌握和熟练。难度高

诗风雅韵
关注
专栏目录
MySQL计算连续3天登陆的用户
sdez12的博客
08-01 3583
table_a （ 用户userid和登录时间time）求连续登录3天的用户数。①可以使用窗口函数，根据用户分组然后再根据登录时间升序，再进行编号，这里的编号函数可以使用row_number()、dense_rank()、rank()都可以，因为同一用户不可能在同一时间登录，所以同一用户不可能出现两个完全相同的时间。 ②如果两个时间连续，必定登录的时间减去编号天数是相同的 ③相同个数大于3的用户即为所求代码如下（示例）： 运行结果如图： 代码如下（示例）： 运行结果如图： 代码如下（示例）：...
Microsoft SQL Server 自定义函数整理大全
weixin_33834628的博客
07-22 2365
01、去除字符串中的html标记及标记中的内容【叶子函数分享一】去除字符串中的html标记及标记中的内容 --1、创建函数 createfunction[dbo].[clearhtml](@macovarchar(8000)) returnsvarchar(8000)asbegin declare@iint while1=1 b...
SQL刷题-连续3天登陆的用户
weixin_45590392的博客
05-16 610
思路：先取得每个用户登陆日期及后两次登陆日期。若两次登陆日期间隔都为1则为连续登录。lead()、窗口函数。
大数据——SQL解题——统计连续登录天数超过3天的用户
最新发布
Rischaos的博客
08-27 429
统计连续登录天数超过3天的用户。
SQL统计连续登陆3天的用户(连续活跃超3天用户)
Taerge0110的博客
11-21 6319
【代码】SQL统计连续登陆3天的用户(连续活跃超3天用户)
SQL-获取至少连续登录三天以上的用户
m0_58654936的博客
08-29 1347
用户每天可能登录多次，现在的数据是进行过去重的，所以目前是用户每天只登陆一次的，首先我们要对用户进行分组，然后我们可以使用开窗函数对用户进行分区然后在对时间排序 连续登录就可以使用时间-行数 假如这个用户一直是连续的 那么相减的日期肯定是相等的。
SQL查询连续三天登录的用户
程序员之道
06-09 1万+
1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。 2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登录日期进行排序。 3、计算登录日期减去第二步骤得到的结果值，用户连续登录情况下，相减的结果都相同。 4、按照id和日期分组并计数，筛选大于等于3的即为连续3天登录的用户。...
sql选取连续三天登录的用户
Caiqiudan的博客
09-23 6877
创建表test_user_login_3days： create table test_user_login_3days( user_id int, login_date date ); insert into test_user_login_3days values (123,'2018-08-02'); insert into test_user_login_3days values (123,'2018-08-03'); insert into test_user_login_3day
seo相关文章
热门推荐
luofengdong的专栏
03-01 3万+
<br />SEO 网址：http://blog.myspace.cn/xmmei/category/41652.aspx<br /><br /><br /> 五个等级SEOer的特征分析<br />  顶尖的SEOer：<br /><br /> 他们几乎不写博客、不回帖、不参与互联网的争论，每天专心耕耘自己的网站，赚钱很低调，生活很滋润。新浪的SEO不知道哪位负责的，我一直想认识一 下，感觉他是顶尖的。另外听说有某SEOer搞英文站做外贸，公司每月营业额好几百万但只有30几个员工，前台小姐的工资都在
【Java，C++，Web, Python】等开发/软件岗位
华鑫的专栏
04-19 471
太太太太太太太太幸运了，真的收获满满，开心到飞起，赶紧发帖留念！------------------------------------------[诶嘿]可乐姐持。暑期实习，后端开发岗美团，base北京，到家平台技术。昨天面完中移信息终面，能不能通过也只能看老天爷了，然后看了下offershow，它给的薪资很低，很好。再来捞一波，有无今年校招在京东北京总部干运营的小伙伴，或者其他岗位也可以，认识一下引流，腾讯，字节，面试全部通过了，昨天早上最后的主管面面完，HR跟我说通过了，让我等通知。
高通成都linux engineer intern 一面面经
2301_78234743的博客
04-22 579
总的来说欧莱雅面试体验都是满分，我面的是管理培训生岗，难度大概中等水平，目前告知通过了。区块链钱包是连接用户与区块链网络的重要工具，它们不仅提供了安全的存储和管理数字资产的功能，还允许用户。周一还没收到oc，深圳云计算开发岗到底还有没有hc了，上周三普联发了offer，一直没签，今天了也没。我是IP本地的，硕士是本地双非，计算机专业，方向是多源信息融合定位，现在手上有两个offer，一个是。本人9本top5硕，秋招签了联洲，最近碰运气投了广西中烟，没想到顺利通关，权衡了两家的利弊，现在还在。
【MySQL】MySQL统计连续登录3天的用户
@苏丶的博客
10-18 2526
mysql统计指定7天内登录3天以上的用户
大数据SQL经典面试题系列(1) - 连续3天登录用户
寒暄的博客
07-09 1236
SELECT user_id, count( 1 ) cnt FROM ( SELECT user_id, login_date, row_number() over ( PARTITION BY user_id ORDER BY login_date ) rn FROM sqllx.tmp_last_3_day ) t GROUP BY user_id, data_sub ( login_date, t.rn ) HAVING count( 1 ) &g.
SQL 经典面试题：统计最近七天连续三天活跃的用户
SUNK2014的博客
10-10 4225
给定 mid，dt 的用户登录记录表，查找最近 7 天内连续 3 天活跃的用户 id，实现思路：获取最近 7 天的用户登录记录数据（在 where 中限定），对数据进行 rank 排序...
SQL—用户连续登录问题
weixin_46020540的博客
08-16 5134
SQL—用户连续登录问题 前言 如果想从事数据相关工作，SQL毋庸置疑是必备技能，而“连续登录”问题作为经典sql面试题，一直是高频考点，本文将用两种不同的窗口函数，解答该类问题，如果有不清楚什么窗口函数的小伙伴，可以看看我之前写过窗口函数的文章。 SQL干货|为你打开一扇窗—窗口函数 【题目】：要求查询连续3天登录的用户 一、构造"用户登录表"数据 字段说明 use_id：用户id login_date：登录时间 二、思路解析及方法 1.使用排序窗口函数 -- 窗口分组排序 SELECT *, ro
MySQL 三天连续登录SQL
LOST_9的博客
03-16 337
mysql三天连续登录
SQL 求最大连续登陆天数
sinat_41207450的博客
09-06 3959
已知登陆表中有 uid、login_time，求每个用户的最大连续登陆天数
SQL经典面试题-连续登录3天用户
qq_43528451的博客
05-06 1141
SQL经典面试题-连续登录3天用户 1、场景-建表 select * from tmp.tmp_last_3_day; 2、分析 3、sql实现 连续3天登录的用户id ­­ sql如下： 结果：
解决SQL Server 2012 sa用户登录错误18456
"SQL Server 2012中sa用户登录遇到错误18456时，通常由登录方式设置错误、sa用户设置错误或IP方式未开启导致。解决这个问题需要按照以下步骤进行： 一、登录方式设置 当sa用户无法登录时，首先检查SQL Server的验证...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值