꿈꾸는 개발자, DBA 커뮤니티 구루비

시간대별 통계 쿼리 확인 부탁드립니다.

by 쪼롱이 [MySQL] MariaDB [2023.02.15 15:39:30]

안녕하세요.

시간대별 통계 쿼리 작성 중인데, 잘 작성되었는지 궁금해서 글 남깁니다.

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
;

by 마농 [2023.02.15 17:51:29]

occurrence_time 은 자료형이 문자인가요 날짜인가요?
MID 함수로 자르는 걸 보면 문자인듯 한데
YEAR, MONTH, DAY 함수를 사용하는 걸 보면 날짜인듯도하고?

by 쪼롱이 [2023.02.15 18:01:54]

해당 칼럼 데이터 타입은 varchar 문자열입니다.

by 마농 [2023.02.15 18:00:14]

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
;

by 쪼롱이 [2023.02.15 18:16:21]

제가 작성한 쿼리가 엉망진창이었네요.

많이 배우고 있습니다.

감사합니다.

by 쪼롱이 [2023.02.16 09:46:04]

<META> 테이블의 IN_COUNT, OUT_COUNT 칼럼의 합이 나와야 했는데, 제가 본문 내용에서 빠트렸네요.

COUNT 대신 SUM으로 변경하고 THEN 반환값에 두 칼럼을 +로 변경하였습니다.

감사합니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변