문제: 데이터가 없는 날짜를 어떻게 출력할까?
회사 서비스의 관리자 페이지를 개발하던 중 일별 통계를 보여줘야 하는 요구사항이 생겼다. 처음에는 단순하게 생각했지만 DB에 입력된 날짜들로 GROUP BY를 한다면 데이터가 입력되지 않은 경우 빈 날짜가 발생하게 된다는 문제가 있었다.
그래서 특정 날짜부터 현재까지 데이터가 있든 없든 빠짐없이 일별 통계를 보여주기 위해 찾아보니 MySQL에 WITH RECURSIVE 구문이 있다는 것을 알게 됐다. 이 구문을 활용해 일별 데이터를 보여주는 방법을 정리해본다.
해결
1. WITH RECURSIVE 구문으로 특정 기간의 날짜를 연속적으로 SELECT 한 임시 테이블 생성
MySQL에서 WITH 절이란 CTE(Common Table Exprssion)을 정의할 수 있도록 만들어주는 키워드다. CTE는 쿼리문 안에서 만드는 임시 테이블이다. 이 임시 테이블로 중간 결과를 만든 후 재사용하는 것이다.
WITH RECURSIVE 는 말 그대로 재귀적 CTE를 만들기 위해 사용된다.
이 점을 활용해 나는 2022년 7월 1일부터 오늘 전까지 날짜를 출력하는 임시 테이블을 만들기 위해 다음과 같은 구문을 사용했다.
WITH RECURSIVE dates AS (
SELECT '2022-07-01' AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 DAY)
FROM dates
WHERE date < CURDATE()
)해당 쿼리를 살펴보면 AS 다음 서브 쿼리에서 '2022-07-01'를 SELECT 하고 UNION ALL 다음 쿼리로 1일씩 날짜를 추가해 오늘 날짜 이전까지 수직적으로 record의 합계를 구한다. (UNION ALL 은 중복 제거를 하지 않으므로 UNION 보다 속도가 빠르다.)
이렇게 만들어진 날짜 임시 테이블에 실제 출력하고자 하는 날짜 테이블을 LEFT JOIN 했다.
2. SUM() OVER(ORDER BY ~)로 누적 합계 보여주기
이제 일별 날짜를 구했으니 그 다음 문제는 해당 날짜의 캠퍼스별 클래스 수, 선생님 수, 학생 수를 보여주는 것이었다.
해당 날짜별 당시의 클래스 수, 선생님 수, 학생 수는 저장하지 않으므로.. 고민하다가 등록일과 퇴원일(삭제일)은 기록하고 있어 이를 기준으로 집계를 하기로 했다. 예를 들어 특정 날짜에 등록된 클래스 수와 삭제된 클래스 수를 각각 카운트한 다음 서로 빼는 것이다. 그러면 해당 날짜에 남아있는 클래스 수를 알게 된다. (선생님, 학생 수도 동일한 패턴)
WITH RECURSIVE 구문 이후로는 콤마,로 임시 테이블을 만들어주면 된다. 각각 데이터를 집계하는 테이블들을 만들어서 이어줬다.
WITH RECURSIVE dates AS (
SELECT '2022-07-01' AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 DAY)
FROM dates
WHERE date < CURDATE()
),
daily_cls_reg AS (
SELECT date(register_date) register_date, count(*) cls_reg_cnt FROM campus_class
WHERE campus_no = 1
GROUP BY date(register_date)
),
.... # 이런 식의 패턴그 다음으로 데이터가 없는 날짜를 채우기 위해서는 누적 합계가 필요하다고 생각했다.
찾아보니 SUM() OVER() 함수로 누적 합계를 구할 수 있는 것 같았다.
# ... 이후 SELECT 절
SELECT ds.date,
CAST(SUM(COALESCE(dtr.tc_reg_cnt, 0)) OVER (ORDER BY ds.date) AS UNSIGNED) - CAST(SUM(COALESCE(dtd.tc_del_cnt, 0)) OVER (ORDER BY ds.date) AS UNSIGNED) AS teacher_count
FROM dates ds
LEFT JOIN daily_cls_reg dcr
ON ds.date = dcr.register_date
LEFT JOIN daily_cls_del dcd
ON ds.date = dcd.delete_date이런 식으로 누적 합계를 구해서 채워주니 일별 통계가 완성됐다!
