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

大数据笔试题 专栏收录该内容
1 篇文章 0 订阅

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

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

不要网名最好了

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值