长按二维码关注
大数据领域必关注的公众号
By大数据研习社
概要：Hive SQL是从事大数据分析同学的基本功，也是数仓建设的重要工具。无论是秋招、春招或者是实习，Hive SQL都是面试官考察的重点。
关键词：数仓、Hive、面试、开窗函数
1 需求分析
统计b站视频观看数topn
统计b站视频分类热度topn
统计b站每个类别视频观看数topn（开窗函数）
统计b站视频不同评分等级的视频数（列转行）
统计上传b站视频最多的用户Top10，以及这些用户上传的视频观看次数在前10的视频
2 数据结构
2.1用户表（user）
2.2视频表（video）
3 准备工作
3.1 创建数据库
create database myvideo;
创建用户原始表user_orignal
create table if not exists user_orignal
(uid int,
name string,
regtime string,
visitnum int,
lastvisit string,
gender int,
birthday string,
country string,
province string,
city string,
uploadvideos int)
row format delimited fields terminated by ","
stored as textfile;
创建原始视频表video_orignal
create table if not exists video_orignal
(vid string,
uid int,
vday int,
vtype string,
vlength int,
visit int,
score int,
comments int,
collection int,
fabulous int,
forward int)
row format delimited fields terminated by ","
stored as textfile;
创建用户表user_orc
create table if not exists user_orc
(uid int,
name string,
regtime string,
visitnum int,
lastvisit string,
gender int,
birthday string,
country string,
province string,
city string,
uploadvideos int)
row format delimited fields terminated by ","
stored as orc
tblproperties("orc.compress"="SNAPPY");
创建视频表video_orc
create table if not exists video_orc
(vid string,
uid int,
vday int,
vtype string,
vlength int,
visit int,
score int,
comments int,
collection int,
fabulous int,
forward int)
row format delimited fields terminated by ","
stored as orc
tblproperties("orc.compress"="SNAPPY");
load data local inpath "/home/hadoop
/shell/data/user.txt" into table user_orignal;
load data local inpath "/home/hadoop/
shell/data/video.txt" into table video_orignal;
3.5 数据加载到orc+Snappy
insert into table user_orc select * from user_orignal;
insert into table video_orc select * from video_orignal;
4 业务分析
select vid,visit from video_orc order by visit desc limit 10;
select vtype,count(vid) hot from video_
orc group by vtype
order by hot desc limit 10;
select v.vtype,v.vid,v.visit from
(select vtype,vid,visit,rank() over(part-
ition by vtype order by visit desc) rk from video_orc) v where rk<=3;
分析函数：用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数：指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
备注：
排序函数rank()：在每个分组类进行排名。
开窗函数over（partition by vtype order by visit）：按照vtype分区，在一个分区内按照visit排序。
select
max(case v.score when 1 then v.num else 0 end) 1star,
max(case v.score when 2 then v.num else 0 end) 2star,
max(case v.score when 3 then v.num else 0 end) 3star,
max(case v.score when 4 then v.num else 0 end) 4star,
max(case v.score when 5 then v.num else 0 end) 5star
from
(select score,count(*) as num from video_orc group by score) v;
select v.vid,v.visit,v.uid from
(select uid,uploadvideos from user_orc order by uploadvideos desc limit 10) u join video_orc v on u.uid=v.uid order by v.visit desc limit 10;
完
