SQL进阶技巧：分段统计的用户平均观看时长问题分析 | 等距分桶算法应用

时间：2024-10-03 07:21:46

目录

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

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)建表

    

  1. 

    

    
create table userview(
    

    2. 

  2. 

    

        view_date bigint,
    

    3. 

  3. 

    

        user_id string,
    

    4. 

  4. 

    

        age int,
    

    5. 

  5. 

    

        programid string,
    

    6. 

  6. 

    

        playtime int

    

    7. 

  7. 

    

    )
    

    8. 

  8. 

    

    
row format delimited fields terminated by '\t'

    

    9.

(3) 加载数据

load data local inpath "/home/centos/dan_test/" into table userview;

（4） 查询数据

    

  1. 

    

    hive> select * from userview;
    

    2. 

  2. 

    

    OK
    

    3. 

  3. 

    

    
20190421	u1	30	a	4

    

    4. 

  4. 

    

    
20190421	u1	30	b	10

    

    5. 

  5. 

    

    
20190421	u1	30	a	2

    

    6. 

  6. 

    

    
20190421	u2	27	c	1

    

    7. 

  7. 

    

    
20190422	u3	35	d	3

    

    8. 

  8. 

    

    
20190423	u2	27	a	2

    

    9. 

  9. 

    

    
Time taken: 0.158 seconds, Fetched: 6 row(s)
    

    10.

2 数据分析

  • （1）统计：用户总量，用户平均年龄，用户平均观看时长
    

  1. 

    

    
select count(user_id)
    

    2. 

  2. 

    

          ,avg(age)
    

    3. 

  3. 

    

          ,avg(sum_plt)
    

    4. 

  4. 

    

    
from(
    

    5. 

  5. 

    

        select user_id
    

    6. 

  6. 

    

              ,age
    

    7. 

  7. 

    

              ,sum(playtime) as sum_plt
    

    8. 

  8. 

    

        from userview
    

    9. 

  9. 

    

        group by user_id,age
    

    10. 

  10. 

    

    ) t
    

    11. 

    

  1. 

    

    OK
    

    2. 

  2. 

    

    
3	30.666666666666668	7.333333333333333

    

    3. 

  3. 

    

    
Time taken: 25.789 seconds, Fetched: 1 row(s)
    

    4.
  • （2）统计：每10岁一个分段，统计每个区间的用户总量，用户平均观看时长

分段统计：一般数据范围比较明确的情况下分段统计用 case when 进行划分，很明显此题数据范围不是很明确，是动态的，是按range的形式进行划分我们可以用分桶的思想。核心代码如下：

int(age/10)或floor(age/10)【可以参考SQL cookbook中的案例】

    

  1. 

    

    
select count(user_id)
    

    2. 

  2. 

    

          ,avg(sum_plt)
    

    3. 

  3. 

    

    
from(
    

    4. 

  4. 

    

        select user_id
    

    5. 

  5. 

    

              ,age
    

    6. 

  6. 

    

              ,sum(playtime) as sum_plt
    

    7. 

  7. 

    

        from userview
    

    8. 

  8. 

    

        group by user_id,age
    

    9. 

  9. 

    

    ) t
    

    10. 

  10. 

    

    
group by int(age/10)
    

    11. 

    

  1. 

    

    OK
    

    2. 

  2. 

    

    
1	3.0

    

    3. 

  3. 

    

    
2	9.5

    

    4. 

  4. 

    

    
Time taken: 9.487 seconds, Fetched: 2 row(s)
    

    5.

其实此题主要应用的是分桶思想： 分桶主要分两类

  • 一类是创建固定大小的数据桶：分多少个桶我不知道，但每个桶装的数量是知道的，即创建固定大小的数据桶。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 后求出。
    

  1. 

    

    --先求出每个用户针对每个节目的观看总时长，及排名
    

    2. 

  2. 

    

    
with tmp as (
    

    3. 

  3. 

    

    
select user_id
    

    4. 

  4. 

    

          ,programid
    

    5. 

  5. 

    

          ,sum(playtime) as sum_playtime
    

    6. 

  6. 

    

          ,dense_rank() over(partition by user_id order by sum(playtime) desc) as rk
    

    7. 

  7. 

    

    
from userview
    

    8. 

  8. 

    

    
group by user_id, programid
    

    9. 

  9. 

    

    )
    

    10. 

  10. 

    

    --找出排名第一的就是用户最喜欢的节目
    

    11. 

  11. 

    

    
select user_id
    

    12. 

  12. 

    

          ,programid
    

    13. 

  13. 

    

          ,sum_playtime
    

    14. 

  14. 

    

    
from tmp
    

    15. 

  15. 

    

    
where rk=1

    

    16.

注意问题：此题开窗函数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吗？答案是否定的。我们可如下语句执行的结果

    

  1. 

    

    
select user_id
    

    2. 

  2. 

    

          ,programid
    

    3. 

  3. 

    

          ,sum(playtime) as sum_playtime
    

    4. 

  4. 

    

          ,dense_rank() over(partition by user_id order by sum_playtime desc) as rk
    

    5. 

  5. 

    

    
from userview
    

    6. 

  6. 

    

    
group by user_id, programid
    

    7.

结果报如下错误：

    

  1. 

    

    
FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
    

    2. 

  2. 

    

    
Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 4:55 Expression not in GROUP BY key 'sum_playtime'

    

    3.

sum_playtime别名不在group by key中，因而order by后也必须用sum(playtime)

验证：去掉select中的sum(playtime)，看执行结果

    

  1. 

    

    
select user_id
    

    2. 

  2. 

    

          ,programid
    

    3. 

  3. 

    

          ,dense_rank() over(partition by user_id order by sum(playtime) desc) as rk
    

    4. 

  4. 

    

    
from userview
    

    5. 

    

  1. 

    

    
FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.
    

    2. 

  2. 

    

    
Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 3:55 Not yet supported place for UDAF 'sum'

    

    3.

报错：Not yet supported place for UDAF 'sum'。说明窗口函数暂不支持使用聚合函数sum，sql语法解析异常。

此文最终结果如下：

    

  1. 

    

    --------------------------------------------------------------------------------
    

    2. 

  2. 

    

    OK
    

    3. 

  3. 

    

    u1	b	10

    

    4. 

  4. 

    

    u2	a	2

    

    5. 

  5. 

    

    u3	d	3

    

    6. 

  6. 

    

    
Time taken: 21.548 seconds, Fetched: 3 row(s)
    

    7.

（4）统计：观看时长大于5min的用户总量，只要有一个节目用户观看时间小于5min就不能算

  • 方法一：not in 的实现。

分析：先求出观看时长小于5分钟的用户

    

  1. 

    

    
select user_id
    

    2. 

  2. 

    

    
from userview
    

    3. 

  3. 

    

    
where playtime < 5

    

    4.

 目标：只要选出的用户不在上述表中即为所要的目标

    

  1. 

    

    
select user_id 
    

    2. 

  2. 

    

    
from userview 
    

    3. 

  3. 

    

    
where user_id not in (
    

    4. 

  4. 

    

    
select user_id
    

    5. 

  5. 

    

    
from userview
    

    6. 

  6. 

    

    
where playtime < 5

    

    7. 

  7. 

    

    ) 
    

    8.

报如下错误：

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

版本不支持not in后面跟子查询。

用left join + is null代替

最终SQL 如下：

    

  1. 

    

    
select count(u.user_id)
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
from userview u
    

    4. 

  4. 

    

     
    

    5. 

  5. 

    

    
left join(
    

    6. 

  6. 

    

     
    

    7. 

  7. 

    

        select user_id
    

    8. 

  8. 

    

     
    

    9. 

  9. 

    

        from userview
    

    10. 

  10. 

    

     
    

    11. 

  11. 

    

        where playtime < 5

    

    12. 

  12. 

    

     
    

    13. 

  13. 

    

    ) t
    

    14. 

  14. 

    

     
    

    15. 

  15. 

    

    
on u.user_id=t.user_id
    

    16. 

  16. 

    

     
    

    17. 

  17. 

    

    
where t.user_id is null

    

    18. 

  18. 

    

     
    

    19. 

  19. 

    

    
group by u.user_id
    

    20. 

  20. 

    

     
    

    21. 

  21. 

    

    ;
    

    22.

总结：hive中in,not in带子查询的实现

in的实现：

Hive中的in的实现方式很多，简单说几种：

  1. 用left semi join实现
  2. 用left outer join+is not null实现
  3. 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如下:

    

  1. 

    

    
select  ,  from skim left outer join buy
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
on  = buy .userId and  = buy .itemId where buy .userId is not null;
    

    4.

或

    

  1. 

    

    
select  ,  from skim left semi join buy
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
on  = buy .userId and  = buy .itemId;
    

    4.

或

    

  1. 

    

    
select  ,  from skim join buy
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
on  = buy .userId and  = buy .itemId;
    

    4.

结果如下:

userID itemID
001 342
002 382
003 458

  • NOT IN实现：

如果要查询在skim表中并且不在buy表中的信息，需要用not in查询，hive sql如下:

    

  1. 

    

    
select ,  from skim left outer join buy
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
on =buy .userId and =buy .itemId where buy .userId is null;
    

    4.

结果如下：

userID itemID
004 468

Hive 不支持 where 子句中的子查询， SQL 常用的 exist in 子句需要改写。这一改写相对简单。考虑以下 SQL 查询语句：

    

  1. 

    

    
SELECT , 
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
FROM a
    

    4. 

  4. 

    

     
    

    5. 

  5. 

    

    
WHERE  in

    

    6. 

  6. 

    

     
    

    7. 

  7. 

    

    (SELECT 
    

    8. 

  8. 

    

     
    

    9. 

  9. 

    

    
FROM B);
    

    10.

可以改写为

    

  1. 

    

    
SELECT , 
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
FROM a LEFT OUTER JOIN b ON ( = )
    

    4. 

  4. 

    

     
    

    5. 

  5. 

    

    
WHERE  <> NULL;
    

    6.

一个更高效的实现是利用 left semi join 改写为：

    

  1. 

    

    
SELECT , 
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
FROM a LEFT SEMI JOIN b on ( = );
    

    4.

left semi join 是 0.5.0 以上版本的特性。hive 的 left semi join 讲解hive 的 left semi join 讲解_HappyRocking的博客-****博客_hive semi

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

not exists 例子

    

  1. 

    

    
select a, b
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
from table1 t1
    

    4. 

  4. 

    

     
    

    5. 

  5. 

    

    
where not exists (select 1

    

    6. 

  6. 

    

     
    

    7. 

  7. 

    

    
from table2 t2
    

    8. 

  8. 

    

     
    

    9. 

  9. 

    

    
where  = 
    

    10. 

  10. 

    

     
    

    11. 

  11. 

    

    
and  = )
    

    12.

可以改为

    

  1. 

    

    
select , 
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
from table1 t1
    

    4. 

  4. 

    

     
    

    5. 

  5. 

    

    
left join table2 t2
    

    6. 

  6. 

    

     
    

    7. 

  7. 

    

    
on ( =  and  = )
    

    8. 

  8. 

    

     
    

    9. 

  9. 

    

    
where  is null

    

    10.
  • 方法2：having子句实现。技巧：利用min()、max()函数全解问题。min()所有都大于，max()所有都小于（推荐解法）

题目中要求：只要有一个节目用户观看时间小于5min就不能算，即用户观看的所有节目中只要有一个节目观看时长小于5min该用户就不能算。所以我们可以利用min()函数做限定，min(playtime)表示用户观看的最小时间，如果用户观看的最小时间都大于等于5min钟，那么该用户观看的所有节目都是大于等于5分钟的，则满足题意。我们可以对用户分组，求出其最小观看时间，然后利用having过滤输出即可，具体SQL如下：

    

  1. 

    

    
select 
    

    2. 

  2. 

    

    
count(user_id)
    

    3. 

  3. 

    

    
from(
    

    4. 

  4. 

    

    
select 
    

    5. 

  5. 

    

    user_id
    

    6. 

  6. 

    

    
from userview
    

    7. 

  7. 

    

    
group by user_id
    

    8. 

  8. 

    

    
having min(playtime)>=5) t;
    

    9. 

    

  1. 

    

    OK
    

    2. 

  2. 

    

    0
    

    3. 

  3. 

    

    
Time taken: 13.167 seconds, Fetched: 1 row(s)
    

    4.

方法2小结：

上述求解这种至少，至多的问题描述可以归结为任意，所有都的问题，把这类问题我们统称为描述性问题。这类问题分以下几类：

  • （1）所有都大于：所有都大于的问题描述即min()函数，只要最小值都比该值大那么所有的都比该值大
  • （2）所有都小于：所有都小于的问题描述是max()函数，只要最大的值比该值小，那么所有的都比该值小。
  •  (3) 至少：本质是所有都大于问题，只要最小值大于该值，那么所有的都将大于
  • （4）至多：本质是所有都小于，只要最大值小于该值，那么所有的都将小于
  • （5）对于所有都的问题思考我们往往可以从反面入手，通过补集的思想解决问题。

4 小结

此道题涉及的知识点及SQL的技巧比较多，能够很好的考察面试者SQL水平。本题主要涉及的知识点包括：

  • 分桶的创建：桶大小固定及桶个数固定。
  • 排名函数求最优（top）问题
  • not in的hive实现
  • 描述性问题求解思路

参考连接：

Hive面试题：hive如何实现in和not in_谦卑t的博客-****博客_hive面试题

Hive之——怎样写exist/in以及not exists/not in子句_小嘎子闯天涯的博客-****博客

欢迎关注石榴姐公众号"会飞的一十六"，关注我不迷路

相关文章