ITS-OP/SRC/PLUGIN/08.VDS/VDSS020M/시간대별 VDS-검지기 교통량 통계.SQL

SELECT LY.CTLR_MNGM_NMBR, LY.VDS_CTLR_ID, LY.LCTN, LY.DTCT_NMBR, LY.ISTL_LANE, LY.VDS_DTCT_NM,
       LX.TRFTYPE,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(TOTTFVL, 0, '-', TOTTFVL)) AS TOTTFVL,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(AVGTFVL, 0, '-', AVGTFVL)) AS AVGTFVL, CNT,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L00, NULL, '-', L00)) AS C00,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L01, NULL, '-', L01)) AS C01,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L02, NULL, '-', L02)) AS C02,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L03, NULL, '-', L03)) AS C03,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L04, NULL, '-', L04)) AS C04,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L05, NULL, '-', L05)) AS C05,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L06, NULL, '-', L06)) AS C06,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L07, NULL, '-', L07)) AS C07,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L08, NULL, '-', L08)) AS C08,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L09, NULL, '-', L09)) AS C09,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L10, NULL, '-', L10)) AS C10,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L11, NULL, '-', L11)) AS C11,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L12, NULL, '-', L12)) AS C12,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L13, NULL, '-', L13)) AS C13,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L14, NULL, '-', L14)) AS C14,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L15, NULL, '-', L15)) AS C15,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L16, NULL, '-', L16)) AS C16,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L17, NULL, '-', L17)) AS C17,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L18, NULL, '-', L18)) AS C18,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L19, NULL, '-', L19)) AS C19,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L20, NULL, '-', L20)) AS C20,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L21, NULL, '-', L21)) AS C21,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L22, NULL, '-', L22)) AS C22,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L23, NULL, '-', L23)) AS C23 
  FROM (
        SELECT GROUPING(DTCT_NMBR) AS GROUPING_DTCT_NMBR,                                      
               GROUPING(ISTL_LANE) AS GROUPING_ISTL_LANE, 
               GROUPING(TRFTYPE)   AS GROUPING_TRFTYPE, 
               CASE WHEN GROUPING(DTCT_NMBR ) = 1 THEN '°ËÁö±â' ELSE DTCT_NMBR END AS GROUP_DTCT_NMBR,                                                              
               CASE WHEN GROUPING(ISTL_LANE)  = 1 THEN 'Â÷·Î'   ELSE ISTL_LANE END AS GROUP_ISTL_LANE, 
               CASE WHEN GROUPING(TRFTYPE)    = 1 THEN '¼Ò°è'   ELSE TRFTYPE   END AS GROUP_TRFTYPE, 
               DTCT_NMBR, ISTL_LANE, TRFTYPE,
               SUM(TFVL)           TOTTFVL, 
               ROUND(AVG(TFVL), 0) AVGTFVL, 
               
               COUNT(1) AS CNT,
               
               SUM(DECODE(STATHOUR, '00', TFVL)) AS L00,
               SUM(DECODE(STATHOUR, '01', TFVL)) AS L01,
               SUM(DECODE(STATHOUR, '02', TFVL)) AS L02,
               SUM(DECODE(STATHOUR, '03', TFVL)) AS L03,
               SUM(DECODE(STATHOUR, '04', TFVL)) AS L04,
               SUM(DECODE(STATHOUR, '05', TFVL)) AS L05,
               SUM(DECODE(STATHOUR, '06', TFVL)) AS L06,
               SUM(DECODE(STATHOUR, '07', TFVL)) AS L07,
               SUM(DECODE(STATHOUR, '08', TFVL)) AS L08,
               SUM(DECODE(STATHOUR, '09', TFVL)) AS L09,
               SUM(DECODE(STATHOUR, '10', TFVL)) AS L10,
               SUM(DECODE(STATHOUR, '11', TFVL)) AS L11,
               SUM(DECODE(STATHOUR, '12', TFVL)) AS L12,
               SUM(DECODE(STATHOUR, '13', TFVL)) AS L13,
               SUM(DECODE(STATHOUR, '14', TFVL)) AS L14,
               SUM(DECODE(STATHOUR, '15', TFVL)) AS L15,
               SUM(DECODE(STATHOUR, '16', TFVL)) AS L16,
               SUM(DECODE(STATHOUR, '17', TFVL)) AS L17,
               SUM(DECODE(STATHOUR, '18', TFVL)) AS L18,
               SUM(DECODE(STATHOUR, '19', TFVL)) AS L19,
               SUM(DECODE(STATHOUR, '20', TFVL)) AS L20,
               SUM(DECODE(STATHOUR, '21', TFVL)) AS L21,
               SUM(DECODE(STATHOUR, '22', TFVL)) AS L22,
               SUM(DECODE(STATHOUR, '23', TFVL)) AS L23
          FROM (
                SELECT Y.DTCT_NMBR, Y.VDS_DTCT_NM, TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE, 
                       X.TRFTYPE, X.STATHOUR, X.TFVL
                  FROM (SELECT /*+ INDEX(A IX_VDS_DTCT_HH_STAT_PK) */
                               'DTCT' AS TRFTYPE,
                               A.DTCT_NMBR AS DTCT_NMBR, SUBSTR(A.STAT_DT, 9, 2) AS STATHOUR, A.TFVL AS TFVL
                          FROM TB_VDS_DTCT_HH_STAT A
                         WHERE 1=1
                           AND A.STAT_DT BETWEEN '20170201000000' AND '20170201235959'
                       ) X,
                       TB_VDS_DTCT_MSTR Y
                 WHERE 1=1
                   AND Y.DEL_YN = 'N'
                   AND Y.DTCT_NMBR = X.DTCT_NMBR 
                )
         GROUP BY ROLLUP (DTCT_NMBR, ISTL_LANE, TRFTYPE) 
        HAVING GROUPING(TRFTYPE) = 0
       ) LX,
       (
        SELECT A.CTLR_MNGM_NMBR, A.VDS_CTLR_ID, A.LCTN, B.DTCT_NMBR, B.ISTL_LANE, B.VDS_DTCT_NM
          FROM TB_VDS_CTLR A,
               TB_VDS_DTCT_MSTR B
         WHERE 1=1
           AND A.DEL_YN = 'N'
           AND B.DEL_YN = 'N'
           AND A.CTLR_MNGM_NMBR = B.CTLR_MNGM_NMBR
       ) LY
 WHERE LY.DTCT_NMBR  = LX.DTCT_NMBR(+)
 ORDER BY LY.LCTN, LY.ISTL_LANE