利用大数据智能更正瑕疵数据的应用样例

-- Created on 2018-09-27 by 三界
declare
  -- Local variables here
  VSDATE    DATE;
  VEDATE    DATE;
  VRealLine varchar2(24);
  vFlagJoin BOOLEAN;
begin
  -- Test statements here   liao xiaohui
  VSDATE := TO_DATE('2017-01-01', 'YYYY-MM-DD');
  VEDATE := TO_DATE('2018-01-01', 'YYYY-MM-DD');
  --应用说明：根据已有大量航班数据，计算航班的实际航程路线,并找出航班的计划航线代码。
  --目标：提升系统智能处理能力，减少基础数据维护量。

  --表F_FLIGHT字段注释：
  --FLIGHT_DATE:航班日期
  --FLIGHTNO:航班号
  --REALCODE:实飞航程代码.
  --LINECODE:计划航线代码.
  --DEPSTN:出发机场代码
  --ARRSTN:到达机场代码
  --OUT_TIME:撤轮档时间
  --实飞航程代码/计划航线代码，初始默认值：出发机场代码-到达机场代码，For Example: HAK-HKG

  --计算存在多航节航班的实飞航程代码.(用于计算存在返航、备降、多航节航班的实飞航程，当前航线网络下此类航班占比不到4%)
  FOR RL IN (SELECT *
               FROM ((SELECT FLIGHT_ID, FLIGHT_DATE, FLIGHTNO, OUT_TIME, DEPSTN, ARRSTN,
                              LAG(ARRSTN, 1, NULL) OVER(PARTITION BY FLIGHT_DATE, FLIGHTNO ORDER BY OUT_TIME ASC) AS PRIOR_ARR,
                              LEAD(DEPSTN, 1, NULL) OVER(PARTITION BY FLIGHT_DATE, FLIGHTNO ORDER BY OUT_TIME ASC) AS NEXT_DEP,
                              ROW_NUMBER() OVER(PARTITION BY FLIGHT_DATE, FLIGHTNO ORDER BY OUT_TIME ASC) AS ROWNO,
                              COUNT(*) OVER(PARTITION BY FLIGHT_DATE, FLIGHTNO) AS CNT
                         FROM F_FLIGHT F
                        WHERE FLIGHT_DATE BETWEEN VSDATE AND VEDATE
                        ORDER BY FLIGHT_DATE, FLIGHTNO, OUT_TIME)) T
              WHERE CNT > 1 --航节总数大于1的情形.
             ) LOOP
    --计算实飞航程代码               
    if RL.ROWNO = 1 then
      VRealLine := RL.DEPSTN;
      --判断飞行点是否衔接,验证数据完整性
      vFlagJoin := (RL.Arrstn = RL.Next_Dep);
    elsif RL.ROWNO = RL.CNT then
      VRealLine := VRealLine || '-' || RL.DEPSTN || '-' || RL.ARRSTN;
      vFlagJoin := (RL.DEPSTN = RL.PRIOR_ARR) and vFlagJoin;
    else
      VRealLine := VRealLine || '-' || RL.DEPSTN;
      vFlagJoin := (RL.DEPSTN = RL.PRIOR_ARR) and vFlagJoin;
    end if;
 
    --循环到最后一节航班时
    IF (RL.ROWNO = RL.CNT) AND vFlagJoin then
      --维护实飞航程                         
      UPDATE F_FLIGHT
         SET REALCODE = VRealLine
       WHERE FLIGHT_DATE = RL.DATOP
         AND FLIGHTNO = RL.FLTID
         AND REALCODE <> VRealLine;
    END IF;
  END LOOP;
  COMMIT;

  --根据大数据推算航班的计划航线代码(理论依据：大量正常航班的实飞航程代码，就是航班的计划航线代码)
  For LN in (SELECT *
               FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY YYYYMM, FLTID ORDER BY CNT DESC) AS ROWNO,
                             COUNT(*) OVER(PARTITION BY YYYYMM, FLTID) AS ROWCNT
                        FROM (SELECT TO_Char(FLIGHT_DATE, 'yyyyMM') AS YYYYMM, SUBSTR(FLIGHTNO, 1, 6) AS FLTID,
                                      REALCODE, COUNT(*) AS CNT, MIN(FLIGHT_DATE) AS STARTDATE,
                                      MAX(FLIGHT_DATE) AS LASTDATE
                                 FROM F_FLIGHT F
                                WHERE FLIGHT_DATE BETWEEN VSDATE AND VEDATE
                                GROUP BY TO_Char(FLIGHT_DATE, 'yyyyMM'), SUBSTR(FLIGHTNO, 1, 6), REALCODE) T) TT
              WHERE ROWCNT > 1
                AND CNT > 4
                AND ROWNO = 1) loop
    UPDATE F_FLIGHT
       SET LINECODE = LN.REALCODE
     WHERE INSTR(FLIGHTNO, LN.FLTID) > 0
       AND TO_Char(FLIGHT_DATE, 'yyyyMM') = LN.YYYYMM
       AND FLIGHT_DATE BETWEEN LN.STARTDATE AND LN.LASTDATE
       AND LINECODE <> LN.REALCODE;
  end loop;
  COMMIT;
end;