本文主要是介绍SQL用户观看时长问题分析--HiveSQL面试题19，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
0 题目
1 数据准备
2 数据分析
4 小结
0 题目
数据如下：
date user_id age programid Playtime
20190421 u1 30 a 4
20190421 u1 30 b 10
20190421 u1 30 a 2
20190421 u2 27 c 1
20190422 u3 35 d 3
20190423 u2 27 a 2
问题：
1.统计：用户总量，用户平均年龄，用户平均观看时长
2.统计：每10岁一个分段，统计每个区间的用户总量，用户平均观看时长
3.统计：每个用户最喜欢的节目
4.统计：观看时长大于5min的用户总量，只要有一个节目用户观看时间小于5min就不能算
1 数据准备
（1）数据
vim userview.txt
date user_id age programid Playtime
20190421 u1 30 a 4
20190421 u1 30 b 10
20190421 u1 30 a 2
20190421 u2 27 c 1
20190422 u3 35 d 3
20190423 u2 27 a 2
(2)建表
create table userview(
view_date bigint,
user_id string,
age int,
programid string,
playtime int
)
row format delimited fields terminated by '\t'
(3) 加载数据
load data local inpath "/home/centos/dan_test/userview.txt" into table userview;
（4） 查询数据
hive> select * from userview;
OK
20190421 u1 30 a 4
20190421 u1 30 b 10
20190421 u1 30 a 2
20190421 u2 27 c 1
20190422 u3 35 d 3
20190423 u2 27 a 2
Time taken: 0.158 seconds, Fetched: 6 row(s)
2 数据分析
（1）统计：用户总量，用户平均年龄，用户平均观看时长
select count(user_id)
,avg(age)
,avg(sum_plt)
from(
select user_id
,age
,sum(playtime) as sum_plt
from userview
group by user_id,age
) t
OK
3 30.666666666666668 7.333333333333333
Time taken: 25.789 seconds, Fetched: 1 row(s)
（2）统计：每10岁一个分段，统计每个区间的用户总量，用户平均观看时长
分段统计：一般数据范围比较明确的情况下分段统计用 case when 进行划分，很明显此题数据范围不是很明确，是动态的，是按range的形式进行划分我们可以用分桶的思想。核心代码如下：
int(age/10)或floor(age/10)【可以参考SQL cookbook中的案例】
select count(user_id)
,avg(sum_plt)
from(
select user_id
,age
,sum(playtime) as sum_plt
from userview
group by user_id,age
) t
group by int(age/10)
OK
1 3.0
2 9.5
Time taken: 9.487 seconds, Fetched: 2 row(s)
其实此题主要应用的是分桶思想： 分桶主要分两类
一类是创建固定大小的数据桶：分多少个桶我不知道，但每个桶装的数量是知道的，即创建固定大小的数据桶。bucket的容量是已知的，比如此题就指明了每10岁一个分段，说明桶的大小为10，每个桶只能装10个元素。其解决方法为：int（需要分桶的字段/桶大小）或floor（需要分桶的字段/桶大小）.如本题中，按年龄10岁一个分段，即int(age/10)
一类是创建固定个数的桶：桶的大小是不知道的，即桶中装多少元素不知道，但我知道需要分多少个桶，也就是说创建固定个数的桶。通俗的来说就是你想把你的数据分别放入到数目固定的桶里面去。对于这类问题的解决方案，像hive,oracle等数据库提供了专门的函数去处理，这个函数就是ntile()函数，ntile负责把排好序的集合分别放入到指定数目的桶里，每一个元素必然会被分配到某个桶中。如果没有ntile()函数，有row_number()函数，可以借助该函数及求余的方式进行构建。如下：mod(row_number()over(order by 数据需要分桶的字段)，分桶个数)+1，加1是为了桶从1开始，而不是从0开始。如果数据库中没有row_number()类分析函数则相对比较复杂需要采用自关联，做出全集进行计算，此处不再进行展开叙述。
（3） 统计：每个用户最喜欢的节目
注意此问题的技巧点：order by 后可以直接写聚合函数，但此聚合函数必须先在select 后求出。
--先求出每个用户针对每个节目的观看总时长，及排名
with tmp as (
select user_id
,programid
,sum(playtime) as sum_playtime
,dense_rank() over(partition by user_id order by sum(playtime) desc) as rk
from userview
group by user_id, programid
)
--找出排名第一的就是用户最喜欢的节目
select user_id
,programid
,sum_playtime
from tmp
where rk=1
注意问题：此题开窗函数order by后如果直接写sum(playtime)的话会报错，必须在select后面先写sum(play time)，然后开窗函数over()里面order by后才能用sum(playtime)。原因是开窗函数实际上是对select后结果的开窗，相当于select后的结果集是开窗函数的输入，但执行顺序是和select同一级别的，因而select后的别名是不能用的。
那么此处order by后可以用sum(playtime) as sum_playtime后的别名sum_playtime吗？答案是否定的。我们可如下语句执行的结果
select user_id
,programid
,sum(playtime) as sum_playtime
,dense_rank() over(partition by user_id order by sum_playtime desc) as rk
from userview
group by user_id, programid
结果报如下错误：
FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 4:55 Expression not in GROUP BY key 'sum_playtime'
sum_playtime别名不在group by key中，因而order by后也必须用sum(playtime)
验证：去掉select中的sum(playtime)，看执行结果
select user_id
,programid
,dense_rank() over(partition by user_id order by sum(playtime) desc) as rk
from userview
FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 3:55 Not yet supported place for UDAF 'sum'
报错：Not yet supported place for UDAF 'sum'。说明窗口函数暂不支持使用聚合函数sum，sql语法解析异常。
此文最终结果如下：
--------------------------------------------------------------------------------
OK
u1 b 10
u2 a 2
u3 d 3
Time taken: 21.548 seconds, Fetched: 3 row(s)
（4）统计：观看时长大于5min的用户总量，只要有一个节目用户观看时间小于5min就不能算
方法一：not in 的实现。
分析：先求出观看时长小于5分钟的用户
select user_id
from userview
where playtime < 5
目标：只要选出的用户不在上述表中即为所要的目标
select user_id
from userview
where user_id not in (
select user_id
from userview
where playtime < 5
)
报如下错误：
SemanticException [Error 10249]: Line 6:6 Unsupported SubQuery Expression 'playtime': SubQuery cannot use the table alias: userview; this is also an alias in the Outer Query and SubQuery contains a unqualified column reference
hive1.X版本不支持not in后面跟子查询。
用left join + is null代替
最终SQL 如下：
select count(u.user_id)
from userview u
left join(
select user_id
from userview
where playtime < 5
) t
on u.user_id=t.user_id
where t.user_id is null
group by u.user_id
;
总结：hive中in,not in带子查询的实现
in的实现：
Hive中的in的实现方式很多，简单说几种：
用left semi join实现
用left outer join+is not null实现
Inner join实现
not in的实现
Left join+is nul（本题案例）
举例说明：
有两个表如下：
skim表
userID itemID time
001 342 2015-05-08
002 382 2015-05-09
002 458 2015-05-09
004 325 2015-05-09
userID itemID time
001 342 2015-05-07
002 382 2015-05-08
003 458 2015-05-09
004 325 2015-05-09
IN实现：
如果要查询在skim表中并且也在buy表中的信息，需要用in查询，hive sql如下:
select skim.userId , skim.itemId from skim left outer join buy
on skim.userId = buy .userId and skim.itemId = buy .itemId where buy .userId is not null;
或
select skim.userId , skim.itemId from skim left semi join buy
on skim.userId = buy .userId and skim.itemId = buy .itemId;
或
select skim.userId , skim.itemId from skim join buy
on skim.userId = buy .userId and skim.itemId = buy .itemId;
结果如下:
userID itemID
001 342
002 382
003 458
NOT IN实现：
如果要查询在skim表中并且不在buy表中的信息，需要用not in查询，hive sql如下:
select skim.userId, skim.itemId from skim left outer join buy
on skim.userId=buy .userId and skim.itemId=buy .itemId where buy .userId is null;
结果如下：
userID itemID
004 468
Hive 不支持 where 子句中的子查询， SQL 常用的 exist in 子句需要改写。这一改写相对简单。考虑以下 SQL 查询语句：
SELECT a.key, a.value
FROM a
WHERE a.key in
(SELECT b.key
FROM B);
可以改写为
SELECT a.key, a.value
FROM a LEFT OUTER JOIN b ON (a.key = b.key)
WHERE b.key <> NULL;
一个更高效的实现是利用 left semi join 改写为：
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key);
left semi join 是 0.5.0 以上版本的特性。hive 的 left semi join 讲解https://blog.csdn.net/happyrocking/article/details/79885071
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
not exists 例子
select a, b
from table1 t1
where not exists (select 1
from table2 t2
where t1.a = t2.a
and t1.b = t2.b)
可以改为
select t1.a, t2.b
from table1 t1
left join table2 t2
on (t1.a = t2.a and t1.b = t2.b)
where t2.a is null
方法2：having子句实现。技巧：利用min()、max()函数全解问题。min()所有都大于，max()所有都小于（推荐解法）
题目中要求：只要有一个节目用户观看时间小于5min就不能算，即用户观看的所有节目中只要有一个节目观看时长小于5min该用户就不能算。所以我们可以利用min()函数做限定，min(playtime)表示用户观看的最小时间，如果用户观看的最小时间都大于等于5min钟，那么该用户观看的所有节目都是大于等于5分钟的，则满足题意。我们可以对用户分组，求出其最小观看时间，然后利用having过滤输出即可，具体SQL如下：
select
count(user_id)
from(
select
user_id
from userview
group by user_id
having min(playtime)>=5) t;
OK
0
Time taken: 13.167 seconds, Fetched: 1 row(s)
方法2小结：
上述求解这种至少，至多的问题描述可以归结为任意，所有都的问题，把这类问题我们统称为描述性问题。这类问题分以下几类：
（1）所有都大于：所有都大于的问题描述即min()函数，只要最小值都比该值大那么所有的都比该值大
（2）所有都小于：所有都小于的问题描述是max()函数，只要最大的值比该值小，那么所有的都比该值小。
(3) 至少：本质是所有都大于问题，只要最小值大于该值，那么所有的都将大于
（4）至多：本质是所有都小于，只要最大值小于该值，那么所有的都将小于
（5）对于所有都的问题思考我们往往可以从反面入手，通过补集的思想解决问题。
4 小结
此道题涉及的知识点及SQL的技巧比较多，能够很好的考察面试者SQL水平。本题主要涉及的知识点包括：
分桶的创建：桶大小固定及桶个数固定。
排名函数求最优（top）问题
not in的hive实现
描述性问题求解思路
参考连接：
https://blog.csdn.net/qq_42246689/article/details/84702253
https://blog.csdn.net/zhangge360/article/details/84865567
这篇关于SQL用户观看时长问题分析--HiveSQL面试题19的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
