HQL,SQL刷题，尚硅谷（中级）

相关表结构：

1、order_info表

2、order_detail表

题目及思路解析：

第一题，查询各品类销售商品的种类数及销量最高的商品

第二题查询用户的累计消费金额及VIP等级

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

总结归纳：

知识补充：

题目及思路解析：

第一题，查询各品类销售商品的种类数及销量最高的商品

题目需求：

从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，期望结果如下：

思路解析：

这道题是典型的分组套环题（或者说是经典的分组top N）

求的是各品类中商品累积销量最好的，”商品种类“，即在每个分类里面的商品数量

这里累积销量比较难求，逻辑上，我们先求累积量，再求商品种类数

代码实现：

select
    t1.category_id,
    category_name,
    sku_id,
    name,
    order_num,
    order_cnt
from (
    select
        category_id,
        si.sku_id,
        name,
        order_num,
        rank() over (partition by category_id order by order_num desc ) rk,
        count(distinct  od.sku_id) over(partition by category_id) order_cnt
    from (
         select
            sku_id,
            sum(sku_num) order_num
         from order_detail
         group by sku_id )od
    left join sku_info si on si.sku_id=od.sku_id  )t1
left join category_info ci  on ci.category_id=t1.category_id
where rk=1;

代码解析：

具体思路：分开求解，多层嵌套

首先第一层查询，从order_detail表中求出各个商品的销售量

接着第二层查询，进行分组排序，先连接sku_info表，使用rank()函数降序排序

然后使用count()函数开窗，求出商品的品类数

最后最后一层查询，连接category_info表，并根据条件筛选出销量最高的商品

执行结果：

补充说明：

1、我们求的是在分类里面的商品数排序(根据分类id进行排序)，但order_detail没有，

因此在第二步时候，应当先连接skuin_fo

2、category_info表可以在前面第二层查询时候就连接

3、这里求商品种类数，这个有两种方法，一个是开窗，另一个是子查询

注意：这个窗口范围就是在每一个分区里面即可，因此可以不用些范围

----------------------------------------------------------------------------------------------

第二题查询用户的累计消费金额及VIP等级

题目需求：

从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。

用户vip等级根据累积消费金额计算，计算规则如下：

设累积消费总额为X，

若0=<X<10000,则vip等级为普通会员

若10000<=X<30000,则vip等级为青铜会员

若30000<=X<50000,则vip等级为白银会员

若50000<=X<80000,则vip为黄金会员

若80000<=X<100000,则vip等级为白金会员

若X>=100000,则vip等级为钻石会员

期望结果如下：

思路解析：

这题倒是不怎么难，根据题目需求，我们需要的是截止每个日期的累积消费金额

这里自然是用开窗函数最适合，然后需要计算金额，那么使用sum()开窗即可，之后的vip等级可以用case when

代码实现：

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;

代码解析：

首先我们需要求出每个用户每天的消费总金额

之后我们进行开窗，范围是第一行到当前行

最后用case when 进行等级匹配划分

注意：

1、由于可能存在同一用户，同一天有多个消费订单，因此我们需要先求出每个用户每天的消费总金额，再开窗

2、开窗范围是每个用户分区，因此可以不用指定范围

执行结果：

----------------------------------------------------------------------------------------------

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

题目需求：

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

思路解析：

首次、第二天，意味着我们需要获取到每个用户的首次下单的日期，与第二天下单的日期

这是时候用排序开窗函数就比较合适，其实这又是一个典型的分组topN的问题，至于那个函数都可以，这里就选择rank

代码实现：

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;

注意：

1、字符串连接函数：concat

语法： concat(string A, string B…)

返回值： string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

hive> select concat(‘abc’,'def’,'gh’) from iteblog;
      abcdefgh

2、取整函数： round

语法： round(double a)

返回值： BIGINT

说明：返回double类型的整数值部分（遵循四舍五入）

hive> select round(3.1415926) from iteblog;
3
hive> select round(3.5) from iteblog;
4
hive> create table iteblog as select round(9542.158) from iteblog;
hive> describe iteblog;
_c0     bigint

代码解析：

1、首先，我们需要获取每个用户及其下单日期，注意这里需要去重，但不需要分组，因为只是简单的查询获取也没用函数

2、接着进行分组tpoN开窗操作，注意后面筛选条件，获取是排名前两名的，应取'<=2'（若'=2'取等于，取的是第二名的)

3、然后将首次与第二天(若存在)的两个日期以min()与max()方式取出（这里情况比较特殊，可以不用开窗），注意需要分组，因为需要的是每个用户的首次与第二天(若存在)的两个日期

4、最后用sum(if())判断，将数据差值为1的保持不变，而非1的置为0，否则需要先获取差值为1的，然后求和,即可获取相应条件的人数，最后除以总人数，即为比率

5、最后的最后，可以百分比形式输出显示

执行结果：