一次数仓面试题目及解答

面试题目

数据如下

问题如下

1. 统计：用户总量，用户平均年龄，用户平均观看时长
2. 统计：每10岁一个分段，统计每个区间的用户总量，用户平均观看时长
3. 统计：每个用户最喜欢的节目
4. 统计：观看时长大于5min的用户总量，只要有一个节目用户观看时间小于5min就不能算

要点总结

1. 不规范的数据需要预先处理
2. 使用case when标记时, 先考虑能否直接总结规律
3. 同时使用开窗函数和group by时, 一定注意分组顺序在开窗之前
4. 子查询中尽量不使用group by和distinct去重, 因为去重操作往往可以在外层查询中进行. 子查询中进行去重会降低代码运行效率
5. not in逻辑的实现
6. 同时求不同维度下的最值, 可以考虑建立中间表, 先计算并存储各纬度下的所有值
7. 不用中间表的一个思路是, 第一层row_number()标记, 第二层将标记逆序排列, 然后case when+row_number()标记出最值, 第三层聚合, 过滤未被标记的null, 从而得到最值. 这种思路可以在最外层聚合之前都保留完整数据. 但三层嵌套+开窗, job太多了

解答

简单预处理

注意到数据源格式不规范.
新建表, 将Playtime列改名为playtime, 将string类型对应转换为int类型

-- 建表
create table test(
	date bigint,
	user string,
	age int,
	programid string,
	playtime int
)
row format delimited
fields terminated by '\t'
stored as textfile
;

-- 加载数据
insert into test
select date, user, age, programid, playtime
, substr(Playtime, 1, length(Playtime) - 3