안녕하세요.
이전 글의 연장선입니다.
해당 데이터들을 다른 테이블에 등록한 뒤 등록한 데이터 테이블을 가져오는 것인데,
WITH ROLLUP을 하게 되면 그룹핑한 것들에 대한 합계도 나와 행도 많아져서 WITH ROLLUP 말고 다른 방법이 있는지 궁금해서 남깁니다.
아래는 원하는 결과 값인데, 행 추가가 아닌 각 열이 추가되도 상관은 없습니다.
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 |
20230221 | 100 | 카메라100 | C | 2023 | 2 | 2 | 10 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 16 |
총계 | 10 | 4 | 0 | 0 | 0 | 0 | 0 | 80 | 41 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 137 |
답변 부탁드립니다.
감사합니다.
-- year, month, day 등의 시스템 예약어는 명칭으로 사용하지 않는 것이 좋습니다. SELECT CASE seq WHEN 1 THEN occurrence_de ELSE '총계' END occurrence_de , CASE seq WHEN 1 THEN eqpmn_id END eqpmn_id , CASE seq WHEN 1 THEN eqpmn_nm END eqpmn_nm , CASE seq WHEN 1 THEN object_ty END object_ty , CASE seq WHEN 1 THEN yyyy END yyyy , CASE seq WHEN 1 THEN mm END mm , CASE seq WHEN 1 THEN dd END dd , COUNT(CASE hh WHEN '00' THEN 1 END) hour_00 , COUNT(CASE hh WHEN '01' THEN 1 END) hour_01 , COUNT(CASE hh WHEN '02' THEN 1 END) hour_02 , COUNT(CASE hh WHEN '03' THEN 1 END) hour_03 , COUNT(CASE hh WHEN '04' THEN 1 END) hour_04 , COUNT(CASE hh WHEN '05' THEN 1 END) hour_05 , COUNT(CASE hh WHEN '06' THEN 1 END) hour_06 , COUNT(CASE hh WHEN '07' THEN 1 END) hour_07 , COUNT(CASE hh WHEN '08' THEN 1 END) hour_08 , COUNT(CASE hh WHEN '09' THEN 1 END) hour_09 , COUNT(CASE hh WHEN '10' THEN 1 END) hour_10 , COUNT(CASE hh WHEN '11' THEN 1 END) hour_11 , COUNT(CASE hh WHEN '12' THEN 1 END) hour_12 , COUNT(CASE hh WHEN '13' THEN 1 END) hour_13 , COUNT(CASE hh WHEN '14' THEN 1 END) hour_14 , COUNT(CASE hh WHEN '15' THEN 1 END) hour_15 , COUNT(CASE hh WHEN '16' THEN 1 END) hour_16 , COUNT(CASE hh WHEN '17' THEN 1 END) hour_17 , COUNT(CASE hh WHEN '18' THEN 1 END) hour_18 , COUNT(CASE hh WHEN '19' THEN 1 END) hour_19 , COUNT(CASE hh WHEN '20' THEN 1 END) hour_20 , COUNT(CASE hh WHEN '21' THEN 1 END) hour_21 , COUNT(CASE hh WHEN '22' THEN 1 END) hour_22 , COUNT(CASE hh WHEN '23' THEN 1 END) hour_23 , COUNT(*) total 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 CROSS JOIN seq_1_to_2 b -- MariaDB 용 GROUP BY seq , CASE seq WHEN 1 THEN occurrence_de ELSE '총계' END , CASE seq WHEN 1 THEN eqpmn_id END , CASE seq WHEN 1 THEN eqpmn_nm END , CASE seq WHEN 1 THEN object_ty END , CASE seq WHEN 1 THEN yyyy END , CASE seq WHEN 1 THEN mm END , CASE seq WHEN 1 THEN dd END ; -- 이전 질문글 : http://gurubee.net/article/86547
답변 감사합니다.
이전 글의 데이터는 처리를 하고 다른 테이블(<META_TIME>)에 저장한 값들을 출력하는 것이라 답변 주신 것에서 조금 수정해서 원하는 값을 얻었습니다.
네이밍 관련해서는 다시 숙지하고 지키겠습니다.
SELECT (CASE seq WHEN 1 THEN occurrence_de ELSE '총계' END) occurrence_de , (CASE seq WHEN 1 THEN eqpmn_id END) eqpmn_id , (CASE seq WHEN 1 THEN eqpmn_nm END) eqpmn_nm , (CASE seq WHEN 1 THEN object_ty END) object_ty , (CASE seq WHEN 1 THEN yyyy END) yyyy , (CASE seq WHEN 1 THEN mm END) mm , (CASE seq WHEN 1 THEN dd END) dd , SUM(H00) AS H00 , SUM(H01) AS H01 , SUM(H02) AS H02 , SUM(H03) AS H03 , SUM(H04) AS H04 , SUM(H05) AS H05 , SUM(H06) AS H06 , SUM(H07) AS H07 , SUM(H08) AS H08 , SUM(H09) AS H09 , SUM(H10) AS H10 , SUM(H11) AS H11 , SUM(H12) AS H12 , SUM(H13) AS H13 , SUM(H14) AS H14 , SUM(H15) AS H15 , SUM(H16) AS H16 , SUM(H17) AS H17 , SUM(H18) AS H18 , SUM(H19) AS H19 , SUM(H20) AS H20 , SUM(H21) AS H21 , SUM(H22) AS H22 , SUM(H23) AS H23 , SUM(TOTAL) AS TOTAL FROM ( -- 이전 글에서 데이터 처리 후 저장한 테이블<META_TIME>에서 값 출력 SELECT occurrence_de , eqpmn_id , eqpmn_nm , object_ty , YEAR AS yyyy , MONTH AS mm , DAY AS dd , TOTAL , H00 , H01 , H02 , H03 , H04 , H05 , H06 , H07 , H08 , H09 , H10 , H11 , H12 , H13 , H14 , H15 , H16 , H17 , H18 , H19 , H20 , H21 , H22 , H23 FROM META_TIME ) a CROSS JOIN seq_1_to_2 b GROUP BY seq , (CASE seq WHEN 1 THEN occurrence_de ELSE '총계' END) , (CASE seq WHEN 1 THEN eqpmn_id END) , (CASE seq WHEN 1 THEN eqpmn_nm END) , (CASE seq WHEN 1 THEN object_ty END) , (CASE seq WHEN 1 THEN yyyy END) , (CASE seq WHEN 1 THEN mm END) , (CASE seq WHEN 1 THEN dd END) ;
답변 다시 한번 감사드립니다.
추가 요구사항이 있어서 수정해 보는데 잘 안돼서 다시 문의드려봅니다.
OBJECT_TY 칼럼이 3가지가 있는데 (P, V, C) 해당 날짜(occurrence_de)에 데이터가 없어도 행에 추가되어 합계부터 시간대별로 0으로 채워야 하는데, 이럴 경우는 어떻게 처리해야 할까요?
EQPMN_ID 하나에 OBJECT_TY 3개의 데이터가 들어가야 하고, 데이터가 있으면 출력해 주고, 없으면 0으로 출력해줘야 하네요.
EQPMN_NM | OBJECT_TY | YYYY | MM | DD | TOTAL | H00 |
카메라 100 | P | 2023 | 2 | 2 | 55 | 0 |
카메라 100 | V | 2023 | 2 | 2 | 0 | 0 |
카메라 100 | C | 2023 | 2 | 2 | 0 | 0 |
카메라 102 | P | 2023 | 2 | 2 | 0 | 0 |
카메라 102 | V | 2023 | 2 | 2 | 0 | 0 |
카메라 102 | C | 2023 | 2 | 2 | 60 | 0 |
시간대는 길어서 생략했습니다.
노란색 배경이 되어 있는 게 없던 데이터입니다.
SELECT CASE d.seq WHEN 1 THEN a.occurrence_de ELSE '총계' END occurrence_de , CASE d.seq WHEN 1 THEN a.eqpmn_id END eqpmn_id , CASE d.seq WHEN 1 THEN a.eqpmn_nm END eqpmn_nm , CASE d.seq WHEN 1 THEN b.object_ty END object_ty , CASE d.seq WHEN 1 THEN a.year END yyyy , CASE d.seq WHEN 1 THEN a.month END mm , CASE d.seq WHEN 1 THEN a.day END dd , IFNULL(SUM(c.total), 0) total , IFNULL(SUM(c.h00), 0) h00 , IFNULL(SUM(c.h01), 0) h01 , IFNULL(SUM(c.h02), 0) h02 , IFNULL(SUM(c.h03), 0) h03 , IFNULL(SUM(c.h04), 0) h04 , IFNULL(SUM(c.h05), 0) h05 , IFNULL(SUM(c.h06), 0) h06 , IFNULL(SUM(c.h07), 0) h07 , IFNULL(SUM(c.h08), 0) h08 , IFNULL(SUM(c.h09), 0) h09 , IFNULL(SUM(c.h10), 0) h10 , IFNULL(SUM(c.h11), 0) h11 , IFNULL(SUM(c.h12), 0) h12 , IFNULL(SUM(c.h13), 0) h13 , IFNULL(SUM(c.h14), 0) h14 , IFNULL(SUM(c.h15), 0) h15 , IFNULL(SUM(c.h16), 0) h16 , IFNULL(SUM(c.h17), 0) h17 , IFNULL(SUM(c.h18), 0) h18 , IFNULL(SUM(c.h19), 0) h19 , IFNULL(SUM(c.h20), 0) h20 , IFNULL(SUM(c.h21), 0) h21 , IFNULL(SUM(c.h22), 0) h22 , IFNULL(SUM(c.h23), 0) h23 FROM (SELECT DISTINCT occurrence_de, eqpmn_id, eqpmn_nm, year, month, day FROM meta_time ) a CROSS JOIN (SELECT 1 object_no, 'P' object_ty UNION ALL SELECT 2, 'V' UNION ALL SELECT 3, 'C' ) b LEFT OUTER JOIN meta_time c ON a.occurrence_de = c.occurrence_de AND a.eqpmn_id = c.eqpmn_id AND b.object_ty = c.object_ty CROSS JOIN seq_1_to_2 d GROUP BY d.seq , CASE d.seq WHEN 1 THEN a.occurrence_de ELSE '총계' END , CASE d.seq WHEN 1 THEN a.eqpmn_id END , CASE d.seq WHEN 1 THEN a.eqpmn_nm END , CASE d.seq WHEN 1 THEN b.object_no END , CASE d.seq WHEN 1 THEN b.object_ty END , CASE d.seq WHEN 1 THEN a.year END , CASE d.seq WHEN 1 THEN a.month END , CASE d.seq WHEN 1 THEN a.day END ;