[hiveSQL面试题2]

一、示例数据：某网店用户访问表部分数据(user_id:用户ID，shop:店铺名，表名：visit）如下

（一）需求：每个店铺访问次数Top3的访客信息(输出店铺名称、访客id、访问次数)。

（1）查询每个店铺被每个用户访问次数

create temporary table tb1 as
select
    shop,
    user_id,
    count(1) cnt
from visit
group by shop,user_id;

输出结果如下图：

（2）计算每个店铺被用户访问次数排名，取每个店铺排名前3的

select
    user_id,shop,cnt,rank
from(
    select
        shop,
        user_id,
        cnt,
        row_number() over(partition by shop order by cnt) rank
    from tb1 a
) b
where rank <= 3;

输出结果如下图：

二、示例数据：某订单表信息(表名：)如下：

visit_Date：日期
Order_id：订单ID
User_id：用户ID
amount：交易金额

（二）需求：求出 2017年每个月的订单数、用户数、总成交金额。

select
    substr(visit_date,1,7) as mth,      --月份
    count(Order_id) as order_cnt,       --月订单数
    count(distinct user_id) as user_cnt,--用户数
    sum(amount) as amount               --月总成交额
from order_info
group by substr(visit_date,1,7);

输出结果如下图：

sql解析：
这里需要用到截取日期的函数，也可以使用date_format(visit_date,‘YYYY-MM’)进行格式转换。

（一）需求：求出2017年1月的新客数(指在1月才有第一笔订单)

select
	user_id,              --这里便于好看理解列出新客的user_id
    count(user_id) as cnt
from order_info
group by user_id
having date_format(min(visit_date),'YYYY-MM') = '2017-01';

输出结果如下图：

sql解析：
我们按user_id进行分组后会得到用户的所有交易日期，通过having过滤指定新客月份即可得出新客总数。

三、示例数据：用户信息表数据如下（表名：user_info）

User_id：用户ID
User_age：用户年龄
Login_date：登录日期

<