SQL查询语句求出用户的连续登陆天数
作者:Heng_bigdate_yan 时间:2024-01-17 22:00:40
一、题目描述
求解用户登陆信息表中,每个用户连续登陆平台的天数,连续登陆基础为汇总日期必须登陆,表中每天只有一条用户登陆数据(计算中不涉及天内去重)。
表描述:user_id:用户的id;
sigin_date:用户的登陆日期。
二、解法分析
注:求解过程有多种方式,下述求解解法为笔者思路,其他解法可在评论区交流。
思路:
该问题的突破的在于登陆时间,计算得到连续登陆标识,以标识分组为过滤条件,得到连续登陆的天数,最后以user_id分组,以count()函数求和得到每个用户的连续登陆天数。
连续登陆标识 =(当日登陆日期 - 用户的登陆日期)- 开窗排序的顺序号(倒序)
三、求解过程及结果展示
1.数据准备
-- 1.建表语句
drop table if exists test_sigindate_cnt;
create table test_sigindate_cnt(
user_id string
,sigin_date string
)
;
-- 2.测试数据插入语句
insert overwrite table test_sigindate_cnt
select 'uid_1' as user_id,'2021-08-03' as sigin_date
union all
select 'uid_1' as user_id,'2021-08-04' as sigin_date
union all
select 'uid_1' as user_id,'2021-08-01' as sigin_date
union all
select 'uid_1' as user_id,'2021-08-02' as sigin_date
union all
select 'uid_1' as user_id,'2021-08-05' as sigin_date
union all
select 'uid_1' as user_id,'2021-08-06' as sigin_date
union all
select 'uid_2' as user_id,'2021-08-01' as sigin_date
union all
select 'uid_2' as user_id,'2021-08-05' as sigin_date
union all
select 'uid_2' as user_id,'2021-08-02' as sigin_date
union all
select 'uid_2' as user_id,'2021-08-06' as sigin_date
union all
select 'uid_3' as user_id,'2021-08-04' as sigin_date
union all
select 'uid_3' as user_id,'2021-08-06' as sigin_date
union all
select 'uid_4' as user_id,'2021-08-03' as sigin_date
union all
select 'uid_4' as user_id,'2021-08-02' as sigin_date
;
2.计算过程
select user_id
,count(1) as sigin_cnt
from (
select
user_id
,datediff('2021-08-06',sigin_date) as data_diff
,row_number() over (partition by user_id order by sigin_date desc) as row_num
from test_sigindate_cnt
) t
where data_diff - row_num = -1
group by
user_id
;
3.计算结果及预期结果对比
3.1 预期结果
汇总日期 | 用户id | 登陆天数 |
2021-08-06 | uid_1 | 6 |
2021-08-06 | uid_2 | 2 |
2021-08-06 | uid_3 | 1 |
3.2 计算结果
来源:https://blog.csdn.net/Heng_bigdate_yan/article/details/120643783
标签:SQL,查询语句
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python实现机器学习算法的分类
2023-08-31 05:50:36
![](https://img.aspxhome.com/file/2023/3/130663_0s.png)
Python基于execjs运行js过程解析
2021-08-10 22:56:47
![](https://img.aspxhome.com/file/2023/8/80568_0s.jpg)
Python3.5内置模块之shelve模块、xml模块、configparser模块、hashlib、hmac模块用法分析
2022-04-16 17:07:04
![](https://img.aspxhome.com/file/2023/8/133768_0s.png)
通过js获取上传的图片信息(临时保存路径,名称,大小)然后通过ajax传递给后端的方法
2024-04-17 10:06:38
win10系统下python3安装及pip换源和使用教程
2023-07-10 19:48:40
![](https://img.aspxhome.com/file/2023/0/121040_0s.jpg)
MSSQL转MYSQL,gb2312转utf-8无乱码解决方法
2024-01-13 22:19:03
![](https://img.aspxhome.com/file/2023/2/106212_0s.jpg)
tensorflow模型的save与restore,及checkpoint中读取变量方式
2022-07-27 17:41:43
![](https://img.aspxhome.com/file/2023/6/95836_0s.jpg)
Python Numpy学习之索引及切片的使用方法
2021-09-04 02:59:01
![](https://img.aspxhome.com/file/2023/3/120943_0s.png)
Django处理Ajax发送的Get请求代码详解
2023-06-29 08:40:40
![](https://img.aspxhome.com/file/2023/7/78787_0s.png)
设计师的底线
2009-05-06 12:51:00
python GUI计算器的实现
2021-11-30 01:00:53
![](https://img.aspxhome.com/file/2023/8/87948_0s.png)
教你学会通过python的matplotlib库绘图
2022-03-04 13:08:52
![](https://img.aspxhome.com/file/2023/5/78605_0s.png)
python实现自动化报表功能(Oracle/plsql/Excel/多线程)
2021-04-02 02:05:02
![](https://img.aspxhome.com/file/2023/0/98870_0s.png)
Python的Django应用程序解决AJAX跨域访问问题的方法
2023-08-07 16:00:24
Vue自定义指令实现弹窗拖拽四边拉伸及对角线拉伸效果
2024-05-28 15:43:08
![](https://img.aspxhome.com/file/2023/5/123195_0s.jpg)
Django日志及中间件模块应用案例
2021-06-02 22:16:50
MySQL基于SSL协议进行主从复制的详细操作教程
2024-01-24 23:10:35
![](https://img.aspxhome.com/file/2023/9/66149_0s.png)
Python的子线程和子进程是如何手动结束的?
2022-09-08 18:54:41
python实现测试工具(一)——命令行发送get请求
2022-04-06 03:49:00
vue实现excel表格的导入导出的示例
2024-05-10 14:10:22