123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109 |
- 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(T00, 0, '-', ROUND(L00*3.6/T00, 0))) AS C00,
- 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
- 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(STATHOUR, '00', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T00,
- SUM(DECODE(STATHOUR, '00', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L00,
- SUM(DECODE(STATHOUR, '01', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T01,
- SUM(DECODE(STATHOUR, '01', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L01,
- SUM(DECODE(STATHOUR, '02', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T02,
- SUM(DECODE(STATHOUR, '02', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L02,
- SUM(DECODE(STATHOUR, '03', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T03,
- SUM(DECODE(STATHOUR, '03', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L03,
- SUM(DECODE(STATHOUR, '04', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T04,
- SUM(DECODE(STATHOUR, '04', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L04,
- SUM(DECODE(STATHOUR, '05', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T05,
- SUM(DECODE(STATHOUR, '05', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L05,
- SUM(DECODE(STATHOUR, '06', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T06,
- SUM(DECODE(STATHOUR, '06', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L06,
- SUM(DECODE(STATHOUR, '07', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T07,
- SUM(DECODE(STATHOUR, '07', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L07,
- SUM(DECODE(STATHOUR, '08', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T08,
- SUM(DECODE(STATHOUR, '08', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L08,
- SUM(DECODE(STATHOUR, '09', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T09,
- SUM(DECODE(STATHOUR, '09', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L09,
- SUM(DECODE(STATHOUR, '10', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T10,
- SUM(DECODE(STATHOUR, '10', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L10,
- SUM(DECODE(STATHOUR, '11', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T11,
- SUM(DECODE(STATHOUR, '11', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L11,
- SUM(DECODE(STATHOUR, '12', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T12,
- SUM(DECODE(STATHOUR, '12', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L12,
- SUM(DECODE(STATHOUR, '13', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T13,
- SUM(DECODE(STATHOUR, '13', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L13,
- SUM(DECODE(STATHOUR, '14', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T14,
- SUM(DECODE(STATHOUR, '14', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L14,
- SUM(DECODE(STATHOUR, '15', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T15,
- SUM(DECODE(STATHOUR, '15', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L15,
- SUM(DECODE(STATHOUR, '16', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T16,
- SUM(DECODE(STATHOUR, '16', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L16,
- SUM(DECODE(STATHOUR, '17', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T17,
- SUM(DECODE(STATHOUR, '17', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L17,
- SUM(DECODE(STATHOUR, '18', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T18,
- SUM(DECODE(STATHOUR, '18', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L18,
- SUM(DECODE(STATHOUR, '19', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T19,
- SUM(DECODE(STATHOUR, '19', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L19,
- SUM(DECODE(STATHOUR, '20', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T20,
- SUM(DECODE(STATHOUR, '20', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L20,
- SUM(DECODE(STATHOUR, '21', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T21,
- SUM(DECODE(STATHOUR, '21', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L21,
- SUM(DECODE(STATHOUR, '22', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T22,
- SUM(DECODE(STATHOUR, '22', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L22,
- SUM(DECODE(STATHOUR, '23', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T23,
- SUM(DECODE(STATHOUR, '23', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L23
- FROM (
- SELECT Y.ARTERYID, Y.ARTERYNAME, Y.DIRECTION, Y.ARTERYLEN, X.TRFTYPE,
- Y.LINKID, Y.LINKLEN, X.STATHOUR, X.SPD, X.TRVTM
- FROM (SELECT /*+ INDEX(A IX_ROAD_HH_STAT_PK) */
- 'FSN' AS TRFTYPE,
- B.ATRD_ID AS ARTERYID, A.ROAD_ID AS LINKID, C.SECT_LNGT AS LINKLEN, SUBSTR(A.STAT_DT, 9, 2) AS STATHOUR, A.SPED AS SPD, A.TRVL_HH AS TRVTM
- FROM TB_ROAD_HH_STAT A, TB_ATRD_ROAD_RLTN B, TB_ROAD C
- WHERE 1=1
- AND A.STAT_DT BETWEEN '20170201000000' AND '20170201235959'
- 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
|