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

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

[도서] SQL 쿡북

목록 보기
6/13
post-thumbnail

09장. 날짜 조작기법

🎨 9.7 달력 만들기

  • 각 DBMS별로 방법은 달라 보일 수 있지만, 모두 같은 방식으로 문제를 해결합니다.
  • 이번 달의 매일을 반환한 다음, 매주 요일을 중심으로 달력을 만듭니다.
  • 달력에 사용할 수 있는 다양한 형식이 존재합니다.
    • Unix CAL: 일요일부터 토요일까지의 날짜 형식을 지정합니다.
    • ISO를 기반으로 하므로 월요일부터 금요일까지 생성하는 것이 가장 편리합니다.
    • 문제는 단순히 피벗하기 전에 ISO 주에서 할당한 값을 수정하면 된다는 것을 기억합시다.

💡TIP 같이 보기
    읽을수 있는 출력을 만들기 위해 SQL에서 다양한 형태의 형식을 사용하기 시작하면
    쿼리가 길어집니다. 긴 쿼리에 부담을 갖지 마세요. 여기서 제시하는 쿼리는 한 번
    세분화하여 하나씩 실행하면 매우 간단합니다.

💎 DB2

  • 재귀 WITH 절을 사용하여 매일을 반환합니다.
  • 그 후, CASE 및 MAX를 사용하여 요일을 기준으로 피벗합니다.
WITH x (dy, dm, mth, dw, wk) AS (
  SELECT 
    (CURRENT_DATE - DAY(CURRENT_DATE) DAY + 1 DAY) AS dy,
    DAY(CURRENT_DATE - DAY(CURRENT_DATE) DAY + 1 DAY) AS dm,
    MONTH(CURRENT_DATE) AS mth,
    DAYOFWEEK(CURRENT_DATE - DAY(CURRENT_DATE) DAY + 1 DAY) AS dw,
    WEEK_ISO(CURRENT_DATE - DAY(CURRENT_DATE) DAY + 1 DAY) AS wk
  FROM t1

  UNION ALL

  SELECT 
    dy + 1 DAY,
    DAY(dy + 1 DAY),
    mth,
    DAYOFWEEK(dy + 1 DAY),
    WEEK_ISO(dy + 1 DAY)
  FROM x
  WHERE MONTH(dy + 1 DAY) = mth
)

SELECT 
  MAX(CASE dw WHEN 2 THEN dm END) AS Mo,
  MAX(CASE dw WHEN 3 THEN dm END) AS Tu,
  MAX(CASE dw WHEN 4 THEN dm END) AS We,
  MAX(CASE dw WHEN 5 THEN dm END) AS Th,
  MAX(CASE dw WHEN 6 THEN dm END) AS Fr,
  MAX(CASE dw WHEN 7 THEN dm END) AS Sa,
  MAX(CASE dw WHEN 1 THEN dm END) AS Su
FROM x
GROUP BY wk
ORDER BY wk;

✅ 해석

이 쿼리는 한 달 동안의 날짜를 순회하면서,
각 ISO 주(wk)에 해당하는 월/화/수/목/금/토/일 날짜를 추출하는 쿼리

  • WITH RECURSIVE x: 한 달 동안의 모든 날짜를 재귀적으로 생성
  • DAYOFWEEK: 요일 (1: 일요일, 2: 월요일, ..., 7: 토요일)
  • GROUP BY wk: ISO 주차별로 묶어서
  • MAX(CASE ...): 요일별 날짜를 열 형태로 추출
    → 결과는 주차별 주간 달력 구조처럼 표현!

💎 Oracle

  • 재귀 CONNECT BY절을 사용하여 이번 달의 매일을 반환합니다.
  • 그 후, CASE 및 MAX를 사용해 요일을 기준으로 피벗합니다.
WITH x AS (
  SELECT *
  FROM (
    SELECT TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'IW') wk,
           TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'DD') dm,
           TO_NUMBER(TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'D')) dw,
           TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'MM') curr_mth,
           TO_CHAR(SYSDATE, 'MM') mth
    FROM dual
    CONNECT BY LEVEL <= 31
  )
  WHERE curr_mth = mth
)

SELECT
  MAX(CASE dw WHEN 2 THEN dm END) Mo,
  MAX(CASE dw WHEN 3 THEN dm END) Tu,
  MAX(CASE dw WHEN 4 THEN dm END) We,
  MAX(CASE dw WHEN 5 THEN dm END) Th,
  MAX(CASE dw WHEN 6 THEN dm END) Fr,
  MAX(CASE dw WHEN 7 THEN dm END) Sa,
  MAX(CASE dw WHEN 1 THEN dm END) Su
FROM x
GROUP BY wk
ORDER BY wk;

✅ 해석

함수역할
TRUNC(SYSDATE, 'MM')이번 달 1일로 자름
LEVEL1 ~ 31 생성 (날짜 반복)
TO_CHAR(..., 'IW')ISO 주차 반환
TO_CHAR(..., 'DD')2자리 일 반환
TO_CHAR(..., 'D')요일 숫자 반환 (1=일, 2=월...)
MAX(CASE ...)요일별 컬럼을 세로 → 가로로 피벗함

💎 PostgreSQL

  • GENERATE_SERIES 함수를 사용하여 이번 달의 매일을 반환합니다.
  • 그 후, MAX 밑 CASE를 사용해 요일을 기준으로 피벗합니다.
SELECT max(case dw when 2 then dm end) as Mo,
       max(case dw when 3 then dm end) as Tu,
       max(case dw when 4 then dm end) as We,
       max(case dw when 5 then dm end) as Th,
       max(case dw when 6 then dm end) as Fr,
       max(case dw when 7 then dm end) as Sa,
       max(case dw when 1 then dm end) as Su
FROM (
  SELECT *
  FROM (
    SELECT CAST(date_trunc('month', current_date) AS date) + x.id,
           to_char(
             CAST(date_trunc('month', current_date) AS date) + x.id, 'iw'
           ) as wk,
           to_char(
             CAST(date_trunc('month', current_date) AS date) + x.id, 'dd'
           ) as dm,
           CAST(
             to_char(
               CAST(date_trunc('month', current_date) AS date) + x.id, 'd'
             ) AS integer
           ) as dw,
           to_char(
             CAST(date_trunc('month', current_date) AS date) + x.id, 'mm'
           ) as curr_mth,
           to_char(current_date, 'mm') as mth
    FROM generate_series(0, 31) x(id)
  ) x
  WHERE mth = curr_mth
) y
GROUP BY wk
ORDER BY wk;

✅ 해석

  • 해석하기
  1. FROM generate_series(0, 31) x(id)
  • 0부터 31까지 숫자를 생성 (id 컬럼으로 사용됨)
    → 이번 달 최대 31일까지 날짜 계산용 반복 인덱스 생성

  1. CAST(date_trunc('month', current_date) AS date) + x.id
  • date_trunc('month', current_date) → 이번 달 1일
  • 여기에 id를 더해 → 이번 달의 날짜 목록 생성
  • 예: 2024-04-01 + 0 → 2024-04-01
        2024-04-01 + 1 → 2024-04-02 ...

  1. to_char(..., 'iw') as wk
  • 날짜의 ISO 주차 번호 추출
  • → 예: '2024-04-01' → '14' (14번째 주)

  1. to_char(..., 'dd') as dm
  • 날짜에서 ‘일(day)’만 두 자리로 추출
  • 예: '2024-04-01' → '01'


    5.to_char(..., 'd') → cast(... as integer) as dw
  • 날짜에서 요일을 숫자로 추출
    • 1: 일요일
    • 2: 월요일
    • ...
    • 7: 토요일

  1. to_char(..., 'mm') as curr_mth
  • 현재 날짜 기준으로 계산한 날짜의 월 ('04', '05' 등)

  1. to_char(current_date, 'mm') as mth
  • 오늘 날짜의 월 (현재 달)
    → 위 두 개를 비교해서 이번 달에 해당하는 날짜만 추출

  1. WHERE mth = curr_mth
  • 계산된 날짜가 이번 달에 속한 경우만 필터링

  1. 서브쿼리 x → 서브쿼리 y
  • 지금까지 만든 날짜 정보들을 x로 감싸고,
  • 그걸 다시 y로 감싸서 group by에 활용하기 위한 준비

  1. 최종 SELECT
    sql
    복사편집
    SELECT
      max(case dw when 2 then dm end) as Mo,
      max(case dw when 3 then dm end) as Tu,
      max(case dw when 4 then dm end) as We,
      max(case dw when 5 then dm end) as Th,
      max(case dw when 6 then dm end) as Fr,
      max(case dw when 7 then dm end) as Sa,
      max(case dw when 1 then dm end) as Su
  • dw가 2면 → 월요일
  • dw가 3이면 → 화요일 ...
  • → 요일별로 날짜를 가로로 피벗해서 한 줄로 보여줌

  1. GROUP BY wk
  • 주차(iw) 기준으로 묶음
  • 즉, 한 주에 대한 날짜들만 한 줄로 출력

  1. ORDER BY wk
  • ISO 주차 순서대로 정렬
    → 달력 형태로 위에서 아래로 나열

💎 MySQL

  • 재귀 CTE를 사용해 이번 달의 매일을 반환합니다.
  • 그 후, MAX 및 CASE를 사용해 요일을 기준으로 피벗합니다.
WITH RECURSIVE x(dy, dm, mth, dw, wk) AS (
  SELECT dy,
         DAY(dy),
         MONTH(dy),
         DAYOFWEEK(dy),
         CASE
           WHEN DAYOFWEEK(dy) = 1 THEN WEEK(dy) - 1
           ELSE WEEK(dy)
         END
  FROM (
    SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) + 1 DAY) dy
    FROM t1
  ) x

  UNION ALL

  SELECT DATE_ADD(dy, INTERVAL 1 DAY),
         DAY(DATE_ADD(dy, INTERVAL 1 DAY)),
         mth,
         DAYOFWEEK(DATE_ADD(dy, INTERVAL 1 DAY)),
         CASE
           WHEN DAYOFWEEK(DATE_ADD(dy, INTERVAL 1 DAY)) = 1
           THEN WEEK(DATE_ADD(dy, INTERVAL 1 DAY)) - 1
           ELSE WEEK(DATE_ADD(dy, INTERVAL 1 DAY))
         END
  FROM x
  WHERE MONTH(DATE_ADD(dy, INTERVAL 1 DAY)) = mth
)

SELECT MAX(CASE dw WHEN 2 THEN dm END) Mo,
       MAX(CASE dw WHEN 3 THEN dm END) Tu,
       MAX(CASE dw WHEN 4 THEN dm END) We,
       MAX(CASE dw WHEN 5 THEN dm END) Th,
       MAX(CASE dw WHEN 6 THEN dm END) Fr,
       MAX(CASE dw WHEN 7 THEN dm END) Sa,
       MAX(CASE dw WHEN 1 THEN dm END) Su
FROM x
GROUP BY wk
ORDER BY wk;

✅ 해석

  1. FROM t1
  • 더미 테이블 (t1)은 쿼리를 실행시키기 위한 목적
    → 이건 반복 구조를 시작하기 위해 사용하는 트릭입니다.
    DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) + 1 DAY)
  • 이번 달의 1일을 구함
  • 예: 2024-04-15 → 2024-04-01
    재귀 CTE 시작: WITH RECURSIVE x(...) AS (...)
  • 이번 달의 날짜들을 한 줄씩 생성하는 재귀 구조

  1. SELECT dy, DAY(dy), MONTH(dy), DAYOFWEEK(dy)
  • 각각의 날짜에 대해:
    • dy: 날짜 자체
    • dm: 일(day)
    • mth: 월(month)
    • dw: 요일 (1: 일요일 ~ 7: 토요일)

  1. CASE WHEN DAYOFWEEK(dy) = 1 THEN WEEK(dy) - 1 ELSE WEEK(dy)
  • 일요일이면 주차(WEEK)를 하나 줄여서 정렬 시 ISO 주차처럼 맞춤

  1. UNION ALL ... WHERE MONTH(DATE_ADD(...)) = mth
  • 다음 날을 계산하고
  • 그 날이 이번 달과 같을 때까지만 재귀 실행
    → 즉, 이번 달의 모든 날짜가 생성됨

  1. 바깥 SELECT
SELECT MAX(CASE dw WHEN ... THEN dm END)
  • 요일별로 dw = 2~7, 1 조건에 맞는 dm(일자) 값을 꺼냄
  • MAX()는 각 주차(wk) 안에서 요일별 일자 1개만 추출하려는 목적

  1. GROUP BY wk
  • ISO 주차 기준으로 묶음
    → 한 주에 해당하는 월~일 날짜를 한 줄로 구성

  1. ORDER BY wk
  • 주차 순서대로 정렬 → 달력처럼 출력

💎 SQL Server

  • 재귀 WITH절을 사용해 이번 달의 매일을 피벗합니다.
  • 그 후, CASE 및 MAX를 사용해 요일을 기준으로 피벗합니다.
WITH x(dy, dm, mth, dw, wk) AS (
  SELECT dy,
         DAY(dy),
         DATEPART(m, dy),
         DATEPART(dw, dy),
         CASE 
           WHEN DATEPART(dw, dy) = 1 THEN DATEPART(ww, dy) - 1
           ELSE DATEPART(ww, dy)
         END
  FROM (
    SELECT DATEADD(day, -DAY(GETDATE()) + 1, GETDATE()) dy
    FROM t1
  ) x

  UNION ALL

  SELECT DATEADD(d, 1, dy),
         DAY(DATEADD(d, 1, dy)),
         mth,
         DATEPART(dw, DATEADD(d, 1, dy)),
         CASE 
           WHEN DATEPART(dw, DATEADD(d, 1, dy)) = 
           1 THEN DATEPART(wk, DATEADD(d, 1, dy)) - 1
           ELSE DATEPART(wk, DATEADD(d, 1, dy))
         END
  FROM x
  WHERE DATEPART(m, DATEADD(d, 1, dy)) = mth
)

SELECT MAX(CASE dw WHEN 2 THEN dm END) as Mo,
       MAX(CASE dw WHEN 3 THEN dm END) as Tu,
       MAX(CASE dw WHEN 4 THEN dm END) as We,
       MAX(CASE dw WHEN 5 THEN dm END) as Th,
       MAX(CASE dw WHEN 6 THEN dm END) as Fr,
       MAX(CASE dw WHEN 7 THEN dm END) as Sa,
       MAX(CASE dw WHEN 1 THEN dm END) as Su
FROM x
GROUP BY wk
ORDER BY wk;

✅ 해석

  1. DATEADD(day, -DAY(GETDATE()) + 1, GETDATE())
    • 이번 달의 1일을 구하는 식입니다.
    • 예: 2024-04-13 → 2024-04-01
    • 재귀 CTE의 시작점 역할을 합니다.

  2. 재귀 CTE WITH x(...)
    • 이번 달의 날짜들을 한 줄씩 생성하는 재귀 구조
    • 각 날짜에 대해:
      • dy: 날짜 자체
      • dm: 일(day)
      • mth: 월(month)
      • dw: 요일 (1: 일요일 ~ 7: 토요일)
      • wk: 주차 (단, 일요일이면 1을 빼서 ISO 주처럼 조정

  3. UNION ALL ... WHERE DATEPART(m, ...) = mth
    • 하루씩 더해가며 날짜 생성
    • 그 날짜의 월이 mth와 같을 때까지만 재귀 실행
      → 즉, 이번 달의 모든 날짜가 생성됨

  4. 바깥 SELECT
    SELECT MAX(CASE dw WHEN 2 THEN dm END) as Mo,
       MAX(CASE dw WHEN 3 THEN dm END) as Tu,
       MAX(CASE dw WHEN 4 THEN dm END) as We,
       MAX(CASE dw WHEN 5 THEN dm END) as Th,
       MAX(CASE dw WHEN 6 THEN dm END) as Fr,
       MAX(CASE dw WHEN 7 THEN dm END) as Sa,
       MAX(CASE dw WHEN 1 THEN dm END) as Su
FROM x
GROUP BY wk
ORDER BY wk
    • dw(요일)에 따라 dm(일자)을 가로로 정렬
    • MAX()는 각 주차 내에서 요일에 해당하는 날짜 하나만 추출

  5. GROUP BY wk
    • 주차 기준으로 묶음
      → 한 주에 해당하는 월~일 날짜를 한 줄로 구성

  6. ORDER BY wk
    • 주차 순서대로 정렬
      → 결과적으로 달력처럼 출력됨
profile
정은아
꾸준함의 가치를 믿는 개발자
이전 포스트

[SQL 쿡북/07장] 숫자 작업

다음 포스트

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

0개의 댓글