ITS_OP/SRC/SQL/BIS 소통정보.sql

SELECT LINK_ID, MAX(PROC_DT) AS CLCT_DT,                                                                                 
       ROUND(AVG(SPED), 0)             AS AVRG_SPED,                                                                     
       ROUND(AVG(FUSN_TRAVEL_TIME), 0) AS AVRG_TRVL_HH,                                                                  
       COUNT(1) AS CLCT_CNT                                                                                              
  FROM (SELECT B.LINK_ID, A.PROC_DT,                                                                                     
               B.SECT_DISTANCE,                                                                                          
               B.SECT_DISTANCE * 3.6 / A.FUSN_TRAVEL_TIME AS SPED,                                                       
               A.FUSN_TRAVEL_TIME                                                                                        
          FROM (SELECT ROUTE_ID, PROC_DT, ST_NODE_ID, ED_NODE_ID,                                                        
                       DECODE(SIGN(FUSN_TRAVEL_TIME), -1, FUSN_TRAVEL_TIME*-1, FUSN_TRAVEL_TIME) AS FUSN_TRAVEL_TIME     
                  FROM M_PROCESS_CURRENT@MPBIS                                                                           
                 WHERE PROC_DT >= TO_CHAR(SYSDATE - 8/1440, 'YYYYMMDDHH24MISS')                                          
                   AND FUSN_TRAVEL_TIME <> 0                                                                             
               ) A                                                                                                       
         INNER JOIN (SELECT LINK_ID, ROUTE_ID, ST_NODE_ID, ED_NODE_ID,                                                   
                            DECODE(SIGN(SECT_DISTANCE), -1, SECT_DISTANCE*-1, SECT_DISTANCE) AS SECT_DISTANCE            
                       FROM M_BUSROUTE_SECTION@MPBIS                                                                     
                    ) B                                                                                                  
                 ON A.ST_NODE_ID = B.ST_NODE_ID                                                                          
                AND A.ED_NODE_ID = B.ED_NODE_ID                                                                          
                AND A.ROUTE_ID   = B.ROUTE_ID                                                                            
       )                                                                                                                 
 WHERE SPED < 100                                                                                                        
 GROUP BY LINK_ID