HSD梳理

Posted on

HSD名称及英文简称映射

名称 英文简称 参考黑屏指令
航班计划数据 iSCH ST
库存数据 iINV RO
客票数据 iETS DETR
EMD票数据 iEMD DEMD
订座PNR数据 iPNR RT
订座PNR数据(历史) iPNRH RTC
离港旅客数据 iCKI PR
离港航班数据 iFLT SY

中转航班的逻辑

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
-- 按 照票号+出票日期 取最新的数据
create table test.djj_temp_hsd_iets as
select 
    *
from  
(
select 
    a.stamp,
    a.dttm,
    a.ticketidentification_tktissuedate,
    a.ticketimage_ticketnumber,
    a.itinerary_ticketcoupon,
    row_number() over(partition by a.ticketidentification_tktissuedate, a.ticketimage_ticketnumber order by a.dttm desc) r
from 
    ods.cst_hsd_iets a
) t  
where  r=1 
;
    
    
-- 拆分json
drop table test.djj_temp_hsd_iets_01;    
create table test.djj_temp_hsd_iets_01 as
select 
    a.stamp,
    a.dttm,
    a.ticketidentification_tktissuedate issuedate, -- 出票日期
    substr(a.ticketimage_ticketnumber,1,13) ticketnumber, --票号
    get_json_object(itinerary, '$.CouponNumber') couponNumber,
    get_json_object(itinerary, '$.CouponStatus') couponStatus, -- 客票状态
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Carrier'), '$.AirlineCode') carrier_airlinecode, 
    get_json_object(get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Carrier'), '$.FlightNumber'), '$.Value') carrier_flightnumber,
    get_json_object(get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Carrier'), '$.FlightNumber'), '$.Suffix') carrier_flightnumber_suffix,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.OperatingCarrier'), '$.AirlineCode') operating_airlinecode, -- 承运航司
    get_json_object(get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.OperatingCarrier'), '$.FlightNumber'), '$.Value') operating_flightnumber, -- 承运航班号
    get_json_object(get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.OperatingCarrier'), '$.FlightNumber'), '$.Suffix') operating_flightnumber_suffix, -- 承运航班号后缀
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Departure'), '$.Date') departure_date,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Departure'), '$.AirportCode') departure_airportcode,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Flight'), '$.Arrival'), '$.AirportCode') arrival_airportcode,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Open'), '$.OpenDeparture'), '$.AirportCode') open_departure_airportcode,
    get_json_object(get_json_object(get_json_object(itinerary, '$.Open'), '$.OpenArrival'), '$.AirportCode') open_arrival_airportcode,
    get_json_object(get_json_object(itinerary, '$.Open'), '$.OperatingCarrier') open_operatingcarrier
from 
    test.djj_temp_hsd_iets a
    LATERAL VIEW explode(split(
     regexp_replace(
         regexp_replace(
            itinerary_ticketcoupon, 
            '\\[|\\]' , ''),-- 将json数组两边的中括号去掉
            
              '\\}\\,\\{' , '\\}\\;\\{'),-- 将json数组元素之间的逗号换成分号
                
                 '\\;')-- 以分号作为分隔符(split函数以分号作为分隔)
          )  a as itinerary
    ;
    

-- 合并出发到达
drop table test.djj_temp_hsd_iets_02;    
create table test.djj_temp_hsd_iets_02 as    
select 
    a.issuedate,
    a.ticketnumber,
    a.couponnumber,
    a.couponstatus,
    coalesce(a.departure_airportcode, a.open_departure_airportcode) departure_airportcode,
    coalesce(a.arrival_airportcode, a.open_arrival_airportcode) arrival_airportcode
from  
    test.djj_temp_hsd_iets_01 a
    ;
    

-- 匹配出发到达城市    
drop table test.djj_temp_hsd_iets_03;    
create table test.djj_temp_hsd_iets_03 as     
select 
    a.issuedate,
    a.ticketnumber,
    a.couponnumber,
    a.couponstatus,
    a.departure_airportcode,
    a.arrival_airportcode,
    b.city_code departure_citycode,
    c.city_code arrival_citycode,
    coalesce(b.city_code, a.departure_airportcode) departure, -- 以防有的机场匹配不到城市
    coalesce(c.city_code, a.arrival_airportcode) arrival -- 以防有的机场匹配不到城市
from 
    test.djj_temp_hsd_iets_02  a 
left join 
    ods.t_airport_info_zp b on a.departure_airportcode=b.airport_code and b.dp='ACTIVE'
left join 
    ods.t_airport_info_zp c on a.arrival_airportcode=c.airport_code and c.dp='ACTIVE'
where 
    a.couponstatus<>'V'  -- 排除废票
    ;
    


-- 匹配出发到达城市    
drop table test.djj_temp_hsd_iets_04;    
create table test.djj_temp_hsd_iets_04 as      
select 
    t.issuedate,
    t.ticketnumber,
    t.seg_num,
    substr(origin,1,3) origin,
    substr(destination,-3,3) destination
from  
(
select 
    a.issuedate,
    a.ticketnumber,
    sum(1) over(partition by  a.issuedate, a.ticketnumber) seg_num, -- 总航段数
    concat_ws(',', collect_list(departure) over(partition by a.issuedate, a.ticketnumber order by a.couponnumber)) as origin,
    concat_ws(',', collect_list(arrival) over(partition by a.issuedate, a.ticketnumber order by a.couponnumber)) as destination,
    row_number() over(partition by a.issuedate, a.ticketnumber order by a.couponnumber desc) as r
from  
    test.djj_temp_hsd_iets_03 a
) t 
where 
    r=1
    and substr(origin,1,3)<>substr(destination,-3,3) -- 排除往返票
    and seg_num>1 -- 筛选多段的
    ;
    
    
select 
    issuedate,
    ticketnumber,
    seg_num,
    origin,
    destination
from  
    test.djj_temp_hsd_iets_04 a
    ;