Table of Contents
로그성 데이터를 일간, 주간, 월간별로 보여줘야하는 때가 있습니다. 예를들어 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에서 일간, 주간, 월간 그루핑하기에 대해서 알아보았습니다. 혹시 궁금하신 점이나 이상한 점이 있으시면 댓글 부탁드리겠습니다.
감사합니다.