SQL查询语句求出用户的连续登陆天数
一、题目描述
求解用户登陆信息表中,每个用户连续登陆平台的天数,连续登陆基础为汇总日期必须登陆,表中每天只有一条用户登陆数据(计算中不涉及天内去重)。
表描述:user_id:用户的id;
【SQL查询语句求出用户的连续登陆天数】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_dateunion allselect 'uid_1' as user_id,'2021-08-04' as sigin_date union allselect 'uid_1' as user_id,'2021-08-01' as sigin_dateunion allselect 'uid_1' as user_id,'2021-08-02' as sigin_dateunion allselect 'uid_1' as user_id,'2021-08-05' as sigin_dateunion allselect 'uid_1' as user_id,'2021-08-06' as sigin_dateunion allselect 'uid_2' as user_id,'2021-08-01' as sigin_dateunion allselect 'uid_2' as user_id,'2021-08-05' as sigin_dateunion allselect 'uid_2' as user_id,'2021-08-02' as sigin_dateunion allselect 'uid_2' as user_id,'2021-08-06' as sigin_dateunion allselect 'uid_3' as user_id,'2021-08-04' as sigin_dateunion allselect 'uid_3' as user_id,'2021-08-06' as sigin_dateunion allselect 'uid_4' as user_id,'2021-08-03' as sigin_dateunion allselect 'uid_4' as user_id,'2021-08-02' as sigin_date;
2.计算过程
selectuser_id,count(1) as sigin_cntfrom(selectuser_id,datediff('2021-08-06',sigin_date)as data_diff,row_number() over (partition by user_id order by sigin_date desc) as row_numfromtest_sigindate_cnt) twheredata_diff - row_num = -1group 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 |
文章图片
以上就是SQL查询语句求出用户的连续登陆天数的详细内容,更多关于SQL语句求用户的连续登陆天数的资料请关注脚本之家其它相关文章!
推荐阅读
- 关于QueryWrapper|关于QueryWrapper,实现MybatisPlus多表关联查询方式
- mybatisplus如何在xml的连表查询中使用queryWrapper
- mybatisplus|mybatisplus where QueryWrapper加括号嵌套查询方式
- MybatisPlus使用queryWrapper如何实现复杂查询
- 数据库总结语句
- py连接mysql
- 2019-01-18Mysql中主机名的问题
- MySql数据库备份与恢复
- Improve|Improve Nested Conditionals(优化嵌套的条件语句) 面对大量的if-else语句
- mysql|InnoDB数据页结构