查询连续3天登录的用户

一、创建表

CREATE TABLE `log`  (
  `user_id` int(11) NOT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Compact;


INSERT INTO `log` VALUES (101, '2021-11-10 20:12:42');
INSERT INTO `log` VALUES (102, '2021-11-26 20:13:04');
INSERT INTO `log` VALUES (101, '2021-11-08 20:12:42');
INSERT INTO `log` VALUES (103, '2021-11-18 20:14:11');
INSERT INTO `log` VALUES (102, '2021-11-26 20:14:20');
INSERT INTO `log` VALUES (101, '2021-11-09 20:12:42');
INSERT INTO `log` VALUES (103, '2021-11-20 20:14:11');
INSERT INTO `log` VALUES (103, '2021-11-19 20:14:11');
INSERT INTO `log` VALUES (102, '2021-11-24 21:02:37');

解决方案分析:
通过对用户id进行分组,并按时间排序。 排序后生成一个临时字段rank存储排序结果,然后通过date_sub()函数用登录时间减去排序rank,如果是连续登录的话得到的时间相等。

方案一:

select *, count(1) ct from (
		select user_id, create_time, row_number() over(partition by user_id order by create_time) as rank
		from log 
	) m
group by m.userId, DATE_SUB(m.createTime,INTERVAL m.rank DAY)  having ct > 2

Oracle 通过 row_number() over partition by 函数实现了分组内排序功能,通过它进行分组排序,然后登录时间减去排序字段,通过data_sub()函数计算后的日期分组,并统计它的个数。

方案二:

mysql是没有row_number() over partition by函数的,所有如何想要解决这个问题,可以通过伪列
格式:

SELECT
 (@r :=@r + 1) AS rank
FROM  (SELECT @r := 0) r 
 ;

实现:

select *, count(1) ct from (
	select createTime, if(@p = userId, @r :=@r + 1,@r :=1) AS rank, @p := userId as userId 
	from (
		select user_id as userId, create_time as createTime
		from log , (SELECT @r := 0, @p := null) d group by userId, createTime order by createTime
		) t
	) m
group by m.userId, DATE_SUB(m.createTime,INTERVAL m.rank DAY)  having ct > 2

定义两个变量@r := 0, @p := null,通过用户id、时间分组,并按时间排序。

if(@p = userId, @r :=@r + 1,@r :=1) AS rank 把用户id赋给变量@p ,判断如果用户id相等则排序加一,否则排序从一开始排。这样是为了实现组内排序

在这里插入图片描述

使用DATE_SUB(m.createTime,INTERVAL m.rank DAY) 函数减去 排序 后进行分组,如果个数大于2则符合

在这里插入图片描述

  • 1
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值