sql基础练习题-最大连续次数等

2022-07-09 本文已影响0人 Eqo

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
;