[Mysql] 최근 한달 일별 가입자 수

Full query

WITH RECURSIVE DateRange AS (
    SELECT CURDATE() - INTERVAL 1 MONTH AS date_range
    UNION ALL
    SELECT date_range + INTERVAL 1 DAY FROM DateRange WHERE date_range < CURDATE()
)

SELECT DATE_FORMAT(date_range, '%Y%m%d') AS registration_day,
       IFNULL(COUNT(account.id), 0) AS daily_registrations
FROM DateRange
LEFT JOIN account ON DATE_FORMAT(account.create_time, '%Y%m%d') = DATE_FORMAT(date_range, '%Y%m%d')
GROUP BY registration_day
ORDER BY registration_day;

---

Description

1. DateRange라는 공통 테이블 표현식(CTE) 을 만든다.

WITH RECURSIVE DateRange AS (
    SELECT CURDATE() - INTERVAL 1 MONTH AS date_range
    UNION ALL
    SELECT date_range + INTERVAL 1 DAY FROM DateRange WHERE date_range < CURDATE()
)

• CTE는 한 달 전부터 어제까지의 날짜 시리즈를 생성하는 데 사용
• CTE는 재귀적으로 정의되며, 각 행은 이전 행에서 파생
• 초기 날짜(CURDATE() - INTERVAL 1 MONTH)로 시작하고, 어제까지의 날짜에 이르기까지 하루씩 이전 날짜에 하루를 더하는 방식으로 생성 date_range < CURDATE()

2. CTE를 활용하는 SELECT 문

SELECT DATE_FORMAT(date_range, '%Y%m%d') AS registration_day,
       IFNULL(COUNT(account.id), 0) AS daily_registrations
FROM DateRange
LEFT JOIN account ON DATE_FORMAT(account.create_time, '%Y%m%d') = DATE_FORMAT(date_range, '%Y%m%d')
GROUP BY registration_day
ORDER BY registration_day;

• registration_day: 이 열은 CTE의 date_range에서 유래되지만, 날짜는 DATE_FORMAT을 사용하여 'YYYYMMDD' 형식으로 서식화

• daily_registrations: 이 열은 지정된 날짜 범위의 각 날짜에 발생한 사용자 등록 수, account 테이블의 id 열에서 COUNT 함수를 사용하여 계산
  (특정 날짜에 등록이 없는 경우를 대비하여 IFNULL을 사용하여 카운트가 0으로 표시)

쿼리는 LEFT JOIN을 사용하여 DateRange CTE와 account 테이블을 조인

조인 조건은 account 테이블의 create_time 열에서 형식화된 날짜와 CTE의 형식화된 날짜를 비교
이를 통해 쿼리는 날짜 범위 내의 각 날짜에 대한 등록을 계산

---

End

결과는 잘 나온다.