美文网首页
sql基础练习题-最大连续次数等

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

作者: Eqo | 来源:发表于2022-07-09 16:39 被阅读0次

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
;

相关文章

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

    SQL连续问题,最大连续登录次数等 在sql连续的时候我们经常会遇到求连续登录N天的活跃用户 或者求每个用户的最大...

  • hive学习(二):练习题——求访问次数

    前言: 以sql为基础,利用题目进行hive的语句练习,逐步体会sql与hive的不同之处。本次练习题来源:htt...

  • sqlzoo练习答案

    这是关于在一个SQL学习网站的练习题答案记录:SQL教程 SQL基础 由一些简单的查询开始 这里的默认表格为WOR...

  • 视图与子查询

    《SQL基础教程第2版》Chap.5练习题 5-2 向视图 ViewPractice5_1 插入数据. ❓为什么S...

  • 计算最大连续次数的终极算法

    求连续累计次数,听说有一个无法超越的超越极限算法DDMethod,本人最终的动态错位算法相比前者提升50%,测试5...

  • SQL基础教程(第二版)学习笔记

    SQL基础教程(第二版) 1 数据库和SQL 1.1 数据库管理系统 DBMS** 层次数据库 HDB 关系数据库...

  • 2018-11-24

    《SQL基础教程》历时14天看完了,反复的看,有些知识点还是有点模糊,需要做练习,练习题已经准备好了,加油! 敲得...

  • MySQL 练习题

    01第一天20180703 【SQL练习】经典SQL练习题 02第二天20180704 sql语句练习50题(My...

  • Spark难点解析:Join实现原理

    Join背景介绍 SQL的所有操作,可以分为简单操作(如过滤where、限制次数limit等)和聚合操作(grou...

  • 数据库(3) | Oralce SQL 数据处理

    SQL语言处理 本章,我们将会重点探讨SQL语言基础,学习用SQL进行数据库的数据增加、删除和修改等操作。另外请注...

网友评论

      本文标题:sql基础练习题-最大连续次数等

      本文链接:https://www.haomeiwen.com/subject/trcibrtx.html