SQL连续问题,最大连续登录次数等
在sql连续的时候我们经常会遇到求连续登录N天的活跃用户
或者求每个用户的最大连续登录次数等
以下是日常学习中总结的思路
连续问题
有以下数据 需求 求连续三天登录的用户
userid , login_date
A,2022-01-01
B,2022-01-01
C,2022-01-01
A,2022-01-02
B,2022-01-02
A,2022-01-03
C,2022-01-03
B,2022-01-04
C,2022-01-04
A,2022-01-05
C,2022-01-05
A,2022-01-06
B,2022-01-06
C,2022-01-06
规律: 每个用户的下(n-1)次登录日期 = 本次登录的日期往下(N-1)天
需要使用开窗函数
lead (column, N,default) 分区内向下N行
lag(coulunb,n,default ) 分区内向上N行
思路:
本次登录的日期往下(N-1)天:使用date_add()计算出本次登录
每个用户的下一次登录日期: 按照用户分区,分区内登录日期 向下 (N-1)行
实现:
查询结果
A,2022-01-03,2022-01-03
A,2022-01-04,2022-01-05
A,2022-01-05,2022-01-06
A,2022-01-07,0
A,2022-01-08,0
select
userid,
date_add(login_date,2) next_date, --本次登录日期向下两天
lead(login_date,2,null) over (partition by userid order by login_date) next_login --用户下一次登录的时间
from tb_user_login;
如果连续 next_Date= next_login 注意需要对 userid 去重 因为只要用户的名字
with tmp as (
select userid,
date_add(login_date, 2) next_date, --本次登录日期向下两天
lead(login_date, 2, null) over (partition by userid order by login_date) next_login--用户下一次登录的时间
from tb_user_login)
select distinct userid ,next_login, next_date from tmp where next_date=next_login;
A,2022-01-03,2022-01-03
C,2022-01-05,2022-01-05
C,2022-01-06,2022-01-06
最大连续问题
需求,求每个用户最大连续登录的天数
A,2022-01-01
A,2022-01-02
A,2022-01-03
A,2022-01-05
A,2022-01-06
B,2022-01-01
B,2022-01-02
规律:
use_id logn_date row_number date
A, 2022-01-01 - 1 +1 2022-01-01
A, 2022-01-02 - 2 2022-01-01
A, 2022-01-03 - 3 2022-01-01
A, 2022-01-05 - 4 2022-01-02
A, 2022-01-06 - 5 022-01-06
B, 2022-01-01
B, 2022-01-02
只要把用户登录时间排序 并打上标签, 让用户登录的时间减去标签数 得到日期相同的就是连续天数.如果再加1 就是连续时间内第一次登录的时间
思路:
给分区内的用户,按照登录时间升序,使用row_number打上标签
计算出 登录日期减去标签数的日期 如果相同就是连续登录的时间
实现
select userid,
login_date,
row_number() over (partition by userid order by login_date) label
from tb_user_login 打标签
A,2022-01-01,1
A,2022-01-02,2
A,2022-01-03,3
with tmp_label as(
select userid,
login_date,
row_number() over (partition by userid order by login_date) label
from tb_user_login)
select userid,date_add(login_date,- (label+1) ) from tmp_label
A,2021-12-30
A,2021-12-30
A,2021-12-30
A,2021-12-31
A,2021-12-31 如果日期相等就是登录日期连续的
然后在对 上表数据 分组 聚合 得出每个用户登录的次数
with tmp_label as(
select userid,
login_date,
row_number() over (partition by userid order by login_date) label
from tb_user_login),
tmp as ( select userid,date_add(login_date,- (label+1) ) login from tmp_label)
select
userid,
count(login)
from tmp group by userid;
;
A,5
B,4
C,5
SQL常见基础面试题
窗口函数语法及分类
functionName() over( partition by | order by|rows | range )
- 聚合函数+over()
count/max/min/avg/sum
-
位置函数+over()
frist_value / last_value/lag/lead
-
分析函数+over()
row_number/rank/dense_rank/ntile
-
window 子句
默认是rows物理行数 range 范围
over内不仅可以指定 分区内显示的物理行数,也能显示分区内的符合range范围的数据
frist_value / last_value :分区内第一条数据最后一条数据
lag/lead 向上/下几行
row_number 1234
rank 1224
dense_rank 1223
nitle(3) 求比例 按照ntile后的数,讲分区内的数据平均分为三列
如果一条需求中出现了每个、每、各个、不同等关键词，是不是一定会用到group by？
-
什么情况下使用group by 什么情况下使用partition by
-
group by：分组：每组只能返回一条
当数据分组后,每组数据只返回一条的时候使用group by 列如 每个城市的人口,每个部门的平均工资
分组本质：将相同分组的数据放在一起然后聚合 分组+聚合
-
partition by：窗口分区：每组内可以返回多条
当数据分组后,每组返回的数据不是一条
列如:每个部门工资最高的三个人
分区本质:将相同分组的数据放在一起 分区+分析
-
窗口聚合问题
聚合窗口函数：count/max/min/avg/sum over……
场景：组内比较、组内计算
-
需求1：统计到当月的累计总存储余额
数据 userid,month,salary A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5 A,2015-03,7 A,2015-03,9 B,2015-03,11 B,2015-03,6 结果 +-------------+----------+---------+-------------+--+ | a.username | a.month | salary | accumulate | +-------------+----------+---------+-------------+--+ | A | 2015-01 | 33 | 33 | | A | 2015-02 | 10 | 43 | | A | 2015-03 | 16 | 59 | | B | 2015-01 | 30 | 30 | | B | 2015-02 | 15 | 45 | | B | 2015-03 | 17 | 62 | +-------------+----------+---------+-------------+--+实现 需求1：统计到当月的累计总存储余额 使用win子句 按照月份升序排序
-- 使用window 子句 指定从上边界到当前行 with a as( select userid, month, sum(salary) month_saraly from text.leiji group by userid,month ) select userid,month, month_saraly, sum( month_saraly) over(partition by userid rows between unbounded preceding and current row ) --指定行数 from a A,2015-01,33,33 A,2015-02,10,43
需求2：查找单科成绩高于该科目平均成绩的学生
userid course score
A 数学 20
B 数学 30
C 数学 20
D 数学 30
A 语文 40
B 语文 10
C 语文 40
D 语文 10
结果
B 数学 30
D 数学 30
A 语文 40
C 语文 40
结果
B 数学 30
D 数学 30
A 语文 40
C 语文 40
实现
-- 需求2：查找单科成绩高于该科目平均成绩的学生
-- 思路 按照学科分区 求出平均值 作为子表 筛选出 单科成绩大于平均值的学生
with tmp as( select
userid,
course,
score,
avg(score) over (partition by score) avg_score
from text.score)
select
userid,
course,
score
from tmp where score >= avg_score
;
分组排名问题(ntile求比例, row_number 三兄弟 )
场景：组内排名，组内划分 row_number 三兄弟
- 需求1：统计每个部门薪资最高的前两名员工的信息
empid ename salary deptno
1101 zhangsan 4000 10
1102 lisi 8000 20
1103 wangwu 3000 10
1104 zhaoliu 9000 20
1105 tianqi 5000 10
1106 qianba 10000 20
结果
1105 tianqi 5000 10
1101 zhangsan 4000 10
1106 qianba 10000 20
1104 zhaoliu 9000 20
实现
-- 思路 使用row_number 给工资做个标记 降序标记
with tmp as( select
empid,
ename,
salary,
deptno,
row_number() over (partition by deptno order by salary desc ) rk
from text.emp)
select * from tmp where rk<=2
;
需求2：统计每个部门薪资最高的前1/3的员工信息 使用 ntile 函数实现
1105 tianqi 5000 10
1106 qianba 10000 20
实现
;
-- 需求2：统计每个部门薪资最高的前1/3的员工信息
-- 使用ntile(3)函数
with tmp as(
select empid,
ename,
salary,
deptno,
ntile(3)over(partition by deptno order by salary) rk
from text.emp
)
select * from tmp where rk=1
;
