ITS_OP/SRC - 스마트교차로 테이블 연계 이전 소스/SQL/VDS구간 교통량 정보조회.sql

SELECT A.LINK_ID, C.NODE_NAME AS STR_NAME, D.NODE_NAME AS END_NAME,
       F.VDS_CTLR_NMBR, F.VDS_NM
  FROM TB_VDS_DTCT_RLTN_LINK A,
       TB_LINK               B,
       TB_NODE               C,
       TB_NODE               D,
       TB_VDS_DTCT           E,
       TB_VDS_CTLR           F
 WHERE A.LINK_ID   = B.LINK_ID
   AND B.F_NODE_ID = C.NODE_ID
   AND B.T_NODE_ID = D.NODE_ID
   AND A.VDS_DTCT_NMBR = E.VDS_DTCT_NMBR
   AND E.VDS_CTLR_NMBR = F.VDS_CTLR_NMBR
   AND F.VDS_TYPE_CD   = 'I'
 GROUP BY A.LINK_ID, C.NODE_NAME, D.NODE_NAME, F.VDS_CTLR_NMBR, F.VDS_NM
 ORDER BY F.VDS_CTLR_NMBR, A.LINK_ID;
 
SELECT STAT_DD, 
       LISTAGG(STAT_HH,',') WITHIN GROUP(ORDER BY STAT_HH) AS STAT_DAY,
       LISTAGG(TFVL,   ',') WITHIN GROUP(ORDER BY STAT_HH) AS TFVL
  FROM (SELECT /*+ INDEX(A PK_VDS_DTCT_HH_STAT) */
               SUBSTR(A.STAT_DT, 1, 8) AS STAT_DD, SUBSTR(A.STAT_DT, 9, 2) AS STAT_HH,
               SUM(A.TFVL) AS TFVL 
          FROM TB_VDS_DTCT_HH_STAT A
         WHERE A.STAT_DT BETWEEN '20190601000000' AND '20190711000000'
           AND A.VDS_DTCT_NMBR IN (SELECT VDS_DTCT_NMBR
                                     FROM TB_VDS_DTCT_RLTN_LINK A
                                    WHERE A.LINK_ID = '3240009700')
         GROUP BY SUBSTR(A.STAT_DT, 1, 8), SUBSTR(A.STAT_DT, 9, 2)
       )
 GROUP BY STAT_DD;

 


SELECT B.VDS_DTCT_NMBR,
               MAX(A.VDS_CTLR_NMBR) VDS_CTLR_NMBR,
               MAX(A.VDS_DTCT_NM) VDS_DTCT_NM,
               SUBSTR(B.STAT_DT,0,8) STAT_DT,
               LISTAGG(SUBSTR(B.STAT_DT,9,2),',') WITHIN GROUP(ORDER BY SUBSTR(B.STAT_DT,9,2)) STAT_DAY,
               LISTAGG(B.TFVL,',') WITHIN GROUP(ORDER BY (SUBSTR(B.STAT_DT,9,2))) TFVL
          FROM TB_VDS_DTCT A,
               TB_VDS_DTCT_HH_STAT B
         WHERE A.DEL_YN = 'N'
           AND A.VDS_CTLR_NMBR = ''
           AND A.VDS_DTCT_NMBR = B.VDS_DTCT_NMBR
           AND SUBSTR(B.STAT_DT,0,8) = #{statsDate}
         GROUP BY B.VDS_DTCT_NMBR, SUBSTR(B.STAT_DT,0,8);


        SELECT /*+ INDEX(A PK_VDS_DTCT_HH_STAT) */
               B.LINK_ID, SUBSTR(A.STAT_DT, 1, 8) AS STAT_DD, SUBSTR(A.STAT_DT, 9, 2) AS STAT_HH,
               SUM(A.TFVL) AS TFVL 
          FROM TB_VDS_DTCT_HH_STAT A,
               (SELECT LINK_ID, VDS_DTCT_NMBR
                  FROM TB_VDS_DTCT_RLTN_LINK
                 WHERE LINK_ID IN ('3240007300','3240007400','3240009700','3240009800','3240011401','3240013600','3240013700','3240336600','3240361500','3240361600','3240362700','3240362800')
               ) B
         WHERE A.STAT_DT BETWEEN '20190601000000' AND '20190711000000'
           AND A.VDS_DTCT_NMBR = B.VDS_DTCT_NMBR
         GROUP BY B.LINK_ID, SUBSTR(A.STAT_DT, 1, 8), SUBSTR(A.STAT_DT, 9, 2)
         ORDER BY B.LINK_ID, SUBSTR(A.STAT_DT, 1, 8), SUBSTR(A.STAT_DT, 9, 2);
         

--VDS±¸°£ ¼ÒÅëÁ¤º¸ ºÐ¼®
SELECT A.ROAD_NAME, B.NODE_NAME AS F_NAME, C.NODE_NAME AS T_NAME,   
       D.*                                                          
  FROM TB_LINK A, TB_NODE B, TB_NODE C,                             
       (                                                            
SELECT A.*, SUBSTR(A.PRCN_DT, 9, 4) AS PRCN_DT2                
  FROM TB_LINK_TRAF_CLCT_HS A                                  
 WHERE A.PRCN_DT BETWEEN '20190713000000'                                  
                     AND '20190713235959'                                  
 AND A.LINK_ID IN ('3240007300','3240007400','3240009700','3240009800','3240011401','3240013600','3240013700','3240336600','3240361500','3240361600','3240362700','3240362800')       ) D                                                          
 WHERE A.LINK_ID   = D.LINK_ID                                      
   AND A.F_NODE_ID = B.NODE_ID                                      
   AND A.T_NODE_ID = C.NODE_ID                                      
 ORDER BY A.LINK_ID, PRCN_DT