ITS-OP/SRC/PLUGIN/13.TRAFFIC-ANALYSIS-SUPPORT/TAS0030M/패턴정보조회-전체SQL.sql

SELECT L.*, M.*, DECODE(L.DRCT_CD, '0', '상행', '하행') AS DRCT_CD_NM
  FROM (SELECT X.ATRD_ID, X.ATRD_NM, X.DRCT_CD, X.DRCT_NM, 
               Y.ROAD_ORD, Y.IFSC_ORD, Y.IFSC_ID, Y.IFSC_NM, Y.STRT_NM, Y.END_NM, Y.SECT_LNGT
          FROM TB_ATRD X, 
               (SELECT A.ATRD_ID, A.ORD AS ROAD_ORD, A.ROAD_ID, B.IFSC_ID, B.ORD AS IFSC_ORD, C.IFSC_NM, C.STRT_NM, C.END_NM, C.SECT_LNGT
                  FROM TB_ATRD_ROAD_RLTN A, TB_ROAD_IFSC_RLTN B, TB_IFSC C
                 WHERE A.ROAD_ID = B.ROAD_ID
                   AND B.IFSC_ID = C.IFSC_ID
                   AND A.ATRD_ID IN (SELECT ATRD_ID FROM TB_ATRD WHERE TRIM(ATRD_NM) = '국도46호선')
                ) Y
         WHERE X.ATRD_ID = Y.ATRD_ID
           AND X.ATRD_ID IN (SELECT ATRD_ID FROM TB_ATRD WHERE TRIM(ATRD_NM) = '국도46호선')
       ) L, 
       (SELECT IFSC_ID AS IFSC_ID_H, 
               SUM(DECODE(STAT_HM, '0000', DECODE(SPED, NULL,      0, SPED),      0)) AS S00,              
               MAX(DECODE(STAT_HM, '0000', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G00,              
               SUM(DECODE(STAT_HM, '0100', DECODE(SPED, NULL,      0, SPED),      0)) AS S01,              
               MAX(DECODE(STAT_HM, '0100', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G01,              
               SUM(DECODE(STAT_HM, '0200', DECODE(SPED, NULL,      0, SPED),      0)) AS S02,              
               MAX(DECODE(STAT_HM, '0200', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G02,              
               SUM(DECODE(STAT_HM, '0300', DECODE(SPED, NULL,      0, SPED),      0)) AS S03,              
               MAX(DECODE(STAT_HM, '0300', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G03,              
               SUM(DECODE(STAT_HM, '0400', DECODE(SPED, NULL,      0, SPED),      0)) AS S04,              
               MAX(DECODE(STAT_HM, '0400', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G04,              
               SUM(DECODE(STAT_HM, '0500', DECODE(SPED, NULL,      0, SPED),      0)) AS S05,              
               MAX(DECODE(STAT_HM, '0500', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G05,              
               SUM(DECODE(STAT_HM, '0600', DECODE(SPED, NULL,      0, SPED),      0)) AS S06,              
               MAX(DECODE(STAT_HM, '0600', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G06,              
               SUM(DECODE(STAT_HM, '0700', DECODE(SPED, NULL,      0, SPED),      0)) AS S07,              
               MAX(DECODE(STAT_HM, '0700', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G07,              
               SUM(DECODE(STAT_HM, '0800', DECODE(SPED, NULL,      0, SPED),      0)) AS S08,              
               MAX(DECODE(STAT_HM, '0800', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G08,              
               SUM(DECODE(STAT_HM, '0900', DECODE(SPED, NULL,      0, SPED),      0)) AS S09,              
               MAX(DECODE(STAT_HM, '0900', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G09,              
               SUM(DECODE(STAT_HM, '1000', DECODE(SPED, NULL,      0, SPED),      0)) AS S10,              
               MAX(DECODE(STAT_HM, '1000', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G10,              
               SUM(DECODE(STAT_HM, '1100', DECODE(SPED, NULL,      0, SPED),      0)) AS S11,              
               MAX(DECODE(STAT_HM, '1100', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G11,              
               SUM(DECODE(STAT_HM, '1200', DECODE(SPED, NULL,      0, SPED),      0)) AS S12,              
               MAX(DECODE(STAT_HM, '1200', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G12,              
               SUM(DECODE(STAT_HM, '1300', DECODE(SPED, NULL,      0, SPED),      0)) AS S13,              
               MAX(DECODE(STAT_HM, '1300', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G13,              
               SUM(DECODE(STAT_HM, '1400', DECODE(SPED, NULL,      0, SPED),      0)) AS S14,              
               MAX(DECODE(STAT_HM, '1400', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G14,              
               SUM(DECODE(STAT_HM, '1500', DECODE(SPED, NULL,      0, SPED),      0)) AS S15,              
               MAX(DECODE(STAT_HM, '1500', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G15,              
               SUM(DECODE(STAT_HM, '1600', DECODE(SPED, NULL,      0, SPED),      0)) AS S16,              
               MAX(DECODE(STAT_HM, '1600', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G16,              
               SUM(DECODE(STAT_HM, '1700', DECODE(SPED, NULL,      0, SPED),      0)) AS S17,              
               MAX(DECODE(STAT_HM, '1700', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G17,              
               SUM(DECODE(STAT_HM, '1800', DECODE(SPED, NULL,      0, SPED),      0)) AS S18,              
               MAX(DECODE(STAT_HM, '1800', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G18,              
               SUM(DECODE(STAT_HM, '1900', DECODE(SPED, NULL,      0, SPED),      0)) AS S19,              
               MAX(DECODE(STAT_HM, '1900', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G19,              
               SUM(DECODE(STAT_HM, '2000', DECODE(SPED, NULL,      0, SPED),      0)) AS S20,              
               MAX(DECODE(STAT_HM, '2000', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G20,              
               SUM(DECODE(STAT_HM, '2100', DECODE(SPED, NULL,      0, SPED),      0)) AS S21,              
               MAX(DECODE(STAT_HM, '2100', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G21,              
               SUM(DECODE(STAT_HM, '2200', DECODE(SPED, NULL,      0, SPED),      0)) AS S22,              
               MAX(DECODE(STAT_HM, '2200', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G22,              
               SUM(DECODE(STAT_HM, '2300', DECODE(SPED, NULL,      0, SPED),      0)) AS S23,              
               MAX(DECODE(STAT_HM, '2300', DECODE(GRAD, NULL, 'LTC0', GRAD), 'LTC0')) AS G23               
          FROM (          
                SELECT A.IFSC_ID, A.STAT_HM, A.SPED, A.TRVL_HH, NVL(B.CMTR_GRAD_CD, 'LTC0') AS GRAD
                  FROM TB_IFSC_HH_PTRN   A,
                       TB_CMTR_GRAD_CLSF B, 
                       TB_IFSC           C
                 WHERE A.STAT_YM = '201801'
                   AND A.DAY_TYPE_CD = 'DTW1'
                   AND A.IFSC_ID IN (SELECT B.IFSC_ID AS IFSC_ID
                                       FROM TB_ATRD_ROAD_RLTN A, TB_ROAD_IFSC_RLTN B, TB_IFSC C
                                      WHERE A.ROAD_ID = B.ROAD_ID
                                        AND B.IFSC_ID = C.IFSC_ID
                                        AND A.ATRD_ID  IN (SELECT ATRD_ID FROM TB_ATRD WHERE TRIM(ATRD_NM) = '국도46호선')
                                      GROUP BY B.IFSC_ID)
                   AND A.IFSC_ID      = C.IFSC_ID
                   AND B.SECT_GRAD_CD = C.SECT_GRAD_CD
                   AND (A.SPED >= B.LWST_TRVL_SPED AND A.SPED <= B.HGHS_TRVL_SPED)
               )
         GROUP BY IFSC_ID
       ) M
 WHERE L.IFSC_ID = M.IFSC_ID_H
 ORDER BY L.ATRD_ID, L.DRCT_CD, L.ROAD_ORD, L.IFSC_ORD