SQL案例练习（1）：店铺经营分析

案例来自书籍《SQL面试宝典：图解数据库求职题》
【数据源】

1. 【整体分析】店铺数、商品种类数、总销售额、总购买人数等

【sql思路】

店铺数、商品种类数：去重计数count(distinct(字段))
总销售额：单价*购买人数，/除以10000，用round保留2位小数，concat连接单位。总购买人数同理。

-- 整体分析，螺狮粉店铺数、总销售额、商品种类数、总购买人数等
select count(distinct 店铺名称) as '店铺数',
        count(distinct 商品名称) as '商品种类数',
        concat(round(sum(价格*购买人数)/10000,2),"万元") as '总销售额',
        concat(round(sum(购买人数)/10000,2),"万人") as '总购买人数'
from sheet1;

【结论】

1.市场饱和度高：店铺数共计1426个，市场竞争较激烈。但每个店平均种类数:4157/4126=2.92，单店商品品种数不多，可能有增加商品多样性的空间。
2.消费者选择较多样：种类共计4157种。
3.市场潜力大：销售额28891.5万，市场潜力巨大。但这个数据是累计销售额，需明确统计时间进一步确认市场潜力。
4.客群规模大：总购买人数为686.51万人，市场可能有进一步扩大客群的空间。
5.消费者购买力一般：人均消费42.08元。

2. 【店铺偏好】销售额排名top10的店铺，并明确其购买人数、销售额

【sql思路】

（1）分组统计排序：先用对店铺名称分组，求出每个店铺的购买人数、统计销售额，并对销售额降序排序
（2）排名top10：limit 10返回前10行

select 店铺名称,
        sum(购买人数) as '购买人数',
        concat(round(sum(价格*购买人数)/10000,2),"万元") as '销售额'
from sheet1
group by 店铺名称
order by sum(价格*购买人数) desc
limit 10;

【结论】

市场集中头部品牌：第一二名销售额均超kw，与第三名差距较大。消费者偏好头部品牌，头部与中腰部品牌差距明显，腰部品牌差别不大。

3. 【商品偏好】购买人数排名top10的商品，并明确购买人数

【sql思路】

参考问题2

select 商品名称,
        sum(购买人数) as '购买人数'
from sheet1
group by 商品名称
order by sum(购买人数) desc
limit 10;

【结论】

得出最受欢迎商品。top1商品购买人数超65w人，远超top2商品购买人数。第4个商品开始，购买人数差距不大。

【疑问】

为什么top1商品购买人数远超其他商品？因为有价格优势？
增加价格字段，继续下钻分析。可以看出top1商品与其他商品价格相当，不具价格优势。从购买人数以及品牌搜索指数上看，倾向是品牌效应带来的。

4. 【定价区间】商品价格区间分布，以及每个区间的占比

粗略看了数据，大致可以划分成0-50、51-100、100-150、150以上

【sql思路】

（1）确认每个价格的区间分布，用case when子句
（2）子查询+group by统计每个区间数量。
（3）统计每个区间的数量占比，可以利用区间数量/原文档中的总行数，求原文档的总行数用子查询

select 价格区间,count(*) as '数量',
                concat(count(*)/(select count(*) from sheet1)*100,'100%') as '占比'
from (select 商品名称,(case when 价格 between 0 and 50 then '0-50元'
       when 价格 between 51 and 100 then '51-100元'
       when 价格 between 101 and 150 then '101-150元'
                         when 价格 >150 then '150元以上'
       else 'null'
       end) as '价格区间'
    from sheet1) a
group by 价格区间;

【结论】

定价在0-50的商品数量约占51%，定价在51-100的商品数量约占31%。定价在100以内的商品数量约占总数的82%。

5. 【地域偏好】购买人数最多的top10地域

【sql思路】

参考问题2、问题3

select 买家地址,
        sum(购买人数) as '购买人数'
from sheet1
group by 买家地址
order by 购买人数 desc
limit 10;

【结论】

购买人数最多的地域为广西柳州，人数超446w人。top2、top3为浙江嘉兴、上海，远高于其他省份。河南多地也出现在top10地域中。

【疑问】

为何浙江嘉兴、上海、河南等地购买螺蛳粉人数多？
猜想1：上海作为一线城市，对新鲜事物接受度高？但数据中非一线的嘉兴、河南等地销量也高，top10未见其他一线城市。
猜想2：袋装螺蛳粉作为宅经济的产物，在疫情期间深受消费者喜爱，需要考虑到样本取样时间，以上城市也许恰好风控较严格？
对样本数据中“地域”字段来源存疑。

总结与建议

以上数据，可以看到：
1.对于螺狮粉这一品类，商家竞争激烈（超1.4k个店铺），且用户偏爱头部品牌，消费额主要集中在头部（约占45%）。
建议：中腰部商家可通过扩大品牌营销力度，增加品牌影响力，以此增加头部店铺的销售占比。
2.单店商品品种数不多（约为3），可能有增加商品多样性的空间。
建议：商家可不断开发打造差异化产品，挖掘更多的市场机会。
3.定价集中于0-100元，定价可参考此区间，广告投放也可增加此区间商品的投放、折扣力度。
4.地域偏好上主要集中在螺狮粉原产地、浙江、上海等地。
建议：广告地域投放除了稳定偏好地域的投放外，也可尝试从这些地区周边城市入手，增加地域投放广告，扩大受众面。