Hive学习----查询操作基础练习

题目一：

我们有如下的用户访问数据

userId	visitDate	visitCount
u01	2017/1/21	5
u02	2017/1/23	6
u03	2017/1/22	8
u04	2017/1/20	3
u01	2017/1/23	6
u01	2017/2/21	8
u02	2017/1/23	6
u01	2017/2/22	4

要求使用SQL统计出每个用户的累积访问次数，如下表所示：

用户id	月份	小计	累积
u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3

解决步骤：

建表：

create table action
(userId string,
visitDate string,
visitCount int) 
row format delimited fields terminated by "\t";

 

1、因为最终的输出结果中，时间日期的格式要求的是用“-”连接年月，那么首先需要将原始数据中的“/”转换成“-”，此时可以使用regexp_replace函数实现对所有出现在字符串中的指定字符替换成另一个指定的字符，它主要包括三个参数，第一个是要进行修改的字符串，第二个参数是要被替换掉的字符，第三个是要填充的字符。那么先进行这一步操作：

select userid,date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') as visitmonth,visitcount from action;

输出结果：

userid    visitmonth    visitcount
u01    2017-01    5
u02    2017-01    6
u03    2017-01    8
u04    2017-01    3
u01    2017-01    6
u01    2017-02    8
u02    2017-01    6
u01    2017-02    4

2、在输出结果中，第三列输出的是每一个人在每个月份的访问次数之和，也就是，针对每个用户，将其相同的月份中的访问次数进行相加，最终输出所有用户在不同月份的的访问次数之和。那么要实现这个结果，就要将上面的查询结果作为子查询，然后基于这个子查询结果进一步进行处理，具体处理就是，对根据用户和月份分组，统计每一个分组的访问次数之和，那么就能求出来每个用户在不同月份的访问次数之和。

select userid,visitmonth,sum(visitcount) as monthcout from 
(select userid,date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') as visitmonth,visitcount from action) a1 
group by userid,visitmonth;

输出结果：

userid    visitmonth    _c2
u01    2017-01    11
u01    2017-02    12
u02    2017-01    12
u03    2017-01    8
u04    2017-01    3

3、那么现在剩下第四列了，第四列其实就是每一个用户随着月份的增加，其访问次数的累加和，也就是说，一月份的累加和就只有一月份的访问次数，二月份的累加和则是一月份和二月份的访问次数相加，依次类推……，

　　那么我们可以利用上面计算出来的每个用户在不同月份的累计访问次数这个结果列计算出现在的这个累加列。具体操作就是，将上面的查询计算结果作为本次查询的子查询，基于这个子查询，我们使用一个窗口函数根据用户id进行分区，然后根据月份对分区内的数据进行排序，那么针对每条记录开出的窗口中有对应的数据集，我们就针对这些窗口中的数据集进行求和操作，这样就实现了累加的效果：

select userid,visitmonth, sum(monthcout) over(partition by userid order by visitmonth) from 
(
    select userid,visitmonth,sum(visitcount) as monthcout 
    from 
    (
        select userid,date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') as visitmonth,visitcount 
        from action
    ) a1 
        group by userid,visitmonth
) a;

输出结果：

userid    visitmonth    monthcout    sum_window_0
u01    2017-01    11    11
u01    2017-02    12    23
u02    2017-01    12    12
u03    2017-01    8    8
u04    2017-01    3    3

 

题目二：

有50W个京东店铺，每个顾客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：

u1    a
u2    b
u1    b
u1    a
u3    c
u4    b
u1    a
u2    c
u5    b
u4    b
u6    c
u2    c
u1    b
u2    a
u2    a
u3    a
u5    a
u5    a
u5    a

1）每个店铺的UV（访客数）

2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

 建表：

create table visit(user_id string,shop string) row format delimited fields terminated by '\t';

第一问：

　　求店铺的访客数，那么就是求对于一个店铺来说，有多少个不同的访客，曾经访问过，而在原始数据中，同一个访客曾访问同一家店铺多次，所以我们在查询的时候就涉及了去重的问题。

解法1：第一种解法，当然是利用group by子句对shop进行分组，然后利用count函数计算每一个分组的记录数，由于有重复数据，所以需要去重，此时我们使用distinct关键字进行去重，获得最终的查询结果

select shop,count(distinct user_id) from visit group by shop;

输出结果：

shop    c1
a    4
b    4
c    3

　　但是distinct关键字一般是不推荐使用的，因为当数据量特别大的时候，使用distinct关键字会发生数据倾斜的问题，也就是说mapreduce程序在处理用distinct标注的去重字段的时候，会将所有的去重字段的数据shuffle到一个reduce中，这就大大的降低了程序的运算效率。在小数据量的时候，这个问题还不会太明显，但是对于大数据量，其运行时间会是不使用distinct关键字进行去重的程序的运行时间的几倍。

　　同时还要注意，使用distinct关键字进行去重的时候，这个关键字必须放在select语句中的最前面，其前面不能加列明，否则会报错。

解法2：不使用distinct关键字进行去重复，只使用group by。首先根据店铺和用户id进行分组，这就能够去掉重复的数据，然后基于这个查询结果，再次对店铺进行分组，利用count函数对每一个分组所拥有的记录数进行计算，最终就能求出每一个店铺的访客数量：

# 1.去重
select
    shop,
    user_id
from
    visit
group by
    shop,user_id;t1

输出结果：

shop    user_id
a    u1
a    u2
a    u3
a    u5
b    u1
b    u2
b    u4
b    u5
c    u2
c    u3
c    u6

基于上面的数据，求访客数：

# 2.计数
select
    shop,
    count(*) uv
from
    (select
    shop,
    user_id
from
    visit
group by
    shop,user_id)t1
group by
    shop;

输出结果：

shop    uv
a    4
b    4
c    3

 

第二问：

　　要求输出每个店铺访问次数top3的访客信息。包括输出店铺名称、访客id、访问次数。最核心的就是要统计出每个访客访问每家店铺的次数，同时还要针对每家店铺，根据每个用户的访问次数排序，选出对于每家店铺而言访问次数最多的前三名。

解法1：

（1）首先计算出每个访客访问每家店铺的总次数

 

select shop,user_id,count(*) over(partition by shop,user_id) from visit;

 

输出结果：

shop    user_id    count_window_0
a    u1    3
a    u1    3
a    u1    3
a    u2    2
a    u2    2
a    u3    1
a    u5    3
a    u5    3
a    u5    3
b    u1    2
b    u1    2
b    u2    1
b    u4    2
b    u4    2
b    u5    1
c    u2    2
c    u2    2
c    u3    1
c    u6    1

（2）因为有重复的数据，所以要对数据进行处理，重复的数据一般就是同一个用户对同一家店铺的访问次数有多条记录，那么就基于上面的查询结果，然后对uerid进行去重处理：

# 因为userid有重复的，所以现在进行去重操作
select distinct user_id,shop,tol 
from (select shop,user_id,count(*) over(partition by shop,user_id) as tol 
from visit) a;

输出结果：

user_id    shop    tol
u1    a    3
u1    b    2
u2    a    2
u2    b    1
u2    c    2
u3    a    1
u3    c    1
u4    b    2
u5    a    3
u5    b    1
u6    c    1

（3）以店铺为单位，对访问每个店铺的每一位顾客，根据其访问对应店铺的次数，进行降序排序，然后标记对应的编号，1号是访问次数最多的，依次递减

select shop,user_id,tol,row_number() over(partition by shop order by tol desc) 
from (
    select distinct user_id,shop,tol 
    from (
        select shop,user_id,count(*) over(partition by shop,user_id) as tol 
        from visit) a
    ) a1;

输出结果：

shop    user_id    row_number_window_0
a    u5    1
a    u1    2
a    u2    3
a    u3    4
b    u4    1
b    u1    2
b    u5    3
b    u2    4
c    u2    1
c    u6    2
c    u3    3

（4）选出每个店铺访问次数前三名的顾客

 

select shop,user_id,tol 
from (
    select shop,user_id,row_number() over(partition by shop order by tol desc) as rk,tol 
    from (
        select distinct user_id,shop,tol 
        from (
            select shop,user_id,count(*) over(partition by shop,user_id) as tol 
            from visit) a) a1) a2 
where rk <=3

输出结果：

shop    user_id    tol
a    u5    3
a    u1    3
a    u2    2
b    u4    2
b    u1    2
b    u5    1
c    u2    2
c    u6    1
c    u3    1

 

解法2：

（1）首先，计算出每一位顾客访问每一店铺的总的访问次数，只不过这里使用的是group by，直接就完成了去重的操作

 

select
    shop,
    user_id,
    count(*) ct
from
    visit
group by
    shop,user_id

 

输出结果：

shop  user_id ct
a u1  3
a u2  2
a u3  1
a u5  3
b u1  2
b u2  1
b u4  2
b u5  1
c u2  2
c u3  1
c u6  1

（2）针对同一个店铺，对访问次数进行逆序排序，并添加rank值

select
    shop,
    user_id,
    ct,
    row_number() over(partition by shop order by ct desc) rk
from
    (select
    shop,
    user_id,
    count(*) ct
from
    visit
group by
    shop,user_id)t1;

输出结果：

shop  user_id ct  rk
a u5  3 1
a u1  3 2
a u2  2 3
a u3  1 4
b u4  2 1
b u1  2 2
b u5  1 3
b u2  1 4
c u2  2 1
c u6  1 2
c u3  1 3

（3）取店铺访问次数的前三名用户

select
    shop,
    user_id,
    ct
from
    (select
    shop,
    user_id,
    ct,
    row_number() over(partition by shop order by ct desc) rk
from
    (select
    shop,
    user_id,
    count(*) ct
from
    visit
group by
    shop,user_id)t1)t2
where
    rk<=3;

 

做题过程中，遇到的一些错误：

1、对于使用了group-by子句的select查询语句，select所查询的列要么是group-by子句中的列，要么是用聚合函数加工过的列，并不支持直接引用非group-by子句的列。否则会报错，无法成功运行。

2、hql语句千万不要出现制表符，否则无法编译通过；

3、子查询一定要起一个别名，否则会报错，无法通过编译；

4、distinct关键字必须在select语句中要查询的各个列的最前面，其前面是不允许有其它列的，否则会报错；

5、partition-by要和order-by结合使用，distribute-by和sort-by结合，不要将二者混淆使用。