안녕하세요.
시간대별 통계 쿼리 작성 중인데, 잘 작성되었는지 궁금해서 글 남깁니다.
T <META> - 발생 시간과 객체 타입, 인 카운트, 아웃 카운트, 카메라 번호가 있습니다.
OCCURRENCE_TIME | OBJECT_TY | IN_COUNT | OUT_COUNT | CAMERA_NO |
20230202070000 | P | 10 | 10 | 100 |
20230202071000 | V | 50 | 10 | 101 |
20230202080500 | P | 30 | 5 | 100 |
20230202081000 | C | 5 | 1 | 102 |
T <CAMERA> - 카메라 번호와 데이터 존재 유무 플래그가 있습니다.
CAMERA_NO | IS_DATA |
100 | 1 |
101 | 1 |
102 | 1 |
103 | 0 |
T <EQUIPMENT> - 장비 아이디는 카메라 아이디와 같으며, 장비 이름이 있습니다.
EQUIPMENT_ID | EQUIPMENT_NM |
100 | 카메라100 |
101 | 카메라101 |
102 | 카메라102 |
103 | 카메라103 |
조건은
1. OCCURRENCE_TIME 칼럼에서 시분초 제외한 날짜로 그룹핑
2. 시간대별로 행을 만들어서 각 시간대별로의 합산과 총계
3. Camera T의 IS_DATA가 있는 경우(=1)
4. OBJECT_TY의 P, V, C는 포함된 데이터
이 정도 이고, 아래는 원하는 결괏값입니다.
OCCURRENCE_DE | EQUIPMENT_ID | EQUIPMENT_NM | OBJECT_TY | YEAR | MONTH | DAY | HOUR_00 | HOUR_01 | HOUR_02 | HOUR_03 | HOUR_04 | HOUR_05 | HOUR_06 | HOUR_07 | HOUR_08 | HOUR_09 | HOUR_10 | HOUR_11 | HOUR_12 | HOUR_13 | HOUR_14 | HOUR_15 | HOUR_16 | HOUR_17 | HOUR_18 | HOUR_19 | HOUR_20 | HOUR_21 | HOUR_22 | HOUR_23 | TOTAL |
20230202 | 100 | 카메라100 | P | 2023 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 | 35 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 55 |
20230202 | 102 | 카메라102 | C | 2023 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 |
20230202 | 101 | 카메라101 | V | 2023 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 60 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 60 |
제가 작성한 쿼리는 아래와 같습니다.
보시고, 수정해야 할 부분이 있다면 답변 부탁드립니다.
감사합니다.
SELECT MID(tbl.OCCURRENCE_TIME, 1, 8) AS OCCURRENCE_DE , tbl.EQPMN_ID , tbl.EQPMN_NM , tbl.OBJECT_TY , tbl.YEAR , tbl.MONTH , tbl.DAY , SUM(tbl.HOUR_00) AS H24 , SUM(tbl.HOUR_01) AS H1 , SUM(tbl.HOUR_02) AS H2 , SUM(tbl.HOUR_03) AS H3 , SUM(tbl.HOUR_04) AS H4 , SUM(tbl.HOUR_05) AS H5 , SUM(tbl.HOUR_06) AS H6 , SUM(tbl.HOUR_07) AS H7 , SUM(tbl.HOUR_08) AS H8 , SUM(tbl.HOUR_09) AS H9 , SUM(tbl.HOUR_10) AS H10 , SUM(tbl.HOUR_11) AS H11 , SUM(tbl.HOUR_12) AS H12 , SUM(tbl.HOUR_13) AS H13 , SUM(tbl.HOUR_14) AS H14 , SUM(tbl.HOUR_15) AS H15 , SUM(tbl.HOUR_16) AS H16 , SUM(tbl.HOUR_17) AS H17 , SUM(tbl.HOUR_18) AS H18 , SUM(tbl.HOUR_19) AS H19 , SUM(tbl.HOUR_20) AS H20 , SUM(tbl.HOUR_21) AS H21 , SUM(tbl.HOUR_22) AS H22 , SUM(tbl.HOUR_23) AS H23 , (SUM(tbl.HOUR_00) + SUM(tbl.HOUR_01) + SUM(tbl.HOUR_02) + SUM(tbl.HOUR_03) + SUM(tbl.HOUR_04) + SUM(tbl.HOUR_05) + SUM(tbl.HOUR_06) + SUM(tbl.HOUR_07) + SUM(tbl.HOUR_08) + SUM(tbl.HOUR_09) + SUM(tbl.HOUR_10) + SUM(tbl.HOUR_11) + SUM(tbl.HOUR_12) + SUM(tbl.HOUR_13) + SUM(tbl.HOUR_14) + SUM(tbl.HOUR_15) + SUM(tbl.HOUR_16) + SUM(tbl.HOUR_17) + SUM(tbl.HOUR_18) + SUM(tbl.HOUR_19) + SUM(tbl.HOUR_20) + SUM(tbl.HOUR_21) + SUM(tbl.HOUR_22) + SUM(tbl.HOUR_23)) AS TOTAL FROM ( SELECT M.OCCURRENCE_TIME , E.EQPMN_ID , E.EQPMN_NM , M.OBJECT_TY , YEAR(M.OCCURRENCE_TIME) AS YEAR , MONTH(M.OCCURRENCE_TIME) AS MONTH , DAYOFMONTH(M.OCCURRENCE_TIME) AS DAY , SUM(HOUR(M.OCCURRENCE_TIME) = 00) AS HOUR_00 , SUM(HOUR(M.OCCURRENCE_TIME) = 01) AS HOUR_01 , SUM(HOUR(M.OCCURRENCE_TIME) = 02) AS HOUR_02 , SUM(HOUR(M.OCCURRENCE_TIME) = 03) AS HOUR_03 , SUM(HOUR(M.OCCURRENCE_TIME) = 04) AS HOUR_04 , SUM(HOUR(M.OCCURRENCE_TIME) = 05) AS HOUR_05 , SUM(HOUR(M.OCCURRENCE_TIME) = 06) AS HOUR_06 , SUM(HOUR(M.OCCURRENCE_TIME) = 07) AS HOUR_07 , SUM(HOUR(M.OCCURRENCE_TIME) = 08) AS HOUR_08 , SUM(HOUR(M.OCCURRENCE_TIME) = 09) AS HOUR_09 , SUM(HOUR(M.OCCURRENCE_TIME) = 10) AS HOUR_10 , SUM(HOUR(M.OCCURRENCE_TIME) = 11) AS HOUR_11 , SUM(HOUR(M.OCCURRENCE_TIME) = 12) AS HOUR_12 , SUM(HOUR(M.OCCURRENCE_TIME) = 13) AS HOUR_13 , SUM(HOUR(M.OCCURRENCE_TIME) = 14) AS HOUR_14 , SUM(HOUR(M.OCCURRENCE_TIME) = 15) AS HOUR_15 , SUM(HOUR(M.OCCURRENCE_TIME) = 16) AS HOUR_16 , SUM(HOUR(M.OCCURRENCE_TIME) = 17) AS HOUR_17 , SUM(HOUR(M.OCCURRENCE_TIME) = 18) AS HOUR_18 , SUM(HOUR(M.OCCURRENCE_TIME) = 19) AS HOUR_19 , SUM(HOUR(M.OCCURRENCE_TIME) = 20) AS HOUR_20 , SUM(HOUR(M.OCCURRENCE_TIME) = 21) AS HOUR_21 , SUM(HOUR(M.OCCURRENCE_TIME) = 22) AS HOUR_22 , SUM(HOUR(M.OCCURRENCE_TIME) = 23) AS HOUR_23 FROM CAMERA C LEFT JOIN META M ON (C.CAMERA_NO = M.CAMERA_NO) LEFT JOIN EQUIPMENT E ON (M.CAMERA_NO = E.EQPMN_ID) WHERE 1 = 1 AND C.IS_DATA = 1 AND M.OBJECT_TY IN ('P', 'V', 'C') # AND M.OCCURRENCE_TIME >= '20230101000000' # AND M.OCCURRENCE_TIME <= '20231231235959' GROUP BY M.OCCURRENCE_TIME ) tbl GROUP BY OCCRRNC_DE ;
SELECT occurrence_de , eqpmn_id , eqpmn_nm , object_ty , yyyy , mm , dd , COUNT(CASE hh WHEN '00' THEN 1 END) h00 , COUNT(CASE hh WHEN '01' THEN 1 END) h01 , COUNT(CASE hh WHEN '02' THEN 1 END) h02 , COUNT(CASE hh WHEN '03' THEN 1 END) h03 , COUNT(CASE hh WHEN '04' THEN 1 END) h04 , COUNT(CASE hh WHEN '05' THEN 1 END) h05 , COUNT(CASE hh WHEN '06' THEN 1 END) h06 , COUNT(CASE hh WHEN '07' THEN 1 END) h07 , COUNT(CASE hh WHEN '08' THEN 1 END) h08 , COUNT(CASE hh WHEN '09' THEN 1 END) h09 , COUNT(CASE hh WHEN '10' THEN 1 END) h10 , COUNT(CASE hh WHEN '11' THEN 1 END) h11 , COUNT(CASE hh WHEN '12' THEN 1 END) h12 , COUNT(CASE hh WHEN '13' THEN 1 END) h13 , COUNT(CASE hh WHEN '14' THEN 1 END) h14 , COUNT(CASE hh WHEN '15' THEN 1 END) h15 , COUNT(CASE hh WHEN '16' THEN 1 END) h16 , COUNT(CASE hh WHEN '17' THEN 1 END) h17 , COUNT(CASE hh WHEN '18' THEN 1 END) h18 , COUNT(CASE hh WHEN '19' THEN 1 END) h19 , COUNT(CASE hh WHEN '20' THEN 1 END) h20 , COUNT(CASE hh WHEN '21' THEN 1 END) h21 , COUNT(CASE hh WHEN '22' THEN 1 END) h22 , COUNT(CASE hh WHEN '23' THEN 1 END) h23 , COUNT(*) tot FROM (SELECT SUBSTR(occurrence_time, 1, 8) occurrence_de , e.eqpmn_id , e.eqpmn_nm , m.object_ty , SUBSTR(m.occurrence_time, 1, 4) AS yyyy , SUBSTR(m.occurrence_time, 5, 2) AS mm , SUBSTR(m.occurrence_time, 7, 2) AS dd , SUBSTR(m.occurrence_time, 9, 2) AS hh FROM camera c LEFT JOIN meta m ON c.camera_no = m.camera_no LEFT JOIN equipment e ON m.camera_no = e.eqpmn_id WHERE 1 = 1 AND c.is_data = 1 AND m.object_ty IN ('P', 'V', 'C') AND m.occurrence_time >= '20230101000000' AND m.occurrence_time <= '20231231235959' ) a GROUP BY occurrence_de , eqpmn_id , eqpmn_nm , object_ty , yyyy , mm , dd ;