hive,spark经典sql题

1. 请使用Spark SQL完成。

有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：
1）每个店铺的UV（访客数）
2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
数据如下：
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

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

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

2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
（1）查询每个店铺被每个用户访问次数

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

（2）计算每个店铺被用户访问次数排名

select shop,user_id,ct,rank() over(partition by shop order by ct desc) rk
from t1;t2

（3）取每个店铺排名前3的

select shop,user_id,ct
from t2
where rk<=3;

（4）最终SQL

select 
shop,
user_id,
ct
from
(select 
shop,
user_id,
ct,
rank() 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;

2. 请使用Hive sql 完成

我们有如下的用户访问数据：
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统计出每个用户的累计访问次数，如下所示：
用户 月份 小计 累积
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）修改数据格式

select
    userId,
    date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
    visitCount
from
action;t1

2）计算每人单月访问量

select
    userId,
    mn,
    sum(visitCount) mn_count
from
    t1
group by userId,mn;t2

3）按月累计访问量

select
    userId,
    mn,
    mn_count,
    sum(mn_count) over(partition by userId order by mn)
from t2;

最终SQL：

select
    userId,
    mn,
    mn_count,
    sum(mn_count) over(partition by userId order by mn)
from 
    ( select
        userId,
        mn,
        sum(visitCount) mn_count
    from
        (select
            userId,
            date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
            visitCount
        from
            action)t1
    group by userId,mn)t2;

3.请用Hive完成

已知一个表order_tab，有如下字段:Date，Order_id，User_id，amount。请给出sql进行统计:数据样例:
2017-01-01,10029028,1000003251,33.57。
1）给出 2017年每个月的订单数、用户数、总成交金额。
2）给出2017年11月的新客数(指在11月才有第一笔订单)
建表语句如下：
create table order_tab(dt string,order_id string,user_id string,amount decimal(10,2)) row format delimited fields terminated by ‘\t’;

1)select
    date_format(dt,'yyyy-MM'),
    count(order_id),
    count(distinct user_id),
    sum(amount)
from
    order_tab
group by date_format(dt,'yyyy-MM');

2)select
    tmp.mn,
    count(*)
from
    (
    select
        date_format(min(dt),'yyyy-MM') mn,
        user_id
    from
        order_tab
    group by user_id
    having date_format(min(dt),'yyyy-MM')='2017-11'
    ) tmp
group by tmp.mn;

4.请用Hive完成

成绩的数据格式exercise5_1.txt 文件中的每一行就是一个学生的成绩信息。字段之间的分隔符是","：

时间,学校,年级,姓名,科目,成绩
2013,北大,1,黄渤,语文,97
2013,北大,1,徐峥,语文,52
2013,北大,1,刘德华,语文,85
2012,清华,0,马云,英语,61
2015,北理工,3,李彦宏,物理,81
2016,北科,4,马化腾,化学,92
2014,北航,2,刘强东,数学,70
2012,清华,0,刘诗诗,英语,59
2014,北航,2,刘亦菲,数学,49
2014,北航,2,刘嘉玲,数学,77
6.1、建表导数据（use sx_bdp_safe库）
6.2、分组TopN，选出2017年每个学校、每个年级、分数前三的科目
6.3、选出每一年、每个学校、每个年级、每科的分数低于该科目的平均分的姓名。

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
-- 列的信息
[(col_name data_type [COMMENT col_comment], ...)] 
-- 表的注释
[COMMENT table_comment] 
-- 是否是分区表，及指定分区字段
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES]

6.1建表

create table use(time string,school string,grade string,subject_id string,score int)
row format delimited fields terminated by ',';
导入数据
load data local inpath '/exercise5_1.txt' into table use