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 AVGTFVLX,
       DECODE(NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+
              NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)+NVL(L13,0)+NVL(L14,0)+
              NVL(L15,0)+NVL(L16,0)+NVL(L17,0)+NVL(L18,0)+NVL(L19,0)+NVL(L20,0)+NVL(L21,0)+
              NVL(L22,0)+NVL(L23,0)+NVL(L24,0)+NVL(L25,0)+NVL(L26,0)+NVL(L27,0)+NVL(L28,0)+
              NVL(L29,0)+NVL(L30,0)+NVL(L31,0), 0, '-',
              ROUND(
              (NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+
               NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)+NVL(L13,0)+NVL(L14,0)+
               NVL(L15,0)+NVL(L16,0)+NVL(L17,0)+NVL(L18,0)+NVL(L19,0)+NVL(L20,0)+NVL(L21,0)+
               NVL(L22,0)+NVL(L23,0)+NVL(L24,0)+NVL(L25,0)+NVL(L26,0)+NVL(L27,0)+NVL(L28,0)+
               NVL(L29,0)+NVL(L30,0)+NVL(L31,0)) / 
              (DECODE(L01,NULL,0,1)+DECODE(L02,NULL,0,1)+DECODE(L03,NULL,0,1)+
               DECODE(L04,NULL,0,1)+DECODE(L05,NULL,0,1)+DECODE(L06,NULL,0,1)+
               DECODE(L07,NULL,0,1)+DECODE(L08,NULL,0,1)+DECODE(L09,NULL,0,1)+
               DECODE(L10,NULL,0,1)+DECODE(L11,NULL,0,1)+DECODE(L12,NULL,0,1)+
               DECODE(L13,NULL,0,1)+DECODE(L14,NULL,0,1)+DECODE(L15,NULL,0,1)+
               DECODE(L16,NULL,0,1)+DECODE(L17,NULL,0,1)+DECODE(L18,NULL,0,1)+
               DECODE(L19,NULL,0,1)+DECODE(L20,NULL,0,1)+DECODE(L21,NULL,0,1)+
               DECODE(L22,NULL,0,1)+DECODE(L23,NULL,0,1)+DECODE(L24,NULL,0,1)+
               DECODE(L25,NULL,0,1)+DECODE(L26,NULL,0,1)+DECODE(L27,NULL,0,1)+
               DECODE(L28,NULL,0,1)+DECODE(L29,NULL,0,1)+DECODE(L30,NULL,0,1)+
               DECODE(L31,NULL,0,1)), 0)) AS AVGTFVL,
       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,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L24, NULL, '-', L24)) AS C24,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L25, NULL, '-', L25)) AS C25,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L26, NULL, '-', L26)) AS C26,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L27, NULL, '-', L27)) AS C27,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L28, NULL, '-', L28)) AS C28,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L29, NULL, '-', L29)) AS C29,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L30, NULL, '-', L30)) AS C30,
       DECODE(LX.DTCT_NMBR, NULL, '-', DECODE(L31, NULL, '-', L31)) AS C31 
  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, 
               SUM(DECODE(STATDAY, '01', TFVL)) AS L01,
               SUM(DECODE(STATDAY, '02', TFVL)) AS L02,
               SUM(DECODE(STATDAY, '03', TFVL)) AS L03,
               SUM(DECODE(STATDAY, '04', TFVL)) AS L04,
               SUM(DECODE(STATDAY, '05', TFVL)) AS L05,
               SUM(DECODE(STATDAY, '06', TFVL)) AS L06,
               SUM(DECODE(STATDAY, '07', TFVL)) AS L07,
               SUM(DECODE(STATDAY, '08', TFVL)) AS L08,
               SUM(DECODE(STATDAY, '09', TFVL)) AS L09,
               SUM(DECODE(STATDAY, '10', TFVL)) AS L10,
               SUM(DECODE(STATDAY, '11', TFVL)) AS L11,
               SUM(DECODE(STATDAY, '12', TFVL)) AS L12,
               SUM(DECODE(STATDAY, '13', TFVL)) AS L13,
               SUM(DECODE(STATDAY, '14', TFVL)) AS L14,
               SUM(DECODE(STATDAY, '15', TFVL)) AS L15,
               SUM(DECODE(STATDAY, '16', TFVL)) AS L16,
               SUM(DECODE(STATDAY, '17', TFVL)) AS L17,
               SUM(DECODE(STATDAY, '18', TFVL)) AS L18,
               SUM(DECODE(STATDAY, '19', TFVL)) AS L19,
               SUM(DECODE(STATDAY, '20', TFVL)) AS L20,
               SUM(DECODE(STATDAY, '21', TFVL)) AS L21,
               SUM(DECODE(STATDAY, '22', TFVL)) AS L22,
               SUM(DECODE(STATDAY, '23', TFVL)) AS L23,
               SUM(DECODE(STATDAY, '24', TFVL)) AS L24,
               SUM(DECODE(STATDAY, '25', TFVL)) AS L25,
               SUM(DECODE(STATDAY, '26', TFVL)) AS L26,
               SUM(DECODE(STATDAY, '27', TFVL)) AS L27,
               SUM(DECODE(STATDAY, '28', TFVL)) AS L28,
               SUM(DECODE(STATDAY, '29', TFVL)) AS L29,
               SUM(DECODE(STATDAY, '30', TFVL)) AS L30,
               SUM(DECODE(STATDAY, '31', TFVL)) AS L31
          FROM (
                SELECT Y.DTCT_NMBR, Y.VDS_DTCT_NM, TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE, 
                       X.TRFTYPE, X.STATDAY, 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, 7, 2) AS STATDAY, A.TFVL AS TFVL
                          FROM TB_VDS_DTCT_HH_STAT A
                         WHERE 1=1
                           AND A.STAT_DT BETWEEN '20170201000000' AND '20170201235959'
                           AND SUBSTR(A.STAT_DT, 9, 4) BETWEEN '0400' AND '0600'
                       ) 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