[SQL] 연속 두 달간 해당 서비스를 이용한 회원 구하기

1. 목표

- 연속 두 달간 해당 서비스를 이용한 회원 구하기

2. 실습

 1) 테이블 생성

CREATE TABLE sql_test.lead_test
(
	IndexId int,
    id int,
    Date datetime
);

 2) 데이터 삽입

INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(1, 1, '2021-01-01');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(2, 2, '2021-02-02');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(3, 2, '2021-02-03');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(4, 1, '2021-02-04');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(5, 3, '2021-03-05');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(6, 1, '2021-03-06');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(7, 3, '2021-04-07');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(8, 2, '2021-04-08');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(9, 4, '2021-05-09');
INSERT INTO sql_test.lead_test(IndexId, id, Date) VALUES(10, 3, '2021-06-10');

 3) 데이터 확인

SELECT * FROM sql_test.lead_test;

 4) id 별 월별 이용 횟수

SELECT
	id,
    DATE_FORMAT(Date, "%Y-%m") AS MonthDate,
    COUNT(id) AS CNT
FROM sql_test.lead_test
GROUP BY id, DATE_FORMAT(Date, "%Y-%m")
ORDER BY id, DATE_FORMAT(Date, "%Y-%m") ASC;

 5) 위 구문을 with 로 감싼 이후 LEAD 함수를 이용하여 바로 밑의 데이터를 하나씩 끌어올림

WITH A AS (
	SELECT
		id,
		DATE_FORMAT(Date, "%Y-%m") AS MonthDate,
		COUNT(id) AS CNT
	FROM sql_test.lead_test
	GROUP BY id, DATE_FORMAT(Date, "%Y-%m")
	ORDER BY id, DATE_FORMAT(Date, "%Y-%m") ASC
),

B AS (
	SELECT 
		A.id,
		A.MonthDate,
		LEAD(A.MonthDate, 1) OVER (PARTITION BY id ORDER BY A.MonthDate) MonthDate2
	FROM A
)

SELECT * FROM B;

 6) CONCAT 을 이용해 -01 을 붙여 데이트 형식으로 만든 이후 timestampdiff 로 월 차이 산출

WITH A AS (
	SELECT
		id,
		DATE_FORMAT(Date, "%Y-%m") AS MonthDate,
		COUNT(id) AS CNT
	FROM sql_test.lead_test
	GROUP BY id, DATE_FORMAT(Date, "%Y-%m")
	ORDER BY id, DATE_FORMAT(Date, "%Y-%m") ASC
),

B AS (
	SELECT 
		A.id,
		A.MonthDate,
		LEAD(A.MonthDate, 1) OVER (PARTITION BY id ORDER BY A.MonthDate) MonthDate2
	FROM A
)

SELECT 
	B.id, 
    B.MonthDate, 
    B.MonthDate2, 
    timestampdiff(MONTH, CONCAT(MonthDate, "-01"), CONCAT(MonthDate2, "-01")) AS MonthDiff
FROM B;

 7) 결과

  - 월 차이가 1인 것은 연속해서 서비스를 이용했다는 뜻

  - 결과해석 : id 가 1인 사람은 두달 연속한 적이 두 번 존재함(이 것은 연속 세 달 일수도 있고 아닐수도 있음) 

WITH A AS (
	SELECT
		id,
		DATE_FORMAT(Date, "%Y-%m") AS MonthDate,
		COUNT(id) AS CNT
	FROM sql_test.lead_test
	GROUP BY id, DATE_FORMAT(Date, "%Y-%m")
	ORDER BY id, DATE_FORMAT(Date, "%Y-%m") ASC
),

B AS (
	SELECT 
		A.id,
		A.MonthDate,
		LEAD(A.MonthDate, 1) OVER (PARTITION BY id ORDER BY A.MonthDate) MonthDate2
	FROM A
),

C AS (
	SELECT 
		B.id, 
		B.MonthDate, 
		B.MonthDate2, 
		timestampdiff(MONTH, CONCAT(MonthDate, "-01"), CONCAT(MonthDate2, "-01")) AS MonthDiff
	FROM B
)

SELECT
	id,
    COUNT(id)
FROM C
WHERE
	MonthDiff = 1
GROUP BY id;

 * 추가생각 : 연속 세 달 서비스 이용 회원을 구하기 위해서는 B 절의 LEAD 구문에서 숫자를 1에서 2으로 바꾼 이후 그 차이가 두 달이면 연속 세 달로 볼 수 있을 거 같음.

2. 참고

 - [MySQL] 현재 날짜와의 차이 계산하기 : 일 차, 주 차, 월 차

 - MySQL | LEAD, LAG 윈도우 함수