店铺排名问题，如何解决？

猴子数据分析

发布于 2023-09-06 11:15:47

1670

发布于 2023-09-06 11:15:47

文章被收录于专栏：猴子数据分析

【面试题】

有两张表，“产品表”包含2个字段：产品、产品线。

“订单表”包含7个字段：订单id、顾客id、交易日期、产品、店铺、件数、金额。

问题：查询产品线（category_3）销量排名第二的店铺。

【解题步骤】

1. 解题思路

问题要分析某个产品线的销量情况。能计算“销量”的字段“件数”在“订单表”里，“产品线”在“产品表”里。

因此，涉及到两个表，要想到《猴子从零学会SQL》里讲过的用“多表查询”

为了保留“订单表”里的全部数据，以“订单表”为左表（记为子查询a1），进行左联结。

“产品表”为右表（记为子查询a2）。

关联条件是字段“产品”。

select 字段
from 
a1
left join 
a2 
on a1.产品 = a2.产品

将子查询a1、a2代入：

select a1.*,a2.产品线
from 
订单表 as a1
left join 
产品表 as a2 
on a1.产品 = a2.产品;

查询结果：

将以上SQL记为子查询a3。

3. 汇总分析

问题是查询产品线（category_3）销量排名第二的店铺，所以：

1）只需要关注产品线为category_3的订单；

2）按店铺分类汇总，以获得产品线（category_3）在各店铺的销量；

3）销量为“件数”求和，因为每一单不一定只有1件产品。

select a3.店铺,
       sum(件数) as category_3销量
from a3
where a3.产品线 = 'category_3'
group by a3.店铺;

将子查询a3代入：

select a3.店铺,
       sum(件数) as category_3销量
from (
select a1.*,a2.产品线
from 
订单表 as a1
left join 
产品表 as a2 
on a1.产品 = a2.产品
) as a3
where a3.产品线 = 'category_3'
group by a3.店铺;

将以上SQL记为子查询a4。

4. 窗口函数

查询排名一般使用窗口函数，常见的关于排名的窗口函数有三种：

防止有并列排名的情况，我们选择dense_rank()。

select *,dense_rank() over(order by category_3销量 desc) as 排名
from a4;

将子查询a4代入：

select *,dense_rank() over(order by a4.category_3销量 desc) as 排名
from (
select a3.店铺,
       sum(件数) as category_3销量
from (
select a1.*,a2.产品线
from 
订单表 as a1
left join 
产品表 as a2 
on a1.产品 = a2.产品
) as a3
where a3.产品线 = 'category_3'
group by a3.店铺
) as a4;

将以上SQL记为子查询a5。

最后，筛选出排名为2的店铺：

select 店铺
from a5
where 排名 = 2;

将子查询a5代入：

select a5.店铺
from (
select *,dense_rank() over(order by a4.category_3销量 desc) as 排名
from (
select a3.店铺,
       sum(件数) as category_3销量
from (
select a1.*,a2.产品线
from 
订单表 as a1
left join 
产品表 as a2 
on a1.产品 = a2.产品
) as a3
where a3.产品线 = 'category_3'
group by a3.店铺
) as a4
) as a5
where a5.排名 = 2;