PAXIS数据分析作业指导书.docx
《PAXIS数据分析作业指导书.docx》由会员分享，可在线阅读，更多相关《PAXIS数据分析作业指导书.docx（18页珍藏版）》请在冰豆网上搜索。
PAXIS数据分析作业指导书
PAXIS数据分析作业指导书
1.PAXIS数据介绍
1.1什么是PAXIS
PAXIS是PassengerIntelligenceServices的简称，PAXIS数据是来源于国际航协IATA的BSP销售数据，属于出票环节的数据。
1.2BSP覆盖国家
BSP主要在以下国家使用
以下地区的销售数据不包括在BPS销售数据当中。
1.3PAXIS数据库及表格
1.3.1PAXIS数据库
目前首尔办事处SQL数据库服务器当中，有两个PAXIS数据库，PAXIS和PAXIS2013，其他的数据库都是没用的或者测试的数据库。
1.3.2PAXIS数据表格
Paxis数据库下面的icnpaxis2012存储的是2012年承运的跟韩国相关航程的paxis数据。
Paxis2013数据库下面的icnpaxis2013存储的是2013年承运的跟韩国相关航程的paxis数据，暂时数据只有1-5月份的，6月份以后的数据还需要继续导入。
当只在某个数据库查询时，直接使用数据表格的名称即可，如icnpaxis2013，或者aa_paxis。
当数据查询涉及到两个或者多个数据库时，按以下形式：
数据库名称.数据表格名称。
如需要同时查询paxis数据库下面的icnpaxis2012数据表格和paxis2013数据库下面的icnpaxis2013数据表格，则分别使用paxis.icnpaxis2012和paxis2013.icnpaxis2013
1.4PAXIS数据字段
1.4.1如何在MYSQL找到PAXIS数据字段
打开SQL数据表后，点击TABLE，即可找到数据表里面的全部字段。
全部字段均可单击复制。
1.4.2PAXIS数据常用字段
MYSQL服务器里的PAXIS数据，每一行表示客票当中的某一个航段。
例如有100行则表示一共有100个航段。
包括以下的字段：
字段名
解析
primary_ticket_key
联票ID,每张连续客票都有唯一的联票ID，可用于计算旅客人数。
如连续客票784-2480369068-69，两张客票的票联ID是一样的，且是唯一的。
agentid
代理人IATA号
agent_name
代理人名称
issue_date
出票日期，常用month（issue_date）计算销售月份
issue_year_month
出票年月
coupon_total
票联数
coupon_no
票联号
origin
航段出发地
origin_country
航段出发国家
destination
航段目的地
destination_country
航段目的国家
stopover
是否stopover
flight_date
航班日期，常用month（flight_date）计算航班月份
flight_time
航班起飞时刻
arrival_time
航班到达时刻
carrier
航段承运人
flight_no
航班号
class
舱位
route
航程
od
OD，来回程划分为两个OD
od_type
OD类型，包括：
直达、中转
od_stops
OD经停点
carrier_lord
航程主承运人
2.SQL创建、查询命令
2.1基本查询格式
2.2Select语句
2.2.1Count函数
Distinct表示不重复的值，因为每个旅客都有不重复的primary_ticket_key，故可以用selectcount（distinctprimary_ticket_key）来统计旅客人数，假如没有加distinct的话，selectcount（primary_ticket_key）统计出来的则是旅客人次，即航段数量。
2.2.2求和函数
2.3where语句
2.3.1比较操作符
例如，提取OD为ICN-LAX的数据，则where语句为：
whereOD=’ICN-LAX’
2.3.2逻辑及连接操作符
操作符
举例解析
AND
提取航段目的地为加拿大，且为2013年4月销售的数据，则where语句为：
wheredestination='CA'andyear（issue_date）=2013andmonth（issue_date）=4
OR
提取航段目的地为加拿大或者美国的数据：
where（destination='CA'ordestination='US'）
注意：
此处要用（）括起来
IN
提取航段目的地为加拿大\美国\俄罗斯\新加坡的数据：
wheredestinationin（'CA','US','RU','SG'）
BETWEEN
提取销售日期在2012-1-1到2012-2-15之间的销售数据：
whereissue_datebetween'2012-1-1'and'2012-2-15'
2.3.3通配符%
通配符%表示一个或者一串字符，经常与like联用，如需要查询ICN始发至LAX的数据，则使用whereroutelike‘icn%lax%’，表示筛选所有航程中以ICN开头，并且航程中含有LAX的所有数据。
2.3.4引号
使用逻辑及连接操作符的内容都要使用英文引号，如Whereroutelike‘ICN%CAN%’。
假如引号里面的内容是数值的话，可以用也可以不用引号，例如year（issue_date）=2013
2.4Createtabe语句
Createtable用户创建用户table，如需要从icn_paxis2012这个表格中，筛选出ICN始发至广州的所有数据，并建立一个table，语句为：
Createtablecan_paxis
Select*fromicn_paxis2012
Whereroutelike‘ICN%CAN%’
2.5Insert语句
用于向已有的table中添加数据，例如：
需要往刚才创建的can_paxis表里再添加首尔始发至武汉的数据，语句为：
Interintocan_paxis
Select*fromicn_paxis2012
Whereroutelike‘ICN%WUH%’
2.6groupby语句
GROUPBY语句用于结合合计函数，根据一个或多个列对结果集进行分组。
常跟count、sum等函数一并使用，用法为:
groupby+count或sum前面的所有字段。
如：
Selectod,od_order,month（issue_date）,count（distinctprimary_ticket_key）aspax
Fromcan_paxis
Groupbyod,od_order,month（issue_date）
2.7Union语句
UNION操作符用于合并两个或多个SELECT语句的结果集，常用unionall。
如需筛选出ICN始发至广州的2012年和2013年数据：
Select*frompaxis.icn_paxis2012
Whereroutelike‘ICN%CAN%’
Unionall
Select*frompaxis2013.icn_paxis2013
Whereroutelike‘ICN%CAN%’
3.Paxis数据分析应用
3.1分析步骤
3.1.1新建目的地table
以2012年的paxis数据为例，icnpaxis2012这个数据表格table大小为7.8G，直接进行分析的话，数据查询速度会非常慢，故需要先将目标数据从数据源中抽取出来，单独建立一个table，这有利于提升查询速度。
如需要分析2012年首尔至rep的航程，则在icnpaxis2012数据的基础上，筛选ICN始发、航程中含有REP的航程，建立新表，命名为rep_paxis：
createtablerep_paxis
Select*
fromicnpaxis2012
whereroutelike'icn%rep%'
3.1.2建立索引
选择常用分析字段作为key：
primary_ticket_key、agentid、od、carrier_lord，一般最常用的是primary_ticket_key
3.1.3查询数据
使用select语句进行查询，详见paxis数据应用
3.1.4查询数据
导出CSV数据文件
3.1.5数据透视分析（以航程分析为例）
将导出的数据作为数据源，生成数据透视表，将航程拉入行标签、PAX拉入计算项两次（第一次的用于计算旅客人数、第二次的用于计算所占比例）。
将两个PAX都进行值字段设置，由计数改为求和。
将第二次拉入的PAX在值字段设置-值显示方式中，改为占同列数据总和的百分比，这样子就可以算出每个航程旅客人数的占比。
将透视表标题栏的行标签、求和项:
PAX、求和项：
PAX2分别改为航程、旅客人数、占比，同时按旅客人数的值进行降序排列。
3.2分析案例
3.2.1航程分析
分析主要航程的旅客人数及占比。
航程
旅客人数
占比
ICN-REP-ICN
37552
36.19%
ICN-HAN-REP-SGN-ICN
20787
20.04%
ICN-SGN-REP-HAN-ICN
16166
15.58%
ICN-PNH//REP-ICN
4638
4.47%
ICN-HAN-REP-HAN-ICN
3554
3.43%
ICN-REP-HAN-ICN
3486
3.36%
SQL语句如下：
selectroute,count（distinctprimary_ticket_key）aspax
Fromrep_paxis
groupbyroute
3.2.2OD及主承运人分析
OD排名
因为往返航程（如ICN-SGN-REP-SGN-REP）会划分成两个OD，去程OD为ICN-REP，回程OD为SGN-REP，故在进行OD排名时，需要使用left函数提取OD的出发地，然后在数据透视表分析时，将出发地选择为ICN，这样就可以避免OD旅客人数的重复计算。
OD类型、主承运人分析时也是这么做。
OD类型
OD类型
旅客人数
占比
直达
82865
79.87%
中转
18456
17.79%
（空白）
2430
2.34%
总计
103751
100.00%
主承运人
OD主承运人
旅客人数
占比
VN
42599
42.04%
KE
30627
30.23%
OZ
27292
26.94%
CZ
568
0.56%
SQL语句：
Selectcarrier_lord,od,od_type,count（distinctprimary_ticket_key）aspax
Fromrep_paxis
groupbycarrier_lord,od,od_type
3.2.3代理市场份额分析
2012年首尔至暹粒的销售TOP4代理人：
TOP4代理人
旅客人数
占比
HANATOURSERVICEINC
20074
19.35%
MODETOURNETWORKINC.
12623
12.17%
HANSHINAIRCO.LTD.
8289
7.99%
WOOREEAGENCYCORPORATION
7790
7.51%
SQL语句：
selectagent_name,count（distinctprimary_ticket_key）aspax
frompaxis.rep_paxis
groupbyagent_name
由于agent_name这个字段比较长，所以在进行查询的时候，有时会特别的慢，这种情况下，就使用agentid来查询数据。
查询出来的结果是每个agentid所对应的旅客人数。
再查询agentid和agent_name的对应表，这样的话，在excel表中就可以将刚才查询到的agendid替换为agent_name。
替换的时候可以使用excel的vlookup函数，这样就很快。
3.2.4代理忠诚度分析
对比1-5月份各代理人的销售增长情况：
SQL语句如下：
selectagent_name,year（flight_date）,carrier_lord,count（distinctprimary_ticket_key）aspax
frompaxis.rep_paxis//跨数据库查询,查询12年的数据
wheremonth（flight_date）<=5
groupbyagent_name,year（flight_date）
unionall//在跨数据库查询中，用于连接多个查询结果，相当于多个查询结果相加
selectagent_name,year（flight_date）,carrier_lord,count（distinctprimary_ticket_key）aspax
frompaxis2013.rep_pax2013//查询13年的数据
wheremonth（flight_date）<=5
groupbyagent_name
3.2.5淡旺季分析
旅客人数月份走势（航班月）
旅客人数月份走势（销售月）
SQL语句如下：
selectissue_year_month,month（flight_date）,count（distinctprimary_ticket_key）aspax
fromrep_paxis
wherecoupon_no='1'
groupbyissue_year_month,flightmonth
备注：
coupon_no=’1’表示fligh_date是选取第一航段的航班日期，而不是回程的日期或者中转航班的日期。
3.2.6中转航班衔接时刻分析
分析ICN经SGN中转至REP的航班衔接时刻：
出发航班
航班号
旅客
人数
到达
时刻
衔接航班
航班号
旅客
人数
起飞
时刻
ICN-SGN
VN409
18335
0125P
SGN-REP
VN3821
8373
0430P
VN813
6655
0520P
SQL语句如下：
selectorigin,destination,carrier,flight_no,count（distinctprimary_ticket_key）aspax
Fromrep_paxis
Whereroutelike‘icn%sgn%rep%’
groupbyorigin,destination,carrier,flight_no
升级会员 