시간을 다루는 함수들에 대해 공부를 마쳤으니 우리는 원하는 시간 단위로 자유롭게 데이터를 집계할 수 있게 되었습니다. 이번 글에서는 시간을 다루어 결과를 도출해 낼 수 있는 예제를 풀어보고자 합니다.
🚨요청사항
2023년의 마케팅 성과를 바탕으로 올해의 마케팅 계획을 추정하기 위해 월별 마케팅 획득 단가를 점검해보고자 합니다. 각 채널별로 월별 가입자 수와 소진액, 획득 단가를 출력해 주세요.
위와 관련된 테이블을 다음과 같습니다.
처음으로 데이터를 추출하는데 필요한 테이블이 두 개인 예제가 등장했습니다. 보통 데이터는 관리의 용이성과 자원 최적화를 위해 하나의 테이블에는 꼭 필요한 데이터를 담고 있도록 설계되어 있습니다. 그렇기 때문에 member 테이블에는 마케팅 성과가 존재하지 않고, 마케팅 성과 테이블에는 회원 정보가 없기 때문에 두 개의 테이블이 모두 필요합니다.
자 그럼 해왔던 것처럼 차근차근 문제를 해결해 봅시다. 먼저 월별 가입자를 출력해 볼까요?
- SELECT 절에는 우리가 얻고자 하는 값을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 다음과 같습니다.
- 값: 월, 가입자 수
- FROM 절에는 해당 데이터가 담긴 테이블을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 테이블 명세서에 명시되어 있습니다.
- 테이블: member
- WHERE 절에는 우리가 원하는 데이터를 추출하기 위한 필터링 조건을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 다음과 같습니다.
- 조건: 2023년 가입
이것을 SQL문으로 작성한다면 다음과 같아집니다.
SELECT 월, COUNT(member_id)
FROM member
WHERE date BETWEEN '2023-01-01' AND '2023-12-01'
GROUP BY 1
SELECT 절에 들어갈 월을 출력하는 방법에는 여러 가지가 있습니다. EXTRACT함수를 통해 월을 뽑아낼 수도 있고 DATE_FORMAT 함수를 통해서도 가능합니다.
SELECT EXTRACT(month from date) AS "월", COUNT(member_id) AS "회원수"
FROM member
WHERE date BETWEEN '2023-01-01' AND '2023-12-01'
GROUP BY 1
SELECT DATE_FORMAT(date, '%Y-%m') AS "월", COUNT(member_id) AS "회원수"
FROM member
WHERE date BETWEEN '2023-01-01' AND '2023-12-01'
GROUP BY 1
SELECT DATE_FORMAT(date, '%Y-%m-01') AS "월", COUNT(member_id) AS "회원수"
FROM member
WHERE date BETWEEN '2023-01-01' AND '2023-12-01'
GROUP BY 1
위에 나열한 세 가지 방법 모두 2023년 월별 회원수를 집계하는 동일한 수행을 하는 쿼리입니다. 그중에 제가 추천하는 방법은 3번 방법입니다. 갑자기 등장한 01이 당황스럽지만 알고 보면 매우 편리합니다. %Y-%m의 경우 가입일인 DATE 컬럼에 담겨있는 날짜값을 그대로 연도와 월을 가져온다는 의미이며 뒤에 붙은 01은 사이사이 들어간 하이픈 기호와 동일하게 날짜를 01로 고정하겠다는 의미입니다. 이렇게 되면 member 테이블의 가입일의 연, 월 부분은 기존 데이터 값을 따라가지만 날짜 부분이 모두 01로 변한다는 의미입니다. 그렇게 되면 모든 데이터가 2023-01-01, 2023-02-01 등의 형태가 되기 때문에 다른 월별 집계 방법과 동일한 역할을 합니다.
다소 복잡하지만 이것을 추천하는 이유는 우리가 데이터를 추출해 엑셀로 데이터를 옮기는 과정에서 월만 있거나 연월만 있는 경우 엑셀에서 해당 데이터를 날짜로 인식하지 못하는 상황이 발생하기 때문에 온전한 날짜의 형태 2023-01-01, 2023-02-01의 모양으로 집계되도록 하는 것입니다.
그럼 다음글에서는 마케팅 성과를 집계한느 방법에 대해 알려드리도록 하겠습니다.
함께 성장해요.