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

项目4Hive数据仓库

任务4.1

任务4.2

任务4.3餐饮数据统计分析

【任务描述】

都市生活紧张忙碌，不少上班族已经习惯于在餐饮外卖平台上订餐，外卖平台的菜品种

类丰富，提供各式风味的美食。但是即便如此，由于个体的口味偏好及菜品质量的差异，上

班族们常常有不知道今天午餐应该吃什么的烦恼。

w餐饮外卖平台，向广大用户提供网上订餐服务，其市场占有量在近年不断增加。当用

户在W平台订餐完成后，平台会引导用户对于品尝过的菜品进行评价打分，最高为5分，

最低为1分。

为提高用户的购买体验，平台需要对用户的在平台上的打分数据、以及平台的相关菜品

数据进行统计分析，在后续的菜品推送时能有所依据。本小节通过平台收集的用户评分数据,

分析外卖平台的受欢迎度、客户的体验度。

【任务分析】

本小节将从如下的分析操作，分析外卖平台的受欢迎度、客户的体验度。

（1）根据用户评分数据统计日销用和日用户量。

（2）统计同时有评分和评分内容的记录。

（3）分析用户的评分分布情况。

（4）统计10大热销菜品。

（5）统计10大评分为5的热销菜品。

（6）统计单口评分超过2次的用户数。

(7)找出评分次数超过两次的用户中，每个用户评分最富的记录。

【任务实施】

4.3.1数据说明

因为业务数据的安全原因,用户评分数据集的数据已做了脱敏处理，只保留部分重要属

性，其各属性及说明如表4・1所示。

表4-1用户评分数据属性及其说明

属性名称属性说明

UserID用户ID

MeallD菜品ID

Rating评分

ReviewTime评分的时间戳

Review评价内容

另外，在网站的后台数据库(MySQL)中保存着菜品的数据集，其数据内容如图4-1所

示，导出后形成Parquet文件。

mealnomeallDmeal_name

1BOOOHOOVBQ口权

2B000H0X79O套保鸡丁

3BOOOH29TXU水煮牛肉

4BOOOH2DMMF番前茄饼

5BOOOH4YNMO球械

6BOOOHAB4NK蛋包饭

7B000HKWE3O鱼香茄子

8B000HZEHL6塔尖大白菜

9BOOOI5PVD8酸溜土豆丝

10B000I5Q0ZG茄汁萌饼

图4-1菜品数据集

在用户评分数据集中，保存了用户对菜品的评分信息，很大程度上反映了用户对菜品的

兴趣度，因此这份数据将作为统计分析的主要数据源.

4.3.2创建数据表并导入数据

将用户评分数据realrating.parquet和菜品数据meal」ist.txt上传至Linux系统的/opt目录

下，启动Hadoop集群、MySQL服务、MelaSlore服务，打开HiveCLL在命令行窗口进行

菜品数据表mealjist和用户评分数据表meal.rating的创建并导入相应数据，如代码4-1所

zjso

代码4”创建数据表并导入数据

createdatabasemeal;

usemeal;

-创建菜单表

createtabicmcal」i$l(

idint,

Mealldstring,

mealnamestring)

rowformatdelimitedfieldstenninatedby

一导入数据

loaddatalocalinpath'/opt/meaLlist.txt'overuriteintotablemeal_list;

--创建评分表

createtablemeal_rating(

useridstring,

mealidstring,

ratingdouble,

reviewstring,

revicwtimestring)

storedasparquet;

一导入数据

loaddatalocalinpath'/opt/mealrating.parquet'overwriteintotablemealrating;

导入数据后，可使用select语句查询前5行数据，险验一下，查询结果如图4-2所示。

hive>select*frommeal_listlimit5;

OK

1B000H0OVBQ口水码

2B00OHOX79O宫保海丁

3B000H29TXU水煮牛肉

4B00OH2DMME香煎茄饼

5B000H4YNM0五彩相取

Timetaken:0.737seconds.Fetched:5row(s)

hive>select*frommealratinglimit5;

OK

A2W0H395IHGS0TB0940HNZTVI5.0风味独特，真的不错！1496177056

A^?KHSf)VNONOHRRfia674RT7S?A有特色,也比较T生14Qfi1771fiR

A1YQ4Z5U9NIGPB09CDBTQCW5.0家常美味，推荐！1496177276

A3E5V5TSTAY3R9B09751IYQ44.0好吃1496179256

A1V50CTTDJ73ZMB09C0OLT6S5.0不得不赘1496180009

Timetaken:0.486seconds.Fetched:5row(s)

图4-2查询数据表前5行数据

4.3.3统计分析任务实现

根据用户评分数据，使用日期函数fisLunix山nc与CHSI实现日期格式的转换，在通过

聚合函数count,结合分组关键字groupby实现日销量和日用户量的统计，最后使用排序关

键字orderby对日期进行升序输出，如代码4-2所示。

代码4-2统计日销量和日用户量

selectymd,count(1)asdaycount,count(distinctuserid)asusercount

from(select*,from_unixtimc(cas((rcview(imeasbiginl),'yyyy-MM-dd')asymdfrommeal_rating)imp

groupbyymd

orderbyymd;

部分运行结果如图4-3所示，其中菜品日销量比日用户量普遍都高，意味着存在有部分

用户在一天内购买同一种菜品的现象。

2017-96-15491371

2017-96-16630466

2017-96-17577427

2017-96-18514393

2017-06-19560431

2017-96-20515378

2017-96-21733497

2017-96-22648446

2617-96-23669460

2017-96-24459326

2017-96-25664480

2017-96-26598427

2017-96-27575418

2017-96-281023733

2017-96-29716502

2017-96-303633

Timetaken:79.159seconds.Fetched:62row(s)

图4-3日销量和日用户量部分统计结果

通过使用聚合函数count,结合条件筛选关键字where,统计同时有评分和评分内容的

记录，如代码4-3所示。

代码4-3查询同时有评分和评分内容的记录

selectcount(*)frommealratingwherereviewisnotnullandratingisnotnull;

统计结果如图4-4所示，使用聚合函数count统计用户评分数据表meaLrating的总数据

量为38383,意味着用户评分数据表中的所有记录均有评分与评分内容。

38383

Timetaken36.655seconds.Fetched^1row(s)

图4-4查询同时有评分和评分内容的记录

使用聚合函数对用户评分数据表分析用户的评分分布情况，如代码4-4所示。

代码4-4创建手游被关注信息表

selectrating,coun((1),round(count(1)/38383*100,2)asusercountfrommeal_ratinggroupbyrating;

selectmax(rating)asmaxrating.min(rating)asminrating.round(avg(niting).3)asavgrating,

round(stcldev(rating),3)asstdratingfromineal_rating;

由图4-5可知，54.77%的用户在评分时都会打5分，其中打3分的用户超过90%；由图

4-6可知，用户评分的最大值为5分，最小值为1分，平均值为4.19分，标准差为LI18分，

说明了用户评分的数值数据较为集中，结合平均值为4.19分，可以推断，评分数值数据较

多为4、5分，即由图4-5所展示的情况。

1.017824.64

2.019605.11

3.0447511.66

4.0914223.82

5.02102454.77

Timetaken:30.697seconds,Fetched:5row(s)

图4-5用户评分分布情况1

0K

5.01.04.191.118

JTimetaken:35.019seconds.Fetched:1row(s)

图4-6用户评分分布情况2

使用分组关键字groupby对菜品ID进行分组，结合聚合函数count统计记录数，然后

使用排序关键字orderby降序输出10条数据，实现统计10大热销菜品，如代码4-5所示。

代码4-5统计10大热销菜品1

selectnicalid,counl(l)asmealcounlfrommealratinggroupbymealidorderbymcalcounldesclimit10;

运行结果如图4-7所示，第1列数据为菜品ID,第2列数据即为销售数据。

B00I3MPDP4467

BO0DAHSVYC460

B00I3MMN4I432

B00APEO0H4398

BOOCDBTQCW371

BO0I3MNGCG353

B0OI3MNVBW335

B00B8P3O9K325

B00I3MMTS8320

BO0CDBR1P6301

Timetaken:66.485seconds,Fetched:10row(s)

图4-710大热销菜品统计情况1

基十代码4-5的分析思路，使用联合关键字join,连接菜品数据表meal」ist,输出10大

热销菜品与其对应菜品名称，如代码4-6所示。

代码4-6统计10大热销菜品2

sclecla.mealid,a.mcalcoun(,b.mcalnumefrom(selectmcalid,coun((l)asmealcounlfrommcal_raiinggroupby

mealidorderbymealcountdes:limit10)ajoinmealjistbona.mealid=b.mealid;

运行结果如图4-8所示，第1列数据为菜品ID,第2列数据为销售数据，第3列数据

为对应的菜品名称。

0K

B00I3MPDP4467蟹柳蔬菜沙拉

B00DAHSVYC460香菜陈皮鹤

B0OI3MMN4I432红酒炖牛猜

B00APEG0H4398红苴南瓜米糊

B00CDBTQCW371素鱼煽苦瓜

B0OI3MNGCG353洋葱炒鸡蛋

B00I3MNVBW335懒人版红烧肉

B0OB8P8O9K325冬瓜茶

B0OI3MNTS8320烤猪颈肉

B00CDBR1P6301金银瓜条

Tim©taken:115.285seconds,Fetched:10row(s)

图4-810大热销菜品统计情况2

参考代码4-6的分析思路，实现统计10大评分为5的热销菜品，如代码4-7所示，运

行结果如图4-9所示。

代码4-7统计10大评分为5的热销菜品

selecta.mealid,a.mealcount,b.niealnainefrom(selectmealid,count(l)asmealcountfrommeal_ratingwhere

rating=5groupbymealidorderbymealcountdesclimit10)ajoinmealjistbona.mealid=b.mealid;

OK

BO0APE00H4289红豆南瓜米糊

B00I3MPDP4253鬟柳蔬菜沙拉

B009FZF0N0217高汤

B006Z48TZS215手撕茄子

BO04MWZLYC193藐菜烤鱼

B00B8P8O9K190冬瓜茶

BO0DAHSVYC177香菜陈皮日鸟

B00DT0YIIE174肉燕

B005544TRQ167X0音拌荷兰豆

B00F87ZUYG167大杏仁桃麻

Timetaken:128.263seconds,Fetched:10row(s)

图4-910大评分为5的热销菜品统计情况

为提高查询效率，创建一张视图mcal-rating-ymd,内容为用户评分数据表的所有数据

与进行了格式转换的日期;对创建好的视图使用聚合函数count,结合条件筛选关键字having,

实现统计单日评分超过2次的用户数，如代码4-8所示。

代码4-8统计单日评分超过2次的用户数

createviewmeal_raiing_ymdasselect*,from_unixiime(casi(reviewiimeasbigini),'yyyy-MM-dd)asymdfrom

meal_raling;

selectcount(distinctuserid)from(selectymd.userid,count(I)asratngcountfromnieal_rating_ymdgroupby

ymd,useridhavingratingcount>2)tnip;

运行结果如图4-10所示，总计2231名用户单日评分超过两次，结合用户评分数据表的

中数据量38383,单日评分超过两次的用户所占比例约为5.81%,数据较小，说明很少有用

户在一天内评分两次。

OK

2231

Tim。taken:72.408seconds.Fetched:1row(s)

图4-10统计单日评分超过2次的用户数情况

使用聚合函数count统计记录数，分析函数rank对通过over。子句划分的分区进行数据

排名，实现统计查询每个用户评分最高的记录，如代码4-9所示，运行结果如图4-10所示。

代码4・9统计每个用户评分最高的记录

select*,coun((l)over(partitionbyuserid)asratingcount,rank()over(partitionbyuseridorderbyratingdesc)as

ratingrankfrommeal_ratinglimit20;

OK

A0705654XT5UCAYOY7THB00IC9XG9O5.0简直太费了149549280081

A0705654XT5UCAYOY7THB00F406S2U3.0有特色，卫生149549280082

A0705654XT5UCAYOY7THB00H7NDSPC2.0基本0K149£08480083

A0705654XT5UCAYOY7THB06B2G2RG62.0基本0K149549280083

A0705654XT5UCAYOY7THB00IKT36S61.0还算不错149549280085

A0705654XT5UCAYOY7THB00F4PKH5E1.0还算不错149817120085

A0705654XT5UCAYOY7THB00BLCHYKU1.0一般般吧149471520085

A0705654XT5UCAYOY7THB60APE06UA1.0还算不错149362720085

A099898949AFP0GMFDCBB001Y5913C5.0简直太赞了149609760071

A0998989>19AFP0GMFDCBB00CeOLT6S5.0太美味了，衽烈推荐11^19817120071

A099898949AFP0GMFDCBB003YUGS6S5.0简直太费了149506680071

A099898949AFPOGMFDCBB005749LXQ5.0太美味了，资烈推荐!149748600071

A099898949AFP0GMFDCBB00B2LCW8O5.0太美味了，强烈推荐！149817120071

A099898949AFPOGMFDCBB006NU7C485.0此味只应天上有！149708320071

A099898949AFP0GMFDCBB002QS5OQ41.0还算不错149627040077

A1004HZ4AR10UIBO05OHSOL65.0太美味了，强烈推荐！149523360061

A1004HZ4AR10UIB00BG2TX9A5.6太美味了，强烈推荐！149734240061

A1004HZ4AR10UIB00DBT2QZY5.0太美味了，强烈推荐！149523360061

A1004HZ4AR10UIB00EY8MCOO5.6太美味了，强烈推荐！149514720061

A1004HZ4AR10UIB00ESNDHQY5.0太美味了，强烈推荐！149514720061

Timetaken:79.212seconds.Fetched:2。row(s)

图4-11每个用户评分最高的记录统计情况

基于代码4-9的分析思路，使用条件筛选关键字where找出评分次数超过两次的用户

中，每个用户评分最高的记录，如代码4-10所示，部分运行结果如图4-12所示。

代码4-10在评分次数超过两次的用户中每个用户评分最高的记录

select*from(select*.count；1)over(partitionbyuserid)asratingcount,rank()overfpartitionbyuserid

orderbyratingdesc)asratingrankfrommeal_rating)tmpwhereratingcount>2andratingrank=l;

AZU6MIE3PA7MB005C4FD0Q5.0此味只应天上有！149863840051

AZV9BLVTYCRC1B00FDZ8S2O5.0太美味了，强烈推荐！149678880051

AZV9BLVTYCRC1B004U8LF7K5.0太美味了，强烈推荐！149678880051

AZV9BLVTYCRC1B0O574P75M5.0太美味了，强烈推荐！149471520051

AZV9BLVTYCRC1B00337ZGIS5.0筒亘太赘了149678880051

AZV9BLVTYCRC1B007427XS45.0太美味了，强烈推荐！149756640051

AZVLTNV02CNP7B00ATLJYL65.0简直太赞了149682400051

AZVLTNV02CNP7B00H7NDSPC5.0太美味了，强烈推荐！149817120051

AZVLTNV02CNP7B00I3MPDP45.0太美味了，强烈推荐！149523360051

AZVLTNV02CNP7B00APE1NZW5.0简直太赘了149682400051

AZWFKYXA6ZAV9B0091P6X2E5.0太美味了，强烈推荐！149812000061

AZWFKYXA6ZAV9B002NWNTLO5.0简直太提了149423200061

AZX26WCIS8CNEB00ICGZIKG5.0此味只应天上有！149436960071

AZX26WCI58CNEB0OJMJOWXG5.0太美味了，弓虽烈推荐！149851680071

AZX26WCIS8CNEB00IJNKQHM5.0太美味了，强烈推荐！149817120071

AZX26WCIS8CNEB003RRW3BC5.0太美味了，强烈推荐！149817120071

AZX8DJ3X10LD4BO08ZXSUO25.0太美味了，强烈推荐！149466400051

AZXS6P5QWNMLCB0O1EUKHRG5.0太美味了，强烈推荐！149509600051

AZXS6P5QWNMLCB000W4Z5ZO5.0太美味了，强烈推荐！149509600051

AZXS6P5QWNMLCB002QNBRYM5.0太美味了，强烈推荐！149509600051

AZXS6P5QWNMLCB002UXAEV05.0太美味了，强烈推荐！149466400051

AZXS6P5QWNMLCB0012H0DBG5.0太美味了，强烈推荐！149509600051

Timetaken:74.46seconds.Fetched:21989row(s)

图4-12在评分次数超过两次的用户中每个用户评分最高的记录

某于代码4-10的分析思路，找出评分次数超过两次的用户中，每个用户评分最高、时

间最近的一条记录，如代码4-11所示，部分运行结果如图4-13所示。

代码4-11在评分次数超过两次的用户中每个用户评分最高、时间最近的一条记录

select♦from(select*,count(1)over(partitionbyuserid)asratingcount.row_number()over(partitionby

useridorderbyratingdesc,cast(reviewtimeasbigint)desc)asratingrankfrommeal_rating)impwhere

ralingcount>2andratingrank=l;

5

AZJ6N3ZYS2CWQB00HD6RLBK太美味了，强烈推荐/p>

AZJDD7W9UUVG0B003NS007050此味只应天上有/p>

AZJOKE3Y0UCBCBO0H7NDSPC50太美味了，强烈推荐/p>

AZJRWV5IDX6BHB0O3N1DFUU50太美味了，强烈推荐！149812000051

AZLIQDH1JWCXNB00252MNV»Y50太美味了，强烈推荐！149544160051

AZP04WRQFEYN8B00JRSBG9U56太美味了，强烈推荐！149678880Q61

AZQJGDWARL3RRB0O337ZGIS50太美味了，强烈推荐！149670240061

AZQP4EIUUNEWDBO0ETRANIO50太美味了，强烈推荐/p>

AZR0M5TIZZW3WB0O4X2M3N850简直太赞了149742880061

AZRRMG7IIE3H1B0OD5P4GUC50简直太赞了149872480059

AZSZQXY81ZSM3BO0D6MQ6ZM50太美味了，强烈推荐/p>

AZT3AX3A4O809B000UU4IX050简直太赘了1498689600155

AZU0LWSMZTIISBO05PK5KPO50太美味了，强烈推荐/p>

AZU6MIE3PA7MB008BQG3RE50此味只应天上有/p>

AZV9BLVTYCRC1B0O7427XS450太美味了，强烈推荐/p>

AZVLTNV02CNP7B0GH7NDSPC50太美味了，强烈推荐/p>

AZWFKYXA6ZAV9B0O91P6X2E50太美味了，强烈推荐/p>

AZX26WCIS8CNEBO0JMJOWXG50太美味了，强烈推荐/p>

AZX8DJ3X10LD4BO08ZXSUO2太美味了，强烈推荐/p>

AZXS6P5QWNMLCB0012H0DBG太美味了，强烈推荐！1495096000

Timetaken:86.007seconds.5130row(s)

图4-13在评分次数超过两次的用户中每个用户评分最高、时间最近的一条记录

若是想保存代码4-11执行后的数据，可以基于代他4-11,先创建一张视图tmp,存储

数据，这样可以有效地提高查询数据的效率；然后使用insertoverwrite命令将视图tmp的所

有数据导出至Linux本地目录/opt/userrating下，如代码4-12所示。

代码4-12UseCountReducer.java

createviewtmpasselect*from(select*.count(1)over(partitionbyuserid)asratingcount.

row_number()