牛客面试题解析笔记——sql【20】
题目信息:Hive中表t_od_qqbook_coin_account是用户在QQ阅读看书时的章节订阅明细表,主要有以下字段:statis_day(日期),qq_no(qq号),bookid(书籍id),cid(章节id),fee(该章节订阅费用,单位:分).
用户订阅章节时支持多章同时订阅,此时表中的cid会把多个章节id通过*连接合并存储。
例如表中,一条订阅流水如下:例如表a中,一条订阅流水如下:20200102,905781969,273561,50*51*52*53*54*55,10
请问在2020年1月里:
Q1:输出每天每本书的阅读人数及其订阅费用(仅保留订阅费用大于10元的记录)并按照订阅费用降序排列
select statis_day, bookid,count(distinct qq_no) as user_uv,sum(fee) as fee
from t_od_qqbook_coin_account
where statis_day>=20200101 and statis_day<20200201
group by statis_day, bookid
having sum(fee) > 1000
order by fee desc
count(distinct qq_no):计算人数
where后的条件筛选出2020年1月01-2020年1月31日的记录,也就是2020年1月的所有数据
sum(fee):求和,每本书订阅费用
group by:因为要输出“每天、每本书”,所以根据statis_day(日期),bookid(书籍id)进行group by
having sum(fee)>1000:仅保留订阅费用大于10元的记录,信息中说了fee的单位是分,所以10元是1000
having作用是对由sum或其它集合函数运算结果的输出进行限制。通常情况下,having从句被放置在SQL命令的结尾处
order by:排序,desc:降序排序
Q2:输出每天每本书每个章节订阅的人数。
select statis_day,bookid,cid ,count(distinct qq_no) user_uv
from (
select statis_day,bookid,cid,qq_no
from t_od_qqbook_coin_account lateral view explode(split(cid, '\\*')) tt as cid
where statis_day>=20200101 and statis_day<20200201
) tt
group by statis_day,bookid,cid
解析:这里主要考察的是lateral view explode用法,题目信息中说了表中的cid会把多个章节id通过*连接合并存储,类如50*51*52*53*54*55,需要用lateral view将一条记录拆分成多行,用法如下:
1.Lateral View 用于和UDTF函数【explode,split】结合来使用。
2.首先通过UDTF函数将数据拆分成多行,再将多行结果组合成一个支持别名的虚拟表。
3..主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF,不能包含其它字段以及多个UDTF的情况。
4.语法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias) 20200102,905781969,273561,50*51*52*53*54*55,10
举个例子,内部的select会把20200102,905781969,273561,50*51*52*53*54*55,10拆分成多行,如下
20200102,905781969,273561,50,10
20200102,905781969,273561,51,10
20200102,905781969,273561,52,10
20200102,905781969,273561,53,10
20200102,905781969,273561,54,10
20200102,905781969,273561,55,10
这样每个章节就都有单独的记录了
然后再进项常规的统计查询
where后的条件筛选出2020年1月01-2020年1月31日的记录,也就是2020年1月的所有数据
group by 根据statis_day(日期),bookid(书籍id),cid(章节id)进行分组
count(distinct qq_no)是根据QQ号计算人数,qq_no(qq号)