文章目录
1.1查询累积销量排名第二的商品
1.1.1 题目需求
查询订单明细表(order_detail)中销量(下单件数)排名第二的商品id,如果不存在返回null,如果存在多个排名第二的商品则需要全部返回。期望结果如下:
方案一:
select nvl(sku_id, null) as sku_id
from
(
select sku_id,num,dense_rank() over(order by num desc) as rank
from
(
select sku_id,sum(sku_num) as num
from order_detail
group by sku_id
) as t1 --求出每个id的销量数
) as t2
where rank = 2; --对销售进行降序排序,选择排序第二名的id
方案二:
select nvl(sku_id, null) as sku_id
from
(
select sku_id,sum(sku_num) as order_num,dense_rank() over(order by sum(sku_num) desc) as rank
from order_detail
group by sku_id
) as t1
where rank = 2; --对销售进行降序排序,选择排序第二名的id
1.2查询至少连续三天下单的用户
1.2.1 题目需求
查询订单信息表(order_info)中最少连续3天下单的用户id,期望结果如下:
判断是否连续,可以对日期进行排序,若日期连续,则日期-排名的数字是相同的否则是不同的
方案一:
select distinct user_id
from
(
select user_id,create_date-rank as group_num
from
(
select user_id,create_date,
row_number() over(partition by user_id order by create_date) as rank
from
(
select distinct user_id,create_date
from ordr_info
) as t1 --一个用户可能在同一天有多次下单,所以需要进行去重处理
) as t2
group by user_id, group_num
having count(*) >= 3