쿼리 합치기 문의합니다. 0 8 885

by 쪼롱이 [MySQL] MariaDB [2022.05.09 15:54:30]


안녕하세요.

2개 이상의 SELECT를 합치려고 하는데, 잘 안되어 글 남깁니다.

ID A_COUNT B_COUNT GROUP_NAME1 GROUP_NAME2
TEST 100 0 DayTime(낮) WeekDay(평일)
TEST 400 0 DayTime(낮) Weekend(주말)
TEST 50 0 NightTime(밤) WeekDay(평일)
TEST 50 0 NightTime(밤) Weekend(주말)

 

위 표를 아래 표와 같이 하려면 어떻게 얻어낼 수 있을까요?

 

ID A_COUNT B_COUNT GROUP_NAME
TEST 500 0 DayTime(낮)
TEST 100 0 NightTime(밤)
TEST 100 0 WeekDay(평일)
TEST 500 0 Weekend(주말)

UNION ALL을 사용하면 가능한데, 쿼리가 더 길어져서 다른 방법이 있나 찾다 헤매서 글 남겨봅니다.

아래는 첫 번째 표의 쿼리입니다.

SELECT "TEST" AS "ID"
     , SUM(A_COUNT) AS "A_COUNT"
     , SUM(B_COUNT) AS "B_COUNT"
     , CASE
           WHEN DATE_FORMAT(DATE_TIME, '%H:%i') BETWEEN '06:00' AND '18:00'
               THEN "DayTime(낮)"
           WHEN (DATE_FORMAT(DATE_TIME, '%H:%i') >= '18:00' OR DATE_FORMAT(DATE_TIME, '%H:%i') < '09:00')
               THEN "NightTime(밤)"
    END AS "GROUP_NAME1"
    , CASE WHEN WEEKDAY(DATE_TIME) IN (5, 6) THEN "Weekend(주말)" ELSE "WeekDay(평일)" END AS "GROUP_NAME2"
FROM tb_cnt_info
WHERE 1 = 1
  AND DATE_TIME BETWEEN '20210101000000' AND '20210102235959'
GROUP BY GROUP_NAME1, GROUP_NAME2
;

답변 부탁드립니다.

감사합니다.

 

* UNION ALL을 사용한 쿼리문도 올립니다.

사용되는 테이블은 [tb_cnt_info], [tb_cnt_info_hourly] 2개입니다.


SELECT A.ID, A.GROUP_NAME, A.TOTAL_CNT
FROM (
         (
             SELECT B.ID
                  , B.GROUP_NAME
                  , SUM(B.A_COUNT) + SUM(B.B_COUNT) AS TOTAL_CNT
             FROM (
                      SELECT "TEST"       AS ID
                           , SUM(A_COUNT) AS A_COUNT
                           , SUM(B_COUNT) AS B_COUNT
                           , CASE
                                 WHEN DATE_FORMAT(DATE_TIME, '%H:%i') BETWEEN '06:00' AND '18:00' THEN "DayTime"
                                 WHEN (DATE_FORMAT(DATE_TIME, '%H:%i') >= '18:00' OR
                                       DATE_FORMAT(DATE_TIME, '%H:%i') < '09:00')
                                     THEN "NightTime"
                          END                GROUP_NAME
                      FROM tb_cnt_info
                      WHERE 1 = 1
                        AND DATE_TIME BETWEEN '20210101000000' AND '20210102235959'
                      GROUP BY GROUP_NAME
                      UNION ALL
                      SELECT "TEST"     AS ID
                           , SUM(A_CNT) AS A_COUNT
                           , SUM(B_CNT) AS B_COUNT
                           , CASE
                                 WHEN DATE_FORMAT(DATE_TIME, '%H:%i') BETWEEN '06:00' AND '18:00' THEN "DayTime"
                                 WHEN (DATE_FORMAT(DATE_TIME, '%H:%i') >= '18:00' OR
                                       DATE_FORMAT(DATE_TIME, '%H:%i') < '09:00')
                                     THEN "NightTime"
                          END              GROUP_NAME
                      FROM tb_cnt_info_hourly
                      WHERE 1 = 1
                        AND DATE_TIME BETWEEN '20210101000000' AND '20210102235959'
                      GROUP BY GROUP_NAME
                  ) B
             GROUP BY B.GROUP_NAME
         )
         UNION ALL
         (
             SELECT C.ID
                  , C.GROUP_NAME
                  , SUM(C.A_COUNT) + SUM(C.B_COUNT) AS TOTAL_CNT
             FROM (
                      SELECT "TEST"       AS        ID
                           , SUM(A_COUNT) AS        A_COUNT
                           , SUM(B_COUNT) AS        B_COUNT
                           , CASE
                                 WHEN WEEKDAY(DATE_TIME) IN (5, 6) THEN "Weekend"
                                 ELSE "WeekDay" END GROUP_NAME
                      FROM tb_cnt_info
                      WHERE 1 = 1
                        AND DATE_TIME BETWEEN '20210101000000' AND '20210102235959'
                      GROUP BY GROUP_NAME
                      UNION ALL
                      SELECT "TEST"     AS          ID
                           , SUM(A_CNT) AS          A_COUNT
                           , SUM(B_CNT) AS          B_COUNT
                           , CASE
                                 WHEN WEEKDAY(DATE_TIME) IN (5, 6) THEN "Weekend"
                                 ELSE "WeekDay" END GROUP_NAME
                      FROM tb_cnt_info_hourly
                      WHERE 1 = 1
                        AND DATE_TIME BETWEEN '20210101000000' AND '20210102235959'
                      GROUP BY GROUP_NAME
                  ) C
             GROUP BY C.GROUP_NAME
         )
     ) A
;

 

by 마농 [2022.05.09 17:30:26]

낮(06~18)과 밤(00~09, 18~24)의 시간이 겹치네요?
- 경계시간이 06 이 맞는지? 09 가 맞는지?
- 경계시간(18:00)의 경우 한쪽에만 포함되어야 하는데 양쪽다 포함입니다. 어느쪽에 포함되어야 할지?


by 쪼롱이 [2022.05.09 17:33:01]

안녕하세요.

낮(06~18[미포함, 17:59:59])

말씀하신 경계시간(18:00)은 밤에 포함됩니다.


by 마농 [2022.05.09 17:44:54]
WITH tb_cnt_info AS
(
SELECT 100 a_count, 0 b_count, '20210101120000' date_time
UNION ALL SELECT 400, 0, '20210102120000'
UNION ALL SELECT  50, 0, '20210101200000'
UNION ALL SELECT  50, 0, '20210102200000'
)
SELECT group_name
     , SUM(a_count) a_count
     , SUM(b_count) b_count
  FROM (SELECT CASE seq
               WHEN 1 THEN CASE WHEN DATE_FORMAT(date_time, '%H%i') >= '0600'
                                 AND DATE_FORMAT(date_time, '%H%i') <  '1800'
                                THEN 'DayTime(낮)'   ELSE 'NightTime(밤)' END
               WHEN 2 THEN CASE WHEN WEEKDAY(date_time) IN (5, 6)
                                THEN 'Weekend(주말)' ELSE 'WeekDay(평일)' END
                END group_name
             , a_count
             , b_count
          FROM tb_cnt_info
             , seq_1_to_2    -- MariaDB 전용, MySQL(X) --
         WHERE date_time BETWEEN '20210101000000' AND '20210102235959'
        ) a
 GROUP BY group_name
;

 


by 우주민 [2022.05.09 17:57:13]

이 질문에 대한 답이 어떤게 있을지 고민 하고 있었는데 union all 말고 join 으로 데이터를 더블링 하는 방법도 있었네요.

이 방법이 union all 과 비교해서 성능적으로 나을 수 있을까요?


by 마농 [2022.05.09 18:01:46]

네.
테이블 두번 읽는 비효율을 제거 하는 방법입니다.


by 쪼롱이 [2022.05.09 18:27:37]

답변 감사합니다.

적용해 보겠습니다.!


by 마농 [2022.05.10 09:08:12]
SELECT group_name
     , SUM(a_count) a_count
     , SUM(b_count) b_count
  FROM (SELECT CASE seq
               WHEN 1 THEN CASE WHEN DATE_FORMAT(date_time, '%H%i') >= '0600'
                                 AND DATE_FORMAT(date_time, '%H%i') <  '1800'
                                THEN 'DayTime(낮)'   ELSE 'NightTime(밤)' END
               WHEN 2 THEN CASE WHEN WEEKDAY(date_time) IN (5, 6)
                                THEN 'Weekend(주말)' ELSE 'WeekDay(평일)' END
                END group_name
             , a_count
             , b_count
          FROM (SELECT date_time, a_count, b_count
                  FROM tb_cnt_info
                 WHERE date_time BETWEEN '20210101000000' AND '20210102235959'
                 UNION ALL
                SELECT date_time, a_count, b_count
                  FROM tb_cnt_info_hourly
                 WHERE date_time BETWEEN '20210101000000' AND '20210102235959'
                ) a
             , seq_1_to_2
        ) a
 GROUP BY group_name
;

 


by 쪼롱이 [2022.05.10 10:05:16]

답변 감사합니다.

안 그래도 알려주신 방법으로 해서 2개 테이블 적용해 보는 중이었는데, 많은 도움이 되었습니다.

다시 한번 감사드립니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입