查询累积销量排名第二的商品
2.1.1 题目需求
查询订单明细表(order_detail)中销量(下单件数)排名第二的商品id,如果不存在返回null,如果存在多个排名第二的商品则需要全部返回。期望结果如下:
1、先查询表order_detail如图
2、分析题目并写代码
--选择sku_id和sku_num,并对sku_num进行sum()求和,又名为total_num
select sku_id,
sum(sku_num) total_num
from order_detail od
group by sku_id
结果展示
--将以上代码包成t1,并对t1按照total_num进行排序。
(怎么排???order by?然后怎么选出第二名???两个第一or第二名怎么办???会不会只有第一名,没有第二名???)——>别担心,排名还得看 rank !!!
据我观察,电商经常允许并列第一的存在,因此直接考虑开窗dense_rank()!!!此排名方法直接顺序显示一列序号以代表名次。
select sku_id
from (select *,
dense_rank() over (order by total_num desc ) `排名`
from (
select sku_id,
sum(sku_num) total_num
from order_detail od
group by sku_id
) t1
)
结果展示
--将以上包成t2,我们只需要通过where筛选出排名是第二的商品就可以啦~~~
select sku_id
from (select *,
dense_rank() over (order by total_num desc ) `排名`
from (
select sku_id,
sum(sku_num) total_num
from order_detail od
group by sku_id
) t1
) t2
where `排名` =2
结果展示
(等等,题目中要求不存在第二名返回null???什么意思???怎么会不存在???)
注意!!!如果代码中改成
where `排名`=210
,则结果中什么都不会显示!!!
(我们写的代码要求普适性,很多场景中可能只销售了一个产品,因此只有第一没有第二;或者说要求查询一个不存在的排名,例如第210名,压根不存在,因此以上情况均要返回null。)
(所以我们还差这一步,该怎么解决呢???这里就不得不提一下"select 1"以及"right join"的妙用↓↓↓)
--select 1:就是生成一张仅有一列、一个值且值为1的临时表。关键字:一列,一值,临时
select 1; --写成2就是值为2,写成3就是值为3......
结果展示
(所以有什么用???别急,再看right join)
-- right join 不加连接条件on:就是笛卡尔积的一种形式,作用就是两表的值一一对应连接(回去翻阅高等线代)。在此题目中就是为查询出的排名,在最后追加一列1(select 1的结果)
(不懂!!!那我来给你演示!——例如我们表t2查询的结果是销量大于等于2的产品的排名,此时我们right join一个select 1,那就是在每个销量排名的最后一列添加一个1,时刻注意,这一列是临时的哦~)
由此可以直观的演示笛卡尔积中right join的效果。
如果我们仅展示sku_id和临时列,如下图:
如果我们仅展示排名第二的sku_id和临时列,如下图:
如果我们仅展示排名第210的sku_id和临时列,如下图:(名次不存在哦~)
此时我们发现最后依旧会外挂一个1,对比之前没有right join的结果,我们可以认为这套组合拳的目的是,在没有查询结果的时候返回一个null。
组合拳"right join" + "select 1" 打出了一个临时列!!!
这个临时列,就是为了保护这个null而存在!!!
尚硅谷2023年大数据新学员,新手程序猴,文中如有用词不当,请务必将就!!!