Cacher is the code snippet organizer for pro developers

--查询国内国际经营情况
SELECT
to_char(to_date(t.flight_date,'yyyy-mm-dd'),'yyyymm') 日期,
CASE WHEN t.departure_airport='SZX' THEN airline
 WHEN t.arrival_airport='SZX' AND length(airline) >7 THEN substr(airline,9,3)||'-'||substr(airline,5,3)||'-'||substr(airline,1,3)
 WHEN t.arrival_airport='SZX' AND length(airline) =7 THEN substr(airline,5,3)||'-'||substr(airline,1,3) END 航线,
nvl(CASE WHEN t.departure_airport='SZX' THEN '始发' ELSE '回程' END,'往返合计') 类型,
count(*) 航班量,
sum(supply_seat2 * segment_space_r)/10000 万ASK,
sum(ticket_income + fuel_income) / nullif(sum(supply_seat2 * segment_space_r), 0) 含油座收,
sum(ticket_income) / nullif(sum(supply_seat2 * segment_space_r), 0) 不含油座收,
sum(ticket_income + fuel_income) / nullif(sum(order_seat), 0) 含油票价,
sum(ticket_income) / nullif(sum(order_seat), 0) 不含油票价,
sum(order_seat * segment_space_r) / nullif(sum(supply_seat2 * segment_space_r), 0) 客座,
sum(ticket_income) / nullif(sum(order_seat * standard_price_r) / 1.09, 0) 折扣, -------只计算国内
sum(ticket_income + ticket_adj + baggage_income + freight_income +parcel_income + fuel_income + p_income2 + p_income3 + p_income4 +f_srje21 - F_FYJE1+ p_turnover)/10000 万不含补边贡,
sum(ticket_income + ticket_adj + baggage_income + freight_income +parcel_income + fuel_income + p_income2 + p_income3 + p_income4 +f_srje21 - F_FYJE1+ p_turnover + p_income1)/10000 万含补边贡,
sum(ticket_income + ticket_adj + baggage_income + freight_income +parcel_income + fuel_income + p_income2 + p_income3 + p_income4 +f_srje21 - F_FYJE1+ p_turnover)
 /nullif(sum(ticket_income + ticket_adj + baggage_income +freight_income + parcel_income + fuel_income + p_income2 + p_income3 + p_income4 + f_srje21+ p_turnover),0) 不含补边贡率,
sum(ticket_income + ticket_adj + baggage_income + freight_income +parcel_income + fuel_income + p_income2 + p_income3 + p_income4 +f_srje21 - F_FYJE1+ p_turnover + p_income1)
 /nullif(sum(ticket_income + ticket_adj + baggage_income +freight_income + parcel_income + fuel_income + p_income2 + p_income3 + p_income4 + f_srje21+ p_turnover + p_income1),0) 含补边贡率,
sum(ticket_income + ticket_adj + baggage_income + freight_income + parcel_income + fuel_income + p_income2 + p_income3 + p_income4 +f_srje21 + p_turnover)/10000 万不含补运输收入,
sum(ticket_income + ticket_adj + baggage_income + freight_income + parcel_income + fuel_income + p_income2 + p_income3 + p_income4 +f_srje21 + p_turnover + p_income1)/10000 万含补运输收入,
sum(ticket_income + fuel_income)/10000 万票面含油收入,
sum(ticket_income)/10000 万票面不含油收入,
sum(F_FYJE28 + F_FYJE1)/10000 万总成本,
sum(F_FYJE28)/10000 万固定成本,
sum(F_FYJE1)/10000 万变动成本,
sum(freight_income + parcel_income)/10000 万货邮收入
FROM jcdb.v_airline@dblink_yxdbnew t
WHERE (t.flight_date BETWEEN '20190701' AND '20191231'
    OR t.flight_date BETWEEN '20180701' AND '20181231')
  AND (t.departure_airport IN ('SZX') OR t.arrival_airport IN ('SZX')) --限制深圳往返航线
--   AND d_or_i = 'D' --限制国内
  AND t.flg_vr IS NULL ------剔除备降返航
  AND t.flg_cs IS NULL ------剔除取消航班
  AND substr(t.flight_no, -1) BETWEEN '0' AND '9'
  AND airline <> 'SZX-SZX'
GROUP BY to_char(to_date(t.flight_date,'yyyy-mm-dd'),'yyyymm'),
CASE WHEN t.departure_airport='SZX' THEN airline
 WHEN t.arrival_airport='SZX' AND length(airline) >7 THEN substr(airline,9,3)||'-'||substr(airline,5,3)||'-'||substr(airline,1,3)
 WHEN t.arrival_airport='SZX' AND length(airline) =7 THEN substr(airline,5,3)||'-'||substr(airline,1,3) END,
ROLLUP(CASE WHEN t.departure_airport='SZX' THEN '始发' ELSE '回程' END)
ORDER BY 日期,航线,decode(类型,'始发',1,'回程',2,'往返合计',3)