Hive SQL必刷练习题:连续问题 & 间断连续(*****)

这篇具有很好参考价值的文章主要介绍了Hive SQL必刷练习题:连续问题 & 间断连续(*****)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

问题描述:

1) 连续问题:找出连续三天(或者连续几天的啥啥啥)。

2) 间断连续:统计各用户连续登录最长天数,间断一天也算连续,比如1、3、4、6也算登陆了6天

问题分析:

这两类问题,都是涉及到连续,只不过连续类问题只要判断出来相邻的时间是和当前行的时间挨着即可,而间断连续,是还可能在期间有中断现象,并且即使中断了,也要算在连续的时间内,比如1、3、4、6号登录,但是算是连续登陆了6天。所以只要中断不超过两天,也能判断出来是连续的,并且在计算连续天数的时候还要算上这个时间。

解决思路:

1)针对连续问题:

可以通过对每一个用户id进行开窗,然后排序里面的时间,加上row_number(),这样对每一个日期都会有一个排序,之后将日期减去排序序号,只要这个值是一样的,就说明这几天是连续的。之后只要根据用户id,以及这个减去过排序序号的差值diff进行一起group by分组,然后通过having count(*) >= 3,这个筛选条件过滤出的结果就是最终连续登录了三天的用户

2)针对间断连续问题:

​ 也是对每一个用户id进行开窗,只不过这次是针对当前行,用lag()取上一行数据。【必须要看上一行,而不是看下一行,因为如果当前行开始变化,当前行需要记录,如果看下一行,那看的是下一行有没有变化,是不是不连续了之类的,下一行的情况无法记录】。之后用当天日期对这个用开窗函数得到的上一天日期进行相减,如果大于2,就说明相邻两天的日期是超过了2天,按照定义不算是“连续”,所以增加个标记,记录为1,这样每一行都会有一个标记,如果是连续的,那这一行后面就标记的0

​ 接下来就是关键的步骤,根据**用户id进行开窗求sum聚合group by,然后从最开始的一行,到当前行,进行求sum。**这样的话,原本是1、0、0、1、0、1、0,比如是这样的标记序列,每到一个1就说明从这天开始,就不是间断一天连续了,然后从最开始一行到当前行求sum的结果就是,1、1、1、2、2、3、3。这就很好的划分出来三段“间断连续时间”。

​ 然后根据用户id,以及这个标记进行分组聚合group by,求出来这个期间日期的max,以及日期的min,相减,别忘了再加1【日期计算时候需要注意的】,就是这个间断连续的天数了。

总结:

​ 所以总的来说,要么就是用开窗函数对日期进行排序,然后当前日期减去排序序号,去看日期一样的就是连续操作的记录

​ 当然,如果想用统计间断连续的方法去看连续n天的问题,也可以,就是取一个lag(),或者取一个lead(),进行开窗,不用加标记,直接一个where过滤diff=2,这就是统计连续3天的用户,不过同一个用户可能会有多个这个符合条件的,最后需要去重count(distinct(user_id)) 。如果是为了找到连续3天登录过的用户

​ 要么就是利用开窗函数取得上一行lag()日期,两个日期相减,去判断是否符合条件,接下来加一个标记,不符合的设为1,用作记录改变的时间点,其余标0。然后开窗函数对标记进行求和,是从第一条到当前条,这样就可以看出来一个用户id,划分了几个连续时间段,然后再根据需求去具体的求。

然后上代码:

1) 连续:

Hive SQL必刷练习题:连续问题 & 间断连续(*****),Hive SQL,hive,sql,hadoop文章来源地址https://www.toymoban.com/news/detail-843763.html

2) 间断连续

select
    user_id,
    max(recent_days) max_recent_days  --求出每个用户最大的连续天数
from
(
    select
        user_id,
        user_flag,
        datediff(max(login_date),min(login_date)) + 1 recent_days --按照分组求每个用户每次连续的天数(记得加1)
    from
    (
        select
            user_id,
            login_date,
            lag1_date,
            concat(user_id,'_',flag) user_flag --拼接用户和标签分组
        from
        (
            select
                user_id,
                login_date,
                lag1_date,
                sum(if(datediff(login_date,lag1_date)>2,1,0)) over(partition by user_id order by login_date) flag  --获取大于2的标签
            from
            (
                select
                    user_id,
                    login_date,
                    lag(login_date,1,'1970-01-01') over(partition by user_id order by login_date) lag1_date  --获取上一次登录日期
                from
                (
                    select
                        user_id,
                        date_format(login_datetime,'yyyy-MM-dd') login_date
                    from login_events
                    group by user_id,date_format(login_datetime,'yyyy-MM-dd')  --按照用户和日期去重
                )t1
            )t2
        )t3
    )t4
    group by user_id,user_flag
)t5
group by user_id;
      )t2
        )t3
    )t4
    group by user_id,user_flag
)t5
group by user_id;

到了这里,关于Hive SQL必刷练习题:连续问题 & 间断连续(*****)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • sql练习题

    DQL练习1-学生表     创建如下学生表      create table student(     id int,     name varchar(20),     gender varchar(20),     chinese int,     math int,     english int     );                   insert into student values      (1,\\\'张明\\\',\\\'男\\\',89,78,90),     (2,\\\'李进\\\',\\\'男\\\',67,53,95),     (3,\\\'王五

    2024年02月16日
    浏览(48)
  • SQL经典练习题

    1.学生表 Student(SId,Sname,Sage,Ssex) SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 2.课程表 Course(CId,Cname,TId) CId 课程编号,Cname 课程名称,TId 教师编号 3.教师表 Teacher(TId,Tname) TId 教师编号,Tname 教师姓名 4.成绩表 SC(SId,CId,score) SId 学生编号,CId 课程编号,score 分数 学生表 Studen

    2024年01月20日
    浏览(80)
  • sql函数练习题

    1. 计算用户8月每天的练题数量 题目:现在运营想要计算出 2021年8月每天用户练习题目的数量 ,请取出相应数据。 示例:question_practice_detail id device_id question_id result date 1 2138 111 wrong 2021-05-03 2 3214 112 wrong 2021-05-09 3 3214 113 wrong 2021-06-15 4 6543 111 right 2021-08-13 5 2315 115 right 2021-08-1

    2024年02月07日
    浏览(44)
  • 50道SQL练习题

    https://www.bilibili.com/video/BV14h411R7F6/?p=4vd_source=76a21f3936db28f5e63b70544272d65e https://www.cnblogs.com/Diyo/p/11424844.html https://zhuanlan.zhihu.com/p/113173133 方法1:自连接 思路:先使用自连接score表找到01\\\"课程比\\\"02\\\"课程成绩高的的学生id,然后关联学生表获取学生信息 方法二: 长形数据变成宽型数

    2024年02月09日
    浏览(61)
  • sql-50练习题16-20

    sql真的非常灵活,一个题目可能有很多很多种解法,我记录的只是我自己的一个解题思路,如果大家有更好的不同解法欢迎在评论区一起探讨 ps:有些题可能解法会重复,比如求大于,我们下一个题可能是求小于,大家如果第一遍没有做出来,看了我写的之后有了思路,可以

    2024年02月06日
    浏览(50)
  • sql-50练习题6-10

    sql真的非常灵活,一个题目可能有很多很多种解法,我记录的只是我自己的一个解题思路,如果大家有更好的不同解法欢迎在评论区一起探讨 ps:有些题可能解法会重复,比如求大于,我们下一个题可能是求小于,大家如果第一遍没有做出来,看了我写的之后有了思路,可以

    2024年02月08日
    浏览(45)
  • sql-50练习题11-15

    sql真的非常灵活,一个题目可能有很多很多种解法,我记录的只是我自己的一个解题思路,如果大家有更好的不同解法欢迎在评论区一起探讨 ps:有些题可能解法会重复,比如求大于,我们下一个题可能是求小于,大家如果第一遍没有做出来,看了我写的之后有了思路,可以

    2024年02月06日
    浏览(41)
  • 【MySQL】- 05 sql 语句练习题

    查询结果就不放了,语句是否成功运行,结果是否正确都容易自行判断 –1. 学生表 Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别 –2. 课程表 Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号 –3. 教师表 Teacher(t_id,t_name) --教师编号,教师姓名 –4. 成

    2024年02月08日
    浏览(61)
  • 【SQL每日一练】分组过滤练习题

    题目:现在运营想查看每个学校用户的平均发贴和回帖情况,寻找低活跃度学校进行重点运营,请取出平均发贴数低于5的学校或平均回帖数小于20的学校。 代码如下: WHERE 无法与合计函数一起使用; SQL语句执行顺序 (8) SELECT (9) DISTINCT (1) FROM (3) JOIN (2) ON (4) WHERE (5) GR

    2024年04月23日
    浏览(42)
  • 【MySQL】- 05 sql 语句练习题(第二部分)

    20、 查询学生的总成绩并进行排名 21、 查询不同老师所教不同课程平均分从高到低显示 22、 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 23、 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 24、 查询学生平均成绩及其名次

    2024年02月09日
    浏览(44)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包