PAXIS數據分析作業(yè)指導書

1、1 paxis 數據分析作業(yè)指導書目錄1.paxis數據介紹 . 21.1 什么是 paxis . 21.2 bsp覆蓋國家 . 21.3 paxis數據庫及表格 . 21.4 paxis數據字段 . 42. sql創(chuàng)建、查詢命令. 62.1 基本查詢格式 . 62.2 select 語句 . 62.3 where 語句 . 72.4 create tabe 語句 . 92.5 insert 語句 . 92.6 group by 語句 . 92.7 union 語句 . 103.paxis數據分析應用 . 錯誤！未定義書簽。3.1 分析步驟 . 103.2 分析案例 . 152 1. paxi

2、s 數據介紹1.1 什么是 paxis paxis 是 passenger intelligence services的簡稱，paxis 數據是來源于國際航協iata的 bsp 銷售數據，屬于出票環(huán)節(jié)的數據。1.2 bsp 覆蓋國家bsp 主要在以下國家使用以下地區(qū)的銷售數據不包括在bps 銷售數據當中。1.3 paxis數據庫及表格3 1.3.1 paxis 數據庫目前首爾辦事處sql 數據庫服務器當中， 有兩個 paxis 數據庫，paxis 和 paxis2013 ，其他的數據庫都是沒用的或者測試的數據庫。1.3.2 paxis數據表格paxis 數據庫下面的icnpaxis2012存儲

3、的是2012 年承運的跟韓國相關航程的paxis數據。paxis2013數據庫下面的icnpaxis2013存儲的是2013年承運的跟韓國相關航程的paxis 數據，暫時數據只有1-5 月份的， 6 月份以后的數據還需要繼續(xù)導入。4 當只在某個數據庫查詢時，直接使用數據表格的名稱即可，如icnpaxis2013，或者aa_paxis 。當數據查詢涉及到兩個或者多個數據庫時，按以下形式：數據庫名稱.數據表格名稱。如需要同時查詢paxis 數據庫下面的icnpaxis2012數據表格和paxis2013數據庫下面的icnpaxis2013 數據表格，則分別使用paxis.icnpaxis2012和

4、paxis2013.icnpaxis2013 1.4 paxis數據字段1.4.1 如何在 mysql 找到 paxis 數據字段打開 sql 數據表后， 點擊 table，即可找到數據表里面的全部字段。全部字段均可單擊復制。1.4.2 paxis 數據常用字段mysql 服務器里的paxis數據，每一行表示客票當中的某一個航段。例如有100 行則表示一共有100 個航段。包括以下的字段：字段名解析5 primary_ticket_key 聯票 id, 每張連續(xù)客票都有唯一的聯票id，可用于計算旅客人數。如連續(xù)客票784-2480369068-69 ，兩張客票的票聯id 是一樣的，且是唯一的。

5、agentid 代理人 iata 號agent_name 代理人名稱issue_date 出票日期，常用month(issue_date) 計算銷售月份issue_year_month 出票年月coupon_total 票聯數coupon_no 票聯號origin 航段出發(fā)地origin_country 航段出發(fā)國家destination 航段目的地destination_country 航段目的國家stopover 是否 stopover flight_date 航班日期，常用month(flight_date) 計算航班月份flight_time 航班起飛時刻arrival_time 航班

6、到達時刻carrier 航段承運人flight_no 航班號class 艙位route 航程od od ，來回程劃分為兩個od od_type od 類型，包括：直達、中轉6 od_stops od 經停點carrier_lord 航程主承運人2. sql 創(chuàng)建、查詢命令2.1 基本查詢格式2.2 select 語句2.2.1 count函數distinct表示不重復的值，因為每個旅客都有不重復的primary_ticket_key，故可以用select count(distinct primary_ticket_key) 來統計旅客人數，假如沒有加distinct的話，select coun

7、t(primary_ticket_key)統計出來的則是旅客人次，即航段數量。7 2.2.2 求和函數2.3 where 語句8 2.3.1 比較操作符例如，提取od 為 icn-lax的數據，則where語句為：where od=icn-lax 2.3.2 邏輯及連接操作符操作符舉例解析and 提取航段目的地為加拿大，且為2013 年 4 月銷售的數據，則where語句為：where destination=ca and year(issue_date)=2013 and month(issue_date)=4 or 提取航段目的地為加拿大或者美國的數據：where (destination

8、=ca or destination=us) 注意：此處要用()括起來in 提取航段目的地為加拿大美國 俄羅斯 新加坡的數據：where destination in(ca,us,ru,sg) between 提取銷售日期在2012-1-1到 2012-2-15之間的銷售數據：where issue_date between 2012-1-1and 2012-2-15 2.3.3 通配符 % 9 通配符 %表示一個或者一串字符，經常與like 聯用，如需要查詢icn 始發(fā)至 lax 的數據，則使用where route like icn%lax% ，表示篩選所有航程中以icn 開頭，并且航程中

9、含有l(wèi)ax 的所有數據。2.3.4 引號使 用 邏 輯 及 連 接 操 作 符 的 內 容 都 要 使 用 英 文 引 號 ， 如where route like icn%can% 。假如引號里面的內容是數值的話，可以用也可以不用引號，例如year(issue_date)=2013 2.4 create tabe語句create table用戶創(chuàng)建用戶table ，如需要從icn_paxis2012這個表格中，篩選出icn始發(fā)至廣州的所有數據，并建立一個table ，語句為：create table can_paxis select * from icn_paxis2012 where rou

10、te like icn%can%2.5 insert語句用于向已有的table 中添加數據， 例如： 需要往剛才創(chuàng)建的can_paxis表里再添加首爾始發(fā)至武漢的數據，語句為：inter into can_paxis select * from icn_paxis2012 where route like icn%wuh%2.6 group by 語句group by 語句用于結合合計函數，根據一個或多個列對結果集進行分組。常跟 count 、10 sum 等函數一并使用，用法為: group by + count或 sum 前面的所有字段。如：select od, od_order,mont

11、h(issue_date),count(distinct primary_ticket_key) as pax from can_paxis group by od, od_order,month(issue_date)2.7 union 語句union 操作符用于合并兩個或多個select 語句的結果集，常用 union all。如需篩選出icn始發(fā)至廣州的2012 年和 2013 年數據：select * from paxis.icn_paxis2012 where route like icn%can%union all select * from paxis2013.icn_paxis

12、2013 where route like icn%can%3.paxis 數據分析應用3.1 分析步驟11 3.1.1 新建目的地 table 以 2012 年的 paxis 數據為例， icnpaxis2012這個數據表格table大小為 7.8g ，直接進行分析的話， 數據查詢速度會非常慢，故需要先將目標數據從數據源中抽取出來，單獨建立一個 table ，這有利于提升查詢速度。如需要分析2012 年首爾至 rep 的航程，則在icnpaxis2012數據的基礎上，篩選icn始發(fā)、航程中含有rep 的航程，建立新表，命名為rep_paxis ：3.1.2 建立索引選擇常用分析字段作為key

13、 ： primary_ticket_key、 agentid、 od 、 carrier_lord，一般最常用的是primary_ticket_key create table rep_paxis select * from icnpaxis2012 where route like icn%rep% 12 3.1.3 查詢數據使用 select 語句進行查詢，詳見paxis 數據應用3.1.4 查詢數據導出 csv 數據文件13 3.1.5 數據透視分析（以航程分析為例）將導出的數據作為數據源，生成數據透視表，將航程拉入行標簽、pax 拉入計算項兩次（第一次的用于計算旅客人數、第二次的用于計

14、算所占比例）。將兩個 pax 都進行值字段設置，由計數改為求和。將第二次拉入的pax 在值字段設置-值顯示方式中， 改為占同列數據總和的百分比，這14 樣子就可以算出每個航程旅客人數的占比。將透視表標題欄的行標簽、求和項:pax、求和項： pax2 分別改為航程、旅客人數、占比，同時按旅客人數的值進行降序排列。15 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/r

15、ep-icn 4638 4.47% 16 icn-han-rep-han-icn 3554 3.43% icn-rep-han-icn 3486 3.36% sql 語句如下：3.2.2 od及主承運人分析od 排名因為往返航程 （如 icn-sgn-rep-sgn-rep） 會劃分成兩個od ， 去程 od 為 icn-rep ，回程 od 為 sgn-rep ，故在進行od 排名時， 需要使用left 函數提取od 的出發(fā)地， 然后在數據透視表分析時，將出發(fā)地選擇為icn ，這樣就可以避免od 旅客人數的重復計算。od 類型、主承運人分析時也是這么做。od 類型od 類型旅客人數占比直達8

16、2865 79.87% 中轉18456 17.79% select route, count(distinct primary_ticket_key) as pax from rep_paxis group by route 17 (空白 ) 2430 2.34% 總計103751 100.00% 主承運人od 主承運人旅客人數占比vn 42599 42.04% ke 30627 30.23% oz 27292 26.94% cz 568 0.56% sql語句：3.2.3 代理市場份額分析2012 年首爾至暹粒的銷售top4 代理人：top 4代理人旅客人數占比hana tour servi

17、ce inc 20074 19.35% modetour network inc. 12623 12.17% hanshin air co. ltd. 8289 7.99% wooree agency corporation 7790 7.51% select carrier_lord,od,od_type, count(distinct primary_ticket_key) as pax from rep_paxis groupby carrier_lord,od,od_type 18 sql語句：由于 agent_name 這個字段比較長，所以在進行查詢的時候，有時會特別的慢，這種情況下

18、，就使用 agentid來查詢數據。 查詢出來的結果是每個agentid所對應的旅客人數。再查詢 agentid和 agent_name的對應表，這樣的話，在excel 表中就可以將剛才查詢到的 agendid替換為agent_name。替換的時候可以使用excel 的 vlookup函數，這樣就很快。select agent_name,count(distinct primary_ticket_key)as pax from paxis.rep_paxis group by agent_name 19 3.2.4 代理忠誠度分析對比 1-5 月份各代理人的銷售增長情況：sql語句如下：sel

19、ect agent_name, year(flight_date),carrier_lord,count(distinct primary_ticket_key)as pax from paxis.rep_paxis / 跨數據庫查詢,查詢 12 年的數據where month(flight_date)=5 group by agent_name, year(flight_date) union all / 在跨數據庫查詢中，用于連接多個查詢結果，相當于多個查詢結果相加select agent_name, year(flight_date),carrier_lord,count(distinct primary_ticket_key)as pax from paxis2013.rep_pax2013 / 查詢 13 年的數據wh