大数据技术之Hive SQL题库-中级4-阿里云开发者社区

大数据技术之Hive SQL题库-中级4

2023-06-12 460

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生大数据计算服务MaxCompute，500CU*H 100GB 3个月

云原生大数据计算服务 MaxCompute，5000CU*H 100GB 3个月

简介： 大数据技术之Hive SQL题库-中级4

2.13 即时订单比例

2.13.1 题目需求

订单配送中，如果期望配送日期和下单日期相同，称为即时订单，如果期望配送日期和下单日期不同，称为计划订单。

请从配送信息表（delivery_info）中求出每个用户的首单（用户的第一个订单）中即时订单的比例，保留两位小数，以小数形式显示。期望结果如下：

percentage

0.6

2.13.2 代码实现

hive>

select
    round(sum(if(order_date=custom_date,1,0))/count(*),2) percentage
from
(
    select
        delivery_id,
        user_id,
        order_date,
        custom_date,
        row_number() over (partition by user_id order by order_date) rn
    from delivery_info
)t1
where rn=1;

骚戴理解：还是那句话，摆脱mysql的惯有思维！也就是使用传统的group by来实现的都优先考虑窗口函数，round(sum(if(order_date=custom_date,1,0))/count(*),2)我一开始想的就是用where来实现，但是round(sum(if(order_date=custom_date,1,0))/count(*),2)这样写要容易很多，简洁很多！

需要用到的表：delivery_info表

*2.14 向用户推荐朋友收藏的商品

2.14.1 题目需求

现需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品，请从好友关系表（friendship_info）和收藏表（favor_info）中查询出应向哪位用户推荐哪些商品。期望结果如下：

1）部分结果展示

user_id（用户id）	sku_id（应向该用户推荐的商品id）
101	2
101	4
101	7
101	9
101	8
101	11
101	1

2）完整结果

user_id sku_id

101 2

101 4

101 7

101 9

101 8

101 11

101 1

102 3

102 5

102 10

103 2

103 1

103 9

104 1

104 4

104 10

104 5

104 2

105 1

105 2

105 6

105 12

105 3

106 11

106 10

106 8

106 9

106 3

107 11

107 7

107 4

107 9

107 12

107 1

107 8

107 6

107 2

108 2

108 6

108 12

108 1

108 7

108 4

108 5

109 6

109 10

109 7

109 1

109 12

109 3

109 11

1010 4

1010 10

1010 6

1010 12

1010 11

1010 8

1010 3

1010 5

1010 7

2.14.2 代码实现

hive>

select
    distinct t1.user_id,
    friend_favor.sku_id
from
(
    select
        user1_id user_id,
        user2_id friend_id
    from friendship_info
    union
    select
        user2_id,
        user1_id
    from friendship_info
)t1
left join favor_info friend_favor
on t1.friend_id=friend_favor.user_id
left join favor_info user_favor
on t1.user_id=user_favor.user_id
and friend_favor.sku_id=user_favor.sku_id
where user_favor.sku_id is null;

2.15 查询所有用户的连续登录两天及以上的日期区间

2.15.1 题目需求

从登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。期望结果如下：

user_id(用户id)	start_date(开始日期)	end_date(结束日期)
101	2021-09-27	2021-09-30
102	2021-10-01	2021-10-02
106	2021-10-04	2021-10-05
107	2021-10-05	2021-10-06

2.15.2 代码实现

hive>

select user_id,
       min(login_date) start_date,
       max(login_date) end_date
from (
         select user_id,
                login_date,
                date_sub(login_date, rn) flag
         from (
                  select user_id,
                         login_date,
                         row_number() over (partition by user_id order by login_date) rn
                  from (
                           select user_id,
                                  date_format(login_ts, 'yyyy-MM-dd') login_date
                           from user_login_detail
                           group by user_id, date_format(login_ts, 'yyyy-MM-dd')
                       ) t1
              ) t2
     ) t3
group by user_id, flag
having count(*) >= 2;
骚戴解法
SELECT 
    user_id ,
    min(date_format(login_ts,'yyyy-MM-dd')) start_date,
    max(date_format(login_ts,'yyyy-MM-dd')) end_date
FROM (
SELECT 
    user_id ,
    login_ts ,
    row_number() over(PARTITION by user_id order by login_ts) rn,
    date_sub(login_ts,row_number() over(PARTITION by user_id order by login_ts)) flag
FROM user_login_detail
)t 
GROUP BY user_id ,flag
HAVING count(flag) >1

骚戴理解：这个题目在消化了第二题后，就可以做出来了，突然就不难了哈哈，以下是需要用到的表

2.16 男性和女性每日的购物总金额统计

2.16.1 题目需求

从订单信息表（order_info）和用户信息表（user_info）中，分别统计每天男性和女性用户的订单总金额，如果当天男性或者女性没有购物，则统计结果为0。期望结果如下：

2.16.2 代码实现

hive>

select create_date,
       sum(if(gender = '男', total_amount, 0)) total_amount_male,
       sum(if(gender = '女', total_amount, 0)) total_amount_female
from order_info oi
         left join
     user_info ui
     on oi.user_id = ui.user_id
group by create_date;

骚戴理解：这题目不难，我没有摆脱惯性思维，没想到用sum(if(gender = '男', total_amount, 0))，理一下思路，首先左连接--->根据下单日期分组--->通过if函数判断是男是女--->sum来统计总数

所需要的两个表：order_info表和user_info表

*2.17 订单金额趋势分析

2.17.1 题目需求

查询截止每天的最近3天内的订单金额总和以及订单金额日平均值，保留两位小数，四舍五入。期望结果如下：

2.17.2 代码实现

hive>

select create_date,
       round(sum(total_amount_by_day) over (order by create_date rows between 2 preceding and current row ),2) total_3d,
       round(avg(total_amount_by_day) over (order by create_date rows between 2 preceding and current row ), 2) avg_3d
from (
         select create_date,
                sum(total_amount) total_amount_by_day
         from order_info
         group by create_date
     ) t1;

骚戴理解：这里我理解的近三天是连续的三天，上面的hql是有问题的，它只能统计所有数据都是连续的情况，例如第一条是2020-10-08，他就不是连续的，那么统计的2021-09-27的总数就应该是29000，但它把2020-10-08的数据也加进来了，这是有问题的，平均值也有问题，所以2021-09-27和2021-09-28的总数和平均值都是错的，但是这样的通过行来求近三天内的思路可以学习一下，order by create_date rows between 2 preceding and current row表示按create_date排序，并且获取排序后的前两行到当前行的数据，通过这个来实现“最近3天内”

2.18 购买过商品1和商品2但是没有购买商品3的顾客

2.18.1 题目需求

从订单明细表(order_detail)中查询出所有购买过商品1和商品2，但是没有购买过商品3的用户，期望结果如下：

user_id

103

105

2.18.2 代码实现

hive>

select user_id
from (
         select user_id,
                collect_set(sku_id) skus
         from order_detail od
                  left join
              order_info oi
              on od.order_id = oi.order_id
         group by user_id
     ) t1
where array_contains(skus, '1')
  and array_contains(skus, '2')
  and !array_contains(skus, '3');

骚戴理解：理清思路，把两个表left join并且按用户id分组--->每个用户下单的商品id收集成一个集合--->通过array_contains来判断是否包含对应的商品id

这里两个亮点：collect_set函数的作用是收集并形成set集合，结果去重，这里用sku_id会有重复的，所以要去重，array_contains来判断集合里有没有这个元素，有就返回true

需要用到的表：order_detail表和order_info表

2.19 统计每日商品1和商品2销量的差值

2.19.1 题目需求

从订单明细表（order_detail）中统计每天商品1和商品2销量（件数）的差值（商品1销量-商品2销量），期望结果如下：

2.19.2 代码实现

hive>

select create_date,
       sum(if(sku_id = '1', sku_num, 0)) - sum(if(sku_id = '2', sku_num, 0)) diff
from order_detail
where sku_id in ('1', '2')
group by create_date;

骚戴理解：这个题目我一开始是这样写的，但是会报错!

所需要的表：order_detail表

2.20 查询出每个用户的最近三笔订单

2.20.1 题目需求

从订单信息表（order_info）中查询出每个用户的最近三笔订单，期望结果如下：

2.20.2 代码实现

hive>

select user_id,
       order_id,
       create_date
from (
         select user_id
              , order_id
              , create_date
              , row_number() over (partition by user_id order by create_date desc) rk
         from order_info
     ) t1
where rk <= 3;

骚戴理解：“最近三笔订单”是通过排名后求最大的日期的前三天，也就是通过row_number() over (partition by user_id order by create_date desc) rk 和where rk <= 3;来实现

需要用到的表：order_info表

2.21 查询每个用户登录日期的最大空档期

2.21.1 题目需求

从登录明细表（user_login_detail）中查询每个用户两个登录日期（以login_ts为准）之间的最大的空档期。统计最大空档期时，用户最后一次登录至今的空档也要考虑在内，假设今天为2021-10-10。期望结果如下：

2.21.2 代码实现

hive>

select
    user_id,
    max(diff) max_diff
from
(
    select
        user_id,
        datediff(next_login_date,login_date) diff
    from
    (
        select
            user_id,
            login_date,
            lead(login_date,1,'2021-10-10') over(partition by user_id order by login_date) next_login_date
        from
        (
            select
                user_id,
                date_format(login_ts,'yyyy-MM-dd') login_date
            from user_login_detail
            group by user_id,date_format(login_ts,'yyyy-MM-dd')
        )t1
    )t2
)t3
group by user_id;

骚戴理解：这个题目有点难度，先理清思路，首先对日期进行格式化并且以用户id和格式化后的日期进行分组--->通过lead（） over（）函数来查询下一个登录日期--->通过datediff函数求差值--->通过max取最大值

group by user_id,date_format(login_ts,'yyyy-MM-dd')这里分组是因为要去重，因为同一天内同一个用户可能登录多次

lead(login_date,1,'2021-10-10') over(partition by user_id order by login_date) 是核心，用来实现求下一次的日期，这个语句要从后往前看，先看over(partition by user_id order by login_date) 这个无非就是分组加排序，并不陌生，值得一提的是这个partition by有时候可以不写！然后再看lead(login_date,1,'2021-10-10')的意思是求login_date的下一次的日期，如果没有下一次就用2021-10-10来作为下一次日期，例如下面红色画出来的部分

这个题目我一开始是这样写的

我一开始想用date_sub函数来求两个日期只差，但是报错

可以看出这个date_sub函数不能用于string类型的日期，当然它这个日期设置为string也不合理，至于这各报错为什么显示的是date_add函数我也不知道，然后我使用datediff函数就可以了

我一开始没有写最后的这个group by user_id，然后报错如下

这里报语法错误是因为select后面用到了max函数，所以必须分组！而且从语义来讲也要分组，因为会有重复的，如下所示，可以看到101有三条重复的1，所以需要分组！

需要用到的表:user_login_detail表

大数据技术之Hive SQL题库-中级4

2.13 即时订单比例

2.13.1 题目需求

2.13.2 代码实现

*2.14 向用户推荐朋友收藏的商品

2.14.1 题目需求

2.14.2 代码实现

2.15 查询所有用户的连续登录两天及以上的日期区间

2.15.1 题目需求

2.15.2 代码实现

2.16 男性和女性每日的购物总金额统计

2.16.1 题目需求

2.16.2 代码实现

*2.17 订单金额趋势分析

2.17.1 题目需求

2.17.2 代码实现

2.18 购买过商品1和商品2但是没有购买商品3的顾客

2.18.1 题目需求

2.18.2 代码实现

2.19 统计每日商品1和商品2销量的差值

2.19.1 题目需求

2.19.2 代码实现

2.20 查询出每个用户的最近三笔订单

2.20.1 题目需求

2.20.2 代码实现

2.21 查询每个用户登录日期的最大空档期

2.21.1 题目需求

2.21.2 代码实现

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

大数据技术之Hive SQL题库-中级4

2.13 即时订单比例

2.13.1 题目需求

2.13.2 代码实现

*2.14 向用户推荐朋友收藏的商品

2.14.1 题目需求

2.14.2 代码实现

2.15 查询所有用户的连续登录两天及以上的日期区间

2.15.1 题目需求

2.15.2 代码实现

2.16 男性和女性每日的购物总金额统计

2.16.1 题目需求

2.16.2 代码实现

*2.17 订单金额趋势分析

2.17.1 题目需求

2.17.2 代码实现

2.18 购买过商品1和商品2但是没有购买商品3的顾客

2.18.1 题目需求

2.18.2 代码实现

2.19 统计每日商品1和商品2销量的差值

2.19.1 题目需求

2.19.2 代码实现

2.20 查询出每个用户的最近三笔订单

2.20.1 题目需求

2.20.2 代码实现

2.21 查询每个用户登录日期的最大空档期

2.21.1 题目需求

2.21.2 代码实现

热门文章

最新文章

相关课程

相关电子书

相关实验场景