http://www.gurubee.net/lecture/2194
출석을 기록하는 테이블이 있을 때
연속적으로 출석한 날짜가 얼마나 되는지 확인하고 싶을 때!
연속 된 날짜인 경우 그룹으로 묶어서 카운팅을 하면 되는데,
연속 된 날짜인 것을 어떻게 확인해야 되는가???
이런 테이블이 있다고 가정하자.
| id | date |
| a | 2017-01-01 |
| a | 2017-01-02 |
| a | 2017-01-03 |
| a | 2017-01-05 |
| a | 2017-01-06 |
| a | 2017-01-09 |
| a | 2017-01-20 |
| b | 2017-01-01 |
| b | 2017-01-02 |
| b | 2017-01-03 |
| b | 2017-01-04 |
| b | 2017-01-31 |
| b | 2017-02-01 |
결과로 이렇게 id 별 연속적으로 출석한 날짜를 확인하고 싶다.
| id | from_date | to_date | duration |
| a | 2017-01-01 | 2017-01-03 | 3 |
| a | 2017-01-05 | 2017-01-06 | 2 |
| a | 2017-01-09 | 2017-01-09 | 1 |
| a | 2017-01-20 | 2017-01-20 | 1 |
| b | 2017-01-01 | 2017-01-04 | 4 |
| b | 2017-01-31 | 2017-02-01 | 2 |
우선 id, date로 정렬을 한다. (맨 위에 데이터는 이미 정렬이 되어 있다.)
그리고 결과에 rownum와 group_date라는 컬럼을 추가한다.
rownum : 행 순서
group_date : date - rownum
여기서 핵심은 바로 group_date 이걸로 group by를 하면 된다.
| id | date | rownum | group_date |
| a | 2017-01-01 | 1 | 2016-12-31 |
| a | 2017-01-02 | 2 | 2016-12-31 |
| a | 2017-01-03 | 3 | 2016-12-31 |
| a | 2017-01-05 | 4 | 2017-01-01 |
| a | 2017-01-06 | 5 | 2017-01-01 |
| a | 2017-01-09 | 6 | 2017-01-03 |
| a | 2017-01-20 | 7 | 2017-01-13 |
| b | 2017-01-01 | 8 | 2016-12-24 |
| b | 2017-01-02 | 9 | 2016-12-24 |
| b | 2017-01-03 | 10 | 2016-12-24 |
| b | 2017-01-04 | 11 | 2016-12-24 |
| b | 2017-01-31 | 12 | 2017-01-19 |
| b | 2017-02-01 | 13 | 2017-01-19 |
위를 보면 행이 1 증가할 때 마다 날짜가 1씩 동일하게 증가할 경우에는 동일한 group_date를 갖게 된다. group_date가 같다는 것은 연속된 날짜이다 라는 것을 말해준다.
그래서 이렇게 구해진 값을 가지고 id, group_date를 가지고 group by를 하게 되면 원했던 결과가 나오게 된다.
SET @rownum:=0;
SELECT id,
date,
MIN(ed_date) AS from_date,
MAX(ed_date) AS to_date,
COUNT(*) AS duration
FROM (SELECT *,
@rownum := @rownum + 1 AS rownum,
DATE_SUB(date, INTERVAL @rownum day) AS group_date
FROM tb_attendance
ORDER BY id,
date) AS t
GROUP BY id,
group_date
ORDER BY id,
from_date
댓글 없음:
댓글 쓰기