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

이러한 테이블 처럼 db 쿼리를 만들 수 있나요? - 저번 질문 답변을 지금 봐서 다시 올립니다.

by 개발자가 되고 싶다 [MySQL] mysql db [2021.08.10 10:37:29]

20210810_102925.png (12,713Bytes)

시작버튼, 정지버튼, 끝(종료)버튼 입니다.

데이터 저장은

no	시간	시작	정지	끝
1	2021.08.01 12:00:00	1	0	0
2	2021.08.01 12:05:00	0	1	0

이런식으로 들어옵니다.

빈칸은 데이터가 들어오지 않아 null 값이고 0은 데이터 값이 들어왔지만 사용하지 않아 0으로 나옵니다.

이미지 형식으로 데이터를 조회하고 싶은데 쿼리문을 잘 모르겠습니다. ㅠ

by 마농 [2021.08.10 11:21:36]

WITH t AS
(
SELECT 1 no, '2021.08.01 12:00:00' dt, 1 s1, 0 s2, 0 s3
UNION ALL SELECT 2, '2021.08.01 12:05:00', 0, 1, 0
UNION ALL SELECT 3, '2021.08.02 01:05:00', 1, 0, 0
UNION ALL SELECT 4, '2021.08.02 02:05:00', 0, 1, 0
UNION ALL SELECT 5, '2021.08.02 03:05:00', 0, 0, 1
)
, tm AS
(
SELECT '00' tm
UNION ALL SELECT '01'
UNION ALL SELECT '02'
UNION ALL SELECT '03'
UNION ALL SELECT '04'
UNION ALL SELECT '05'
UNION ALL SELECT '06'
UNION ALL SELECT '07'
UNION ALL SELECT '08'
UNION ALL SELECT '09'
UNION ALL SELECT '10'
UNION ALL SELECT '11'
UNION ALL SELECT '12'
UNION ALL SELECT '13'
UNION ALL SELECT '14'
UNION ALL SELECT '15'
UNION ALL SELECT '16'
UNION ALL SELECT '17'
UNION ALL SELECT '18'
UNION ALL SELECT '19'
UNION ALL SELECT '20'
UNION ALL SELECT '21'
UNION ALL SELECT '22'
UNION ALL SELECT '23'
)
SELECT a.tm
     , SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.01' THEN b.s1 END) cnt1_20210801
     , SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.01' THEN b.s2 END) cnt2_20210801
     , SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.01' THEN b.s3 END) cnt3_20210801
     , SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.02' THEN b.s1 END) cnt1_20210802
     , SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.02' THEN b.s2 END) cnt2_20210802
     , SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.02' THEN b.s3 END) cnt3_20210802
  FROM tm a
  LEFT OUTER JOIN t b
    ON b.dt >= '2021.08.01'
   AND b.dt <  '2021.08.03'
   AND a.tm = DATE_FORMAT(b.dt, '%H')
 GROUP BY a.tm
;

by 개발자가 되고 싶다 [2021.08.10 11:58:16]

감사합니다.

by 개발자가 되고 싶다 [2021.08.10 16:08:32]

아 그리고 한가지 더 궁금한게 있습니다.

1~31일까지 다 표시 하려면

SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.01' THEN b.s1 END) cnt1_20210801

, SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.01' THEN b.s2 END) cnt2_20210801

, SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.01' THEN b.s3 END) cnt3_20210801

, SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.02' THEN b.s1 END) cnt1_20210802

, SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.02' THEN b.s2 END) cnt2_20210802

, SUM(CASE DATE_FORMAT(b.dt, '%Y.%m.%d') WHEN '2021.08.02' THEN b.s3 END) cnt3_20210802

이 부분을 31개 복붙으로 만들어야하나요??

by 마농 [2021.08.10 16:54:32]

네. 다 적어줘야 합니다.
다만. 월단위 조회라면? 년,월은 체크할 필요 없이 일자만 체크하면 되겠죠.
인라인뷰를 이용해 반복되는 부분을 최소화 시키세요.

WITH t AS
(
SELECT 1 no, '2021.08.01 12:00:00' dt, 1 s1, 0 s2, 0 s3
UNION ALL SELECT 2, '2021.08.01 12:05:00', 0, 1, 0
UNION ALL SELECT 3, '2021.08.02 01:05:00', 1, 0, 0
UNION ALL SELECT 4, '2021.08.02 02:05:00', 0, 1, 0
UNION ALL SELECT 5, '2021.08.02 03:05:00', 0, 0, 1
)
, tm AS
(
SELECT '00' tm
UNION ALL SELECT '01'
UNION ALL SELECT '02'
UNION ALL SELECT '03'
UNION ALL SELECT '04'
UNION ALL SELECT '05'
UNION ALL SELECT '06'
UNION ALL SELECT '07'
UNION ALL SELECT '08'
UNION ALL SELECT '09'
UNION ALL SELECT '10'
UNION ALL SELECT '11'
UNION ALL SELECT '12'
UNION ALL SELECT '13'
UNION ALL SELECT '14'
UNION ALL SELECT '15'
UNION ALL SELECT '16'
UNION ALL SELECT '17'
UNION ALL SELECT '18'
UNION ALL SELECT '19'
UNION ALL SELECT '20'
UNION ALL SELECT '21'
UNION ALL SELECT '22'
UNION ALL SELECT '23'
)
SELECT tm
, SUM(CASE d WHEN '01' THEN s1 END) c1_01, SUM(CASE d WHEN '01' THEN s2 END) c2_01, SUM(CASE d WHEN '01' THEN s3 END) c3_01
, SUM(CASE d WHEN '02' THEN s1 END) c1_02, SUM(CASE d WHEN '02' THEN s2 END) c2_02, SUM(CASE d WHEN '02' THEN s3 END) c3_02
, SUM(CASE d WHEN '03' THEN s1 END) c1_03, SUM(CASE d WHEN '03' THEN s2 END) c2_03, SUM(CASE d WHEN '03' THEN s3 END) c3_03
, SUM(CASE d WHEN '04' THEN s1 END) c1_04, SUM(CASE d WHEN '04' THEN s2 END) c2_04, SUM(CASE d WHEN '04' THEN s3 END) c3_04
, SUM(CASE d WHEN '05' THEN s1 END) c1_05, SUM(CASE d WHEN '05' THEN s2 END) c2_05, SUM(CASE d WHEN '05' THEN s3 END) c3_05
, SUM(CASE d WHEN '06' THEN s1 END) c1_06, SUM(CASE d WHEN '06' THEN s2 END) c2_06, SUM(CASE d WHEN '06' THEN s3 END) c3_06
, SUM(CASE d WHEN '07' THEN s1 END) c1_07, SUM(CASE d WHEN '07' THEN s2 END) c2_07, SUM(CASE d WHEN '07' THEN s3 END) c3_07
, SUM(CASE d WHEN '08' THEN s1 END) c1_08, SUM(CASE d WHEN '08' THEN s2 END) c2_08, SUM(CASE d WHEN '08' THEN s3 END) c3_08
, SUM(CASE d WHEN '09' THEN s1 END) c1_09, SUM(CASE d WHEN '09' THEN s2 END) c2_09, SUM(CASE d WHEN '09' THEN s3 END) c3_09
, SUM(CASE d WHEN '10' THEN s1 END) c1_10, SUM(CASE d WHEN '10' THEN s2 END) c2_10, SUM(CASE d WHEN '10' THEN s3 END) c3_10
, SUM(CASE d WHEN '11' THEN s1 END) c1_11, SUM(CASE d WHEN '11' THEN s2 END) c2_11, SUM(CASE d WHEN '11' THEN s3 END) c3_11
, SUM(CASE d WHEN '12' THEN s1 END) c1_12, SUM(CASE d WHEN '12' THEN s2 END) c2_12, SUM(CASE d WHEN '12' THEN s3 END) c3_12
, SUM(CASE d WHEN '13' THEN s1 END) c1_13, SUM(CASE d WHEN '13' THEN s2 END) c2_13, SUM(CASE d WHEN '13' THEN s3 END) c3_13
, SUM(CASE d WHEN '14' THEN s1 END) c1_14, SUM(CASE d WHEN '14' THEN s2 END) c2_14, SUM(CASE d WHEN '14' THEN s3 END) c3_14
, SUM(CASE d WHEN '15' THEN s1 END) c1_15, SUM(CASE d WHEN '15' THEN s2 END) c2_15, SUM(CASE d WHEN '15' THEN s3 END) c3_15
, SUM(CASE d WHEN '16' THEN s1 END) c1_16, SUM(CASE d WHEN '16' THEN s2 END) c2_16, SUM(CASE d WHEN '16' THEN s3 END) c3_16
, SUM(CASE d WHEN '17' THEN s1 END) c1_17, SUM(CASE d WHEN '17' THEN s2 END) c2_17, SUM(CASE d WHEN '17' THEN s3 END) c3_17
, SUM(CASE d WHEN '18' THEN s1 END) c1_18, SUM(CASE d WHEN '18' THEN s2 END) c2_18, SUM(CASE d WHEN '18' THEN s3 END) c3_18
, SUM(CASE d WHEN '19' THEN s1 END) c1_19, SUM(CASE d WHEN '19' THEN s2 END) c2_19, SUM(CASE d WHEN '19' THEN s3 END) c3_19
, SUM(CASE d WHEN '20' THEN s1 END) c1_20, SUM(CASE d WHEN '20' THEN s2 END) c2_20, SUM(CASE d WHEN '20' THEN s3 END) c3_20
, SUM(CASE d WHEN '21' THEN s1 END) c1_21, SUM(CASE d WHEN '21' THEN s2 END) c2_21, SUM(CASE d WHEN '21' THEN s3 END) c3_21
, SUM(CASE d WHEN '22' THEN s1 END) c1_22, SUM(CASE d WHEN '22' THEN s2 END) c2_22, SUM(CASE d WHEN '22' THEN s3 END) c3_22
, SUM(CASE d WHEN '23' THEN s1 END) c1_23, SUM(CASE d WHEN '23' THEN s2 END) c2_23, SUM(CASE d WHEN '23' THEN s3 END) c3_23
, SUM(CASE d WHEN '24' THEN s1 END) c1_24, SUM(CASE d WHEN '24' THEN s2 END) c2_24, SUM(CASE d WHEN '24' THEN s3 END) c3_24
, SUM(CASE d WHEN '25' THEN s1 END) c1_25, SUM(CASE d WHEN '25' THEN s2 END) c2_25, SUM(CASE d WHEN '25' THEN s3 END) c3_25
, SUM(CASE d WHEN '26' THEN s1 END) c1_26, SUM(CASE d WHEN '26' THEN s2 END) c2_26, SUM(CASE d WHEN '26' THEN s3 END) c3_26
, SUM(CASE d WHEN '27' THEN s1 END) c1_27, SUM(CASE d WHEN '27' THEN s2 END) c2_27, SUM(CASE d WHEN '27' THEN s3 END) c3_27
, SUM(CASE d WHEN '28' THEN s1 END) c1_28, SUM(CASE d WHEN '28' THEN s2 END) c2_28, SUM(CASE d WHEN '28' THEN s3 END) c3_28
, SUM(CASE d WHEN '29' THEN s1 END) c1_29, SUM(CASE d WHEN '29' THEN s2 END) c2_29, SUM(CASE d WHEN '29' THEN s3 END) c3_29
, SUM(CASE d WHEN '30' THEN s1 END) c1_30, SUM(CASE d WHEN '30' THEN s2 END) c2_30, SUM(CASE d WHEN '30' THEN s3 END) c3_30
, SUM(CASE d WHEN '31' THEN s1 END) c1_31, SUM(CASE d WHEN '31' THEN s2 END) c2_31, SUM(CASE d WHEN '31' THEN s3 END) c3_31
  FROM (SELECT a.tm
             , DATE_FORMAT(b.dt, '%d') d
             , b.s1, b.s2, b.s3
          FROM tm a
          LEFT OUTER JOIN t b
            ON b.dt >= '2021.08.01'
           AND b.dt <  '2021.09.01'
           AND a.tm = DATE_FORMAT(b.dt, '%H')
        ) a
 GROUP BY tm
;

by 개발자가 되고 싶다 [2021.08.10 17:06:17]

감사합니다. 많이 배웠습니다.

댓글등록

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

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

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

데이터베이스 질문과 답변