간선도로 시간대별 통계.SQL 8.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109
  1. SELECT LY.ARTERYID, LY.ARTERYNAME, LY.DIRECTION, LY.DIRECTIONNAME, LY.ARTERYLENGHT, LX.TRFTYPE,
  2. DECODE(LX.ARTERYID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS ARTERYSPD,
  3. DECODE(LX.ARTERYID, NULL, '-', DECODE(T00, 0, '-', ROUND(L00*3.6/T00, 0))) AS C00,
  4. DECODE(LX.ARTERYID, NULL, '-', DECODE(T01, 0, '-', ROUND(L01*3.6/T01, 0))) AS C01,
  5. DECODE(LX.ARTERYID, NULL, '-', DECODE(T02, 0, '-', ROUND(L02*3.6/T02, 0))) AS C02,
  6. DECODE(LX.ARTERYID, NULL, '-', DECODE(T03, 0, '-', ROUND(L03*3.6/T03, 0))) AS C03,
  7. DECODE(LX.ARTERYID, NULL, '-', DECODE(T04, 0, '-', ROUND(L04*3.6/T04, 0))) AS C04,
  8. DECODE(LX.ARTERYID, NULL, '-', DECODE(T05, 0, '-', ROUND(L05*3.6/T05, 0))) AS C05,
  9. DECODE(LX.ARTERYID, NULL, '-', DECODE(T06, 0, '-', ROUND(L06*3.6/T06, 0))) AS C06,
  10. DECODE(LX.ARTERYID, NULL, '-', DECODE(T07, 0, '-', ROUND(L07*3.6/T07, 0))) AS C07,
  11. DECODE(LX.ARTERYID, NULL, '-', DECODE(T08, 0, '-', ROUND(L08*3.6/T08, 0))) AS C08,
  12. DECODE(LX.ARTERYID, NULL, '-', DECODE(T09, 0, '-', ROUND(L09*3.6/T09, 0))) AS C09,
  13. DECODE(LX.ARTERYID, NULL, '-', DECODE(T10, 0, '-', ROUND(L10*3.6/T10, 0))) AS C10,
  14. DECODE(LX.ARTERYID, NULL, '-', DECODE(T11, 0, '-', ROUND(L11*3.6/T11, 0))) AS C11,
  15. DECODE(LX.ARTERYID, NULL, '-', DECODE(T12, 0, '-', ROUND(L12*3.6/T12, 0))) AS C12,
  16. DECODE(LX.ARTERYID, NULL, '-', DECODE(T13, 0, '-', ROUND(L13*3.6/T13, 0))) AS C13,
  17. DECODE(LX.ARTERYID, NULL, '-', DECODE(T14, 0, '-', ROUND(L14*3.6/T14, 0))) AS C14,
  18. DECODE(LX.ARTERYID, NULL, '-', DECODE(T15, 0, '-', ROUND(L15*3.6/T15, 0))) AS C15,
  19. DECODE(LX.ARTERYID, NULL, '-', DECODE(T16, 0, '-', ROUND(L16*3.6/T16, 0))) AS C16,
  20. DECODE(LX.ARTERYID, NULL, '-', DECODE(T17, 0, '-', ROUND(L17*3.6/T17, 0))) AS C17,
  21. DECODE(LX.ARTERYID, NULL, '-', DECODE(T18, 0, '-', ROUND(L18*3.6/T18, 0))) AS C18,
  22. DECODE(LX.ARTERYID, NULL, '-', DECODE(T19, 0, '-', ROUND(L19*3.6/T19, 0))) AS C19,
  23. DECODE(LX.ARTERYID, NULL, '-', DECODE(T20, 0, '-', ROUND(L20*3.6/T20, 0))) AS C20,
  24. DECODE(LX.ARTERYID, NULL, '-', DECODE(T21, 0, '-', ROUND(L21*3.6/T21, 0))) AS C21,
  25. DECODE(LX.ARTERYID, NULL, '-', DECODE(T22, 0, '-', ROUND(L22*3.6/T22, 0))) AS C22,
  26. DECODE(LX.ARTERYID, NULL, '-', DECODE(T23, 0, '-', ROUND(L23*3.6/T23, 0))) AS C23
  27. FROM (
  28. SELECT GROUPING(ARTERYID) AS GROUPING_ARTERYID,
  29. GROUPING(DIRECTION) AS GROUPING_DIRECTION,
  30. GROUPING(TRFTYPE) AS GROUPING_TRFTYPE,
  31. CASE WHEN GROUPING(ARTERYID ) = 1 THEN 'µµ·Î' ELSE ARTERYID END AS GROUP_ARTERYID,
  32. CASE WHEN GROUPING(DIRECTION) = 1 THEN '¹æÇâ' ELSE DIRECTION END AS GROUP_DIRECTION,
  33. CASE WHEN GROUPING(TRFTYPE) = 1 THEN '¼Ò°è' ELSE TRFTYPE END AS GROUP_TRFTYPE,
  34. ARTERYID, DIRECTION, TRFTYPE,
  35. SUM(DECODE(TRVTM, NULL, 0, LINKLEN)) TLL,
  36. SUM(DECODE(TRVTM, NULL, 0, TRVTM )) TTM,
  37. SUM(DECODE(STATHOUR, '00', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T00,
  38. SUM(DECODE(STATHOUR, '00', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L00,
  39. SUM(DECODE(STATHOUR, '01', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T01,
  40. SUM(DECODE(STATHOUR, '01', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L01,
  41. SUM(DECODE(STATHOUR, '02', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T02,
  42. SUM(DECODE(STATHOUR, '02', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L02,
  43. SUM(DECODE(STATHOUR, '03', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T03,
  44. SUM(DECODE(STATHOUR, '03', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L03,
  45. SUM(DECODE(STATHOUR, '04', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T04,
  46. SUM(DECODE(STATHOUR, '04', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L04,
  47. SUM(DECODE(STATHOUR, '05', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T05,
  48. SUM(DECODE(STATHOUR, '05', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L05,
  49. SUM(DECODE(STATHOUR, '06', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T06,
  50. SUM(DECODE(STATHOUR, '06', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L06,
  51. SUM(DECODE(STATHOUR, '07', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T07,
  52. SUM(DECODE(STATHOUR, '07', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L07,
  53. SUM(DECODE(STATHOUR, '08', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T08,
  54. SUM(DECODE(STATHOUR, '08', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L08,
  55. SUM(DECODE(STATHOUR, '09', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T09,
  56. SUM(DECODE(STATHOUR, '09', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L09,
  57. SUM(DECODE(STATHOUR, '10', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T10,
  58. SUM(DECODE(STATHOUR, '10', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L10,
  59. SUM(DECODE(STATHOUR, '11', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T11,
  60. SUM(DECODE(STATHOUR, '11', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L11,
  61. SUM(DECODE(STATHOUR, '12', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T12,
  62. SUM(DECODE(STATHOUR, '12', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L12,
  63. SUM(DECODE(STATHOUR, '13', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T13,
  64. SUM(DECODE(STATHOUR, '13', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L13,
  65. SUM(DECODE(STATHOUR, '14', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T14,
  66. SUM(DECODE(STATHOUR, '14', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L14,
  67. SUM(DECODE(STATHOUR, '15', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T15,
  68. SUM(DECODE(STATHOUR, '15', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L15,
  69. SUM(DECODE(STATHOUR, '16', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T16,
  70. SUM(DECODE(STATHOUR, '16', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L16,
  71. SUM(DECODE(STATHOUR, '17', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T17,
  72. SUM(DECODE(STATHOUR, '17', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L17,
  73. SUM(DECODE(STATHOUR, '18', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T18,
  74. SUM(DECODE(STATHOUR, '18', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L18,
  75. SUM(DECODE(STATHOUR, '19', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T19,
  76. SUM(DECODE(STATHOUR, '19', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L19,
  77. SUM(DECODE(STATHOUR, '20', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T20,
  78. SUM(DECODE(STATHOUR, '20', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L20,
  79. SUM(DECODE(STATHOUR, '21', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T21,
  80. SUM(DECODE(STATHOUR, '21', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L21,
  81. SUM(DECODE(STATHOUR, '22', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T22,
  82. SUM(DECODE(STATHOUR, '22', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L22,
  83. SUM(DECODE(STATHOUR, '23', DECODE(TRVTM, NULL, 0, TRVTM), 0)) AS T23,
  84. SUM(DECODE(STATHOUR, '23', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L23
  85. FROM (
  86. SELECT Y.ARTERYID, Y.ARTERYNAME, Y.DIRECTION, Y.ARTERYLEN, X.TRFTYPE,
  87. Y.LINKID, Y.LINKLEN, X.STATHOUR, X.SPD, X.TRVTM
  88. FROM (SELECT /*+ INDEX(A IX_ROAD_HH_STAT_PK) */
  89. 'FSN' AS TRFTYPE,
  90. 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
  91. FROM TB_ROAD_HH_STAT A, TB_ATRD_ROAD_RLTN B, TB_ROAD C
  92. WHERE 1=1
  93. AND A.STAT_DT BETWEEN '20170201000000' AND '20170201235959'
  94. AND A.ROAD_ID = B.ROAD_ID
  95. AND B.ROAD_ID = C.ROAD_ID
  96. ) X,
  97. VW_ATRD_ROAD_RLTN Y
  98. WHERE Y.LINKID = X.LINKID
  99. )
  100. GROUP BY ROLLUP (ARTERYID, DIRECTION, TRFTYPE)
  101. HAVING GROUPING(TRFTYPE) = 0) LX,
  102. VW_ATRD LY
  103. WHERE LY.ARTERYID = LX.ARTERYID(+)
  104. AND LY.DIRECTION = LX.DIRECTION(+)
  105. ORDER BY LY.ARTERYNAME, LY.DIRECTION, LX.TRFTYPE