ITS-OP/SRC/PLUGIN/13.TRAFFIC-ANALYSIS-SUPPORT/TAS0020M___/간선도로 일별 통계.SQL

SELECT LY.ARTERYID, LY.ARTERYNAME, LY.DIRECTION, LY.DIRECTIONNAME, LY.ARTERYLENGHT, LX.TRFTYPE,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS ARTERYSPD,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T01, 0, '-', ROUND(L01*3.6/T01, 0))) AS C01,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T02, 0, '-', ROUND(L02*3.6/T02, 0))) AS C02,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T03, 0, '-', ROUND(L03*3.6/T03, 0))) AS C03,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T04, 0, '-', ROUND(L04*3.6/T04, 0))) AS C04,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T05, 0, '-', ROUND(L05*3.6/T05, 0))) AS C05,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T06, 0, '-', ROUND(L06*3.6/T06, 0))) AS C06,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T07, 0, '-', ROUND(L07*3.6/T07, 0))) AS C07,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T08, 0, '-', ROUND(L08*3.6/T08, 0))) AS C08,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T09, 0, '-', ROUND(L09*3.6/T09, 0))) AS C09,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T10, 0, '-', ROUND(L10*3.6/T10, 0))) AS C10,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T11, 0, '-', ROUND(L11*3.6/T11, 0))) AS C11,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T12, 0, '-', ROUND(L12*3.6/T12, 0))) AS C12,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T13, 0, '-', ROUND(L13*3.6/T13, 0))) AS C13,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T14, 0, '-', ROUND(L14*3.6/T14, 0))) AS C14,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T15, 0, '-', ROUND(L15*3.6/T15, 0))) AS C15,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T16, 0, '-', ROUND(L16*3.6/T16, 0))) AS C16,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T17, 0, '-', ROUND(L17*3.6/T17, 0))) AS C17,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T18, 0, '-', ROUND(L18*3.6/T18, 0))) AS C18,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T19, 0, '-', ROUND(L19*3.6/T19, 0))) AS C19,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T20, 0, '-', ROUND(L20*3.6/T20, 0))) AS C20,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T21, 0, '-', ROUND(L21*3.6/T21, 0))) AS C21,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T22, 0, '-', ROUND(L22*3.6/T22, 0))) AS C22,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T23, 0, '-', ROUND(L23*3.6/T23, 0))) AS C23,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T24, 0, '-', ROUND(L24*3.6/T24, 0))) AS C24,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T25, 0, '-', ROUND(L25*3.6/T25, 0))) AS C25,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T26, 0, '-', ROUND(L26*3.6/T26, 0))) AS C26,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T27, 0, '-', ROUND(L27*3.6/T27, 0))) AS C27,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T28, 0, '-', ROUND(L28*3.6/T28, 0))) AS C28,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T29, 0, '-', ROUND(L29*3.6/T29, 0))) AS C29,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T30, 0, '-', ROUND(L30*3.6/T30, 0))) AS C30,
       DECODE(LX.ARTERYID, NULL, '-', DECODE(T31, 0, '-', ROUND(L31*3.6/T31, 0))) AS C31 
  FROM (
SELECT GROUPING(ARTERYID)  AS GROUPING_ARTERYID,                                      
               GROUPING(DIRECTION) AS GROUPING_DIRECTION, 
               GROUPING(TRFTYPE)   AS GROUPING_TRFTYPE, 
               CASE WHEN GROUPING(ARTERYID ) = 1 THEN 'µµ·Î' ELSE ARTERYID  END AS GROUP_ARTERYID,                                                              
               CASE WHEN GROUPING(DIRECTION) = 1 THEN '¹æÇâ' ELSE DIRECTION END AS GROUP_DIRECTION, 
               CASE WHEN GROUPING(TRFTYPE)   = 1 THEN '¼Ò°è' ELSE TRFTYPE   END AS GROUP_TRFTYPE, 
               ARTERYID, DIRECTION, TRFTYPE,
               SUM(DECODE(TRVTM, NULL, 0, LINKLEN)) TLL, 
               SUM(DECODE(TRVTM, NULL, 0, TRVTM  )) TTM,
               SUM(DECODE(STATDAY, '01', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T01,
               SUM(DECODE(STATDAY, '01', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L01,
               SUM(DECODE(STATDAY, '02', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T02,
               SUM(DECODE(STATDAY, '02', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L02,
               SUM(DECODE(STATDAY, '03', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T03,
               SUM(DECODE(STATDAY, '03', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L03,
               SUM(DECODE(STATDAY, '04', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T04,
               SUM(DECODE(STATDAY, '04', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L04,
               SUM(DECODE(STATDAY, '05', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T05,
               SUM(DECODE(STATDAY, '05', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L05,
               SUM(DECODE(STATDAY, '06', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T06,
               SUM(DECODE(STATDAY, '06', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L06,
               SUM(DECODE(STATDAY, '07', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T07,
               SUM(DECODE(STATDAY, '07', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L07,
               SUM(DECODE(STATDAY, '08', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T08,
               SUM(DECODE(STATDAY, '08', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L08,
               SUM(DECODE(STATDAY, '09', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T09,
               SUM(DECODE(STATDAY, '09', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L09,
               SUM(DECODE(STATDAY, '10', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T10,
               SUM(DECODE(STATDAY, '10', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L10,
               SUM(DECODE(STATDAY, '11', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T11,
               SUM(DECODE(STATDAY, '11', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L11,
               SUM(DECODE(STATDAY, '12', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T12,
               SUM(DECODE(STATDAY, '12', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L12,
               SUM(DECODE(STATDAY, '13', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T13,
               SUM(DECODE(STATDAY, '13', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L13,
               SUM(DECODE(STATDAY, '14', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T14,
               SUM(DECODE(STATDAY, '14', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L14,
               SUM(DECODE(STATDAY, '15', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T15,
               SUM(DECODE(STATDAY, '15', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L15,
               SUM(DECODE(STATDAY, '16', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T16,
               SUM(DECODE(STATDAY, '16', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L16,
               SUM(DECODE(STATDAY, '17', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T17,
               SUM(DECODE(STATDAY, '17', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L17,
               SUM(DECODE(STATDAY, '18', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T18,
               SUM(DECODE(STATDAY, '18', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L18,
               SUM(DECODE(STATDAY, '19', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T19,
               SUM(DECODE(STATDAY, '19', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L19,
               SUM(DECODE(STATDAY, '20', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T20,
               SUM(DECODE(STATDAY, '20', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L20,
               SUM(DECODE(STATDAY, '21', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T21,
               SUM(DECODE(STATDAY, '21', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L21,
               SUM(DECODE(STATDAY, '22', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T22,
               SUM(DECODE(STATDAY, '22', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L22,
               SUM(DECODE(STATDAY, '23', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T23,
               SUM(DECODE(STATDAY, '23', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L23,
               SUM(DECODE(STATDAY, '24', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T24,
               SUM(DECODE(STATDAY, '24', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L24,
               SUM(DECODE(STATDAY, '25', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T25,
               SUM(DECODE(STATDAY, '25', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L25,
               SUM(DECODE(STATDAY, '26', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T26,
               SUM(DECODE(STATDAY, '26', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L26,
               SUM(DECODE(STATDAY, '27', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T27,
               SUM(DECODE(STATDAY, '27', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L27,
               SUM(DECODE(STATDAY, '28', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T28,
               SUM(DECODE(STATDAY, '28', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L28,
               SUM(DECODE(STATDAY, '29', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T29,
               SUM(DECODE(STATDAY, '29', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L29,
               SUM(DECODE(STATDAY, '30', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T30,
               SUM(DECODE(STATDAY, '30', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L30,
               SUM(DECODE(STATDAY, '31', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T31,
               SUM(DECODE(STATDAY, '31', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L31 
          FROM (
SELECT Y.ARTERYID, Y.ARTERYNAME, Y.DIRECTION, Y.ARTERYLEN, X.TRFTYPE,
       Y.LINKID, Y.LINKLEN, X.STATDAY, X.SPD, X.TRVTM
  FROM (SELECT /*+ INDEX(A IX_ROAD_15M_STAT_PK) */
               'FSN' AS TRFTYPE,
               B.ATRD_ID AS ARTERYID, A.ROAD_ID AS LINKID, C.SECT_LNGT AS LINKLEN, SUBSTR(A.STAT_DT, 7, 2) AS STATDAY, A.SPED AS SPD, A.TRVL_HH AS TRVTM
          FROM TB_ROAD_15M_STAT A, TB_ATRD_ROAD_RLTN B, TB_ROAD C
         WHERE 1=1
           AND A.STAT_DT BETWEEN '20170201000000' AND '20170231235959'
           AND SUBSTR(A.STAT_DT, 9, 4) BETWEEN '0400' AND '0600'
           AND A.ROAD_ID = B.ROAD_ID
           AND B.ROAD_ID = C.ROAD_ID
       ) X,
       VW_ATRD_ROAD_RLTN Y
 WHERE Y.LINKID = X.LINKID 
)
          GROUP BY ROLLUP (ARTERYID, DIRECTION, TRFTYPE) 
         HAVING GROUPING(TRFTYPE) = 0) LX,
       VW_ATRD LY
 WHERE LY.ARTERYID  = LX.ARTERYID(+)
   AND LY.DIRECTION = LX.DIRECTION(+)
 ORDER BY LY.ARTERYNAME, LY.DIRECTION, LX.TRFTYPE