目录
相关表结构：
1、order_info表
2、order_detail表
题目及思路解析：
第一题，查询各品类销售商品的种类数及销量最高的商品
第二题 查询用户的累计消费金额及VIP等级
第三题 查询首次下单后第二天连续下单的用户比率
总结归纳：
知识补充：
相关表结构：
1、order_info表
2、order_detail表
题目及思路解析：
第一题，查询各品类销售商品的种类数及销量最高的商品
题目需求：
从订单明细表(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;
abcdefgh2、取整函数： 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、最后的最后，可以百分比形式输出显示
执行结果：
总结归纳：
这三题主要是考察开窗函数的使用，比如sum()over()、rank()over()
第一和第三题是分组topN的问题，第二题是case when 匹配题
知识补充：
这里如果报这样的错误，原因是 你没有给子查询写别名，
记住，给每一个子查询都写上别名，才能避免出现一些问题，文章来源：https://www.toymoban.com/news/detail-857956.html
如：select * from （select * from table）a文章来源地址https://www.toymoban.com/news/detail-857956.html
到了这里，关于HQL,SQL刷题，尚硅谷（中级）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
