实训目的

• 了解Hive 数据预处理与分析；
• 熟悉Hive 的查询语句：
• 掌握tez 和Spark 引擎的使用方法；
• 具有使用Hive知识完成航空公司客户价值数据预处理与分析的能力。

1.任务描述

飞机被认为是迄今为止最安全、高效的交通工具。如何在给顾客提供优质服务的同时保障利益最大化，这个间题时刻困扰着航空公司。为了解决这一问题， 可以

• 使用Hive 对客户进行分群，如重要保持客户、重要发展客户、重要挽留客户、一般客户和低价值客户

• 再针对不同的客户群体制定相应的优惠政策来实现利益最大化。

• 本任务通过对Hive数据分析知识的学习， 最终实现基于Hive 的航空公司客户价值数据预处理与分析。

2.功能描述

1. 加裁数据；
2. 数据统计清洗；
3. 建立 LRFMC模型。

3.任务背景

精准化运营的基础是客户关系管理与维护，客户关系管理的核心是客户分类，对不同的客户群体开展不同的个性化服务，将有限的资源合理地分配给不同价值的客户，从而实现效益最大化。
本教程使用的数据集包含 68 828 条数据，有44个字段。主要字段如下表所示：

实训内容

1.创建数据库和数据表

1. 在Hive中创建名为air_data的数据库，并在数据库中创建名为air_table的表

create database air_data;
use air_data;
create table air_table(
member_no string,
ffp_date string,
first_flight_date string,
gender string,
ffp_tier int,            
work_city string,            
work_province string,            
work_country string,            
age int,            
load_time string,            
flight_count int,            
bp_sum bigint,            
ep_sum_yr_1 int,            
ep_sum_yr_2 bigint,            
sum_yr_1 bigint,            
sum_yr_2 bigint,            
seg_km_sum bigint,            
weighted_seg_km double,            
last_flight_date string,            
avg_flight_count double,            
avg_bp_sum double,            
begin_to_first int,            
last_to_end int,            
avg_interval float,            
max_interval int,            
add_points_sum_yr_1 bigint,            
add_points_sum_yr_2 bigint,            
exchange_count int,            
avg_discount float,            
p1y_flight_count int,            
l1y_flight_count int,            
p1y_bp_sum bigint,            
1y_bp_sum bigint,            
ep_sum bigint,            
add_point_sum bigint,            
eli_add_point_sum bigint,            
l1y_eli_add_points bigint,            
points_sum bigint,            
l1y_points_sum float,            
ration_l1y_flight_count float,            
ration_p1y_flight_count float,            
ration_p1y_bps float,            
ration_l1y_bps float,            
point_notflight int            )            
row format delimited fields terminated by ',';

2.导入数据

2. 文件中导入air_table表中，并查看是否导入成功，命令如下。

load data local inpath '/root/hivedata/data/air_data.csv' overwrite into table air_table;
select * from air_table limit 10;

3. 统计空记录

统计观测窗口内的票价收入(sum_yr_l)、观测窗口内的总飞行公里数（seg_km_sum)和平均折扣率（avg_discount）三个字段的空记录，并将结果保存到名为count_null的表中，命令如下。

create table count_null as select * from       
(select count(*) as sum_yr_1_null_count from air_table where sum_yr_1 is null)       
sum_yr_1,       
(select count(*) as seg_km_sum_null from air_table where seg_km_sum is null)       
seg_km_sum,       
(select count(*) as avg_discount_null from air_table where avg_discount is null)       
avg_discount;

select * from count_null;

4. 统计最小值

统计出观测窗口内的票价收入（sum_yr_l)、观测窗口内的总飞行公里数（seg_km_sum)和平均折扣率(avg_discount)三个字段的最小值并保存到count_min表中，命令如下。

create table count_min as select           
min(sum_yr_1) sum_yr_1,            
min(seg_km_sum) seg_km_sum,            
min(avg_discount) avg_discount            
from air_table;
 
select * from count_min;

5. 数据清洗

通过对数据的分析可以发现数据中存在缺失值，但缺失值占总体数据的比例较小，所以直接将缺失值过滤掉，分别过滤掉票价为空的记录，平均折扣率为0 的记录，票价为0、平均折扣率不为0、总飞行公里数大于0 的记录。

create table sum_yr_1_notnull as            
select * from air_table where            
sum_yr_1 is not null;

create table avg_discount_not_0 as 
select *            
from sum_yr_1_notnull 
where             
avg_discount <> 0;

create table sas_not_0 as             
select * from avg_discount_not_0             
where !(sum_yr_1=0 and avg_discount <> 0             
and seg_km_sum > 0);

6. 属性筛选

为了建立LRFMC模型，从清洗后的数据中集中选择与指标相关的六个属性：ffp_date、load_time、flight_count、seg_km_sum、 avg_discount、 last_to_end

create table flfasl as 
select 
ffp_date,
load_time,
flight_count,
avg_discount,
seg_km_sum,
last_to_end 
from sas_not_0;

select * from flfasl limit 10;

7. 数据转换

将数据转换为适当的格式，使其能够满足挖掘任务和算法的需要构造LRFMC的五个指标

• L的构造

会员入会距离观测窗口结束的月数=观测窗口的结束时间-入会时间 【单位：月】L = load_time-ffp_date

• R 的构造

会员最近一次乘坐飞机的时间距离观测窗口结束的月数=最后一次乘机至观测窗口结束时长
R=last_to_end

• F的构造

会员在观测窗口内乘坐飞机的次数=观测窗口内的飞行次数【单位：次】
F = flight_count

• M 的构造

会员在观测窗口内累积的飞行距离里程=观测窗口内的总飞行公里数【单位：公里】
M=seg_km_sum

• C的构造

会员在观测窗口内乘坐的舱位所对应的折扣系数的平均值=平均折扣率【单位：无】
C = avg_discount

根据以上公式对规约后的数据进行计算，得到LRFMC

create table lrfmc as select        
round((unix_timestamp(LOAD_TIME,'yyyy/MM/dd')-unix_timestamp(FFP_DATE,'yyyy/MM/dd'))/(30*24*60*60),2) as l,       
round(last_to_end/30,2) as r,       
FLIGHT_COUNT as f,       
SEG_KM_SUM as m,       
round(AVG_DISCOUNT,2) as c       
from flfasl;

8. 标准化

对数据进行标准化操作，公式为 标准化值=（x-min(x))/(max(x)-min(x)) 将标准化后的数据保存到名为 standardlrfmc的表中

 create table standardlrfmc as    
 select (lrfmc.l-minlrfmc.l)/(maxlrfmc.l-minlrfmc.l) as l,    
 (lrfmc.r-minlrfmc.r)/(maxlrfmc.r-minlrfmc.r) as r,    
 (lrfmc.f-minlrfmc.f)/(maxlrfmc.f-minlrfmc.f) as f,    
 (lrfmc.m-minlrfmc.m)/(maxlrfmc.m-minlrfmc.m) as m,    
 (lrfmc.c-minlrfmc.c)/(maxlrfmc.c-minlrfmc.c) as c    
 from lrfmc,    
 (select max(l) as l,
 max(r) as r,
 max(f) as f,
 max(m) as m,
 max(c) as c from lrfmc) as maxlrfmc,    
 (select min(l) as l,
 min(r) as r,
 min(f) as f,
 min(m) as m,
 min(c) as c from lrfmc) as minlrfmc;

9. 导出数据

将标准化后的数据导出到本地的standardlrfmc.csv中并使用逗号作为分隔符

hive -e "insert overwrite local directory '/root/hivedata/data/standardlrfmc' row format delimited fields terminated by ',' select * from air_data.standardlrfmc;"

• 保存成功

• 客户分类
• 根据业务模型将客户大致分为五类，通过k=5以及标准化后的数据，利用Kmeans模型，计算出这五类客户群体的聚类中心。
• 由于虚拟机环境下python库文件安装不完整，可以先把虚拟机中的standardlrfmc.csv文件下载到宿主机的桌面上，然后在宿主机系统中ananconda包含的Spyder中完成编译。
• 使用任意python编辑器编写如下代码，然后运行。（注意数据文件standardlrfmc.csv的保存路径）

import pandas as pd
from sklearn.cluster import KMeans
dt=pd.read_csv("standardlrfmc.csv",encoding='UTF-8')
dt.columns=['L','R','F','M','C']
model=KMeans(n_clusters=5)
model.fit(dt)
r1=pd.Series(model.labels_).value_counts()
r2=pd.DataFrame(model.cluster_centers_)
r=pd.concat([r2,r1],axis=1)
r.columns=list(dt.columns)+['Clustercategory']
print(r)

• 根据聚类中心结果，再结合航空公司的业务逻辑，可得到如下结果

1. 客户群体1 C属性最大，可定义为重要挽留客户
2. 客户群体2 L属性最大，可定义为重要发展客户
3. 客户群体3 F、M属性较小，可定义为低价值客户
4. 客户群体4 L、C属性较大，可定义为一般客户
5. 客户群体5 F、M属性最小，可定义为低价值客户

实训总结

注意
命令的使用与输入
文件路径的引用
建表字段的顺序