MySQL를 사용하여 데이터를 일자별로 조회하여 이를 그래프로 보여주거나, 통계 계산을 할 때,
특정 일자의 데이터가 기록되지 않는다면 출력 결과에도 보이지 않게 된다.
그래서 With 절을 이용하여 날짜 범위를 생성하고 Left Join으로 데이터가 없는 날짜로 출력해야 한다.
1. 문제 상황 : 거래가 없는 날짜가 존재하는 데이터셋
일자별로 거래 데이터를 집계한다고 하면, Select문을 작성 후, Group by 혹은 윈도우 함수를 사용하여 집계할 수 있다.
그리고 Where절에서 date 범위 값을 지정하면 손쉽게 출력할 수 있다.
그러나, 특정일에 데이터가 없다면, 다시 말해 집계할 데이터가 존재하지 않는 null 상태라면 일자값도 가져올 수 없다.
아래와 같은 데이터가 있다고 가정하자.
2024-08-01부터 2024-08-10까지의 매출 데이터를 포함하고 있으나, 2024-08-02, 2024-08-04, 2024-08-06, 2024-08-08, 2024-08-09 날짜에는 매출 데이터가 없다.
-- create
CREATE TABLE example (
id INT AUTO_INCREMENT PRIMARY KEY,
date_column DATE NOT NULL,
value INT NOT NULL
);
-- insert
INSERT INTO example (date_column, value)
VALUES
('2024-08-01', 10),
('2024-08-03', 30),
('2024-08-05', 50),
('2024-08-07', 70),
('2024-08-10', 100);
SQL 데이터를 추출 후, 가공하는 프로세스 혹은 상황이면 크게 문제되지 않을테지만,
바로 대시보드를 구성하는 경우 심미적으로 불균형적인 그래프 모양이 그려질 것이고, 데이터를 해석하는 직관성도 떨어지게 될 것이다.
2. 해결 : WITH절로 날짜 데이터 생성하기
'WITH RECURSIVE' 날짜 범위 생성
특정 범위의 날짜를 임시로 생성하는 'WITH RECURSIVE'를 사용해보자.
WITH RECURSIVE DateRange AS (
SELECT '2024-08-01' AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 DAY)
FROM DateRange
WHERE date < '2024-08-31'
)
UNION ALL과 DATE_ADD를 사용해 매일 하루씩 증가시키며 날짜를 생성한다.
범위는 DateRange라는 CTE(Common Table Expression)를 사용하여 2024-08-01부터 2024-08-31까지 지정한다.
Left Join으로 일자별 데이터 조회
SELECT
dr.date,
IFNULL(e.value, 0) AS value
FROM
DateRange dr
LEFT JOIN
example e
ON
dr.date = DATE(e.date_column)
ORDER BY
dr.date;
만들어진 with절 테이블과 Left Join을 사용하여 날짜 범위의 모든 날짜를 포함시킨다.
여기서 IFNULL 함수를 사용하여 값이 NULL인 경우 0으로 채워준다.
3. 전체 쿼리 요약
-- create
CREATE TABLE example (
id INT AUTO_INCREMENT PRIMARY KEY,
date_column DATE NOT NULL,
value INT NOT NULL
);
-- insert
INSERT INTO example (date_column, value)
VALUES
('2024-08-01', 10),
('2024-08-03', 30),
('2024-08-05', 50),
('2024-08-07', 70),
('2024-08-10', 100);
-- fetch
WITH RECURSIVE DateRange AS (
SELECT '2024-08-01' AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 DAY)
FROM DateRange
WHERE date < '2024-08-31'
)
SELECT
dr.date,
IFNULL(e.value, 0) AS value
FROM
DateRange dr
LEFT JOIN
example e
ON
dr.date = DATE(e.date_column)
ORDER BY
dr.date;
4. 응용하기
날짜 범위 바꾸기
예시를 위해 범위 날짜는 하드코딩을 했지만, 실제로 대시보드에 적용하기 위해서는 동적인 변수를 넣거나 시스템 변수를 넣어서 유지보수를 좀 더 원활하게 만들 수도 있다.
-- 이번 달 시작일
LAST_DAY(CURDATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY
-- 이번 달 마지막일
LAST_DAY(CURDATE())
특히, 날짜 범위가 너무 넓으면 성능 이슈가 발생할 수 있으니, 이러한 방식으로 범위를 최소화하는 것이 좋다.
** 사실과 다른 내용이 있을 수 있습니다. 언제든지 피드백 부탁드립니다!
댓글