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

2.4.2 代码实现

hive>

select user_id,
       create_date,
       sum_so_far,
       case
           when sum_so_far >= 100000 then '钻石会员'
           when sum_so_far >= 80000 then '白金会员'
           when sum_so_far >= 50000 then '黄金会员'
           when sum_so_far >= 30000 then '白银会员'
           when sum_so_far >= 10000 then '青铜会员'
           when sum_so_far >= 0 then '普通会员'
           end vip_level
from (
         select user_id,
                create_date,
                sum(total_amount_per_day) over (partition by user_id order by create_date) sum_so_far
         from (
                  select user_id,
                         create_date,
                         sum(total_amount) total_amount_per_day
                  from order_info
                  group by user_id, create_date
              ) t1
     ) t2;

骚戴理解：

1. 这个题目我知道用case-when-then-end，但是我一开始是想这么写，结果报错！这个题目的解法是从最大的判断when sum_so_far >= 100000开始写，这里要结合题意，很巧妙，然后还要注意这里when-then一条后面是没有逗号的！
   

2.这里我没有用到第二层select子查询，所以我查询出来的是每个用户每一天的消费金额，就是单独的一天的消费额度，我没有看懂题目意思。重大理解下面的这个语句：

sum(total_amount_per_day) over (partition by user_id order by create_date) sum_so_far这个语句就是用来实现“统计每个用户截止其每个下单日期的累积消费金额”这个效果，这句的意思就是以user_id分组，并且通过create_date排序，然后求和每一天的消费金额，也就是把当前的消费金额加上之前的所有天的消费金额的总金额就是总消费金额！

2.5 查询首次下单后第二天连续下单的用户比率

2.5.1 题目需求

从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示，期望结果如下：

2.5.2 代码实现

hive>

select concat(round(sum(if(datediff(buy_date_second, buy_date_first) = 1, 1, 0)) / count(*) * 100, 1), '%') percentage
from (
         select user_id,
                min(create_date) buy_date_first,
                max(create_date) buy_date_second
         from (
                  select user_id,
                         create_date,
                         rank() over (partition by user_id order by create_date) rk
                  from (
                           select user_id,
                                  create_date
                           from order_info
                           group by user_id, create_date
                       ) t1
              ) t2
         where rk <= 2
         group by user_id
     ) t3;

骚戴理解：

最里面的select是通过user_id, create_date分组来确定唯一的一条用户下单的数据

“首次下单后第二天仍然下单”是通过 rank() over (partition by user_id order by create_date) rk和where rk <= 2来实现的，也就是先按create_date升序排名，然后再筛选出前面两天的日期，也就是第一次下单和第二次下单的时间，用户后面判断是否是连续的两天

这里巧妙的用最大值max(create_date) buy_date_second和最小值min(create_date) buy_date_first来锁定死首次下单的日期和第二次下单的日期，这一步骤是因为后面的datediff函数要用到！

一开始我不能理解为什么第二个select要用group by user_id分组，因为我一开始把group by user_id当做第三个select的条件了，所以一直没有能够理解。这里如果不分组就会报错！这个group by user_id是为了把筛选出来的两条下单记录给并列在一起！

concat(round(sum(if(datediff(buy_date_second, buy_date_first) = 1, 1, 0)) / count(*) * 100, 1), '%')是这个Hql里的灵魂所在，datediff函数就是求两个日期相差多少天，下面的语句表示相差一天，datediff(buy_date_second, buy_date_first) = 1，也就是首次下单和第二次下单是连续的，“首次下单后第二天仍然下单”的实现。if是判断如果首次下单和第二次下单是连续的，那就返回1，否则返回0，然后通过sum对所有if的返回值求和，这样得到的就是首次下单和第二次下单是连续的用户数量，sum求和后除以总数count(*) 再乘100得到的就是所占比，这里的count（*）没有去重是因为在第二个select里就通过group by user_id达到了去重的效果。round函数是四舍五入，round函数的第二个参数为1是保留一位小数，最后用concat函数拼接一个%得到一个百分数！

2.6 每个商品销售首年的年份、销售数量和销售金额

2.6.1 题目需求

从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。

期望结果如下：

2.6.2 代码实现

hive>

select sku_id,
       year(create_date) year,
       sum(sku_num) order_num,
       sum(price*sku_num) order_amount
from (
         select order_id,
                sku_id,
                price,
                sku_num,
                create_date,
                rank() over (partition by sku_id order by year(create_date)) rk
         from order_detail
     ) t1
where rk = 1
group by sku_id,year(create_date);

骚戴理解：这个题目不是很难，rank() over (partition by sku_id order by year(create_date)) rk这个排名不是第一次见了，但是order by year(create_date)这里的用到了year函数是第一见，我没想到这两个可以用在一起，所以这里就是通过这语句和where rk = 1来实现“首年”这个效果，然后要注意这里的分组是group by sku_id,year(create_date)，注意这里不能用别名，也就是group by sku_id,year；这会报错！ 这里的sum(price*sku_num)我一开始是想直接写成order_num*price，但是会报错，因为分组里没有price这个字段！

2.7 筛选去年总销量小于100的商品

2.7.1 题目需求

从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量，假设今天的日期是2022-01-10，不考虑上架时间小于一个月（30天）的商品，期望结果如下：

2.7.2 代码实现

hive>

select t1.sku_id,
       name,
       order_num
from (
         select sku_id,
                sum(sku_num) order_num
         from order_detail
         where year(create_date) = '2021'
           and sku_id in (
             select sku_id
             from sku_info
             where datediff('2022-01-10', from_date) > 30
         )
         group by sku_id
         having sum(sku_num) < 100
     ) t1
         left join
     sku_info t2
     on t1.sku_id = t2.sku_id;

骚戴理解：

1. 这里要用到sku_info表和order_detail表
   
2. “假设今天的日期是2022-01-10，不考虑上架时间小于一个月的商品”是通过下面语句实现的
   

where datediff('2022-01-10', from_date) > 30

3.“筛选出去年总销量小于100的商品及其销量”这里我一开始想错了，我以为是要实现2022-01-10的前一年，也就是2021-01-10到2022-01-10的总销量小于100的商品及其销量，其实只要通过year(create_date) = '2021'来实现就好了

2.8 查询每日新用户数

2.8.1 题目需求

从用户登录明细表（user_login_detail）中查询每天的新增用户数，若一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户。期望结果如下：

2.8.2 代码实现

hive>

select
    login_date_first,
    count(*) user_count
from
(
    select
        user_id,
        min(date_format(login_ts,'yyyy-MM-dd')) login_date_first
    from user_login_detail
    group by user_id
)t1
group by login_date_first;

骚戴理解：这个题目的思路很简单，先求出每个用户的首次登录的时间，然后在以这个时间分组统计用户的数量就是那一天的新增用户数量，实现“一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户”，这里要注意对日期进行格式化！因为数据里的日期是2021-09-21 08:00:00这样的格式，为什么要格式化呢？下面就是没有格式化跑出来的结果，可以看到红色画出来的部分应该是合在一起的，但是由于时间不一样，即使日期一样那么在分组的时候也会被分为两组！！！

2.9 统计每个商品的销量最高的日期

2.9.1 题目需求

从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期。期望结果如下：

2.9.2 代码实现

hive>

select sku_id,
       create_date,
       sum_num
from (
         select sku_id,
                create_date,
                sum_num,
                row_number() over (partition by sku_id order by sum_num desc,create_date asc) rn
         from (
                  select sku_id,
                         create_date,
                         sum(sku_num) sum_num
                  from order_detail
                  group by sku_id, create_date
              ) t1
     ) t2
where rn = 1;

骚戴理解：

这里“如果有同一商品多日销量并列的情况，取其中的最小日期“实现是通过下面

row_number() over (partition by sku_id order by sum_num desc,create_date asc) rn

巧妙的实现的，更准确的说是通过order by sum_num desc,create_date asc实现的！

这里我一开始是用的rank() over()函数，结果跟row_number() over()函数是一样的，这两个的区别就在于rank() over()函数会跳，row_number() over()函数不会跳，不懂就看下面的文章！

3. 需要用到的表：order_detail表
   

2.10 查询销售件数高于品类平均数的商品

2.10.1 题目需求

从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品，期望结果如下：

2.10.2 代码实现

hive>

select sku_id,
       name,
       sum_num,
       cate_avg_num
from (
         select od.sku_id,
                category_id,
                name,
                sum_num,
                avg(sum_num) over (partition by category_id) cate_avg_num
         from (
                  select sku_id,
                         sum(sku_num) sum_num
                  from order_detail
                  group by sku_id
              ) od
              left join sku_info sku 
              on od.sku_id = sku.sku_id) t1
where sum_num > cate_avg_num;

骚戴理解：

这里我之前一直想给查询的数据排序，比如按sku_id排序，但是一直都没用，到现在才找到原因，那就是因为sku_id是string类型，所以对于字符串来说1是最小的，有时候才会出现1,10,2,3这样的排序结果

解题思路：在order_detail表中求每个商品的下单总数--->把上一步的结果表和sku_info表做left join连接，求出分类的平均值--->筛选出累积销售件数高于其所属品类平均数的商品

解题核心：avg(sum_num) over (partition by category_id) cate_avg_num语句和来where sum_num > cate_avg_num语句来实现“查询累积销售件数高于其所属品类平均数的商品"，这里我一开始是想通过传统的group by来实现分组，后面发现这样写就只能查询分类id，不能查询sku_id，所以以后遇到这样的情况要学会考虑用窗口函数！！！

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

2.11 用户注册、登录、下单综合统计

2.11.1 题目需求

从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。期望结果如下：

2.11.2 代码实现

hive>

select login.user_id,
       register_date,
       total_login_count,
       login_count_2021,
       order_count_2021,
       order_amount_2021
from (
         select user_id,
                min(date_format(login_ts, 'yyyy-MM-dd')) register_date,
                count(1) total_login_count,
                count(if(year(login_ts) = '2021', 1, null)) login_count_2021
         from user_login_detail
         group by user_id
     ) login
         join
     (
         select user_id,
                count(order_id) order_count_2021,
                sum(total_amount) order_amount_2021
         from order_info
         where year(create_date) = '2021'
         group by user_id
     ) oi
     on login.user_id = oi.user_id;

骚戴理解：

count(1) 和count（*）是一个效果，不过count（1）更好一点

count(if(year(login_ts) = '2021', 1, null)) 语句很妙，我想的是传统的where方法来实现

min(date_format(login_ts, 'yyyy-MM-dd'))这里记得格式化

这里两个结果表是通过join来连接，我一开始想的是left join

所需要的表：order_info表和user_login_detail表

2.12 查询指定日期的全部商品价格

2.12.1 题目需求

从商品价格修改明细表（sku_price_modify_detail）中查询2021-10-01的全部商品的价格，假设所有商品初始价格默认都是99。期望结果如下：

2.12.2 代码实现

hive>

select sku_info.sku_id,
       nvl(new_price, 99) price
from sku_info
         left join
     (
         select sku_id,
                new_price
         from (
                  select sku_id,
                         new_price,
                         change_date,
                         row_number() over (partition by sku_id order by change_date desc) rn
                  from sku_price_modify_detail
                  where change_date <= '2021-10-01'
              ) t1
         where rn = 1
     ) t2
     on sku_info.sku_id = t2.sku_id;

骚戴理解：

1.这里首先要看懂题目的意思，sku_price_modify_detail表记录的就是商品价格变化的记录，那一个商品可能会价格变了很多次，所以要求出小于等于2021-10-01的所有日期里面的最大值，因为这个值是最新的价格值，当然还有可能价格没有改过，一直都是99，所以这个表里没有记录

2.“求出小于等于2021-10-01的所有日期里面的最大值”我一开始是想着分组后求最大值，这样的话select就只能查询sku_id，能用窗口函数解决优先使用窗口函数！

3.“如果价格没有变，那么返回初始值99”是通过nvl(new_price, 99) 和left join来实现的，这个函数的意思是如果new_price为null，那么返回99，如果不为null，返回new_price本身的值

4. 所需要的表：sku_price_modify_detail表和sku_info表