HIVE-SQL经典面试题_要求使用hql统计出每个用户每月的访问次数及累积访问次数

HIVE-SQL经典面试题

1. 需求：
   我们有如下的用户访问数据
   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
   2.数据准备

CREATE TABLE test_sql.test1 ( 
        userId string, 
        visitDate string,
        visitCount INT )
    ROW format delimited FIELDS TERMINATED BY "\t";
    INSERT INTO TABLE test_sql.test1
    VALUES
        ( '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 );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

思路分析：
1.利用日期函数与政策表达式替换，将原始数据中的日转换成需要的月份 date_formate(regexp_replace(visitdate,'\','-'),'yyyy-MM')
2.利用userid和visitdate(月份)分组，将每个月访问总数求和
3. 在上述子查询基础上，利用窗口函数,对uersid进行分组求和
over(partition by userid order by visitmonth)
最终实现

select userid,visitmonth,subTotal,
sum(subTotal) over(partition by userid order by visitmonth) as total
from
(select userid,
visitmonth,
sum(visitcount) as subTotal
from
(select userid, 
date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') as visitmonth,
visitcount
from test1) as t1
group by userid,visitmonth) as t2
1
2
3
4
5
6
7
8
9
10
11
12

结果：

2. 需求：
有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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

请统计：
(1)每个店铺的UV（访客数）
(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
数据准备：

CREATE TABLE test_sql.test2 ( 
                         user_id string, 
                         shop string )
            ROW format delimited FIELDS TERMINATED BY '\t'; 
            INSERT INTO TABLE test_sql.test2 VALUES
            ( '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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

思路分析：
（1）注意访客数目要去重
（2）第二题先按子查询，把每个店铺每个人有次访问该店铺求出来，然后利用排名函数，根据店铺shop进行分组，对次数进行排序

（1） SELECT shop,
               count(DISTINCT user_id)
        FROM test_sql.test2
        GROUP BY shop
（2）select shop,userid,visitNum,rankOrder from 
(select shop,userid,visitNum,
row_number() over(partition by shop order by visitNum desc) as rankOrder
from 
(select shop,userid,count(userid) as visitNum from test2 
group by shop,userid) as t1) 
as t2
where rankOrder<=3

1
2
3
4
5
6
7
8
9
10
11
12
13

3.需求：
已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。
数据样例:2017-01-01,10029028,1000003251,33.57。
请给出sql进行统计:
(1)给出 2017年每个月的订单数、用户数、总成交金额。
(2)给出2017年11月的新客数(指在11月才有第一笔订单)
数据准备：

CREATE TABLE test_sql.test3 ( 
            dt string,
            order_id string, 
            user_id string, 
            amount DECIMAL ( 10, 2 ) )
ROW format delimited FIELDS TERMINATED BY '\t';
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','10029028','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','10029029','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','100290288','1000003252',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','10029088','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','100290281','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','100290282',
1
2
3
4
5
6
7
8
9
10
11