Mysql에서 일간, 주간, 월간 그루핑하기 (Date group by daily, weekly, monthly in Mysql)

Mysql에서 일간, 주간, 월간 그루핑하기 (Date group by daily, weekly, monthly in Mysql)

로그성 데이터를 일간, 주간, 월간별로 보여줘야하는 때가 있습니다. 예를들어 google analytics에서 일간, 주간, 월간으로 보여주는 기능이 있습니다. 이 번에는 mysql에서 DATE 타입의 데이터로 일간, 주간, 월간으로 그루핑 하는 방법에 대해서 알아보도록 하겠습니다.

사전 테이블 정의

리포트 테이블이 있고, 이 테이블은 노출수클릭수를 저장한다고 하겠습니다

CREATE TABLE report(
  record_date DATE NULL SYS_DATE PRIMARY KEY,
  impression INT NOT NULL DEFAULT 0,
  click INT NOT NULL DEFAULT 0
);

일간 리포트 (Daily report)

일간 리포트의 경우, 아주 간단하게 표현할 수 있습니다.

SELECT 
  rerport.record_date,
  impression,
  click
FROM report

주간 리포트 (Weekly report)

주간 리포트의 경우가 가장 까다로운 조건입니다. 코드로 먼저 확인하시고 아래 설명을 하도록 하겠습니다.

SELECT
  CONCAT(
    DATE_FORMAT(
      DATE_ADD(report.record_date, INTERVAL (WEEKDAY(report.record_date)) * -1 DAY),
      '%Y-%m-%d'
    ),
    ' ~ ',
    DATE_FORMAT(
      DATE_ADD(report.record_date, INTERVAL (6 - WEEKDAY(report.record_date)) * +1 DAY),
      '%Y-%m-%d'
    )
  ) AS `record_date`,
  SUM(impression) AS impression,
  SUM(click) AS click
FROM report
GROUP BY record_date

DATE_ADD

날짜를 더하는 mysql 함수입니다.

DATE_ADD(NOW(), INTERVAL 1 DAY)

이런식으로 할 경우, 현재날짜 + 시간 + 하루를 리턴하게 됩니다. 이제 아래코드를 해석해보겠습니다.

DATE_ADD(report.record_date, INTERVAL (WEEKDAY(report.record_date)) * -1 DAY)

record_date = '2020-03-06'이라고 가정한다면, 다음과 같이 변경이 되는데요.

DATE_ADD('2020-03-06', INTERVAL (WEEKDAY('2020-03-06')) * -1 DAY)

-- WEEKDAY(날짜)는 한 주의 몇 번째 요일인지를 나타내는 숫자입니다.
-- 월 = 0, 화 = 1, 수 = 2, 목 = 3, 금 = 4, 토 = 5, 일 = 6

2020-03-06은 월요일이기 때문에 2020-03-06을 리턴할 것이고, 확장해서 생각해보면 2020-03-06 ~ 2020-03-12의 날짜들은 전부 2020-03-06을 리턴할 것입니다.

DATE_ADD('2020-03-06', INTERVAL (6 - WEEKDAY('2020-03-06')) * +1 DAY)

위의 경우는 어떨까요?

2020-03-06 ~ 2020-03-12의 날짜들은 전부 2020-03-12를 리턴할 것입니다.

record_date impression click
2020-03-06 ~ 2020-03-12 1,500 1,000

따라서, DATE_FORMAT함수와 CONCAT함수를 통해서 위와 같은 문자열이 만들어지고, 이 문자열로 그룹핑을 하면 주간 리포트를 얻을 수 있습니다.


월간 리포트 (Monthly report)

월간의 경우도 간단하게 표현할 수 있습니다.

SELECT
  DATE_FORMAT(report.record_date, '%Y-%m') AS record_date,
  SUM(impression) AS impression,
  SUM(click) AS click,
FROM report
GROUP BY record_date

이렇게 Mysql에서 일간, 주간, 월간 그루핑하기에 대해서 알아보았습니다.

감사합니다.