휴식 중인 멤버의 앚프메가 며칠 동안 오지 않았는지, 히토미님처럼 거의 매일 보내는 멤버는 며칠이나 연속해서 프메를 보내는지가 궁금했다. 질문 항목으로도 추가할 계획이라서, PHP에서의 계산은 최소화 하고, 데이터베이스에서 원하는 데이터를 가공해서 받아올 수 있는 방법을 찾아보았다.
http://www.artfulsoftware.com/infotree/qrytip.php?id=76에 나와 있는 LEFT JOIN을 이용한 방법으로는 구현이 잘되지 않았고, 억지로 구현했더니 날짜 기반이라서 그런지 결과 도출에 시간이 너무 많이 걸렸다.
다른 몇 가지 솔루션을 테스트 해보다가 마지막으로 https://stackoverflow.com/questions/12276087/how-to-group-continuous-ranges-using-mysql 여기에 있는 솔루션을 적용했다.
서브 쿼리는 순번 적용하면서 어느정도 익숙하다 싶었는데, 내용이 너무 복잡해서 이해하는데 한참 걸렸다. 기록 안해두면 또 까먹을 것 같아 상세한 리록을 남겨둔다. 기본적인 원리는 카테고리순 + 날짜순으로 정렬을 한 뒤에, 연속된 날의 첫번째 날짜를 구해서 별도의 Column에 기록을 해두는 것이다. 그 뒤에 첫번째 날짜를 기준으로 다시 정렬해서 연속된 마지막 날짜를 구한다.
1. 날짜순으로 데이터 정렬
SELECT
'테이블'.*,
@f := CONVERT(IF(@c <=> '멤버ID' AND DATEDIFF('수신일', @d) <=1, @f, '수신일'), DATE) AS startdate,
@c := '멤버ID',
@d := '수신일'
FROM
'테이블'
JOIN
(
SELECT
@c := NULL,
@f := NULL,
@d := NULL
) AS init
ORDER BY
'멤버ID',
'수신일'
간단하게 정리를 해보면 다음과 같다.
SELECT (1) :: 2~5열 FROM 테이블 JOIN ( SELECT (2) :: 11~13열 ) AS init ORDER BY 멤버ID, 수신일
멤버별로 데이터를 가져와야 하기 때문에 멤버ID로 1차 정렬하고, 수신일 순으로 2차 정렬한다. 테이블은 데이터가 들어있는 테이블을 지정하면 된다. (1)과 (2) 부분은 아래에 따로 정리를 했다. (1)이 그냥 *라고 가정하면 아래와 같은 정렬된 데이터를 얻을 수 있다. 멤버ID와 수신일을 제외한 데이터는 사실상 의미가 없기 때문에 이하 생략하기로 한다.
| 멤버ID | 수신일 | 데이터1 | 데이터2 | … |
|---|---|---|---|---|
| 1 | 2019-02-08 | 데이터1 | 데이터2 | … |
| 1 | 2019-02-09 | 데이터1 | 데이터2 | … |
| 1 | 2019-02-10 | 데이터1 | 데이터2 | … |
| 1 | 2019-02-13 | 데이터1 | 데이터2 | … |
| 1 | 2019-02-13 | 데이터1 | 데이터2 | … |
| 1 | 2019-02-14 | 데이터1 | 데이터2 | … |
| 1 | 2019-02-20 | 데이터1 | 데이터2 | … |
| 1 | 2019-02-22 | 데이터1 | 데이터2 | … |
| 1 | 2019-04-25 | 데이터1 | 데이터2 | … |
| 2 | 2019-02-08 | 데이터1 | 데이터2 | … |
| 2 | 2019-02-12 | 데이터1 | 데이터2 | … |
| 2 | 2019-02-12 | 데이터1 | 데이터2 | … |
| 2 | 2019-02-13 | 데이터1 | 데이터2 | … |
| 2 | 2019-02-14 | 데이터1 | 데이터2 | … |
| 2 | 2019-02-15 | 데이터1 | 데이터2 | … |
| 2 | 2019-03-25 | 데이터1 | 데이터2 | … |
| … | … | … | … | … |
| 12 | 2019-04-25 | 데이터1 | 데이터2 | … |
(2) 부분에 있는 @c, @d, @f는 (1)에서 사용될 값들의 초기값을 지정하는 부분이다. 모두 NULL로 초기화를 해서 테이블에 JOIN을 시키면, 해당 테이블을 불러올때 @c, @d, @f의 초기값도 같이 가져오게 된다. 하나라도 빠지면 계산이 제대로 되지 않으니 주의가 필요하다. AS init은 서브 쿼리의 name을 init으로 지정하는 것이고, 이름은 뭐가 되도 상관이 없지만, 지정을 해주지 않으면 쿼리 오류가 발생한다.
(1) 부분에 있는 테이블.*은 테이블의 모든 항목을 가져온다는 뜻이다. 그 뒤의 3~5열에 있는 @f, @k, @c 등은 계산된 값을 추가하겠다는 의미이다. 보통 SELECT * 이라고 하면 모든 Column을 가져오게 되는데, 여기에 @f, @k, @c의 Column이 추가된다고 보면 된다.
@는 데이터베이스에서 변수를 의미한다. f라는 변수를 지정하고, 데이터베이스가 계산하는 동안 이 값을 계속 유지한다. @f := 와 같이 :가 들어간 등호를 같이 사용해주어야 한다.
2. 연속된 날의 첫번째 날짜 계산
@f := CONVERT(IF(@c <=> 멤버ID AND DATEDIFF(수신일, @d) <=1, @f, 수신일), DATE) AS startdate,
IF(@c <=> 멤버ID AND DATEDIFF(수신일, @d) <=1, @f, 수신일) 부분만 떼서 보면, @c라는 값이 멤버ID와 같고(<=>), @d라는 값이 수신일과 하루 이내(<=1)의 차이라면 @f가 선택되고, 둘 중 하나라도 만족하지 못한다면 수신일이 선택된다.
(2)에서 @c, @d, @f를 NULL로 초기화를 했다. 위의 예제 표에서 첫번째 데이터를 보면, 첫번째 조건인 @c(NULL) <=> 1 이 만족하지 않으므로 수신일이 선택된다. IF문을 정리하면, @f := CONVERT(수신일, DATE) AS startdate가 되고, CONVERT는 수신일을 2019-04-28 의 형식으로 변환한다. 결국 @f에는 수신일이 2019-04-28 형식으로 들어가게 된다. 실제 테이블의 데이터의 수신일에는 시간이 포함되어 있고, 연속된 날짜 기준으로 정렬을 해야할 필요가 있기 때문에, CONVERT로 시간을 제외한 날짜만 걸러내 준다.
다음 4열의 SELECT 항목이 @c := 멤버ID 이므로, @C에 현재 멤버ID 값인 1이 들어간다.(갱신 시점) 5열의 @d := 수신일에서는 @d에 역시 현재 수신일인 2019-02-08이 들어간다.(갱신 시점) 다음 데이터를 처리할때는 이 갱신된 값들을 참조하게 된다.
위의 예제 표의 첫번째 데이터를 정리해보면 다음과 같다. @c부터 @d갱신까지 순차적으로 계산이 된다고 이해하면 쉽다.
| 멤버ID | 수신일 | @c | @c<=>멤버ID | @d | DATEDIFF(@d, 수신일)<=1 | @f선택 | @f갱신 | @c갱신 | @d갱신 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 2019-02-08 | NULL | FALSE | NULL | FALSE | 수신일 | 2019-02-08 | 1 | 2019-02-08 |
두번째 데이터를 보면, @c는 1이고 멤버ID도 1이다. -> TRUE. 수신일은 2019-02-09, @d는 2019-02-08 이므로 DATEDIFF 결과가 1이다. -> TRUE. 두 조건을 모두 만족하기 때문에 @f가 선택된다. @f의 값은 2019-02-08이고, @c는 1, @d는 2019-02-09로 갱신된다.
다음 세번째 데이터는 2019-02-10으로 연속된 날짜 이므로 두번째 데이터와 동일하게 계산이 된다. 그 다음 네번째 2019-02-13 데이터에서는 @c는 변함이 없고, 수신일은 2019-02-13, @d는 2019-02-10 이므로 DATEDIFF 결과가 3이다. 조건문의 FALSE이므로 @f는 수신일인 2019-02-13이 선택된다.
이와 같이 반복해보면 아래와 같은 표를 얻을 수 있다.
| 멤버ID | 수신일 | @c | @c<=>멤버ID | @d | DATEDIFF(@d, 수신일)<=1 | @f선택 | @f갱신 | @c갱신 | @d갱신 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 2019-02-08 | NULL | FALSE | NULL | FALSE | 수신일 | 2019-02-08 | 1 | 2019-02-08 |
| 1 | 2019-02-09 | 1 | TRUE | 2019-02-08 | TRUE | @f | 2019-02-08 | 1 | 2019-02-09 |
| 1 | 2019-02-10 | 1 | TRUE | 2019-02-09 | TRUE | @f | 2019-02-08 | 1 | 2019-02-10 |
| 1 | 2019-02-13 | 1 | TRUE | 2019-02-10 | FALSE | 수신일 | 2019-02-13 | 1 | 2019-02-13 |
| 1 | 2019-02-13 | 1 | TRUE | 2019-02-13 | TRUE | @f | 2019-02-13 | 1 | 2019-02-13 |
| 1 | 2019-02-14 | 1 | TRUE | 2019-02-13 | TRUE | @f | 2019-02-13 | 1 | 2019-02-14 |
| 1 | 2019-02-20 | 1 | TRUE | 2019-02-14 | FALSE | 수신일 | 2019-02-20 | 1 | 2019-02-20 |
| 1 | 2019-02-22 | 1 | TRUE | 2019-02-20 | FALSE | 수신일 | 2019-02-22 | 1 | 2019-02-22 |
| 1 | 2019-04-25 | 1 | TRUE | 2019-02-22 | FALSE | 수신일 | 2019-04-25 | 1 | 2019-04-25 |
| 2 | 2019-02-08 | 1 | FALSE | 2019-04-25 | FALSE | 수신일 | 2019-02-08 | 2 | 2019-02-08 |
| 2 | 2019-02-12 | 2 | TRUE | 2019-02-08 | FALSE | 수신일 | 2019-02-12 | 2 | 2019-02-12 |
| 2 | 2019-02-12 | 2 | TRUE | 2019-02-12 | TRUE | @f | 2019-02-12 | 2 | 2019-02-12 |
| 2 | 2019-02-13 | 2 | TRUE | 2019-02-12 | TRUE | @f | 2019-02-12 | 2 | 2019-02-13 |
| 2 | 2019-02-14 | 2 | TRUE | 2019-02-13 | TRUE | @f | 2019-02-12 | 2 | 2019-02-14 |
| 2 | 2019-02-15 | 2 | TRUE | 2019-02-14 | TRUE | @f | 2019-02-12 | 2 | 2019-02-15 |
| 2 | 2019-03-25 | 2 | TRUE | 2019-02-15 | FALSE | 수신일 | 2019-03-25 | 2 | 2019-03-25 |
위의 중간 계산 부분은 엑셀에서 시뮬레이션해서 계산이 어떻게 되는지를 확인한 부분이고, 실제 쿼리를 수행해보면 아래와 같은 결과값을 얻게 된다.
| 멤버ID | 수신일 | startdate | @c | @d |
|---|---|---|---|---|
| 1 | 2019-02-08 | 2019-02-08 | 1 | 2019-02-08 |
| 1 | 2019-02-09 | 2019-02-08 | 1 | 2019-02-09 |
| 1 | 2019-02-10 | 2019-02-08 | 1 | 2019-02-10 |
| 1 | 2019-02-13 | 2019-02-13 | 1 | 2019-02-13 |
| 1 | 2019-02-13 | 2019-02-13 | 1 | 2019-02-13 |
| 1 | 2019-02-14 | 2019-02-13 | 1 | 2019-02-14 |
| 1 | 2019-02-20 | 2019-02-20 | 1 | 2019-02-20 |
| 1 | 2019-02-22 | 2019-02-22 | 1 | 2019-02-22 |
| 1 | 2019-04-25 | 2019-04-25 | 1 | 2019-04-25 |
| 2 | 2019-02-08 | 2019-02-08 | 2 | 2019-02-08 |
| 2 | 2019-02-12 | 2019-02-12 | 2 | 2019-02-12 |
| 2 | 2019-02-12 | 2019-02-12 | 2 | 2019-02-12 |
| 2 | 2019-02-13 | 2019-02-12 | 2 | 2019-02-13 |
| 2 | 2019-02-14 | 2019-02-12 | 2 | 2019-02-14 |
| 2 | 2019-02-15 | 2019-02-12 | 2 | 2019-02-15 |
| 2 | 2019-03-25 | 2019-03-25 | 2 | 2019-03-25 |
@f의 마지막에 AS startdate로 선언을 해주었기 때문에, 해당 column은 @f가 아닌 startdate로 표시가 된다. startdate 결과를 보면 연속된 날짜의 첫번째 날이 기록되어 있음을 알 수 있다.
3. 마지막 날짜 계산
이제 이 데이터에서 마지막 날짜를 가져온다. 1의 쿼리를 서브쿼리로 사용하는 새로운 쿼리가 필요하다.
SELECT
'멤버ID',
startdate,
CONVERT(MAX('수신일'), DATE) AS enddate,
FROM
(
1의 쿼리 삽입
) AS tt
GROUP BY
'멤버ID',
startdate
ORDER BY
'멤버ID' ASC,
enddate DESC
멤버ID와 startdate로 그룹화 하고, 수신일중 가장 큰 값을 enddate로 가져오면, 아래와 같이 날짜가 깔끔하게 정리가 된다.
| 멤버ID | startdate | enddate |
|---|---|---|
| 1 | 2019-02-08 | 2019-02-10 |
| 1 | 2019-02-13 | 2019-02-14 |
| 1 | 2019-02-20 | 2019-02-20 |
| 1 | 2019-02-22 | 2019-02-22 |
| 1 | 2019-04-25 | 2019-04-25 |
| 2 | 2019-02-08 | 2019-02-08 |
| 2 | 2019-02-12 | 2019-02-15 |
| 2 | 2019-03-25 | 2019-03-25 |