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