<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.its.op.dao.mapper.its.atrd.AtrdStatisticsMapper">

    <select id="findAllHourly" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.atrd.AtrdStatHourlyDto">
        SELECT LY.ARTERYID AS ATRD_ID, LY.ARTERYNAME AS ATRD_NM, LY.DIRECTION AS DRCT_CD, LY.DIRECTIONNAME AS DRCT_CD_DESC,
               LY.ARTERYLENGHT AS SECT_LNGT, LX.TRFTYPE AS TRF_TYPE,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS AVG_SPED,
               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_STAT_HH_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_STAT_HH A, TB_ATRD_RLTN_ROAD B, TB_ROAD C
                                WHERE 1=1
                                  AND A.STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                                  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
    </select>

    <select id="findAllDaily" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.atrd.AtrdStatDailyDto">
        SELECT LY.ARTERYID AS ATRD_ID, LY.ARTERYNAME AS ATRD_NM, LY.DIRECTION AS DRCT_CD, LY.DIRECTIONNAME AS DRCT_CD_DESC,
               LY.ARTERYLENGHT AS SECT_LNGT, LX.TRFTYPE AS TRF_TYPE,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS AVG_SPED,
               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,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(T24, 0, '-', ROUND(L24*3.6/T24, 0))) AS C24,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(T25, 0, '-', ROUND(L25*3.6/T25, 0))) AS C25,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(T26, 0, '-', ROUND(L26*3.6/T26, 0))) AS C26,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(T27, 0, '-', ROUND(L27*3.6/T27, 0))) AS C27,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(T28, 0, '-', ROUND(L28*3.6/T28, 0))) AS C28,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(T29, 0, '-', ROUND(L29*3.6/T29, 0))) AS C29,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(T30, 0, '-', ROUND(L30*3.6/T30, 0))) AS C30,
               DECODE(LX.ARTERYID, NULL, '-', DECODE(T31, 0, '-', ROUND(L31*3.6/T31, 0))) AS C31
        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(STATDAY, '01', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T01,
                        SUM(DECODE(STATDAY, '01', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L01,
                        SUM(DECODE(STATDAY, '02', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T02,
                        SUM(DECODE(STATDAY, '02', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L02,
                        SUM(DECODE(STATDAY, '03', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T03,
                        SUM(DECODE(STATDAY, '03', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L03,
                        SUM(DECODE(STATDAY, '04', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T04,
                        SUM(DECODE(STATDAY, '04', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L04,
                        SUM(DECODE(STATDAY, '05', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T05,
                        SUM(DECODE(STATDAY, '05', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L05,
                        SUM(DECODE(STATDAY, '06', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T06,
                        SUM(DECODE(STATDAY, '06', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L06,
                        SUM(DECODE(STATDAY, '07', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T07,
                        SUM(DECODE(STATDAY, '07', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L07,
                        SUM(DECODE(STATDAY, '08', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T08,
                        SUM(DECODE(STATDAY, '08', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L08,
                        SUM(DECODE(STATDAY, '09', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T09,
                        SUM(DECODE(STATDAY, '09', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L09,
                        SUM(DECODE(STATDAY, '10', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T10,
                        SUM(DECODE(STATDAY, '10', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L10,
                        SUM(DECODE(STATDAY, '11', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T11,
                        SUM(DECODE(STATDAY, '11', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L11,
                        SUM(DECODE(STATDAY, '12', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T12,
                        SUM(DECODE(STATDAY, '12', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L12,
                        SUM(DECODE(STATDAY, '13', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T13,
                        SUM(DECODE(STATDAY, '13', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L13,
                        SUM(DECODE(STATDAY, '14', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T14,
                        SUM(DECODE(STATDAY, '14', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L14,
                        SUM(DECODE(STATDAY, '15', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T15,
                        SUM(DECODE(STATDAY, '15', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L15,
                        SUM(DECODE(STATDAY, '16', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T16,
                        SUM(DECODE(STATDAY, '16', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L16,
                        SUM(DECODE(STATDAY, '17', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T17,
                        SUM(DECODE(STATDAY, '17', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L17,
                        SUM(DECODE(STATDAY, '18', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T18,
                        SUM(DECODE(STATDAY, '18', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L18,
                        SUM(DECODE(STATDAY, '19', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T19,
                        SUM(DECODE(STATDAY, '19', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L19,
                        SUM(DECODE(STATDAY, '20', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T20,
                        SUM(DECODE(STATDAY, '20', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L20,
                        SUM(DECODE(STATDAY, '21', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T21,
                        SUM(DECODE(STATDAY, '21', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L21,
                        SUM(DECODE(STATDAY, '22', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T22,
                        SUM(DECODE(STATDAY, '22', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L22,
                        SUM(DECODE(STATDAY, '23', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T23,
                        SUM(DECODE(STATDAY, '23', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L23,
                        SUM(DECODE(STATDAY, '24', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T24,
                        SUM(DECODE(STATDAY, '24', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L24,
                        SUM(DECODE(STATDAY, '25', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T25,
                        SUM(DECODE(STATDAY, '25', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L25,
                        SUM(DECODE(STATDAY, '26', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T26,
                        SUM(DECODE(STATDAY, '26', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L26,
                        SUM(DECODE(STATDAY, '27', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T27,
                        SUM(DECODE(STATDAY, '27', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L27,
                        SUM(DECODE(STATDAY, '28', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T28,
                        SUM(DECODE(STATDAY, '28', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L28,
                        SUM(DECODE(STATDAY, '29', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T29,
                        SUM(DECODE(STATDAY, '29', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L29,
                        SUM(DECODE(STATDAY, '30', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T30,
                        SUM(DECODE(STATDAY, '30', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L30,
                        SUM(DECODE(STATDAY, '31', DECODE(TRVTM, NULL, 0, TRVTM),   0)) AS T31,
                        SUM(DECODE(STATDAY, '31', DECODE(TRVTM, NULL, 0, LINKLEN), 0)) AS L31
                 FROM (
                          SELECT Y.ARTERYID, Y.ARTERYNAME, Y.DIRECTION, Y.ARTERYLEN, X.TRFTYPE,
                                 Y.LINKID, Y.LINKLEN, X.STATDAY, X.SPD, X.TRVTM
                          FROM (SELECT /*+ INDEX(A IX_ROAD_STAT_15M_PK) */
                                    'FSN' AS TRFTYPE,
                                    B.ATRD_ID AS ARTERYID, A.ROAD_ID AS LINKID, C.SECT_LNGT AS LINKLEN,
                                    SUBSTR(A.STAT_DT, 7, 2) AS STATDAY, A.SPED AS SPD, A.TRVL_HH AS TRVTM
                                FROM TB_ROAD_STAT_15M A, TB_ATRD_RLTN_ROAD B, TB_ROAD C
                                WHERE 1=1
                                  AND A.STAT_DT               BETWEEN #{FROM_DT} AND #{TO_DT}
                                  AND SUBSTR(A.STAT_DT, 9, 4) BETWEEN #{FROM_HM} AND #{TO_HM}
                                  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
    </select>

</mapper>
