牛客面试题解析笔记——大数据方向

牛客面试题解析笔记——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号)