Home Explore Help
Register Sign In
GUMI
/
ITS_OP
2
0
Fork 0
Files Issues 0 Pull Requests 0 Wiki
Tree: 6b5755de68
Branches Tags
ITS_OP
/
SRC
/
SQL
/
ATRD_1.sql

ATRD_1.sql 8.5 KB
History Raw

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293 
  1. SELECT LX.ATRD_ID, LX.DRCT_CD, LX.DRCT_CD_NM, LX.DRCT_NM, LX.ATRD_NM, LX.ATRD_LENG,
    2. 

  2.        LX.IFSC_NM, LX.IFSC_STRT_NM, LX.IFSC_END_NM, LX.IFSC_LENG, 
    3. 

  3.        LX.LINK_ID, LX.LINK_STRT_NM, LX.LINK_END_NM, LX.LINK_LENG,
    4. 

  4.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS ARTERYSPD,
    5. 

  5.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T00, 0, '-', ROUND(L00*3.6/T00, 0))) AS C00,      
    6. 

  6.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T01, 0, '-', ROUND(L01*3.6/T01, 0))) AS C01,      
    7. 

  7.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T02, 0, '-', ROUND(L02*3.6/T02, 0))) AS C02,      
    8. 

  8.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T03, 0, '-', ROUND(L03*3.6/T03, 0))) AS C03,      
    9. 

  9.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T04, 0, '-', ROUND(L04*3.6/T04, 0))) AS C04,      
    10. 

  10.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T05, 0, '-', ROUND(L05*3.6/T05, 0))) AS C05,      
    11. 

  11.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T06, 0, '-', ROUND(L06*3.6/T06, 0))) AS C06,      
    12. 

  12.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T07, 0, '-', ROUND(L07*3.6/T07, 0))) AS C07,      
    13. 

  13.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T08, 0, '-', ROUND(L08*3.6/T08, 0))) AS C08,      
    14. 

  14.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T09, 0, '-', ROUND(L09*3.6/T09, 0))) AS C09,      
    15. 

  15.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T10, 0, '-', ROUND(L10*3.6/T10, 0))) AS C10,      
    16. 

  16.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T11, 0, '-', ROUND(L11*3.6/T11, 0))) AS C11,      
    17. 

  17.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T12, 0, '-', ROUND(L12*3.6/T12, 0))) AS C12,      
    18. 

  18.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T13, 0, '-', ROUND(L13*3.6/T13, 0))) AS C13,      
    19. 

  19.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T14, 0, '-', ROUND(L14*3.6/T14, 0))) AS C14,      
    20. 

  20.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T15, 0, '-', ROUND(L15*3.6/T15, 0))) AS C15,      
    21. 

  21.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T16, 0, '-', ROUND(L16*3.6/T16, 0))) AS C16,      
    22. 

  22.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T17, 0, '-', ROUND(L17*3.6/T17, 0))) AS C17,      
    23. 

  23.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T18, 0, '-', ROUND(L18*3.6/T18, 0))) AS C18,      
    24. 

  24.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T19, 0, '-', ROUND(L19*3.6/T19, 0))) AS C19,      
    25. 

  25.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T20, 0, '-', ROUND(L20*3.6/T20, 0))) AS C20,      
    26. 

  26.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T21, 0, '-', ROUND(L21*3.6/T21, 0))) AS C21,      
    27. 

  27.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T22, 0, '-', ROUND(L22*3.6/T22, 0))) AS C22,      
    28. 

  28.        DECODE(LX.ATRD_ID, NULL, '-', DECODE(T23, 0, '-', ROUND(L23*3.6/T23, 0))) AS C23  
    29. 

  29.   FROM MV_ATRD_LINK LX,
    30. 

  30.        (SELECT /*+ INDEX(A PK_LINK_HH_STAT) */
    31. 

  31.                B.ATRD_ID, B.IFSC_ID, B.LINK_ID,
    32. 

  32.                SUM(DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG)) TLL, 
    33. 

  33.                SUM(DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH  )) TTM, 
    34. 

  34.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '00', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T00, 
    35. 

  35.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '00', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L00, 
    36. 

  36.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '01', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T01, 
    37. 

  37.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '01', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L01, 
    38. 

  38.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '02', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T02, 
    39. 

  39.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '02', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L02, 
    40. 

  40.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '03', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T03, 
    41. 

  41.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '03', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L03, 
    42. 

  42.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '04', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T04, 
    43. 

  43.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '04', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L04, 
    44. 

  44.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '05', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T05, 
    45. 

  45.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '05', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L05, 
    46. 

  46.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '06', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T06, 
    47. 

  47.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '06', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L06, 
    48. 

  48.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '07', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T07, 
    49. 

  49.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '07', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L07, 
    50. 

  50.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '08', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T08, 
    51. 

  51.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '08', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L08, 
    52. 

  52.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '09', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T09, 
    53. 

  53.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '09', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L09, 
    54. 

  54.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '10', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T10, 
    55. 

  55.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '10', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L10, 
    56. 

  56.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '11', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T11, 
    57. 

  57.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '11', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L11, 
    58. 

  58.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '12', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T12, 
    59. 

  59.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '12', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L12, 
    60. 

  60.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '13', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T13, 
    61. 

  61.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '13', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L13, 
    62. 

  62.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '14', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T14, 
    63. 

  63.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '14', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L14, 
    64. 

  64.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '15', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T15, 
    65. 

  65.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '15', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L15, 
    66. 

  66.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '16', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T16, 
    67. 

  67.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '16', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L16, 
    68. 

  68.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '17', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T17, 
    69. 

  69.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '17', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L17, 
    70. 

  70.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '18', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T18, 
    71. 

  71.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '18', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L18, 
    72. 

  72.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '19', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T19, 
    73. 

  73.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '19', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L19, 
    74. 

  74.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '20', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T20, 
    75. 

  75.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '20', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L20, 
    76. 

  76.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '21', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T21, 
    77. 

  77.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '21', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L21, 
    78. 

  78.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '22', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T22, 
    79. 

  79.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '22', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L22, 
    80. 

  80.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '23', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T23, 
    81. 

  81.                SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '23', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L23  
    82. 

  82.           FROM TB_LINK_HH_STAT A,
    83. 

  83.                MV_ATRD_LINK    B
    84. 

  84.          WHERE 1=1
    85. 

  85.            AND A.STAT_DT BETWEEN '20190501000000' AND '20190631235959'
    86. 

  86.            AND A.DAY_TYPE_CD IN ('DTW1', 'DTW2', 'DTW3', 'DTW4', 'DTW5', 'DTW6', 'DTW7')
    87. 

  87.            AND A.LINK_ID(+) = B.LINK_ID
    88. 

  88.            --AND B.ATRD_ID IN ('ATRD000100', 'ATRD000101')
    89. 

  89.          GROUP BY ROLLUP (B.ATRD_ID, B.IFSC_ID, B.LINK_ID)
    90. 

  90.         ) LY
    91. 

  91.   WHERE LX.ATRD_ID = LY.ATRD_ID
    92. 

  92.     AND LX.LINK_ID = LY.LINK_ID
    93. 

  93.   ORDER BY LX.ATRD_ID, LX.DRCT_CD, LX.LINK_SEQ
    94.