[SQL 쿡북/09장] 8절 날짜 조작기법

정은아·2025년 5월 23일
SQL 쿡북

[도서] SQL 쿡북

목록 보기
7/13
post-thumbnail

09장. 날짜 조작 기법

  • 1년에 4개 분기가 있으므로 4개의 행을 생성해야합니다.
  • 원하는 수의 행을 생성한 후 RDBMS에서 제공하는 날짜 함수를 사용하여 시작 및 종료 날짜가 속하는 분기로 돌아갑니다.

🎨 DB2

  • EMP 테이블과 윈도우 함수 ROW_NUMBER OVER를 사용하여 4개의 행을 생성합니다.
  • 또는 WITH 절을 사용하여 행을 생성하거나, 행이 4개 이상인 테이블에 대해 쿼리할 수 있습니다.
  • 다음은 ROW_NUMBER OVER방식으로 접근하는 과정입니다.
SELECT QUARTER(dy - 1 DAY) QTR,
       dy - 3 MONTH Q_start,
       dy - 1 DAY Q_end
FROM (
  SELECT (CURRENT_DATE - (DAYOFYEAR(CURRENT_DATE) - 1) DAY + (rn * 3) MONTH) dy
  FROM (
    SELECT ROW_NUMBER() OVER() rn
    FROM emp
    FETCH FIRST 4 ROWS ONLY
  ) x
) y

✅ 해석

  1. SELECT ROW_NUMBER() OVER() rn FROM emp FETCH FIRST 4 ROWS ONLY
    • emp 테이블에서 1~4까지 번호를 생성
    • → rn = 1, 2, 3, 4
    • 1년은 4개의 분기로 구성되어 있으므로 4행만 필요

  2. SELECT (CURRENT_DATE-(DAYOFYEAR(CURRENT_DATE)-1) DAY+(rn * 3) MONTH) dy
    • CURRENT_DATE - (DAYOFYEAR(...) - 1) DAY
      → 올해 1월 1일을 계산
    • (rn * 3) MONTH 더하기
      • 1분기 종료 다음달: 1월 1일 + 3*1 = 4월 1일
      • 2분기 종료 다음달: 1월 1일 + 3*2 = 7월 1일 …
    • 결과적으로 dy는 각 분기 종료 다음달의 1일이 됨
  3. 바깥 SELECT
    SELECT
  QUARTER(dy - 1 DAY) AS QTR,
  dy - 3 MONTH AS Q_start,
  dy - 1 DAY AS Q_end
    • dy - 1 DAY → 해당 분기의 마지막 날짜
    • QUARTER(...) → 그 날짜가 속한 분기 (1~4)
    • dy - 3 MONTH → 분기 시작일
    • dy - 1 DAY → 분기 종료일

🎨 Oracle

  • ADD_MONTHS 함수를 사용해 각 분기의 시작 및 종료 날짜를 찾습니다.
  • ROWNUM을 사용해 시작 및 종료 날짜가 속한 분기를 나타냅니다.
SELECT ROWNUM qtr,
       ADD_MONTHS(TRUNC(SYSDATE, 'Y'), (ROWNUM - 1) * 3) q_start,
       ADD_MONTHS(TRUNC(SYSDATE, 'Y'), ROWNUM * 3) - 1 q_end
FROM emp
WHERE ROWNUM <= 4;

✅ 해석

  1. FROM emp

    • emp 테이블에서 데이터를 불러옴
    • 실제 데이터는 사용하지 않고, ROWNUM만 활용
    • 행 수 만큼 반복을 만드는 용도

  2. WHERE ROWNUM <= 4

    • 최대 4행까지만 처리
    • → ROWNUM = 1, 2, 3, 4 생성
    • 1년은 4개의 분기로 구성되어 있음

  3. TRUNC(SYSDATE, 'Y')

    • 올해의 첫 날, 1월 1일을 반환
    • 예: 오늘이 2024-04-17이면 → 2024-01-01

  4. ADD_MONTHS(..., (ROWNUM - 1) * 3)

    • 1월 1일 기준으로 각 분기의 시작일 계산
    • 예시:
      • ROWNUM = 1 → 0 * 3 = 0개월 → 1월 1일
      • ROWNUM = 2 → 3개월 → 4월 1일
      • ROWNUM = 3 → 6개월 → 7월 1일
      • ROWNUM = 4 → 9개월 → 10월 1일
    • 결과 컬럼: q_start

  5. ADD_MONTHS(..., ROWNUM * 3) - 1

    • 다음 분기의 첫 날 - 1일 → 현재 분기의 마지막 날
    • 예시:
      • 4월 1일 - 1일 = 3월 31일
      • 7월 1일 - 1일 = 6월 30일
    • 결과 컬럼: q_end

🎨 PostgreSQL

  • 현재 날짜를 기준으로 올 해의 첫 번째 날을 찾고, 각 분기의 마지막 날을 찾기 전에 재귀 CTE를 사용하여 나머지 3분기의 첫 번째 날짜를 입력합니다.
WITH RECURSIVE x(dy, cnt) AS (
  SELECT
    current_date - CAST(EXTRACT(DAY FROM current_date) AS integer) + 1 dy,
    id
  FROM t1

  UNION ALL

  SELECT
    CAST(dy + INTERVAL '3 months' AS date),
    cnt + 1
  FROM x
  WHERE cnt + 1 <= 4
)

SELECT
  CAST(dy - INTERVAL '3 months' AS date) Q_start,
  dy - 1 Q_end
FROM x;

✅ 해석

  1. current_date - EXTRACT(DAY FROM current_date) + 1

    • 이번 달의 1일을 구하는 식
    • 예: 2024-04-17 → 2024-04-01

  2. FROM t1

    • 임시 테이블 t1의 id 값을 초기 카운터로 사용
    • id = 1부터 시작한다고 가정

  3. WITH RECURSIVE ...

    • 3개월씩 증가하는 방식으로 분기 종료일 후보인 dy를 생성
      | cnt | dy |
      |-----|--------------|
      | 1 | 2024-04-01 |
      | 2 | 2024-07-01 |
      | 3 | 2024-10-01 |
      | 4 | 2025-01-01 |

  4. dy - INTERVAL '3 months'

    • dy는 다음 분기 시작일
    • 3개월을 빼면 → 현재 분기의 시작일
    • 출력 컬럼: Q_start

  5. dy - 1

    • 다음 분기 시작일 - 1일 → 현재 분기의 종료일
    • 출력 컬럼: Q_end

  6. 최종 SELECT

   SELECT
     dy - INTERVAL '3 months' AS Q_start,
     dy - 1 AS Q_end
  • 각 분기의 시작일과 종료일 출력

🎨 MySQL

  • 현재 날짜로부터 올 해의 첫 번째 날을 찾고 CTE를 사용하여 분기당 하나씩 4개의 행을 만듭니다.
  • ADDDATE를 사용하여 각 분기의 마지막 날을 찾습니다. (이전 분기의 마지막 날 이후 3개월 또는 분기의 첫 번째 날에서 1을 뺍니다.)
WITH RECURSIVE x(dy, cnt) AS (
  SELECT
    ADDDATE(CURRENT_DATE, (-DAYOFYEAR(CURRENT_DATE)) + 1) dy,
    id
  FROM t1

  UNION ALL

  SELECT
    ADDDATE(dy, INTERVAL 3 MONTH),
    cnt + 1
  FROM x
  WHERE cnt + 1 <= 4
)

SELECT
  QUARTER(ADDDATE(dy, -1)) QTR,
  DATE_ADD(dy, INTERVAL -3 MONTH) Q_start,
  ADDDATE(dy, -1) Q_end
FROM x
ORDER BY 1;

✅ 해석

  1. ADDDATE(CURRENT_DATE, (-DAYOFYEAR(CURRENT_DATE)) + 1)

    • 올해의 1월 1일을 구하는 식
    • 예: 2024-04-17 → 2024-01-01

  2. id FROM t1

    • t1 테이블에서 id를 가져옴
      → cnt로 사용 (초기값은 1부터 시작한다고 가정)

  3. 재귀: cnt + 1, ADDDATE(dy, INTERVAL 3 MONTH)

    • dy 값을 3개월 단위로 증가시키며 재귀적으로 생성
    • 최대 4회 반복 → 1년 4분기에 해당

  4. 테이블 x의 결과 (예시)

    cntdy
    12024-04-01
    22024-07-01
    32024-10-01
    42025-01-01
    • dy는 다음 분기의 시작일

  5. 최종 SELECT

    SELECT
  QUARTER(ADDDATE(dy, -1)) AS QTR,
  DATE_ADD(dy, INTERVAL -3 MONTH) AS Q_start,
  ADDDATE(dy, -1) AS Q_end
    • QTR: dy - 1일의 분기 번호
      • 예: dy = 2024-04-01 → 2024-03-31 → QTR = 1
    • Q_start: 현재 분기의 시작일 (3개월 전)
    • Q_end: 현재 분기의 종료일 (하루 전)

🎨 SQL Server

  • 재귀 WITH 절을 사용하여 4개의 행을 생성합니다.
  • DATEADD함수를 사용하여 시작 및 종료 날짜를 찾습니다.
  • DATEPART 함수를 사용하여 시작 및 종료 날짜가 속하는 분기를 알아냅니다.
WITH x(dy, cnt) AS (
  SELECT DATEADD(d, -(DATEPART(dy, GETDATE()) - 1), GETDATE()), 1
  FROM t1

  UNION ALL

  SELECT DATEADD(m, 3, dy), cnt + 1
  FROM x
  WHERE cnt + 1 <= 4
)

SELECT DATEPART(q, DATEADD(d, -1, dy)) QTR,
       DATEADD(m, -3, dy) Q_start,
       DATEADD(d, -1, dy) Q_end
FROM x
ORDER BY 1;

✅ 해석

  1. SELECT DATEADD(d, -(DATEPART(dy, GETDATE()) - 1), GETDATE())

    • 현재 날짜에서 그 해의 1월 1일을 계산
    • 예: 2024-04-17 → 2024-01-01

  2. cnt = 1

    • 분기 카운터의 시작값 설정

  3. 재귀: DATEADD(m, 3, dy), cnt + 1

    • dy에 3개월씩 더해가며 분기 시작일 생성
    • 반복 조건: cnt + 1 <= 4 → 최대 4개의 분기만 생성
      | cnt | dy (다음 분기 시작일) |
      |-----|------------------------|
      | 1 | 2024-01-01 |
      | 2 | 2024-04-01 |
      | 3 | 2024-07-01 |
      | 4 | 2024-10-01 |
  4. DATEPART(q, DATEADD(d, -1, dy))
    • dy - 1일 날짜가 속한 분기 번호(QTR) 추출
    • 예: 2024-04-01 - 1일 = 2024-03-31 → 1분기

  5. DATEADD(m, -3, dy)

    • dy에서 3개월을 빼면 해당 분기의 시작일
    • 컬럼: Q_start

  6. DATEADD(d, -1, dy)

    • dy에서 1일을 빼면 해당 분기의 종료일
    • 컬럼: Q_end

최종 결과 예시

QTRQ_startQ_end
12024-01-012024-03-31
22024-04-012024-06-30
32024-07-012024-09-30
42024-10-012024-12-31
profile
정은아
꾸준함의 가치를 믿는 개발자
이전 포스트

[SQL 쿡북/09장] 07절. 날짜 조작기법

다음 포스트

[SQL 쿡북/09장] 9절. 지정 분기의 시작일 및 종료일 알아내기

0개의 댓글