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

您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >

文章目录

@[TOC](文章目录)
一、介绍

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

数据准备
方案1：常规思路

针对对数据user_id分组，根据用户的活动日期排序
用登录日期与rn求date_sub，得到的差值日期如果是相等的，则说明这两天肯定是连续的
根据user_id和日期差sub_date分组，登录次数即为分组后的count(1)
方案2：使用lag和lead函数

使用LEAD和LAG函数求出前后1天日期
针对每个用户，进行前一天和后一天的日期与当期日期相差值=1则属于连续登录。
针对用户分组，datediff函数求出最大活动时间和最小活动时间的天数，求出>=3天的用户

对比方案1和方案2

作为大数据开发人员，绝不能丢下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

拿捏SQL：以“统计连续登录天数超过3天的用户“为例拿捏同类型SQL需求,在这里插入图片描述,第1张

方案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

拿捏SQL：以“统计连续登录天数超过3天的用户“为例拿捏同类型SQL需求,在这里插入图片描述,第2张

用登录日期与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

拿捏SQL：以“统计连续登录天数超过3天的用户“为例拿捏同类型SQL需求,在这里插入图片描述,第3张

根据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;

拿捏SQL：以“统计连续登录天数超过3天的用户“为例拿捏同类型SQL需求,在这里插入图片描述,第4张

从结果可以看出用户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

拿捏SQL：以“统计连续登录天数超过3天的用户“为例拿捏同类型SQL需求,在这里插入图片描述,第5张

针对每个用户，进行前一天和后一天的日期与当期日期相差值=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;

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

针对用户分组，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;

拿捏SQL：以“统计连续登录天数超过3天的用户“为例拿捏同类型SQL需求,在这里插入图片描述,第7张

对比方案1和方案2

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

上一篇：PHP从入门到精通—PHP开发入门-PHP概述、PHP开发环境搭建、PHP开发环境搭建、第一个PHP程序、PHP开发流程

下一篇：Mysql之binlog日志浅析