PAXIS数据剖析功课引诱书[精品]

1、paxis数据分析作业指导书目录1. paxis数据介绍21.1 什么是paxis21.2 bsp覆盖国家21.3 paxis数据库及表格21.4 paxis数据字段42. sql创建、查询命令62.1基本查询格式62.2 select 语句62.3 where 语句72.4 create tabe 语句82.5 insert 语句92.6 group by 语句92.7 union 语句93. paxis数据分析应用错误！未定义书签。3.1分析步骤103.2分析案例141. paxis数据介绍1.1什么是paxispaxis 是 passenger intelligence services

2、 的简称，paxis 数据是来源于国际航协 iata的bsp销售数据,属于出票环节的数据。以下地区的销售数据不包括在bps销售数据当中。1.2 bsp覆盖国家bsp主要在以下国家使用国.日本、ms.香港、台北、泰国、马来西亚、印度尼西亚、h律窶、， 加坡，阿联酋、餉西兰、加畫大、印屋、澳大利亚7尼日利亚、埃及、嗣e、肯尼亚、巴酋、委内拉、阿很廷8个国寂门堆萨克斯坦、土耳具、巴基断坦、俄罗斯、马克兰、尼泊尔、孟屜越南心13 paxis数据库及表格1.3.1 paxis 数据库目前首尔办事处sql数据库服务器当中，有两个paxis数据库，paxis和paxis2013 ,其他的数据库都是没用的或者

3、测试的数据库。101 zhenginfom ati o sch em a10.7 gb0 mysql0 n) paxis0 jj) pbxis2013performanceschema0 sakila0 test0 world13.2 paxis数据表格paxis数据库下面的icnpaxis2012存储的是2012年承运的跟韩国相关航程的paxis数据。 q paxis10.7 gb| 込paxis16.0 kb| ac.paxis43.6 mbj bompaxis16.5 mbj busanpax145.7 mbj cdgpax268.5 mb| cn-kr-causl2126.7 mb|h

4、| cxpaxis53.6 mb冃一亠icn paxis20127.8 gbj|阍 icnpaximlljib.u kbi<navir1 03 q h人口paxis2013数据库下面的icnpaxis2013存储的是2013年承运的跟韩国相关航程的paxis数据,暂时数据只有1-5月份的，6月份以后的数据还需要继续导入。 paxis20136.8 gbicn paxis201363 gb|peklax_pax8.5 mbroute_2013pax458.0 mb当只在某个数据库查询时，直接使用数据表格的名称即可，如icnpaxis2013 ,或者 aa_paxiso当数据查询涉及到两个或

5、者多个数据库时，按以下形式:数据库名称数据表格名称。 如需要同时查询paxis数据库下面的icnpaxis2012数据表格和paxis2013数据库下面的 icnpaxis2013 数据表格，则分另（j使用 paxis.icnpaxis2012 和 paxis2013.icnpaxis20131.4 paxis数据字段1.4.1如何在mysql找到paxis数据字段打开sql数据表后，点击table ,即可找到数据表里面的全部字段。全部字段均可单击复制。o * xt h100 xhengsf omwbon.sc bema9jg8cj vdd呻2033 mb団 <9ks2j6mb-my &

6、gt;51804 m8,knp<k>i20127.7 g8kulaxh64j6mbmnlpnis16umblj paxisttmp32j0kb目 p«npam"mbl_j pnh-pwis216 mbu report108-7mb_ $gnhenl2new289j0m8 sgnhan,pans65 mb已 sgnjjam1713 f-4b$m< gkmnllculpws429jomb西 sinjxm18s-8wtestuble144dkb_ vhp«o5816 mb* vmd.vn20122639 mbkk edit search tooh he

7、lpi m m o o x臭 vsyd p4xi$2013ofhions |indexes # foegn keys ) ,cmatt code -alkr codename:cd9jmxcommentco*umnvq addo remove downlmgtlv3cczerofill defaultvarchar16nullpnmeryjkketjioatvarcharisnull>gentidvarchar8nulldgent_nmevarchar300nullgent.dcpartfncntvarchar200nullagent.cityvarchar50nullagentcoun

8、tryvarchar2nullboobng.flodtvarchar10nulldircodevarchar5null1stitrttkal.codevarchar3null1tr«nmct>on.codevarchar1null1system jdvarchar4null1i$ue d«tedatenull上issue vc«r monthvarcharfitw.4事 host 192168.100.100 i1.4.2 paxis数据常用字段mysql服务器里的paxis数据，每一行表示客票当中的某一个航段。例如有100行则表 示一-共有100个航段。包

9、括以下的字段：字段名解析primary_ticket_key联票id,毎张连续客票都冇唯-的联票id, m用于计算旅客人数。如 连续客票784-2480369068-69,两张客票的票联id是一样的,且是唯一 的。age ntid代理人iata号age nt_ name代理人名称issuedate出票日期/常用month(issue_date)计算销售月份issue_year_m onth出票年月couponjotal票联数coupon_no票联号origi n航段岀发地origin_co untry航段岀发国家desti nation航段目的地destinati on_ country航段目

10、的国家stopover是否 stopoverflight_date航班日期，常用month(flight_date)计算航班月份flight_time航班起飞时刻arrivaltime航班到达时刻carrier航段承运人flight, no删号class舱位route斷呈od0d,来回程划分为两个0dodjype0d类型，包括：直达、中转od_stops0d经停点2. sql创建.查询命令2.1基本查询格式select.选择查询字段from.选择数据源名称where.定义查询条件order by.定义排序方式2.2 select 语句2.2.1 count 函数distinct表示不重复的值,

11、因为每个旅客都有不重复的primary_ticket_key，故可以用select count(distinct primary_ticket_key)来统计旅客人数，假如没有加 distinct 的话，select count(primary_ticket_key)统计出来的则是旅客人次，即航段数量。selectselect *查询所有字段select count ()统计记录数select count (distinct )统计不重复记录数2.2.2求和函数求和记录中某字段值select sum ()select avg求某字段平均值select max/min 返回某字段最大/最小值2.

12、3 where 语句andorlikeetween通配符引号逻辑及连接操作符2.3.1比较操作符例如，提取0d为icn-lax的数据，则where语句为：where 0d=, icn-laxz2.3.2逻辑及连接操作符操作符举例解析提取航段目的地为加拿大，且为2013年4月销售的数据，则whereand语句为：where destinationca and year(issue_date)=2013 and mon th(issue.date) =4or提取航段目的地为加拿大或者美国的数据where(destination二'ca or destination二us') 注意：

13、此处要用()括起来in提取翅目的地为加拿大僕国俄罗斯新加坡的数据:where destination in(,ca,；us,；ru,/,sg,)between:where提取销售日期在2012-1-1到2012-2-15之间的销售issue.date between '2012j jand 2012-2 j512.3.3通配符通配符表示一个或者一串字符,经常与like联用，如需要查询icn始发至lax的数据, 则使用where route like ,icn%lax%/ ,表示筛选所有航程中以icn开头，并且航程中含有 lax的所有数据。2.3.4引号使用逻辑及连接操作符的内容都要使用

14、英文引号，如where route like 'icn%can%'。假如引号里面的内容是数值的话，可以用也可以不用引号，例如 year(issue_date)=20132.4 create tabe 语句create table用户创建用户table ,如需要从icn_paxis2012这个表格中，筛选出icn始 发至广州的所有数据，并建立一个table ,语句为：create table can_paxisselect * from icn_paxis2012where route like 'icn%can%'2.5 insert 语句用于向已有的table

15、中添加数据，例如：需要往刚才创建的can_paxis表里再添加首尔始 发至武汉的数据，语句为：in ter into can _paxisselect * from icn_paxis2012where route like 'icn%wuh%'2.6 group by 语句group by语句用于结合合计函数，根据一个或多个列对结果集进行分组。常跟count.sum等函数一并使用,用法为:group by + count或sum前面的所有字段。如：select od, od_orderzmonth(issue_date)/count(distinet primary.tick

16、et.key) as paxfrom can_paxisgroup by odr od_order/month(issue_date)2.7 union 语句union操作符用于合并两个或多个select语句的结果集,常用union all。如需筛选出icn始发至广州的2012年和2013年数据：select * from paxis.icn_paxis2012where route like 'icn%can%'union allselect * from paxis2013.icn_paxis2013where route like /icn%can%r3. paxis数据

17、分析应用3.1分析步骤3.1.1新建目的地table以2012年的paxis数据为例,icnpaxis2012这个数据表格table大小为7.8g ,直接进行 分析的话，数据查询速度会非常慢，故需要先将目标数据从数据源中抽取岀来，单独建立一个table ,这有利于提升查询速度。如需要分析2012年首尔至rep的航程，则在icnpaxis2012数据的基础上筛选icn始发、 航程中含有rep的航程,建立新表，命名为rep_paxis :create table rep_paxisselect *from icnpaxis2012 where route like ficn%rep%'3.

18、1.2建立索引选择常用分析字段作为 key : primaryjicket_keyx agentid. odx carrierjord # 般最常用的是primaryjicket_key3.1.3查询数据使用select语句进行查询,详见paxis数据应用3.1.4查询数据导出csv数据文件file edit search tools helphanpaxis j icnpaxis2012_| kulpaxis _i mnljjaxis _ paxistemppenpaxispnhjjaxisrepj)axis j sgnhanl2new sgnhan j)axis $gnj)axis sin

19、cgkmnlkulpaxis sin.paxis testtable回画国s)i2is)vhpaxis vmelvn2012vsin vsyd1805 mb7.7 gb646 mb161-8 mb32.0 kb8.0 mb23£ mb108.7 mb289.0 mb65 mb171 mb429.0 mb188-8 mb144.0 kb816 mb2639 mb h hosts 192168.10 心3 where month(4 and agentid i 回5 and coupon_nt 両6 group by ager7 union all8 select筍9 fro»

20、 pdxis20110 where month(i 11 and agentid 1 q12 and coupon_no13 group by ager 丿result 1hana tour service hanshin air co. lt modetourwoor 圧 atoup. serhcthanshin air co. lcopy pasteinsert valueimage previewsave blob to file .ctrl+c 弋ctrl+v.7：insert files into text/blob fields.insert rowduplic

21、ate rowpostcancel editingdelete selected row(s)reset sorting quick filterexport grid rows .neip refreshinsctrl+ins17：ctri+delexport grid rowsen ceding:ansi予d:舞分析ppt寰§汇总表cdg月份csvoutput formatq excel compatibledelimited text©html table ©xmlsql insertssql replaces©latexc wiki markup

22、field separatorrencloser:line terminator:rn亍row selectionc selection (1 rows, 25 b) complete (8 rows, 208 b)opti ons0 column names in first row3.1.5数据透视分析（以航程分析为例）将导出的数据作为数据源，生成数据透视表，将航程拉入行标签、pax拉入计算项两次（第一次的用于计算旅客人数、第二次的用于计算所占比例）。将两个pax都进行值字段设置,由计数改为求和。行标签ich-bkk/rep-han-icn ich-bkk/rep-icn ich-bkk/

23、rep-pus ich-bkk-cmb/bkk-rsp/icn-bkk-cmb/bkk- ich-bkk-cmb/sin-! icm-bkk-pkz-rep-i icn-bkk-pnh/rep- ich-bkk-rep-bkk-i ich-bkk-rep-bkk-p ich-bkk-rep-icn icn-bkk-rgh-bkk-p ich-bkk-rgh-bkk-v ich-bkk-sih-rep-sc ich-bkk-ubp/pkzt ich-bkk-vte-rep/ ich-can-bkk/rep-< ” sheeda偵字段设置渾名称p<x自定义名»(c)：求和i页

24、px：总方式值显示方式 值字段汇总方式q) 迭择用于匸总所迭宁段数提的数字格式01)an列惊签e數值1 昵取満在以下区斥月拖如用 /报痢送i计数项：p"计数項:p"211送審畫杀加到报册字段v route推迟布貝更斎将第二次拉入的pax在值字段设置值显示方式中，改为占同列数据总和的百分比，这样子就可以算出每个航程旅客人数的占比。123456789101112131415161718192021行标签ich-ekk/rep-han-icn ich-bkk/rep-icn ich-bkk/rep-pus icn-ekk-chb/bkk- ich-bkk-ckb/bkk- ici

25、i-bkk<j!b/sin- ich-bkk-pkz-rep-i ich-bkk-phh/rep- icii-bkk-rep-bkk-i ich-bkk-rep-bkk-p icm-bkk-rep-icn ich-bkk-rgk-bkk-p ich-bkk-rgk-bkk-v ich-bkk-sih-rep-s ich-bkk-ubp/pkz-i ich-bkk-vte-rep/ ich-cak-bkk/rephsheet】 航程就绪cb值显示方式©昔通数手格式q)取消俏求和项；pax求和项:pax2|2113111312525p i /dumtpm 值字段设肯源名称：pq自定

26、义名称© 术和项:pax2 上总方式|值显示方歹100%将透视表标题栏的行标签.求和项:pax、求和项：pax2分别改为航程、旅客人数.占比, 同时按旅客人数的值进行降序排列。kx®i0 s0 el ii 刽ii 39sii 96iz exm 9罔m co0 守 800 co960p p egoe d6gcoe舄t;o®0h 去临冢ywn上f<u<hivzn111.c如<r ® 118xrnoiinos 二 d困foi 81 之匕亠困二乂曽总吕匚 _5iinosd 宦nosfoi 91 noild 困二 nvhinoirgl: noil

27、nusd 困一01 寸 i noihnd二亠困noiool: noimv鱼noim noild 困nvhnoih noilnvhid困noi ot no i nuhid 宦nthifo16 noild 困二hndlnoico so i in vhd 宦nosfui z so i nos id 困 in vhno19npi-jaa胃 4slii.aj36.19%20.04%15.58%yzs009icn-rep-icnicn-han-rep-sgn-icnicn-sgn-rep-han-icnicn-pnh/rep-icnt < r背 z .0 sgcoo0黑lju hc0 y aaap

28、kpr f t c noild 宦二 nosinoio'z _5iinu0ld 困nvoinoi 61icn-han-rep-han-icn35543.43%icn-rep-han-icn34863.36%sql语句如下:select route, count(distinct primary_ticket_key) as pax from rep_paxisgroup by route3.2.2 od及主承运人分析od排名因为往返航程(如icn-sgn-rep-sgn-rep )会划分成两个od ,去程od为icn-rep , 回程od为sgn-rep ,故在进行od排名时，需要使用

29、left函数提取od的出发地,然后在数 据透视表分析时，将出发地选择为icn ,这样就可以避免0d旅客人数的重复计算。od类型、主承运人分析时也是这么做。出发地icn0d 旅客人数占比icn-rep6561064. 93%icn-han2771727. 43%icn-pnh47944. 74%icn-sgn13911. 38%icn-bkk11281. 12%tnktctt.t1 ooc 1 oft?od类型od类型旅客人数占比直达8286579.87%中转1845617.79%（空白）24302.34%总计103751100.00%主承运人od主承运人旅客人数占比vn4259942.04%k

30、e3062730.23%oz2729226.94%cz5680.56%sql语句:select camer_lord,od,od_type,count(distinct primary_ticket_key) as paxfrom rep.paxisgroup by camer_lord,od,od_type3.2.3代理市场份额分析2012年首尔至暹粒的销售top4代理人：top 4代理人旅客人数占比hana tour service inc2007419.35%modetour network inc.1262312.17%hanshin air co. ltd.82897.99%woor

31、ee agency corporation77907.51%sql语句:select agent_name,count(distinet primary_ticket_key)as pax from paxis.rep_paxis group by agent_name由于这个字段比较长，所以在进行查询的时候，有时会特别的慢，这种情况 下，就使用agentid来查询数据。查询出来的结果是每个agentid所对应的旅客人数。il host: 01 | j database: paxis j table: reppaxis il data f* que

32、ry1 select agentid.count(distinct primary_ticket_key)as pax2 from paxis.reppaxis3 group by agentidi rep paxispaxage ntid 02356572 02357876 0830 1451 08307736 08309184 08309560 08309722 16304643再查询agentid和的对应表z这样的话，在excel表中就可以将刚才查询到的agendid替换为agent_nameo替换的时候可以使用excel的vlookup函数，这样就很快。age nt

33、idage ntjnam 己17315465interpark travel corporation17302401top trvl svc co ltd17315465interpark int corp17315465interpark int corp17315465interpark int corp17315465interpark int corp17317451online tour co. ltd17317451online tour co. ltdj.133c1_omltme-un rr.- _ .rep paxis j» qu吋 | q1 select agentidr agent_name2 from paxi3.rep_pdxi3 |l host: 01database: paxistable: rep.paxisii data3.2.4代理忠诚度分析对比1-5月份各代理人的销售增长情况:什壬甲a2012年15月2013年15月ke