데이터베이스 질문과 답변
안녕하세요.
이전 글의 연장선입니다.
해당 데이터들을 다른 테이블에 등록한 뒤 등록한 데이터 테이블을 가져오는 것인데,
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 |
답변 부탁드립니다.
감사합니다.
by 마농
[2023.02.21 11:25:20]
-- 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
by 마농
[2023.02.21 12:33:36]
서브쿼리(인라인뷰) 는 굳이 안쓰셔도 될 듯 합니다.
by 쪼롱이
[2023.02.21 17:11:30]
추가 요구사항이 있어서 수정해 보는데 잘 안돼서 다시 문의드려봅니다.
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 |
시간대는 길어서 생략했습니다.
노란색 배경이 되어 있는 게 없던 데이터입니다.
by 쪼롱이
[2023.02.21 11:51:32]
답변 감사합니다.
이전 글의 데이터는 처리를 하고 다른 테이블(<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)
;
답변 다시 한번 감사드립니다.
by 마농
[2023.02.22 09:36:45]
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
;
by 쪼롱이
[2023.02.22 10:51:28]
답변 정말 정말 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.