Hadoop大数据平台构建与应用(工作手册式)（微课版） 案例 4.3 餐饮数据统计分析

Hive数据仓库餐饮数据统计分析【任务描述】都市生活紧张忙碌，不少上班族已经习惯于在餐饮外卖平台上订餐，外卖平台的菜品种类丰富，提供各式风味的美食。但是即便如此，由于个体的口味偏好及菜品质量的差异，上班族们常常有不知道今天午餐应该吃什么的烦恼。W餐饮外卖平台，向广大用户提供网上订餐服务，其市场占有量在近年不断增加。当用户在W平台订餐完成后，平台会引导用户对于品尝过的菜品进行评价打分，最高为5分，最低为1分。为提高用户的购买体验，平台需要对用户的在平台上的打分数据、以及平台的相关菜品数据进行统计分析，在后续的菜品推送时能有所依据。本小节通过平台收集的用户评分数据，分析外卖平台的受欢迎度、客户的体验度。【任务分析】本小节将从如下的分析操作，分析外卖平台的受欢迎度、客户的体验度。根据用户评分数据统计日销量和日用户量。统计同时有评分和评分内容的记录。分析用户的评分分布情况。统计10大热销菜品。统计10大评分为5的热销菜品。统计单日评分超过2次的用户数。找出评分次数超过两次的用户中，每个用户评分最高的记录。【任务实施】数据说明因为业务数据的安全原因，用户评分数据集的数据已做了脱敏处理，只保留部分重要属性，其各属性及说明如REF_Ref492644507\h表41所示。表STYLEREF1\s4SEQ表\*ARABIC\s11用户评分数据属性及其说明属性名称属性说明UserID用户IDMealID菜品IDRating评分ReviewTime评分的时间戳Review评价内容另外，在网站的后台数据库（MySQL）中保存着菜品的数据集，其数据内容如REF_Ref492644556\h图41所示，导出后形成Parquet文件。图STYLEREF1\s4SEQ图\*ARABIC\s11菜品数据集在用户评分数据集中，保存了用户对菜品的评分信息，很大程度上反映了用户对菜品的兴趣度，因此这份数据将作为统计分析的主要数据源。创建数据表并导入数据将用户评分数据realrating.parquet和菜品数据meal_list.txt上传至Linux系统的/opt目录下，启动Hadoop集群、MySQL服务、MetaStore服务，打开HiveCLI，在命令行窗口进行菜品数据表meal_list和用户评分数据表meal_rating的创建并导入相应数据，如REF_Ref99465396\h代码41所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s11创建数据表并导入数据createdatabasemeal;usemeal;--创建菜单表createtablemeal_list(idint,MealIdstring,mealnamestring)rowformatdelimitedfieldsterminatedby',';--导入数据loaddatalocalinpath'/opt/meal_list.txt'overwriteintotablemeal_list;--创建评分表createtablemeal_rating(useridstring,mealidstring,ratingdouble,reviewstring,reviewtimestring)storedasparquet;--导入数据loaddatalocalinpath'/opt/mealrating.parquet'overwriteintotablemeal_rating;导入数据后，可使用select语句查询前5行数据，检验一下，查询结果如REF_Ref119337386\h图42所示。图STYLEREF1\s4SEQ图\*ARABIC\s12查询数据表前5行数据统计分析任务实现根据用户评分数据，使用日期函数from_unixtime与cast实现日期格式的转换，在通过聚合函数count，结合分组关键字groupby实现日销量和日用户量的统计，最后使用排序关键字orderby对日期进行升序输出，如REF_Ref99465389\h代码42所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s12统计日销量和日用户量selectymd,count(1)asdaycount,count(distinctuserid)asusercountfrom(select*,from_unixtime(cast(reviewtimeasbigint),'yyyy-MM-dd')asymdfrommeal_rating)tmpgroupbyymdorderbyymd;部分运行结果如REF_Ref119414698\h图43所示，其中菜品日销量比日用户量普遍都高，意味着存在有部分用户在一天内购买同一种菜品的现象。图STYLEREF1\s4SEQ图\*ARABIC\s13日销量和日用户量部分统计结果通过使用聚合函数count，结合条件筛选关键字where，统计同时有评分和评分内容的记录，如REF_Ref99465370\h代码43所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s13查询同时有评分和评分内容的记录selectcount(*)frommeal_ratingwherereviewisnotnullandratingisnotnull;统计结果如REF_Ref119414708\h图44所示，使用聚合函数count统计用户评分数据表meal_rating的总数据量为38383，意味着用户评分数据表中的所有记录均有评分与评分内容。图STYLEREF1\s4SEQ图\*ARABIC\s14查询同时有评分和评分内容的记录使用聚合函数对用户评分数据表分析用户的评分分布情况，如REF_Ref118451989\h代码44所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s14创建手游被关注信息表selectrating,count(1),round(count(1)/38383*100,2)asusercountfrommeal_ratinggroupbyrating;selectmax(rating)asmaxrating,min(rating)asminrating,round(avg(rating),3)asavgrating,round(stddev(rating),3)asstdratingfrommeal_rating;由REF_Ref119414717\h图45可知，54.77%的用户在评分时都会打5分，其中打3分的用户超过90%；由REF_Ref119414731\h图46可知，用户评分的最大值为5分，最小值为1分，平均值为4.19分，标准差为1.118分，说明了用户评分的数值数据较为集中，结合平均值为4.19分，可以推断，评分数值数据较多为4、5分，即由REF_Ref119414717\h图45所展示的情况。图STYLEREF1\s4SEQ图\*ARABIC\s15用户评分分布情况1图STYLEREF1\s4SEQ图\*ARABIC\s16用户评分分布情况2使用分组关键字groupby对菜品ID进行分组，结合聚合函数count统计记录数，然后使用排序关键字orderby降序输出10条数据，实现统计10大热销菜品，如REF_Ref118451994\h代码45所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s15统计10大热销菜品1selectmealid,count(1)asmealcountfrommeal_ratinggroupbymealidorderbymealcountdesclimit10;运行结果如REF_Ref119414742\h图47所示，第1列数据为菜品ID，第2列数据即为销售数据。图STYLEREF1\s4SEQ图\*ARABIC\s1710大热销菜品统计情况1基于REF_Ref118451994\h代码45的分析思路，使用联合关键字join，连接菜品数据表meal_list，输出10大热销菜品与其对应菜品名称，如REF_Ref118452000\h代码46所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s16统计10大热销菜品2selecta.mealid,a.mealcount,b.mealnamefrom(selectmealid,count(1)asmealcountfrommeal_ratinggroupbymealidorderbymealcountdesclimit10)ajoinmeal_listbona.mealid=b.mealid;运行结果如REF_Ref119414821\h图48所示，第1列数据为菜品ID，第2列数据为销售数据，第3列数据为对应的菜品名称。图STYLEREF1\s4SEQ图\*ARABIC\s1810大热销菜品统计情况2参考REF_Ref118452000\h代码46的分析思路，实现统计10大评分为5的热销菜品，如REF_Ref99465360\h代码47所示，运行结果如REF_Ref99465259\h图49所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s17统计10大评分为5的热销菜品selecta.mealid,a.mealcount,b.mealnamefrom(selectmealid,count(1)asmealcountfrommeal_ratingwhererating=5groupbymealidorderbymealcountdesclimit10)ajoinmeal_listbona.mealid=b.mealid;图STYLEREF1\s4SEQ图\*ARABIC\s1910大评分为5的热销菜品统计情况为提高查询效率，创建一张视图meal_rating_ymd，内容为用户评分数据表的所有数据与进行了格式转换的日期；对创建好的视图使用聚合函数count，结合条件筛选关键字having，实现统计单日评分超过2次的用户数，如REF_Ref99465350\h代码48所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s18统计单日评分超过2次的用户数createviewmeal_rating_ymdasselect*,from_unixtime(cast(reviewtimeasbigint),'yyyy-MM-dd')asymdfrommeal_rating;selectcount(distinctuserid)from(selectymd,userid,count(1)asratingcountfrommeal_rating_ymdgroupbyymd,useridhavingratingcount>2)tmp;运行结果如REF_Ref99465270\h图410所示，总计2231名用户单日评分超过两次，结合用户评分数据表的中数据量38383，单日评分超过两次的用户所占比例约为5.81%，数据较小，说明很少有用户在一天内评分两次。图STYLEREF1\s4SEQ图\*ARABIC\s110统计单日评分超过2次的用户数情况使用聚合函数count统计记录数，分析函数rank对通过over()子句划分的分区进行数据排名，实现统计查询每个用户评分最高的记录，如REF_Ref99465291\h代码49所示，运行结果如REF_Ref99465270\h图410所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s19统计每个用户评分最高的记录select*,count(1)over(partitionbyuserid)asratingcount,rank()over(partitionbyuseridorderbyratingdesc)asratingrankfrommeal_ratinglimit20;图STYLEREF1\s4SEQ图\*ARABIC\s111每个用户评分最高的记录统计情况基于REF_Ref99465291\h代码49的分析思路，使用条件筛选关键字where找出评分次数超过两次的用户中，每个用户评分最高的记录，如REF_Ref99465318\h代码410所示，部分运行结果如REF_Ref118473636\h图412所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s110在评分次数超过两次的用户中每个用户评分最高的记录select*from(select*,count(1)over(partitionbyuserid)asratingcount,rank()over(partitionbyuseridorderbyratingdesc)asratingrankfrommeal_rating)tmpwhereratingcount>2andratingrank=1;图STYLEREF1\s4SEQ图\*ARABIC\s112在评分次数超过两次的用户中每个用户评分最高的记录基于REF_Ref99465318\h代码410的分析思路，找出评分次数超过两次的用户中，每个用户评分最高、时间最近的一条记录，如REF_Ref118477741\h代码411所示，部分运行结果如REF_Ref119415135\h图413所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s111在评分次数超过两次的用户中每个用户评分最高、时间最近的一条记录select*from(select*,count(1)over(partitionbyuserid)asratingcount,row_number()over(partitionbyuseridorderbyratingdesc,cast(reviewtimeasbigint)desc)asratingrankfrommeal_rating)tmpwhereratingcount>2andratingrank=1;图STYLEREF1\s4SEQ图\*ARABIC\s113在评分次数超过两次的用户中每个用户评分最高、时间最近的一条记录若是想保存REF_Ref118477741\h代码411执行后的数据，可以基于REF_Ref118477741\h代码411，先创建一张视图tmp，存储数据，这样可以有效地提高查询数据的效率；然后使用insertoverwrite命令将视图tmp的所有数据导出至Linux本地目录/opt/userrating下，如REF_Ref118477734\h代码412所示。代码STYLEREF1\s4SEQ代码\*ARABIC\s112UseCountReducer.javacreateviewtmpasselect*from(select*,count(1)over(partitionbyuserid)asratingcount,row_number()over(partitionby