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