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

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

2023-06-12 265

版权

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

本文涉及的产品

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

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

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

2.34 销售订单首购和次购分析

2.34.1 题目需求

通过商品信息表（sku_info）订单信息表（order_info）订单明细表（order_detail）分析如果有一个用户成功下单两个及两个以上的购买成功的手机订单（购买商品为xiaomi 10，apple 12，小米13）那么输出这个用户的id及第一次成功购买手机的日期和第二次成功购买手机的日期，以及购买手机成功的次数。

结果如下：

2.34.2 代码实现

hive>

select
  distinct oi.user_id,
  first_value(od.create_date)over(partition by oi.user_id order by od.create_date rows between unbounded preceding and unbounded following ) first_date,
  last_value(od.create_date)over(partition by oi.user_id order by od.create_date rows between unbounded preceding and unbounded following ) last_date,
  count(*)over(partition by oi.user_id order by od.create_date rows between unbounded preceding and unbounded following) cn
from
   order_info oi
join
   order_detail od
on
  oi.order_id=od.order_id
join
  sku_info si
on
  od.sku_id=si.sku_id
where
  si.name in('xiaomi 10','apple 12','xiaomi 13')

2.35 同期商品售卖分析表

2.35.1 题目需求

从订单明细表（order_detail）中。

求出同一个商品在2021年和2022年中同一个月的售卖情况对比。

结果如下（截取部分）：

2.35.2 代码实现

hive>

select
  if(t1.sku_id is null,t2.sku_id,t1.sku_id),
  month(if(t1.ym is null,t2.ym,t1.ym)) ,
  if(t1.sku_sum is null ,0 ,t1.sku_sum) 2020_skusum,
  if(t2.sku_sum is null ,0 ,t2.sku_sum) 2020_skusum
from
  (
    select
      sku_id,
      concat(date_format(create_date,'yyyy-MM'),'-01') ym,
      sum(sku_num) sku_sum
    from
      order_detail
    where
      year(create_date)=2020
    group by
      sku_id,date_format(create_date,'yyyy-MM')
)t1
full join
  (
    select
      sku_id,
      concat(date_format(create_date,'yyyy-MM'),'-01')  ym,
      sum(sku_num) sku_sum
    from
      order_detail
    where
      year(create_date)=2021
    group by
      sku_id,date_format(create_date,'yyyy-MM')
)t2
on
  t1.sku_id=t2.sku_id and month(t1.ym) = month(t2.ym)

2.36 国庆期间每个品类的商品的收藏量和购买量

2.36.1 题目需求

从订单明细表（order_detail）和收藏信息表（favor_info）统计2021国庆期间，每个商品总收藏量和购买量

结果如下：

2.36.2 代码实现

hive>

select
  t1.sku_id,
  t1.sku_sum,
  t2.favor_cn
from
  (
    select
      sku_id,
      sum(sku_num) sku_sum
    from
      order_detail
    where
      create_date>='2021-10-01' and create_date<='2021-10-07'
    group by 
      sku_id
)t1
join
  (
    select
      sku_id,
      count(*) favor_cn
    from
      favor_info
    where
      create_date>='2021-10-01' and create_date<='2021-10-07'
    group by 
      sku_id
)t2
on 
  t1.sku_id=t2.sku_id

2.37 统计活跃间隔对用户分级结果

2.37.1 题目需求

用户等级：

忠实用户：近7天活跃且非新用户

新晋用户：近7天新增

沉睡用户：近7天未活跃但是在7天前活跃

流失用户：近30天未活跃但是在30天前活跃

假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数

结果如下：

Level（用户等级）	Cn（用户数量)
忠实用户	6
新增用户	3
沉睡用户	1

2.37.2 代码实现

hive>

select
  t2.level,
  count(*)
from
  (
    select
      uld.user_id,
      case
         when (date_format(max(uld.login_ts),'yyyy-MM-dd') <=date_sub(today, 30))
             then '流失用户'-- 最近登录时间三十天前
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') <=date_sub(today, 7) and date_format(max(uld.login_ts),'yyyy-MM-dd') >=date_sub(today, 7))
             then '忠实用户' -- 最早登陆时间是七天前,并且最近七天登录过
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') >=date_sub(today, 7))
             then '新增用户' -- 最早登录时间是七天内
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') <= date_sub(today, 7) and date_format(max(uld.login_ts),'yyyy-MM-dd') <= date_sub(today, 7))
             then '沉睡用户'-- 最早登陆时间是七天前,最大登录时间也是七天前
       end level
    from
      user_login_detail  uld
    join
      (
        select 
          date_format(max(login_ts),'yyyy-MM-dd') today 
        from
          user_login_detail
    )t1
    on
      1=1
    group by 
      uld.user_id,t1.today
    )t2
group by
  t2.level

2.38 连续签到领金币数

2.38.1 题目需求

用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。

每连续签到7天重新累积签到天数。

从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序

结果如下：

User_id（用户id）	Sum_coin_cn（金币总数)
101	7
109	3
107	3
102	3
106	2
104	2
103	2
1010	2
108	1
105	1

2.38.2 代码实现

hive>

-- 求连续并标志是连续的第几天
select
  t1.user_id,
  t1.login_date,
  date_sub(t1.login_date,t1.rk) login_date_rk,
  count(*)over(partition by t1.user_id, date_sub(t1.login_date,t1.rk) order by t1.login_date) counti_cn
from
  (
   select
     user_id,
     date_format(login_ts,'yyyy-MM-dd') login_date,
     rank()over(partition by user_id order by date_format(login_ts,'yyyy-MM-dd')) rk
   from
     user_login_detail
   group by
     user_id,date_format(login_ts,'yyyy-MM-dd')
)t1

--求出金币数量，以及签到奖励的金币数量
select
  t2.user_id,
  max(t2.counti_cn)+sum(if(t2.counti_cn%3=0,2,0))+sum(if(t2.counti_cn%7=0,6,0)) coin_cn
from
  (
select
  t1.user_id,
     t1.login_date,
     date_sub(t1.login_date,t1.rk) login_date_rk,
     count(*)over(partition by t1.user_id, date_sub(t1.login_date,t1.rk) order by t1.login_date) counti_cn
   from
     (
      select
        user_id,
        date_format(login_ts,'yyyy-MM-dd') login_date,
        rank()over(partition by user_id order by date_format(login_ts,'yyyy-MM-dd')) rk
      from
        user_login_detail
      group by
        user_id,date_format(login_ts,'yyyy-MM-dd')
   )t1
)t2
group by
  t2.user_id,t2.login_date_rk

-- 求出每个用户的金币总数
select
  t3.user_id,
  sum(t3.coin_cn) sum_coin_cn
from 
  (
    select
      t2.user_id,
      max(t2.counti_cn)+sum(if(t2.counti_cn%3=0,2,0))+sum(if(t2.counti_cn%7=0,6,0)) coin_cn
    from
      (
    select
      t1.user_id,
         t1.login_date,
         date_sub(t1.login_date,t1.rk) login_date_rk,
         count(*)over(partition by t1.user_id, date_sub(t1.login_date,t1.rk) order by t1.login_date) counti_cn
       from
         (
          select
            user_id,
            date_format(login_ts,'yyyy-MM-dd') login_date,
            rank()over(partition by user_id order by date_format(login_ts,'yyyy-MM-dd')) rk
          from
            user_login_detail
          group by
            user_id,date_format(login_ts,'yyyy-MM-dd')
       )t1
    )t2
    group by
      t2.user_id,t2.login_date_rk
    )t3
group by
  t3.user_id
order by
  sum_coin_cn desc

2.39 国庆期间的7日动销率和滞销率

2.39.1 题目需求

动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。

滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/ 已上架总商品数）。

只要当天任一店铺有任何商品的销量就输出该天的结果

从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天每天每个品类的商品的动销率和滞销率

结果如下（截取部分）：

Category_id （品类id）	1号（动销)	1号（滞销)	2号（动销)	2号（滞销)	3号（动销)	3号（滞销)
1	1.0	0.0	0.5	0.5	0.75	0.25
2	0.75	0.25	0.75	0.25	0.75	0.25
3	0.25	0.75	0.75	0.25	0.75	0.25

2.39.2 代码实现

hive>

-- 国庆每一天 每个商品品类有多少商品被销售了
select
  t1.category_id,
  sum(if(t1.create_date='2021-10-01',1,0)) `第1天`,
  sum(if(t1.create_date='2021-10-02',1,0)) `第2天`,
  sum(if(t1.create_date='2021-10-03',1,0)) `第3天`,
  sum(if(t1.create_date='2021-10-04',1,0)) `第4天`,
  sum(if(t1.create_date='2021-10-05',1,0)) `第5天`,
  sum(if(t1.create_date='2021-10-06',1,0)) `第6天`,
  sum(if(t1.create_date='2021-10-07',1,0)) `第7天`
from
  (
    select 
      distinct 
      si.category_id,
      od.create_date,
      si.name
    from 
      order_detail od
    join
      sku_info si
    on
      od.sku_id=si.sku_id
    where
      od.create_date>='2021-10-01' and od.create_date<='2021-10-07'
)t1
group by
  t1.category_id

-- 每一天的动销率 和 滞销率
select
  t2.category_id,
  t2.`第1天`/t3.cn,
  1-t2.`第1天`/t3.cn,
  t2.`第2天`/t3.cn,
  1-t2.`第2天`/t3.cn,
  t2.`第3天`/t3.cn,
  1-t2.`第3天`/t3.cn,
  t2.`第4天`/t3.cn,
  1-t2.`第4天`/t3.cn,
  t2.`第5天`/t3.cn,
  1-t2.`第5天`/t3.cn,
  t2.`第6天`/t3.cn,
  1-t2.`第6天`/t3.cn,
  t2.`第7天`/t3.cn,
  1-t2.`第7天`/t3.cn
from
  (
   select
     t1.category_id,
     sum(if(t1.create_date='2021-10-01',1,0)) `第1天`,
     sum(if(t1.create_date='2021-10-02',1,0)) `第2天`,
     sum(if(t1.create_date='2021-10-03',1,0)) `第3天`,
     sum(if(t1.create_date='2021-10-04',1,0)) `第4天`,
     sum(if(t1.create_date='2021-10-05',1,0)) `第5天`,
     sum(if(t1.create_date='2021-10-06',1,0)) `第6天`,
     sum(if(t1.create_date='2021-10-07',1,0)) `第7天`
   from
     (
       select 
         distinct 
         si.category_id,
         od.create_date,
         si.name
       from 
         order_detail od
       join
         sku_info si
       on
         od.sku_id=si.sku_id
       where
         od.create_date>='2021-10-01' and od.create_date<='2021-10-07'
   )t1
   group by
     t1.category_id
   )t2
join
  (
    select
      category_id,
      count(*) cn
    from
      sku_info
    group by
      category_id
    )t3
on 
  t2.category_id=t3.category_id

2.40 同时在线最多的人数

2.40.1 题目需求

根据用户登录明细表（user_login_detail），求出平台同时在线最多的人数。

结果如下：

Cn（人数）

2.40.2 代码实现

hive>

-- 登录标记1 下线标记-1
select
  login_ts l_time,
  1 flag
from
  user_login_detail
union
select
  logout_ts l_time,
  -1 flag
from
  user_login_detail

-- 按照时间求和
select
  sum(flag)over(order by t1.l_time) sum_l_time
from
  (
    select
      login_ts l_time,
      1 flag
    from
      user_login_detail
    union
    select
      logout_ts l_time,
      -1 flag
    from
      user_login_detail
)t1

-- 拿到最大值 就是同时在线最多人数
select
  max(sum_l_time)
from
  (
    select
      sum(flag)over(order by t1.l_time) sum_l_time
    from
      (
        select
          login_ts l_time,
          1 flag
        from
          user_login_detail
        union
        select
          logout_ts l_time,
          -1 flag
        from
          user_login_detail
    )t1 
)t2

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

2.34 销售订单首购和次购分析

2.34.1 题目需求

2.34.2 代码实现

2.35 同期商品售卖分析表

2.35.1 题目需求

2.35.2 代码实现

2.36 国庆期间每个品类的商品的收藏量和购买量

2.36.1 题目需求

2.36.2 代码实现

2.37 统计活跃间隔对用户分级结果

2.37.1 题目需求

2.37.2 代码实现

2.38 连续签到领金币数

2.38.1 题目需求

2.38.2 代码实现

2.39 国庆期间的7日动销率和滞销率

2.39.1 题目需求

2.39.2 代码实现

2.40 同时在线最多的人数

2.40.1 题目需求

2.40.2 代码实现

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

2.34 销售订单首购和次购分析

2.34.1 题目需求

2.34.2 代码实现

2.35 同期商品售卖分析表

2.35.1 题目需求

2.35.2 代码实现

2.36 国庆期间每个品类的商品的收藏量和购买量

2.36.1 题目需求

2.36.2 代码实现

2.37 统计活跃间隔对用户分级结果

2.37.1 题目需求

2.37.2 代码实现

2.38 连续签到领金币数

2.38.1 题目需求

2.38.2 代码实现

2.39 国庆期间的7日动销率和滞销率

2.39.1 题目需求

2.39.2 代码实现

2.40 同时在线最多的人数

2.40.1 题目需求

2.40.2 代码实现

热门文章

最新文章

相关课程

相关电子书

相关实验场景