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

--기간별-일간
SELECT SUBSTR(STAT_DT, 1, 8) AS STAT_DT, COUNT(1) DATA_CNT, 
       TO_CHAR(TO_DATE(SUBSTR(STAT_DT, 1, 8), 'YYYYMMDD'), 'YYYY-MM-DD') AS STAT_DTNM, 
       INCD_TYPE_CD AS INCD_TYPE_CD,
       SUM(INCD_OCRR_NUM) INCD_OCRR_NUM, 
       SUM(FTLT_PNUM)     FTLT_PNUM, 
       SUM(INJR_PNUM)     INJR_PNUM, 
       SUM(PRPR_DMGE_AMT) PRPR_DMGE_AMT 
  FROM TB_INCD_TYPE_DD_STAT 
 WHERE STAT_DT BETWEEN '20130101000000' AND '20141231000000'
   AND INCD_TYPE_CD IN (SELECT CMMN_CD
                          FROM TB_CMMN_CD a
                         WHERE CMMN_CLSF_CD = 'IDTU')
 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 8), INCD_TYPE_CD)
 ORDER BY STAT_DT

--기간별-월간
SELECT SUBSTR(STAT_DT, 1, 6) AS STAT_DT, COUNT(1) DATA_CNT, 
       TO_CHAR(TO_DATE(SUBSTR(STAT_DT, 1, 6), 'YYYYMM'), 'YYYY-MM') AS STAT_DTNM, 
       INCD_TYPE_CD AS INCD_TYPE_CD,
       SUM(INCD_OCRR_NUM) INCD_OCRR_NUM, 
       SUM(FTLT_PNUM)     FTLT_PNUM, 
       SUM(INJR_PNUM)     INJR_PNUM, 
       SUM(PRPR_DMGE_AMT) PRPR_DMGE_AMT 
  FROM TB_INCD_TYPE_MN_STAT 
 WHERE STAT_DT BETWEEN '20130101000000' AND '20141231000000'
   AND INCD_TYPE_CD IN (SELECT CMMN_CD
                          FROM TB_CMMN_CD a
                         WHERE CMMN_CLSF_CD = 'IDTU')
 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 6), INCD_TYPE_CD)
 ORDER BY STAT_DT

--기간별-년간
SELECT SUBSTR(STAT_DT, 1, 4) AS STAT_DT, COUNT(1) DATA_CNT, 
       SUBSTR(STAT_DT, 1, 4) AS STAT_NM, 
       INCD_TYPE_CD AS INCD_TYPE_CD,
       SUM(INCD_OCRR_NUM) INCD_OCRR_NUM, 
       SUM(FTLT_PNUM) FTLT_PNUM, 
       SUM(INJR_PNUM) INJR_PNUM, 
       SUM(PRPR_DMGE_AMT) PRPR_DMGE_AMT 
  FROM TB_INCD_TYPE_MN_STAT 
 WHERE STAT_DT BETWEEN '20130101000000' AND '20141231000000'
   AND INCD_TYPE_CD IN (SELECT CMMN_CD
                          FROM TB_CMMN_CD a
                         WHERE CMMN_CLSF_CD = 'IDTU')
 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 4), INCD_TYPE_CD)
 ORDER BY STAT_DT





--구간별-일간
SELECT SUBSTR(STAT_DT, 1, 8) AS STAT_DT, COUNT(1) DATA_CNT, 
       TO_CHAR(TO_DATE(SUBSTR(STAT_DT, 1, 8), 'YYYYMMDD'), 'YYYY-MM-DD') AS STAT_DTNM, 
       LINK_ID AS LINK_ID,
       SUM(INCD_OCRR_NUM) INCD_OCRR_NUM, 
       SUM(FTLT_PNUM)     FTLT_PNUM, 
       SUM(INJR_PNUM)     INJR_PNUM, 
       SUM(PRPR_DMGE_AMT) PRPR_DMGE_AMT 
  FROM TB_INCD_SECT_DD_STAT 
 WHERE STAT_DT BETWEEN '20130101000000' AND '20141231000000'
 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 8), LINK_ID)
 ORDER BY STAT_DT

--구간별-월간
SELECT SUBSTR(STAT_DT, 1, 6) AS STAT_DT, COUNT(1) DATA_CNT, 
       TO_CHAR(TO_DATE(SUBSTR(STAT_DT, 1, 6), 'YYYYMM'), 'YYYY-MM') AS STAT_DTNM, 
       LINK_ID AS LINK_ID,
       SUM(INCD_OCRR_NUM) INCD_OCRR_NUM, 
       SUM(FTLT_PNUM)     FTLT_PNUM, 
       SUM(INJR_PNUM)     INJR_PNUM, 
       SUM(PRPR_DMGE_AMT) PRPR_DMGE_AMT 
  FROM TB_INCD_SECT_MN_STAT 
 WHERE STAT_DT BETWEEN '20130101000000' AND '20141231000000'
 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 6), LINK_ID)
 ORDER BY STAT_DT

--구간별-년간
SELECT SUBSTR(STAT_DT, 1, 4) AS STAT_DT, COUNT(1) DATA_CNT, 
       SUBSTR(STAT_DT, 1, 4) AS STAT_NM, 
       LINK_ID AS INCD_TYPE_CD,
       SUM(INCD_OCRR_NUM) INCD_OCRR_NUM, 
       SUM(FTLT_PNUM) FTLT_PNUM, 
       SUM(INJR_PNUM) INJR_PNUM, 
       SUM(PRPR_DMGE_AMT) PRPR_DMGE_AMT 
  FROM TB_INCD_SECT_MN_STAT 
 WHERE STAT_DT BETWEEN '20130101000000' AND '20141231000000'
 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 4), LINK_ID)
 ORDER BY STAT_DT

























--유형별 일간 통계정보
SELECT STAT_DT, INCD_TYPE_CD, TO_CHAR(TO_DATE(STAT_DT, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD') AS STAT_DTNM, 
       INCD_OCRR_NUM, FTLT_PNUM, INJR_PNUM, PRPR_DMGE_AMT 
  FROM TB_INCD_TYPE_DD_STAT 
 WHERE STAT_DT BETWEEN '20140101000000' AND '20141231000000'
 GROUP BY STAT_DT
 ORDER BY STAT_DT, INCD_TYPE_CD

SELECT STAT_DT, INCD_TYPE_CD, TO_CHAR(TO_DATE(STAT_DT, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD') AS STAT_DTNM, 
       INCD_OCRR_NUM, FTLT_PNUM, INJR_PNUM, PRPR_DMGE_AMT 
  FROM TB_INCD_TYPE_DD_STAT 
 WHERE STAT_DT BETWEEN '20140101000000' AND '20141231000000'
   AND INCD_TYPE_CD = '1'
 ORDER BY STAT_DT, INCD_TYPE_CD

--유형별 월간 통계정보
SELECT STAT_DT, INCD_TYPE_CD, TO_CHAR(TO_DATE(STAT_DT, 'YYYYMMDDHH24MISS'), 'YYYY-MM') AS STAT_DTNM, 
       INCD_OCRR_NUM, FTLT_PNUM, INJR_PNUM, PRPR_DMGE_AMT 
  FROM TB_INCD_TYPE_MN_STAT 
 WHERE STAT_DT BETWEEN '20140101000000' AND '20141231000000'
 ORDER BY STAT_DT, INCD_TYPE_CD

-- 유형별 월간 통계정보
SELECT SUBSTR(STAT_DT, 1, 4) AS STAT_NM, 
       SUM(INCD_TYPE_CD) INCD_TYPE_CD, 
       SUM(INCD_OCRR_NUM) INCD_OCRR_NUM, 
       SUM(FTLT_PNUM) FTLT_PNUM, 
       SUM(INJR_PNUM) INJR_PNUM, 
       SUM(PRPR_DMGE_AMT) PRPR_DMGE_AMT 
  FROM TB_INCD_TYPE_MN_STAT 
 WHERE STAT_DT BETWEEN '20130101000000' AND '20141231000000'
 GROUP BY SUBSTR(STAT_DT, 1, 4)
 ORDER BY STAT_NM, INCD_TYPE_CD