09장. 날짜 조작 기법
- 이 부분의 요령은, 사원이 고용되지 않은 경우에도 매월 행을 반환하는 것입니다. (즉, 개수가 0이 됩니다.)
🎨 DB2
- 재귀 WITH 절을 사용하여 매월(2000년 1월 1일부터 2003년 12월 1일까지 매월 1일)을 생성합니다.
- 필요한 날짜 범위에 대한 모든 월이 만들어지면 EMP 테이블에 외부 조인하고 집계 함수 COUNT를 사용하여 매월 고용 수를 계산합니다.
WITH x(start_date, end_date) AS (
SELECT (MIN(hiredate) - DAYOFYEAR(MIN(hiredate)) DAY + 1 DAY) start_date,
(MAX(hiredate) - DAYOFYEAR(MAX(hiredate)) DAY + 1 DAY) + 1 YEAR end_date
FROM emp
UNION ALL
SELECT start_date + 1 MONTH, end_date
FROM x
WHERE (start_date + 1 MONTH) < end_date
)
SELECT x.start_date mth,
COUNT(e.hiredate) num_hired
FROM x
LEFT JOIN emp e
ON x.start_date = (e.hiredate - DAY(e.hiredate) DAY + 1 DAY)
GROUP BY x.start_date
ORDER BY 1;
✅ 해석
-
MIN(hiredate) - DAYOFYEAR(...) + 1 DAY
- 입사일 중 가장 이른 날짜 기준으로 그 해의 1월 1일을 계산
- 이후 월 단위로 반복 생성할 출발점이 됨
-
MAX(hiredate) - DAYOFYEAR(...) + 1 DAY + 1 YEAR
- 가장 늦은 입사일 기준으로 다음 해의 1월 1일을 계산
- 반복 종료 지점을 넉넉하게 설정하기 위한 기준
-
WITH RECURSIVE x(start_date, end_date)
start_date부터 1개월씩 증가하는 날짜 목록 생성- 종료 조건:
start_date < end_date인 동안만 생성
-
LEFT JOIN emp e ON x.start_date = (e.hiredate - DAY(...) + 1 DAY)
- 입사일을 해당 월의 1일로 보정한 값과
start_date를 매칭
- 즉, 입사한 달의 1일을 기준으로 JOIN 수행
- 채용 내역이 없는 달도 포함하기 위해
LEFT JOIN 사용
-
COUNT(e.hiredate)
✅ 예시 결과
💎 채용 기록이 없는 월도 누락 없이 출력됨 (LEFT JOIN + 재귀 CTE 덕분!)
| mth | num_hired |
|---|
| 1981-01-01 | 0 |
| 1981-02-01 | 2 |
| 1981-03-01 | 0 |
| ... | ... |
🎨 Oracle
- CONNECT BY 절을 사용하여 2000년과 2003년 사이에 매월을 생성합니다.
- 그런 다음 EMP 테이블에 외부 조인하고 집계 함수 COUNT를 사용하여 매월 고용된 사원 수를 계산
WITH x AS (
SELECT ADD_MONTHS(start_date, LEVEL - 1) start_date
FROM (
SELECT MIN(TRUNC(hiredate, 'Y')) start_date,
ADD_MONTHS(MAX(TRUNC(hiredate, 'Y')), 12) end_date
FROM emp
)
CONNECT BY LEVEL <= MONTHS_BETWEEN(end_date, start_date)
)
SELECT x.start_date MTH,
COUNT(e.hiredate) num_hired
FROM x
LEFT JOIN emp e
ON x.start_date = TRUNC(e.hiredate, 'MM')
GROUP BY x.start_date
ORDER BY 1;
✅ 해석
-
MIN(TRUNC(hiredate, 'Y'))
- 입사일 기준으로 가장 오래된 연도의 1월 1일을 계산
-
MAX(TRUNC(hiredate, 'Y')) + 12개월
- 가장 최근 연도의 1월 1일에 12개월을 더함 → 다음 해의 1월 1일
- ✅ 전체 조회 범위: 첫 해 1월 1일 ~ 마지막 해의 다음 해 1월 1일
-
CONNECT BY LEVEL <= MONTHS_BETWEEN(end_date, start_date)
start_date부터 end_date까지 한 달 단위로 증가하는 행 생성LEVEL은 1부터 시작- 각 월:
ADD_MONTHS(start_date, LEVEL - 1)로 계산
-
x.start_date = TRUNC(e.hiredate, 'MM')
- 입사일을 월 단위(해당 월의 1일) 로 자름
- 해당 월 기준으로 조인
-
COUNT(e.hiredate)
- 월별 입사자 수 집계
LEFT JOIN 사용 → 입사자가 없는 달도 0명으로 출력
✅ 예시 결과
CONNECT BY + LEFT JOIN 덕분에, 입사 기록이 없는 달도 누락 없이 출력된다.
| mth | num_hired |
|---|
| 1981-01-01 | 0 |
| 1981-02-01 | 2 |
| 1981-03-01 | 0 |
| ... | ... |
🎨 PostgreSQL
- CTE를 사용하여 최초 고용일 이후 월을 입력한 다음, 생성된 각 월의 월과 연도를 사용하여 EMP 테이블에 LEFT OUTER JOIN을 사용해 각 기간의 고용일수를 계산할 수 있습니다.
WITH RECURSIVE x(start_date, end_date) AS (
SELECT
CAST(MIN(hiredate) - (CAST(EXTRACT(DAY FROM MIN(hiredate))
AS integer) - 1) AS date), MAX(hiredate)
FROM emp
UNION ALL
SELECT
CAST(start_date + INTERVAL '1 month' AS date),
end_date
FROM x
WHERE start_date < end_date
)
SELECT x.start_date,
COUNT(emp.hiredate)
FROM x
LEFT JOIN emp
ON EXTRACT(MONTH FROM start_date) = EXTRACT(MONTH FROM emp.hiredate)
AND EXTRACT(YEAR FROM start_date) = EXTRACT(YEAR FROM emp.hiredate)
GROUP BY x.start_date
ORDER BY 1;
✅ 해석
-
- 가장 빠른 입사일에서 일(day)을 빼고 1을 더해, 해당 월의 1일을 구함
- 예:
1981-03-15 → 1981-03-01
-
MAX(hiredate)
- 입사일 중 가장 늦은 날짜
- 반복 종료 조건으로 사용됨
-
RECURSIVE x(start_date, end_date)
start_date부터 시작하여 매번 +1달씩 증가start_date < end_date일 때까지만 재귀적으로 생성
-
LEFT JOIN ON (MONTH/YEAR 일치)
emp.hiredate와 x.start_date의 연도와 월이 일치할 경우 매칭- PostgreSQL에서는
TRUNC(hiredate, 'MM') 대신 EXTRACT(YEAR) + EXTRACT(MONTH) 조합 사용
-
COUNT(emp.hiredate)
- 해당 월에 입사한 직원 수를 카운트
LEFT JOIN으로 입사자가 없는 달도 0으로 표시
✅ 결과 예시
| start_date | count |
|---|
| 1980-12-01 | 0 |
| 1981-01-01 | 0 |
| 1981-02-01 | 2 |
| 1981-03-01 | 1 |
| ... | ... |
🎨 MySQL
- 재귀 CTE 사용하여 시작일과 종료일 사이에 매월을 생성한 다음 EMP 테이블에 대한 외부 조인을 사용하여 고용 내역을 확인합니다.
WITH RECURSIVE x(start_date, end_date) AS (
SELECT
ADDDATE(MIN(hiredate), -DAYOFYEAR(MIN(hiredate)) + 1) start_date,
ADDDATE(MAX(hiredate), -DAYOFYEAR(MAX(hiredate)) + 1) end_date
FROM emp
UNION ALL
SELECT
DATE_ADD(start_date, INTERVAL 1 MONTH),
end_date
FROM x
WHERE DATE_ADD(start_date, INTERVAL 1 MONTH) < end_date
)
SELECT x.start_date mth,
COUNT(e.hiredate) num_hired
FROM x
LEFT JOIN emp e
ON EXTRACT(YEAR_MONTH FROM start_date) = EXTRACT(YEAR_MONTH FROM e.hiredate)
GROUP BY x.start_date
ORDER BY 1;
✅ 해석
-
ADDDATE(MIN(hiredate), -DAYOFYEAR(...) + 1)
- 입사일 중 가장 이른 날짜를 기준으로 그 해의 1월 1일을 계산
- → 월 생성의 시작점
-
ADDDATE(MAX(hiredate), -DAYOFYEAR(...) + 1)
- 가장 늦은 입사일을 기준으로 그 해의 1월 1일을 계산
- → 월 생성의 종료 조건
-
RECURSIVE ... UNION ALL
- 시작일부터 1개월씩 증가하며 매달 1일을 재귀적으로 생성
- 종료일에 도달할 때까지 반복
-
start_date와 e.hiredate의 연월(YEAR * 100 + MONTH)이 일치할 경우 매칭- 월 기준으로 입사자 데이터를 연결
-
COUNT(e.hiredate)
- 해당 월에 입사한 인원 수를 집계
LEFT JOIN이므로 입사자가 없는 달도 0으로 출력
✅ 결과 예시
| mth | num_hired |
|---|
| 1981-01-01 | 0 |
| 1981-02-01 | 1 |
| 1981-03-01 | 2 |
| ... | ... |
🎨 SQL Server
- 재귀 WITH 절을 사용하여 매월(2000년 1월 1일부터 2003년 12월 1일까지 매월 1일)을 생성합니다.
- 필요한 날짜 범위에 대한 모든 월이 있으면 EMP 테이블에 외부 조인하고 집계 함수 COUNT를 사용하여 매월 고용 일수를 계산합니다.
WITH x(start_date, end_date) AS (
SELECT
DATEADD(dd, 1 - DATEPART(dy, MIN(hiredate)), MIN(hiredate)),
DATEADD(yy, 1, DATEADD(dd, 1 - DATEPART(dy, MAX(hiredate)), MAX(hiredate)))
FROM emp
UNION ALL
SELECT DATEADD(mm, 1, start_date), end_date
FROM x
WHERE DATEADD(mm, 1, start_date) < end_date
)
SELECT x.start_date mth,
COUNT(e.hiredate) num_hired
FROM x
LEFT JOIN emp e
ON x.start_date = DATEADD(dd, 1 - DAY(e.hiredate), e.hiredate)
GROUP BY x.start_date
ORDER BY 1;
✅ 해석
-
DATEADD(dd, 1 - DATEPART(dy, MIN(hiredate)), MIN(hiredate))
- 가장 이른 입사일을 기준으로 해당 해의 1월 1일을 계산
- → 월 생성의 시작점
-
DATEADD(yy, 1, DATEADD(dd, 1 - DATEPART(dy, MAX(hiredate)), MAX(hiredate)))
- 가장 늦은 입사일에서 그 해의 1월 1일을 구한 뒤 +1년
- → 월 생성의 종료점
-
RECURSIVE UNION ALL
start_date부터 시작해 매번 +1개월씩 재귀 증가- 종료 조건: 생성한 날짜가
end_date보다 이전일 때까지만 반복
-
LEFT JOIN ON x.start_date = DATEADD(dd, 1 - DAY(e.hiredate), e.hiredate)
e.hiredate를 해당 월의 1일로 바꾸고x.start_date와 조인 → 월 기준 매칭
-
COUNT(e.hiredate)
- 해당 월에 입사한 인원 수를 집계
LEFT JOIN 덕분에 입사자가 없는 달도 0으로 출력
✅ 예시 결과
| mth | num_hired |
|---|
| 1981-01-01 | 0 |
| 1981-02-01 | 1 |
| 1981-03-01 | 2 |
| ... | ... |
