Sql练习记录
题目：
第一道，有一个成绩表数据t1
uid class_name score
A Chinese 90
A English 89
A Math 88
B ... ...
求出总分并排序
uid chinese_score English_score Math_score total_score rank
A 90 89 88 267 1
B ...
- 行转列 + rank()函数
CREATE TABLE Person (
uid varchar(255),
class_name varchar(255),
score int
);
INSERT INTO Person VALUES ("A", "Chinese", 90), ("A", 'Math', 100),("B", 'Chinese', 99),("B", 'Math', 100);
SELECT
*,
rank() over(order by total_score desc)
from(
SELECT
uid,
max(
case
when class_name="Chinese" then score
else 0
end
) as chineses_score,
max(
case
when class_name="Math" then score
else 0
end
) as math_score,
sum(score) total_score
FROM Person
group by uid
) tmp;
-
总结：
1、行转列：转成多列，select 肯定要求出现这几列，直接写出来，然后case when
2、rank() 函数 rank() over(partition by ... order by...) -
用户留存：去重，然后将日期-1，关联到一起说明是连续登录的那几天
求11月份的新用户次日留存率（牛客网题目）
id login_time
1 2021-11-01 12：00：01
2 2021-11-01 12：00：01
3 2021-11-01 12：00：01
1 2021-11-02 12：00：01
2 2021-11-02 12：00：01
4 2021-11-02 12：00：01
...
比如这张表的新用户留存率为66.7%
11.1 有三个新用户 1 2 3
11.2 两个新用户留下来了 1 2
所以新用户留存率为 2/3=66.7%
https://www.nowcoder.com/questionTerminal/126083961ae0415fbde061d7ebbde453?f=discussion
- 总结
1、date_sub(date, interval 1 day) 这个函数需要记住
2、计算留存：用户聚合后，操作日期，两个表left join，能join的上的就是第二天、第三天登录的
select
avg(if(b.device_id is not null,1,0)) as avg_ret
from
(
select
distinct
device_id,
date
from
question_practice_detail
)a
left join(
select
distinct
device_id,
date_sub(date, interval 1 day) as date
from
question_practice_detail
)b on a.device_id = b.device_id and a.date = b.date
- hive实现：非北京市的学生中，每个班级 平均分最高的TO10用户及信息，
结果格式为：class, uid, avg_score, scores,province
用户学分表Score：
字段名 中文名 字段类型 字段示例
uid 用户id bigint 23145
class 班级 string 2-1
scores 用户信息 array<string> ['math_100','music_90',......]
用户信息表 stu
字段名 中文名 字段类型 字段示例
uid 用户id bigint 23145
province 省份 string 北京
本题的核心就是将各科目成绩列举出来
配合 LATERAL VIEW、explode 2个函数，可以实现把一个array类型的值分开。
LATERAL VIEW通常用于一行转多行的处理逻辑。
在hive中实现多行转一行的处理逻辑可以使用<collect_set> <collect_list>函数来实现, 他们返回的对象都是一个array集合，那么又回到上面array对象的处理逻辑了，差别在在于前者会去重。concat(subject,'-',score),然后将结果collect_set
select
uid,
class
temp_score
from
Score
lateral view
explode(scores) v_temp as temp_score
;
-- 写到这，我们可以拿到
id class subject-score
1 1 math-90
1 1 music-100
然后怎么办？
select split('math_90', '\_')[0];
select
*
from(
select
uid,
class,
province,
avg_score,
sum_score,
rank() over(partition by class order by avg_score desc) ranking
from(
select
t.uid,
t.class,
t.province,
avg(score) avg_score,
sum(score) sum_score
from(
-- 到这 表正常，可以进行排名操作
select
a.uid,
a.class,
a.province,
split(temp_score, '\_')[0] as subject,
split(temp_score, '\_')[1] as score
from(
select
uid,
class,
province,
temp_score
from
Score
join(
select
uid,
province
from
stu
where
province != '北京'
) pro on pro.uid = Score.uid
lateral view
explode(scores) v_temp as temp_score
)a
)t
group by t.uid, t.class,t.province
)scores
)rank_temp
where rank_temp.ranking <= 10;
总结：
1、通过explode 将array_string 炸成多行，借助lateral view
关联到对应的学生上，由此表变成
【学生 班级 科目 成绩 省份】
2、取排名，需要先算出平均分，套一层基于平均分，再最后一行追加上班级排名
3、再套一层，where 筛掉 ranking <= 10
总结：
1、get_json_object -- 输入字符串，输出指定key的值get_json_object('{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}','$.movie')
2、json_tuple -- 可以做到将字符串一次性提取多个key
select b.b_movie,b.b_rate,b.b_timeStamp,b.b_uid from json a
lateral view json_tuple(a.data,'movie','rate','timeStamp','uid') b as b_movie,b_rate,b_timeStamp,b_uid;
但如果给的是json数组，就不管用, 像这种 [{},{}]，咋办？
explode 将数组，炸成多行，但一般给的都是字符串。。。
1、regexp_replace(string, '\[|\]', '') 先将字符串[]去掉
2、regexp_replace 再将{},{} 之间的,换成; 避免split勿切割
3、split(string, ';') -- 产出一个个'{}' 的数组
4、explode上场，将其炸成多行
SELECT explode(split(
regexp_replace(
regexp_replace(
'[
{"website":"www.baidu.com","name":"百度"},
{"website":"google.com","name":"谷歌"}
]',
'\\[|\\]',''), --将 Json 数组两边的中括号去掉
'\\}\\,\\{' --将 Json 数组元素之间的逗号换成分号
,'\\}\\;\\{'),
'\\;')); --以分号作为分隔符
