SQL의 EXTRACT() 함수로 날짜의 일부를 추출하는 고급 기술

SQL의 EXTRACT() 함수는 날짜와 시간에서 특정 요소를 추출하는 강력한 도구입니다. 이 기사에서는 기본적인 사용법부터 시작하여 연도, 월, 일, 시간 등의 요소를 추출하는 방법, 더 나아가 분기나 요일 등의 응용 예제, 복잡한 쿼리와의 조합 방법, 그리고 성능 최적화 팁까지 폭넓게 설명합니다. 이를 통해 날짜 데이터를 효과적으로 다룰 수 있게 되어, 데이터 분석이나 보고서 작성에 큰 편리함을 제공합니다.

EXTRACT() 함수의 기본적인 사용법

EXTRACT() 함수는 SQL에서 날짜나 시간에서 특정 요소를 추출하기 위해 사용됩니다. 기본적인 구문은 다음과 같습니다.

EXTRACT(요소 FROM 날짜)

예를 들어, 어떤 테이블orders에 주문일을 포함한 order_date 컬럼이 있다고 가정해 보겠습니다. 여기서 연도를 추출하려면, 다음과 같이 작성합니다.

SELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

이 쿼리는 orders 테이블의 각 주문일의 연도를 추출하여 order_year라는 컬럼명으로 결과를 표시합니다. 다른 요소도 이와 동일한 방법으로 추출할 수 있습니다. 예를 들어, 월을 추출하려면 YEAR를 MONTH로 변경합니다.

연도, 월, 일을 추출하는 기술

EXTRACT() 함수를 사용하여 날짜 데이터에서 연도, 월, 일을 추출하는 방법을 자세히 살펴보겠습니다.

연도를 추출하기

연도를 추출하려면 YEAR를 지정합니다. 아래는 orders 테이블의 order_date에서 연도를 추출하는 예제입니다.

SELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

이 쿼리는 각 주문일의 연도를 가져와 order_year로 표시합니다.

월을 추출하기

월을 추출하려면 MONTH를 지정합니다. 다음 예제는 order_date에서 월을 추출합니다.

SELECT EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

이 쿼리는 각 주문일의 월을 가져와 order_month로 표시합니다.

일을 추출하기

일을 추출하려면 DAY를 지정합니다. 다음 예제는 order_date에서 일을 추출합니다.

SELECT EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

이 쿼리는 각 주문일의 일을 가져와 order_day로 표시합니다.

시간, 분, 초를 추출하는 기술

EXTRACT() 함수를 사용하여 날짜 시간 데이터에서 시간, 분, 초를 추출하는 방법을 소개합니다.

시간을 추출하기

시간을 추출하려면 HOUR를 지정합니다. 아래는 orders 테이블의 order_time에서 시간을 추출하는 예제입니다.

SELECT EXTRACT(HOUR FROM order_time) AS order_hour
FROM orders;

이 쿼리는 각 주문 시각의 시간을 가져와 order_hour로 표시합니다.

분을 추출하기

분을 추출하려면 MINUTE를 지정합니다. 다음 예제는 order_time에서 분을 추출합니다.

SELECT EXTRACT(MINUTE FROM order_time) AS order_minute
FROM orders;

이 쿼리는 각 주문 시각의 분을 가져와 order_minute로 표시합니다.

초를 추출하기

초를 추출하려면 SECOND를 지정합니다. 다음 예제는 order_time에서 초를 추출합니다.

SELECT EXTRACT(SECOND FROM order_time) AS order_second
FROM orders;

이 쿼리는 각 주문 시각의 초를 가져와 order_second로 표시합니다.

분기나 요일을 추출하는 응용 예제

EXTRACT() 함수는 연도나 월, 일뿐만 아니라 분기나 요일 등의 정보도 추출할 수 있습니다. 아래에서는 분기와 요일을 추출하는 방법을 소개합니다.

분기를 추출하기

분기를 추출하려면 QUARTER를 지정합니다. 다음 예제는 orders 테이블의 order_date에서 분기를 추출합니다.

SELECT EXTRACT(QUARTER FROM order_date) AS order_quarter
FROM orders;

이 쿼리는 각 주문일의 분기를 가져와 order_quarter로 표시합니다. 분기는 1월에서 3월이 1분기, 4월에서 6월이 2분기, 7월에서 9월이 3분기, 10월에서 12월이 4분기가 됩니다.

요일을 추출하기

요일을 추출하려면 DOW(Day of Week)를 지정합니다. 다음 예제는 order_date에서 요일을 추출합니다.

SELECT EXTRACT(DOW FROM order_date) AS order_day_of_week
FROM orders;

이 쿼리는 각 주문일의 요일을 가져와 order_day_of_week로 표시합니다. DOW는 0이 일요일, 1이 월요일, 2가 화요일과 같은 방식으로 0에서 6까지의 숫자로 요일을 나타냅니다.

특정 기간에 따른 데이터 추출

특정 기간에 따라 데이터를 추출할 때, EXTRACT() 함수를 사용해 여러 조건을 결합하여 특정 범위나 기간의 데이터를 효율적으로 가져올 수 있습니다.

회계연도에 따른 데이터 추출

회계연도를 기준으로 데이터를 추출할 때, 시작 월이 1월이 아닌 경우가 많습니다. 예를 들어 회계연도가 4월부터 시작하는 경우, 다음과 같이 추출합니다.

SELECT *
FROM orders
WHERE (EXTRACT(YEAR FROM order_date) = 2023 AND EXTRACT(MONTH FROM order_date) >= 4)
   OR (EXTRACT(YEAR FROM order_date) = 2024 AND EXTRACT(MONTH FROM order_date) <= 3);

이 쿼리는 2023년 4월부터 2024년 3월까지의 주문을 추출합니다.

영업일에 따른 데이터 추출

영업일만의 데이터를 추출할 때는 보통 주말이나 공휴일을 제외합니다. 아래 예제에서는 토요일과 일요일을 제외하고 영업일 데이터를 추출합니다.

SELECT *
FROM orders
WHERE EXTRACT(DOW FROM order_date) NOT IN (0, 6);

이 쿼리는 주문일이 월요일부터 금요일인 데이터를 추출합니다. 공휴일을 제외하려면 별도로 공휴일 리스트를 준비하고, 그것을 기반으로 제외하는 조건을 추가합니다.

특정 시간대에 따른 데이터 추출

특정 시간대, 예를 들어 영업시간(오전 9시부터 오후 6시)에 따른 데이터를 추출할 때, 다음과 같이 작성합니다.

SELECT *
FROM orders
WHERE EXTRACT(HOUR FROM order_time) BETWEEN 9 AND 18;

이 쿼리는 주문 시각이 오전 9시부터 오후 6시까지의 데이터를 추출합니다.

EXTRACT() 함수를 결합한 복잡한 쿼리

EXTRACT() 함수는 다른 SQL 함수와 결합함으로써 더 고급스러운 데이터 추출을 실현할 수 있습니다. 아래에 몇 가지 예를 제시합니다.

월별 매출 합계를 계산하는 쿼리

월별 매출 합계를 계산할 때 EXTRACT() 함수와 집계 함수를 결합합니다.

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       SUM(order_amount) AS total_sales
FROM orders
GROUP BY order_year, order_month
ORDER BY order_year, order_month;

이 쿼리는 각 연도, 각 월의 매출 합계를 계산하고, order_year와 order_month로 그룹화하여 결과를 표시합니다.

특정 요일에 따른 평균 매출을 계산하는 쿼리

특정 요일, 예를 들어 금요일의 평균 매출을 계산할 때, 다음과 같이 작성합니다.

SELECT EXTRACT(DOW FROM order_date) AS day_of_week,
       AVG(order_amount) AS average_sales
FROM orders
WHERE EXTRACT(DOW FROM order_date) = 5
GROUP BY day_of_week;

이 쿼리는 주문일이 금요일(5)인 데이터를 추려내어, 그 평균 매출을 계산합니다.

분기별 매출을 비교하는 쿼리

분기별 매출을 비교하기 위해 EXTRACT() 함수와 CASE문을 결합합니다.

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(QUARTER FROM order_date) AS order_quarter,
       SUM(order_amount) AS total_sales
FROM orders
GROUP BY order_year, order_quarter
ORDER BY order_year, order_quarter;

이 쿼리는 각 연도, 각 분기의 매출 합계를 계산하고, order_year와 order_quarter로 그룹화하여 결과를 표시합니다.

여러 기간 조건을 결합한 쿼리

여러 기간 조건을 결합하여 데이터를 추출할 때, 여러 EXTRACT() 함수를 사용합니다. 예를 들어, 특정 월(1월과 2월)과 특정 시간대(오전 9시부터 오후 6시)에 따라 데이터를 추출하려면, 다음과 같이 작성합니다.

SELECT *
FROM orders
WHERE EXTRACT(MONTH FROM order_date) IN (1, 2)
  AND EXTRACT(HOUR FROM order_time) BETWEEN 9 AND 18;

이 쿼리는 1월과 2월의 오전 9시부터 오후 6시까지의 주문 데이터를 추출합니다.

성능 최적화를 위한 팁

EXTRACT() 함수를 사용한 쿼리의 성능을 최적화하기 위한 방법에 대해 설명합니다. 대량의 데이터를 다룰 때, 쿼리의 효율성이 중요해집니다.

인덱스 활용

날짜나 시간에 따른 쿼리의 성능을 향상시키기 위해, 관련 컬럼에 인덱스를 설정합니다. 예를 들어 order_date 컬럼에 인덱스를 생성합니다.

CREATE INDEX idx_order_date ON orders(order_date);

인덱스를 사용하면 날짜에 따른 검색이 빨라집니다. 다만, 인덱스 추가는 데이터베이스의 업데이트 성능에 영향을 미칠 수 있으므로, 필요한 컬럼에만 설정하는 것이 중요합니다.

부분 인덱스 활용

특정 조건에서의 검색을 최적화하기 위해 부분 인덱스를 사용합니다. 예를 들어 특정 연도나 월의 데이터를 자주 검색하는 경우, 해당 조건에 따른 부분 인덱스를 생성합니다.

CREATE INDEX idx_order_date_partial ON orders(order_date)
WHERE EXTRACT(YEAR FROM order_date) = 2023;

이 인덱스는 2023년의 데이터 검색을 빠르게 합니다.

쿼리 작성 방식의 개선

쿼리 작성 방식을 개선함으로써 성능을 향상시킬 수 있습니다. 예를 들어, 불필요한 계산이나 변환을 피하고, 직접적인 조건을 사용하는 것이 좋습니다.

-- 비효율적인 쿼리
SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

-- 효율적인 쿼리
SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

후자의 쿼리는 인덱스를 효과적으로 활용하며 성능이 향상됩니다.

뷰(View) 활용

자주 사용하는 복잡한 쿼리는 뷰로 변환하여 재사용 가능한 형태로 만듭니다. 뷰를 사용하면 쿼리의 가독성과 관리가 향상됩니다.

CREATE VIEW monthly_sales AS
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       SUM(order_amount) AS total_sales
FROM orders
GROUP BY order_year, order_month;

이와 같이 뷰를 생성하여 필요한 데이터를 쉽게 가져올 수 있으며, 쿼리 성능도 향상됩니다.

정리

이 기사에서는 SQL의 EXTRACT() 함수를 사용하여 날짜와 시간 데이터에서 특정 요소를 추출하는 다양한 방법을 설명했습니다. 기본적인 사용법부터 시작해 연도, 월, 일, 시간, 분, 초의 추출 방법, 더 나아가 분기나 요일의 추출, 특정 기간에 따른 데이터 추출, 복잡한 쿼리 응용, 그리고 성능 최적화 팁까지 모두 다루었습니다. EXTRACT() 함수를 효과적으로 활용하면 데이터 분석이나 보고서 작성의 효율이 크게 향상됩니다. 이러한 기술을 실천함으로써 SQL을 사용한 데이터 처리가 더욱 강력하고 유연해질 것입니다.