集合函数和连续性问题（两道SQL）

一、集合函数

arry_contains（）作用：判断数组是否包含某元素

语法：array_contains(数组，值），返回布尔类型 

直接上案例：（8月23日作业第五题）

第五题：
有如下数据，表示1、2、3三名学生选修了a、b、c、d、e、f中的若干课程
id  course
1   a
1   b
1   c
1   e
2   a
2   c
2   d
2   f
3   a
3   b
3   c
3   e
根据如上数据，查询出如下结果，其中1表示选修，0表示未选修
id  a  b  c  d  e  f
1   1  1  1  0  1  0
2   1  0  1  1  0  1
3   1  1  1  0  1  0

--第一种方法
select id,
       sum(case when course='a' then 1 else 0 end ) a,
       sum(case when course='b' then 1 else 0 end ) b,
       sum(case when course='c' then 1 else 0 end ) c,
       sum(case when course='d' then 1 else 0 end ) d,
       sum(case when course='e' then 1 else 0 end ) e,
       sum(case when course='f' then 1 else 0 end ) f
       from zhoukao03 group by id;
--第二种方法
select id,
       if(array_contains(collect_set(course),'a'),1,0) a,
       if(array_contains(collect_set(course),'b'),1,0) b,
       if(array_contains(collect_set(course),'c'),1,0) c,
       if(array_contains(collect_set(course),'d'),1,0) d,
       if(array_contains(collect_set(course),'e'),1,0) e,
       if(array_contains(collect_set(course),'f'),1,0) f
       from courses group by id;

假如查询出如下结果

id     a        b          c         d            e            f
1   选修  选修      选修   未选修    选修      未选修
2   选修  未选修  选修    选修      未选修   选修
3   选修  选修     选修    未选修    选修      未选修

create  table courses (
id  int,
course string
)
row format delimited
fields terminated by '\t';

load data local inpath '/home/hivedata/zuoye5.txt' into table courses;

select id,
       if(array_contains(collect_set(course),'a'),'选修','未选修') a,
       if(array_contains(collect_set(course),'b'),'选修','未选修') b,
       if(array_contains(collect_set(course),'c'),'选修','未选修') c,
       if(array_contains(collect_set(course),'d'),'选修','未选修') d,
       if(array_contains(collect_set(course),'e'),'选修','未选修') e,
       if(array_contains(collect_set(course),'f'),'选修','未选修') f
       from courses group by id;

总结：1.当遇到行转列问题 并且列里面要求写入字符串时  可以考虑使用arry_contains函数。

           2.写SQL时，如果思考后仍无法进行下去，可以想是否有某个函数可以使用。

 二、连续性SQL题

问题： 现有用户登录记录表，请查询出用户连续三天登录的所有数据记录

id           dt

1    2024-04-25 
1    2024-04-26 
1    2024-04-27
1    2024-04-28
1    2024-04-30
1    2024-05-01
1    2024-05-02
1    2024-05-04
1    2024-05-05
2    2024-04-25
2    2024-04-28
2    2024-05-02
2    2024-05-03
2    2024-05-04

期望结果：

1	2024-04-25
1	2024-04-26
1	2024-04-27
1	2024-04-26
1	2024-04-27
1	2024-04-28
1	2024-04-30
1	2024-05-01
1	2024-05-02
2	2024-05-02
2	2024-05-03
2	2024-05-04

步骤：1.求解这个问题前，先解决 用户连续登录三天的次数                                                               2.

--建表
create table user_log(
    id int,
    dt string
)
row format delimited
fields terminated by '\t';
load data local inpath '/home/hivedata/lianxu.txt' into table user_log;
--第一步：求解每行日期后面第三行的日期 lead()和 真正第三天的日期
select*,
      lead(dt,2) over(partition by id order by dt) later3dt,
      date_add(dt,2) true3dt
    from user_log;
--第二步：判断是否连续登录三天
with t as (
    select*,
      lead(dt,2) over(partition by id order by dt) later3dt,
      date_add(dt,2) true3dt
    from user_log
) select *,if(later3dt==true3dt,1,0) num from t;
--第三步：筛选出连续登录三天的每个起始日期
with t as (
    select*,
      lead(dt,2) over(partition by id order by dt) later3dt,
      date_add(dt,2) true3dt
    from user_log
) ,t1 as (
    select *,if(later3dt==true3dt,1,0) num from t
)select * from t1 where num=1;
-- 第四步：表合并求最终结果（和一个三行的表进行合并）（笛卡尔积）
with t as (
    select*,
      lead(dt,2) over(partition by id order by dt) later3dt,
      date_add(dt,2) true3dt
    from user_log
) ,t1 as (
    select *,if(later3dt==true3dt,1,0) num from t
),t2 as (
    select * from t1 where num=1
) select id,dt,list,date_add(dt,d.list) dt2  from t2,(select explode(array(0,1,2)) list) d;

 总结：1.用户连续登录n天的次数 ，都可以使用这个来解决

            2.连续性问题：

                ①盯着日期进行改变：lead()  ,   lag() ,  datediff() , date_add()  , date_sub()  要联想这些关于日期的函数，翻来覆去使用一般可以解决；

--计算每个用户最大的连续登录天数，可以间隔一天.
with t as (
    select *,
       lag(dt,1,0) over (partition by id order by dt) lastdt,
       date_sub(dt,dense_rank() over (partition by id order by dt)) dt1
       from game_data
),t1 as (
    select id,dt,lastdt,
    date_sub(dt1,dense_rank() over (partition by id order by dt1)) dt2
    from t
),t2 as (
    select id,dt2,
         if(datediff(dt,lastdt)=1,1,if(datediff(dt,lastdt)=2,2,0)) num
         from t1
),t3 as (
    select id,sum(num)+1 num from t2 group by id,dt2
) select id,max(num) `最大连续登录天数` from t3 group by id;