패턴데이터 분석.sql 4.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475
  1. SELECT MAX(LINK_ID) LINK_ID, MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,
  2. MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM) STAT_HM,
  3. MAX(DECODE(STAT_YM, '201403', TFVL, 0)) TFVL_A, MAX(DECODE(STAT_YM, '201403', SPED, 0)) SPED_A,
  4. MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A,
  5. MAX(DECODE(STAT_YM, '201404', TFVL, 0)) TFVL_B, MAX(DECODE(STAT_YM, '201404', SPED, 0)) SPED_B,
  6. MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B
  7. FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,
  8. B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
  9. FROM (SELECT T2.LINK_ID, T2.F_NODE, T2.T_NODE, T2.LINK_LENG, T3.NODE_NAME||'-'||T4.NODE_NAME SECT_NAME, T2.SECT_GRAD_CD
  10. FROM TB_LINK T2, TB_NODE T3, TB_NODE T4
  11. WHERE T2.LINK_ID = '3850428200'
  12. AND T2.F_NODE = T3.NODE_ID
  13. AND T2.T_NODE = T4.NODE_ID
  14. ) A,
  15. (SELECT STAT_YM, LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
  16. FROM TB_LINK_15M_PTRN --
  17. WHERE LINK_ID = '3850428200'
  18. AND STAT_YM IN( '201403', '201404' )
  19. AND STAT_HM BETWEEN '2200' AND '2359'
  20. AND DAY_TYPE_CD = 'DTW7'
  21. ) B
  22. WHERE A.LINK_ID = B.LINK_ID
  23. )
  24. GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
  25. ORDER BY STAT_HM
  26. SELECT MAX(LINK_ID) LINK_ID, MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,
  27. MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM) STAT_HM,
  28. MAX(DECODE(STAT_YM, '201403', TFVL, 0)) TFVL_A, MAX(DECODE(STAT_YM, '201403', SPED, 0)) SPED_A,
  29. MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A,
  30. MAX(DECODE(STAT_YM, '201404', TFVL, 0)) TFVL_B, MAX(DECODE(STAT_YM, '201404', SPED, 0)) SPED_B,
  31. MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B
  32. FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,
  33. B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
  34. FROM (SELECT T2.IFSC_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD
  35. FROM TB_IFSC T2
  36. WHERE T2.IFSC_ID = '3850428200'
  37. ) A,
  38. (SELECT STAT_YM, IFSC_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
  39. FROM TB_IFSC_15M_PTRN --
  40. WHERE IFSC_ID = '3850428200'
  41. AND STAT_YM IN( '201403', '201404' )
  42. AND STAT_HM BETWEEN '2200' AND '2359'
  43. AND DAY_TYPE_CD = 'DTW7'
  44. ) B
  45. WHERE A.LINK_ID = B.LINK_ID
  46. )
  47. GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
  48. ORDER BY STAT_HM
  49. SELECT MAX(LINK_ID) LINK_ID, MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,
  50. MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM) STAT_HM,
  51. MAX(DECODE(STAT_YM, '201403', TFVL, 0)) TFVL_A, MAX(DECODE(STAT_YM, '201403', SPED, 0)) SPED_A,
  52. MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A,
  53. MAX(DECODE(STAT_YM, '201404', TFVL, 0)) TFVL_B, MAX(DECODE(STAT_YM, '201404', SPED, 0)) SPED_B,
  54. MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B
  55. FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,
  56. B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
  57. FROM (SELECT T2.ROAD_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD
  58. FROM TB_ROAD T2
  59. WHERE T2.ROAD_ID = '3850428200'
  60. ) A,
  61. (SELECT STAT_YM, ROAD_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
  62. FROM TB_ROAD_15M_PTRN --
  63. WHERE ROAD_ID = '3850428200'
  64. AND STAT_YM IN( '201403', '201404' )
  65. AND STAT_HM BETWEEN '2200' AND '2359'
  66. AND DAY_TYPE_CD = 'DTW7'
  67. ) B
  68. WHERE A.LINK_ID = B.LINK_ID
  69. )
  70. GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
  71. ORDER BY STAT_HM