汽车销售数据分析

hadoop jar send_car.jar /input /output

hdfs dfs -tail /output/part-r-00000

create external table send_car(province string,month int,city string,town string,year int,cartype string,manufacturer string,brand string,mold string,ownership string,nature string,number int,enginetype string,caroutput int,power double,fueltype string,carlength int,carwidth int,carheight int,xlength int,xwidth int,xheight int,count int,base int,front int,norm string,tnumber int,totalkg int,curbkg int,hcurbkg int,passenger string,zcurb int,underfirm string,underbrand string,undertype string,fcompany  string,carname string,age int,sex string)row format delimited fields terminated by ',';

create external table send_car(province string, month int,city string, town string, year int,cartype string,manufacturer string,brand string, mold string,ownership string,nature string, number int,enginetype string,caroutput int,power double, fueltype string,carlength int,carwidth int,carheight int,xlength int,xwidth int,xheight int,count int,base int,front int,norm string,tnumber int,totalkg int,curbkg int,hcurbkg int,passenger string,zcurb int,underfirm string,underbrand string,undertype  string,fcompany string,name string,age int,sex string)row format delimited fields terminated by ',';

province string, —省份
month int, —月
city string, —市
county string, —区县— town
year int, —年
cartype string,—车辆型号
productor string,—制造商 — manufacturer
brand string, —品牌
mold string,—车辆类型
owner string,—所有权 —-ownership string,
nature string, —使用性质
number int,—数量
ftype string,—发动机型号 —enginetype string,
outv int,—排量 —-caroutput int,
power double, —功率
fuel string,—燃料种类 —-fueltype string,
length int,—车长 —-carlength int,
width int,—车宽—-carwidth int,
height int,—车高——carheight int,
xlength int,—厢长
xwidth int,—厢宽
xheight int,—厢高
count int,—轴数
base int,—轴距
front int,—前轮距
norm string,—轮胎规格
tnumber int,—轮胎数
total int,—总质量——totalkg int,
curb int,—整备质量——curbkg int,
hcurb int,—核定载质量—-hcurbkg int,
passenger string,—核定载客
zhcurb int,—准牵引质量—-zcurb int,
business string,—底盘企业—-underfirm string,
dtype string,—底盘品牌—underbrand string,
fmold string,—底盘型号—undertype string,
fbusiness string,—发动机企业—fcompany string,
name string,—车辆名称
age int,—年龄
sex string —性别

load data inpath  '/output/part-r-00000' into table send_car;

  select * from send_car;

select nature,count(number) as number from send_car group by nature;

select nature,province,city,town,count(number) as number from send_car group by nature,province,city,town;

select month,round(summon/sumcount,2)as per 
      from (select month,count(number) as summon from send_car where
       month is not null group by month) as a,
      (select count(number) as sumcount from send_car) as b;

set hive.strict.checks.cartesian.product; 
# 首先查看hive.strict.checks.cartesian.product
set hive.strict.checks.cartesian.product=false;  
# 设置hive.strict.checks.cartesian.product为false

select city,town,round(sumtown/sumcount,2)as per 
      from (select month,city,town,count(number) as sumtown from send_car where town is not null and month=4 group by month,city,town) as a,
      (select month,count(number) as sumcount from send_car where month=4 group by month) as b;

select sex,round((sumsex/sumcount),2) as sexper 
from (select sex,count(number) as sumsex from send_car where sex!='' group by sex ) as a, (select count(number) as sumcount
from send_car where sex !='') as b;

![AFS4S0WZ%2J0}SVMVCY1ZP.png

 select ownership,cartype,mold,count(number) as number from send_car group by ownership,cartype,mold;

select mold,age,sex,count(number) as number from send_car group by mold,age,sex;

select mold,month,count(number) as number from send_car group by mold,month;

select brand,mold,collect_set(enginetype),collect_set(fueltype) from send_car where brand is not null and brand != '' and mold is not null and mold != ''  group by brand,mold;

select brand,year,month,count(number) as number from send_car where brand="五菱"  group by year,month,brand;