SQL案例练习（4）：产品评价分析

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

【背景】

餐饮集团在线上渠道投放获取客流，定期对平台上的评价与留言分析。目的是了解消费者满意度与产品运营情况，及时调整优化门店运营。

【数据源】

表1：评价表
评价星级得分在0~5之间。

表2：全国门店分布表

【明确问题】

为了掌握清楚整体、局部门店情况，需要从全局与数据对比两个维度拆解。

1.全局维度

（1）全部门店数量/评价数量、好评率、平均星级
评价星级<=3，定义为差评；评价星级>3，定义为好评。
好评率=好评数量/评价总数量
（2）找出满意度top10和bottom 10的门店
本书作者提供了一个新指标，消费者满意度：
每条评价包括字数、星级等维度，评价星级越高，评价字数越多，说明消费者满意度越高。
星级越低，评价字数越多，说明消费者满意度越低。
故给每条评价设置“消费者满意度”指标，消费者满意度=评价字数x评价星级x星级系数
星级系数设定：[0,2)为-2，[2,3)为-1，[3,4)为1，[4,5]为2
——可能存在刷好评、恶意差评导致数据严重失真

PS：3个关于评论的指标，好评率、消费者满意度、平均星级。
好评率：单看好评占比，好评率=好评数量/评价总数量。用于快速评估服务质量。
平均星级：仅看评价星级，平均星级=avg(平均星级)。反映消费者对服务的整体满意程度。
消费者满意度：先对每条评论打满意度（综合评价字数、评价星级、星级系数），消费者满意度=评价字数x评价星级x星级系数，再对门店每条评价的满意度取平均。考虑了消费者的评级，还考虑了评论的详细程度和可能的星级加权，这有助于更全面地理解消费者的体验和感受。

2.对比维度

（1）平台对比：两个平台的门店/评价数量、平均星级的对比
（2）城市对比：不同城市的门店/评价数量、平均星级的对比
（3）门店对比：核心城市每日评价数量对比

【分析过程】

1.全局维度

（1）全部门店数量/评价数量，好评率

评价星级<=3，定义为差评；评价星级>3，定义为好评。
好评率=好评数量/评价总数量

select count(distinct 门店名称) as 门店数量,
        count(id) as 评价数量,
        concat((select count(id) from 评价表 where 评价星级>3)/count(id)*100,'%') as 好评率
from 评价表

【结论】

总共有65家门店，累计2359条评论，好评率为97.67%。

（2）消费者满意度排行，找出满意度均值top10、bottom 10的门店

消费者满意度=评价数量评价星级星级系数
星级系数设定：[0,2)为-2，[2,3)为-1，[3,4)为1，[4,5]为2

select a.ID,a.门店名称,a.评价字数,a.平台,a.评价时间,a.评价星级,b.星级系数,
        a.评价字数*a.评价星级*b.星级系数 as 消费者满意度
from 评价表 a join (select ID,(case when 评价星级<2 then '-2'
                        when 评价星级>= 2 and 评价星级< 3 then '-1'
                        when 评价星级>= 3 and 评价星级< 4 then '1'
                        else '2' end) as 星级系数
from 评价表) b on a.ID=b.ID

创建新视图“消费者满意度表”，便于后续调用

create view 消费者满意度表 as (select a.ID,a.门店名称,a.评价字数,a.平台,a.评价时间,a.评价星级,b.星级系数,
        a.评价字数*a.评价星级*b.星级系数 as 消费者满意度
from 评价表 a join (select ID,(case when 评价星级<2 then '-2'
                        when 评价星级>= 2 and 评价星级< 3 then '-1'
                        when 评价星级>= 3 and 评价星级< 4 then '1'
                        else '2' end) as 星级系数
from 评价表) b on a.ID=b.ID)

消费者满意度均值top10的门店

select 门店名称,round(avg(消费者满意度),0) as 平均满意度
from 消费者满意度表
group by 门店名称
order by avg(消费者满意度) desc
limit 10

消费者满意度均值bottom 10的门店

select 门店名称,round(avg(消费者满意度),0) as 平均满意度
from 消费者满意度表
group by 门店名称
order by avg(消费者满意度) asc
limit 10

【结论】

满意度均值较高的店铺可以总结分享经验。
满意度均值垫底的店铺需进一步核实原因，整改工作。其中NK店远远落后于其他门店；JJ店和YY店因没有评价，满意度为0分。

2.对比维度

（1）平台对比：两个平台的门店/评价数量、好评率、平均满意度的对比

select 平台,
        count(distinct 门店名称) as 门店数量,
        count(ID) as 评论数量,
        sum(case when 评价星级 >3 then 1 else 0 end) as 好评数量,
        round(sum(case when 评价星级 >3 then 1 else 0 end)/count(ID),2) as 好评率,
        round(avg(评价星级),2) as 平均星级,
        round(avg(消费者满意度),0) as 平均满意度
from 消费者满意度表
group by 平台

【结论】

可以看到用户更倾向在大众点评上使用，评论数量达2065条，远超美团。
且大众点评的好评率、平均星级、平均满意度均略高于美团。

（2）城市对比：不同城市的门店/评价数量、平均星级的对比

select b.城市,
        count(distinct a.门店名称) as 门店数量,
        count(a.ID) as 评论数量,
        sum(case when a.评价星级 >3 then 1 else 0 end) as 好评数量,
        round(sum(case when a.评价星级 >3 then 1 else 0 end)/count(ID),2) as 好评率,
        round(avg(评价星级),2) as 平均星级,
        round(avg(a.消费者满意度),0) as 平均满意度
from 消费者满意度表 a join 全国店铺分布 b on a.门店名称=b.门店名称  
group by b.城市
order by 门店数量 desc

从上可以看出，门店超过2家的城市有8个，将数据复制到excel中，将数据可视化。

【结论】

1.品牌在郑州、北京、无锡、上海的门店较多。但郑州跟无锡的评价数量低于预期，不如北京、上海。
2.平均星级评价上看，各城市平均星级在4.5以上。目前北京、无锡、上海、南京、洛阳等城市居于整体平均星级线以下。
3.好评率上看，目前洛阳、无锡、西安、北京、郑州居于整体好评率之下。

（3）门店对比：核心城市每日评价数量对比

与业务沟通后，确认北京、郑州、上海、南京为重点关注城市。

select b.城市,
        a.评价日期,
        count(a.ID) as 评论数量
from 评价表 a join 全国店铺分布 b on a.门店名称=b.门店名称
where 城市 in ('北京','郑州','上海','南京')
group by b.城市,a.评价日期
order by b.城市,a.评价日期 asc

【结论】

上海、南京评价数量走势较为平稳，上海11日之后骤升。北京、郑州走势波动较大，且走势相似。北京、郑州、南京在11号之后有较明显的回落。

【总结与建议】

1.可以看到用户更倾向在大众点评上使用，评论数量达2065条，远超美团。且大众点评的好评率、平均星级、平均满意度均略高于美团。
建议：可适当减少在美团上的邀约，将投放重心让大众点评倾斜。
2.好评率为97.67%，洛阳、无锡、西安、北京、郑州城市的好评率低于整体水平。
建议：需重点关注，挖掘存在的问题，及时调整优化。
3.关注消费者满意度均值bottom10的门店，其中NK店远远落后于其他门店；JJ店和YY店因没有评价，满意度为0分。
建议：有必要进行实地调研，并做针对性的指导培训。
4.品牌在郑州、北京、无锡、上海的门店较多。但郑州跟无锡的评价数量低于预期，不如北京、上海。
建议：北京、上海等城市总结归纳经验并做分享；郑州、无锡等地建议采取开展营销活动，鼓励用户餐后评价。
5.各城市平均星级在4.5以上。目前北京、无锡、上海、南京、洛阳等城市居于整体平均星级线以下。
建议：梳理北京、无锡、上海、南京等地星级评价低于4.5分的评价内容，梳理现存问题并改进。