牛客网SQL大厂真题二刷小白白话总结（五）某宝店铺分析（电商模式）

目录

SQL25 某宝店铺的SPU数量（简单）

SQL26 某宝店铺的实际销售额与客单价（简单）

SQL27 某宝店铺折扣率（中等）

SQL28 某宝店铺动销率与售罄率（较难）

SQL29 某宝店铺连续2天及以上购物的用户及其对应的天数（较难）

---

SQL25 某宝店铺的SPU数量（简单）

select style_id,count(item_id) as SPU_num
from product_tb
group by style_id
order by SPU_num desc

很简单，略

SQL26 某宝店铺的实际销售额与客单价（简单）

select sum(sales_price) as sales_total,
round(sum(sales_price)/count(distinct user_id),2) as per_trans
from sales_tb

略

SQL27 某宝店铺折扣率（中等）

select round(sum(s.sales_price)/sum(s.sales_num*p.tag_price)*100,2) as "discount_rate(%)"
from sales_tb as s
left join product_tb as p
using(item_id)

这道题虽说是中等，但也比较简单，值得注意的就是sales_price并不是每个商品的出售单价，而是结算总金额，即可能是多个加总在一起的价格，因此直接相加就是所有商品卖了多少钱；相比之下，不同商品的sales_num和tag_price相乘再加总才是标签价格之和。

SQL28 某宝店铺动销率与售罄率（较难）

select style_id,
round(sum(sales_num)/(sum(inventory)-sum(sales_num))*100,2) as pin_rate,
round(sum(sales_price)/sum(tag_price*inventory)*100,2) as "sell-through_rate"
from
product_tb t1
left join
(select item_id,sum(sales_num) as sales_num,
sum(sales_price) as sales_price
from
sales_tb
group by item_id) t2
on t1.item_id=t2.item_id
group by style_id
order by style_id

这一题我一开始报错是因为想的太简单了，我直接把两个表连接，然后group by style_id，

select style_id,

       round(100*sum(sales_num)/ (sum(inventory)-sum(sales_num)),2) as 'pin_rate(%)',

       round(100*sum(sales_price)/ sum(inventory*tag_price),2) as 'sell-through_rate(%)'

from product_tb t1

join sales_tb t2

on t1.item_id=t2.item_id

group by style_id

order by style_id

但是这一题两个表中的信息只有第一个表有style_id，第二个表同一个style下的item还可能会重复出现，直接做连接做计算会导致有重复购买记录的商品的inventory多次计算，计算结果会变小，因为每个style_id下有多个item_id的购买纪录，比如一个item买了两次，那么连接表里面的inventory也就重复了两次，加总起来就多了，因此考虑单独把第二个表用group by item_id处理之后再和第一个表进行连接，这样的话，就保证每个style下的item_id只出现一次，那么存货也就只计算一次。

一定要考虑清楚出售纪录和存货纪录之间的关系，并不是一一对应，尤其是出售纪录，每种产品可能会重复出现，这种情况下就要考虑是否需要局部使用group by.

SQL29 某宝店铺连续2天及以上购物的用户及其对应的天数（较难）

select user_id,count(*) days_count
from
(select distinct user_id,sales_date,dense_rank() over(partition by user_id 
                                                     order by sales_date) rn
from sales_tb) a
group by user_id,date_add(sales_date,interval -rn day)
having days_count>=2
order by user_id

这一题就是经典题型——解决“连续两天”这个条件。

考虑“将编号和日期相减，如果连续的话，差值会相等”

这个技巧很好理解，因为如果按照日期来对购买纪录进行排序，那么日期和排序是同时由小变大的，只是变化的幅度可能不一样，对于排序来讲，DENSE_RANK() 排名会得到：1，1，2，3，4，所以相同日期的编号一样，但是编号变化值一直都是1，这就意味着，天数变化值也是1的时候，两者之差保持不变。

因此答案也就很好理解了，对每一个user的购买纪录进行时间排序，再针对时间和排序之差进行group by，并计算相同差值下的购买纪录条数：count(*)

再筛选出连续2天及以上的用户即可。