예제로 배우는 SQL 날짜 함수

이번 글에서는 SQL을 사용하여 날짜를 처리하는 방법에 대해 알아보자. 이 글에서는 주요 SQL 날짜 함수인 v.number, EOMONTH, DATEADD와 DATEDIFF에 대해 자세히 살펴보겠다.

■ 사례

온라인 광고와 캠페인 관리를 전문으로 하는 디지털 마케팅 에이전시가 있다고 하자. 이 에이전시에서는 다양한 고객을 대상으로 여러 달에 걸쳐 디지털 마케팅 캠페인을 운영한다. 캠페인은 종종 월 단위로 청구되며, 여러 기간에 걸친 캠페인 성과에 따라 에이전시에게 커미션이 적립된다.

고객 ABC는 새로운 의류에 대한 프로모션 캠페인을 운영하고 있다. 이 캠페인은 2023년 11월 15일부터 2024년 1월 31일까지 실행될 예정이다. 캠페인의 총 커미션은 9,000달러로 책정되었다.

고객에게 청구할 때는 월별 내역을 포함해야 한다. 즉, 해당 월의 캠페인 기간을 기준으로 매월 총 커미션의 일부를 계산해야 한다. 그러나 이 캠페인은 달력 월과 깔끔하게 일치하지 않고 월 중순에 시작하고 종료된다. 따라서 11월, 12월, 1월에 대한 커미션 계산이 약간 복잡해진다. 예를 들어, 11월에 진행되는 캠페인의 일수는 16일이고 12월에 진행되는 캠페인의 일수는 31일, 1월에는 31일이다. 캠페인이 매월 동일한 일수에 걸쳐 진행되지 않기 때문에 월별 커미션은 단순히 총 커미션의 3분의 1이 아니다. SQL 자동화를 통해 여러 캠페인에 대한 계산을 처리해보자

■ SQL 날짜 관련 함수 (v.number, EOMONTH, DATEADD, DATEDIFF) 개념

v.number 함수는 내장된 SQL 함수는 아니지만 master.dbo.spt_values 시스템 테이블을 사용하는 SQL Server의 일반적인 기술이다. 이 함수는 일련의 숫자를 생성하며, 시퀀스를 만들거나 범위를 반복하는 데 자주 사용된다. 여기서는 캠페인의 개월 수에 걸쳐 반복하는 데 사용된다.

INNER JOIN master.dbo.spt_values v 
ON v.type = 'P' AND v.number BETWEEN 0 AND DATEDIFF(MONTH, a.campaign_start, a.campaign_end)

위 SQL 코드에서 v.type = ‘P’는 숫자 집합을 만드는 데 사용되는 행을 나타낸다. 즉, 이 쿼리는 캠페인 시작월(campaign_start)에서 캠페인 종료월(campaign_end)에 대한 순서를 0부터 부여한다. 예를 들어, 캠페인 시작월이 2023년 11월이고 캠페인 종료월이 2024년 1월이라고 할 때 v.number는 다음과 같다.

EOMONTH는 지정된 날짜의 월 마지막 날을 가져오는 데 사용된다. 이 함수는 특정 개월이 남은 날짜의 월말을 계산할 수도 있다. 예를 들어 EOMONTH(‘2021-01-15’, 1)는 2021년 2월의 마지막 날을 반환한다.

DATEADD 함수는 날짜에 특정 시간 단위(일, 월, 년 등)를 더하거나 뺀다. 예를 들어 DATEADD(month, 1, ‘2021-01-15’)는 2021년 1월 15일에 한 달을 더해 2021년 2월 15일을 반환한다.

DATEDIFF는 두 날짜 간의 차이를 계산한다. 예를 들어, DATEDIFF(day, ‘2021-01-01’, ‘2021-01-31’)는 두 날짜 사이의 일수 차이, 즉 30일을 반환한다.

■ SQL 예제 테이블 만들기

먼저 예제 테이블 2개를 만들어 보겠다.

campaign_meta 테이블에는 캠페인에 대한 메타데이터가 포함되어 있다.

• campaign_id: 각 캠페인의 고유 식별자
• campaign_start: 캠페인의 시작일
• campaign_end: 캠페인 종료일

campaign_splits 테이블에는 각 캠페인에 대한 커미션 분할 정보가 포함되어 있다.

• site_id: 캠페인이 실행되는 각 사이트의 고유 식별자
• campaign_id: 캠페인 식별자 (campaign_meta 테이블과 연결 키)
• commission: 캠페인의 총 커미션

-- Create campaign_meta
CREATE TABLE campaign_meta (
    campaign_id VARCHAR(50) PRIMARY KEY,
    campaign_start DATE,
    campaign_end DATE
);

-- Create campaign_splits
CREATE TABLE campaign_splits (
    site_id INT,
    campaign_id VARCHAR(50),
    commission DECIMAL(10, 2),
    FOREIGN KEY (campaign_id) REFERENCES campaign_meta(campaign_id)
);

-- Insert sample data into campaign_meta 
INSERT INTO campaign_meta (campaign_id, campaign_start, campaign_end) VALUES 
('CAMP123', '2023-11-01', '2024-01-31'),
('CAMP456', '2023-12-15', '2024-01-15');

-- Insert sample data into campaign_splits
INSERT INTO campaign_splits(site_id, campaign_id, commission) VALUES 
(101, 'CAMP123', 9000.00),
(102, 'CAMP456', 5000.00);

campaign_meta 테이블에는 두 개의 캠페인이 있다.

CAMP123은 2023년 11월 1일부터 2024년 1월 31일까지 진행되며,
CAMP456은 2023년 12월 15일부터 2024년 2월 15일까지 진행된다

campaign_splits에는 커미션이 기록된다.

사이트 101의 CAMP123의 경우 총 수수료는 $9,000이며,
사이트 102의 CAMP456의 경우 총 수수료는 $5,000이다.

■ 문제 해결 예시 SQL

이 사례에서는 캠페인 기간을 개별 월로 나누고, 각 월에 대한 커미션을 비례로 계산해야 하는 다소 복잡한 계산 과정을 거쳐야 한다. 이런 경우, 월별 기간에 대한 CTE를 만드는 것이 좋다. 아래 테이블을 SQL로 만들어보자 (정답은 아래 있으나 먼저 한 번 연습해보세요).

SELECT 
        b.site_id,
        a.campaign_id,
        a.campaign_start,
        a.campaign_end,
        b.commission,
        CASE 
            WHEN v.number = 0 THEN a.campaign_start 
            ELSE DATEADD(DAY, 1, EOMONTH(a.campaign_start, v.number - 1))
        END as month_start,
        EOMONTH(a.campaign_start, v.number) as month_end,
        v.number as month_number
    FROM 
        campaign_meta as a
    INNER JOIN 
        campaign_splits as b ON a.campaign_id = b.campaign_id
    INNER JOIN 
        master.dbo.spt_values v ON v.type = 'P' AND v.number BETWEEN 0 AND DATEDIFF(MONTH, a.campaign_start, a.campaign_end);

이제 해당 CTE를 이용하여 커미션이 해당하는 월(month_of_commission), 해당 월에 대한 캠페인 기간(days_in_month), 해당 월에 대한 커미션(monthly_commission)을 계산해보자. 최종 결과물은 다음과 같아야 한다.

WITH MonthlyPeriods AS (
    SELECT 
        b.site_id,
        a.campaign_id,
        a.campaign_start,
        a.campaign_end,
        b.commission,
        CASE 
            WHEN v.number = 0 THEN a.campaign_start 
            ELSE DATEADD(DAY, 1, EOMONTH(a.campaign_start, v.number - 1))
        END as month_start,
        EOMONTH(a.campaign_start, v.number) as month_end,
        v.number as month_number
    FROM 
        campaign_meta as a
    INNER JOIN 
        campaign_splits as b ON a.campaign_id = b.campaign_id
    INNER JOIN 
        master.dbo.spt_values v ON v.type = 'P' AND v.number BETWEEN 0 AND DATEDIFF(MONTH, a.campaign_start, a.campaign_end)
)
SELECT 
    site_id,
    campaign_id,
    campaign_start,
    campaign_end,
    commission,
    FORMAT(month_start, 'yyyy-MM') as month_of_commission,
    DATEDIFF(DAY, 
        CASE WHEN month_start < campaign_start THEN campaign_start ELSE month_start END,
        CASE WHEN month_end > campaign_end THEN campaign_end ELSE month_end END
    ) + 1 as days_in_month,
    monthly_commission
FROM 
    (SELECT *, 
        (commission / (DATEDIFF(DAY, campaign_start, campaign_end) + 1)) * 
        (DATEDIFF(DAY, 
            CASE WHEN month_start < campaign_start THEN campaign_start ELSE month_start END,
            CASE WHEN month_end > campaign_end THEN campaign_end ELSE month_end END
        ) + 1) as monthly_commission
    FROM MonthlyPeriods) as MonthlyCommissions;

이 글에서는 MSSQL의 날짜 관련 함수들의 사용법과 이를 활용한 실제 사례를 살펴보았다. DATEADD, EOMONTH, DATEDIFF 같은 함수들은 데이터베이스에서 날짜 데이터를 효율적으로 다루는 데 필수적이며, 이를 통해 데이터 관리와 분석이 더욱 용이해진다.