123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475 |
- SELECT MAX(LINK_ID) LINK_ID, MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,
- MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM) STAT_HM,
- MAX(DECODE(STAT_YM, '201403', TFVL, 0)) TFVL_A, MAX(DECODE(STAT_YM, '201403', SPED, 0)) SPED_A,
- MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A,
- MAX(DECODE(STAT_YM, '201404', TFVL, 0)) TFVL_B, MAX(DECODE(STAT_YM, '201404', SPED, 0)) SPED_B,
- MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B
- FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,
- B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
- 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
- FROM TB_LINK T2, TB_NODE T3, TB_NODE T4
- WHERE T2.LINK_ID = '3850428200'
- AND T2.F_NODE = T3.NODE_ID
- AND T2.T_NODE = T4.NODE_ID
- ) A,
- (SELECT STAT_YM, LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
- FROM TB_LINK_15M_PTRN --
- WHERE LINK_ID = '3850428200'
- AND STAT_YM IN( '201403', '201404' )
- AND STAT_HM BETWEEN '2200' AND '2359'
- AND DAY_TYPE_CD = 'DTW7'
- ) B
- WHERE A.LINK_ID = B.LINK_ID
- )
- GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
- ORDER BY STAT_HM
- SELECT MAX(LINK_ID) LINK_ID, MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,
- MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM) STAT_HM,
- MAX(DECODE(STAT_YM, '201403', TFVL, 0)) TFVL_A, MAX(DECODE(STAT_YM, '201403', SPED, 0)) SPED_A,
- MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A,
- MAX(DECODE(STAT_YM, '201404', TFVL, 0)) TFVL_B, MAX(DECODE(STAT_YM, '201404', SPED, 0)) SPED_B,
- MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B
- FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,
- B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
- FROM (SELECT T2.IFSC_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD
- FROM TB_IFSC T2
- WHERE T2.IFSC_ID = '3850428200'
- ) A,
- (SELECT STAT_YM, IFSC_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
- FROM TB_IFSC_15M_PTRN --
- WHERE IFSC_ID = '3850428200'
- AND STAT_YM IN( '201403', '201404' )
- AND STAT_HM BETWEEN '2200' AND '2359'
- AND DAY_TYPE_CD = 'DTW7'
- ) B
- WHERE A.LINK_ID = B.LINK_ID
- )
- GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
- ORDER BY STAT_HM
- SELECT MAX(LINK_ID) LINK_ID, MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,
- MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM) STAT_HM,
- MAX(DECODE(STAT_YM, '201403', TFVL, 0)) TFVL_A, MAX(DECODE(STAT_YM, '201403', SPED, 0)) SPED_A,
- MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A,
- MAX(DECODE(STAT_YM, '201404', TFVL, 0)) TFVL_B, MAX(DECODE(STAT_YM, '201404', SPED, 0)) SPED_B,
- MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B
- FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,
- B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
- FROM (SELECT T2.ROAD_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD
- FROM TB_ROAD T2
- WHERE T2.ROAD_ID = '3850428200'
- ) A,
- (SELECT STAT_YM, ROAD_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
- FROM TB_ROAD_15M_PTRN --
- WHERE ROAD_ID = '3850428200'
- AND STAT_YM IN( '201403', '201404' )
- AND STAT_HM BETWEEN '2200' AND '2359'
- AND DAY_TYPE_CD = 'DTW7'
- ) B
- WHERE A.LINK_ID = B.LINK_ID
- )
- GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
- ORDER BY STAT_HM
|