데이터베이스 질문과 답변
안녕하세요.
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개 테이블 적용해 보는 중이었는데, 많은 도움이 되었습니다.
다시 한번 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.