一、集合函数
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;