Hive实例-汽车销售

声明

本数据来自网络，不代表任何意义。

数据样例

打开集群，进入hive,根据所有字段建表。

create external table cars(
province string, --省份
month int, --月
city string, --市 
county string, --区县
year int, --年
cartype string,--车辆型号
productor string,--制造商
brand string, --品牌
mold string,--车辆类型
owner string,--所有权
nature string, --使用性质
number int,--数量
ftype string,--发动机型号
outv int,--排量
power double, --功率
fuel string,--燃料种类
length int,--车长
width int,--车宽
height int,--车高
xlength int,--厢长
xwidth int,--厢宽
xheight int,--厢高
count int,--轴数
base int,--轴距
front int,--前轮距
norm string,--轮胎规格
tnumber int,--轮胎数
total int,--总质量
curb int,--整备质量
hcurb int,--核定载质量
passenger string,--核定载客
zhcurb int,--准牵引质量
business string,--底盘企业
dtype string,--底盘品牌
fmold string,--底盘型号
fbusiness string,--发动机企业
name string,--车辆名称
age int,--年龄
sex string --性别
)
row format delimited
fields terminated by '\t'
location '/cars';

将数据写入表中

案例需求

1.统计乘用车辆和商用车辆的数量（即非营运和营运车辆）

select nature,count(nature) from cars where nature != '' group by nature;

2.统计山西省2013年每个月的汽车销售数量的比例

select month,round(sum_month/sum,2) as per
from (select month,count(month) as sum_month
from cars 
where month is not null
group by month) as a,
(select count(number) as sum from cars) as b;

3.统计买车的男女比例

select sex,round(sin/sum,2) as per
from (select sex,count(sex) as sin from cars
where sex is not null and sex != '' group by sex) as a,
(select count(sex) as sum from cars) as b;

4.统计的车的所有权、型号和类型

select owner,cartype,mold
from cars
where owner is not null
and cartype is not null
and mold is not null
and owner!=''
and cartype!=''
and mold!=''

5.统计不同类型车在一个月（对一段时间：如每个月或每年）的总销售量

select mold,month,count(number)
from cars
where length(mold) != 0 and month is not null
group by month,mold
order by mold,month;

6.通过不同类型（品牌）车销售情况，来统计发动机型号和燃料种类

select brand,mold,collect_set(concat(fuel,'  ',ftype))
from cars
where length(brand) != 0
and length(mold) != 0
and fuel!=''
and ftype!=''
group by brand,mold;

7.统计五菱每一个月的销售量

select brand,month,count(brand)
from cars
where month is not null
and length(brand)!=0
and brand='五菱'
group by month,brand;