DW/ETL - 기간 누계(YTD, MTD) 데이터

개요

DW 비즈니스 환경에서는 기간 단위로 누계 데이터를 요구하는 케이스가 무척 많다. 대표적으로 연 누계(Year-To-Date, YTD)와 월 누계(Month-To-Date, MTD)이다. 연 누계란 특정 년도의 시작일부터 기준일까지의 누적 데이터를 의미하며 MTD는 특정 월 시작일부터 기준일까지의 누적데이터를 의미한다. 이 두가지 누계 데이터가 많이 사용되는 이유는 비즈니스 환경에서 데이터를 유의미하게 나누는 기준이 년, 월 단위이기 때문이다. 년, 월 단위 누계 데이터를 조회할 경우 성장세, 목표달성률, 트랜드 등을 한 눈에 보기 편하고 유의미한 전략적 의사결정을 내리기 쉽다. 따라서 기간 누계 데이터를 다루는 방법에 대해서 알 필요가 있다.

MTD와 YTD 물리 적재의 문제점

우선 DW에서는 MTD, YTD를 물리적으로 저장하는 것을 권장하지 않는다는 점을 알 필요가 있다. MTD, YTD를 저장한다면 그레인으로 데이터를 저장하는 펙트 테이블에서 한 번 더 계산을 거쳐 데이터 마트 혹은 집계 테이블에 저장된다. 한 번 더 계산을 거쳐야하기에 원본이 되는 펙트 테이블의 그레인에 변동이 생겼을 때 이를 반영한 계산을 한 번 더 해주지 않으면 저장된 데이터와 차이가 생기게 된다. 이는 데이터 일관성을 해칠 수 있고 데이터 조회시에 문제를 야기한다. 뿐만 아니라 원본 데이터에서 계산되어 나오는 데이터이기에 데이터의 중복이 발생하였따고 볼 수 있고 저장 리소스의 사용 증가로 이어질 수 있다. 따라서 이러한 문제점을 피하기 위해서는 BI 툴 및 데이터 조회 애플리케이션 단위에서 계산을 하여 사용하는 것이 권장된다.

MTD, YTD의 물리 적재

하지만 위의 문제점에도 불구하고 MTD, YTD를 물리적재하여 사용하는 경우는 많이 있다. 그 원인은 애플리케이션 단위로 계산하여 사용한다는 것은 심각한 성능 저하를 야기하기 때문이다. BI 보고서 및 대시보드는 데이터를 조회하는 데에 있어서 성능 문제를 일으키는 경우가 많다. 월 단위로 계산이 되어 있는 데이터 마트에서 데이터를 불러오는 데에도 화면 조회에 분 단위 시간이 소요되는 케이스도 본 적이 있다. 이러한 문제를 조금이라도 줄이기 위해서는 현실적인 이유로 데이터 마트 및 집계 테이블을 구성해야만 한다. 일단위 나누어져 있는 데이터를 월 단위, 년 단위로 묶어서 조회할 수 있다면 그것만으로도 데이터의 사용이 원활해질 수 있다.

MTD, YTD 집계 예시

집계 테이블

Date	Product_ID	Store_ID	YTD_Sales_Amount	MTD_Sales_Amount
2023-01-01	101	1	100	100
2023-01-02	101	1	200	200
2023-01-03	101	1	300	300
2023-01-04	101	1	400	400
2023-01-05	101	1	500	500
...
2023-02-01	101	1	3200	100
2023-02-02	101	1	3300	200

예시에서는 Date가 조회 기준일이 되고 YTD_Sales_Amount가 연 누계 MTD_Sales_Amount가 월 누계가 된다. 연 누계에서는 당년 1월 1일부터 기준일까지의 누계를 계산하여 적재하고 월 누계에서는 기준일이 포함된 월의 1일부터 기준일까지의 누계를 적재한다.

 

INSERT INTO ytd_mtd_sales (sales_date, product_id, store_id, ytd_sales_amount, mtd_sales_amount)
SELECT
    sales_date,
    product_id,
    store_id,
    SUM(sales_amount) 
    OVER (PARTITION BY product_id, store_id, EXTRACT(YEAR FROM sales_date) 
    ORDER BY sales_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd_sales_amount,
    SUM(sales_amount) 
    OVER (PARTITION BY product_id, store_id, TRUNC(sales_date, 'MM') 
    ORDER BY sales_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS mtd_sales_amount
FROM
    sales;

 

집계 쿼리를 작성한다면 아래와 같이 사용할 수 있다.

• PARTITION BY로 제품, 매장, 월/년별로 파티션을 나눈다.
• ORDER BY sales_date로 날짜별 정렬한다.
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 파티션의 시작점으로부터 현재일까지 계산한다.

이를 기준 날짜 값을 입력받는 프로시저 개발한다면 아래와 같이 정리할 수 있다.

CREATE OR REPLACE PROCEDURE calculate_and_insert_ytd_mtd_sales (
    p_date IN DATE
) IS
BEGIN
    -- 기존에 동일한 날짜 데이터가 있을 경우 삭제
    DELETE FROM ytd_mtd_sales WHERE sales_date = p_date;

    -- 모든 제품 및 매장에 대한 YTD 및 MTD 계산 후 삽입
    INSERT INTO ytd_mtd_sales (sales_date, product_id, store_id, ytd_sales_amount, mtd_sales_amount)
    SELECT
        p_date,
        product_id,
        store_id,
        SUM(sales_amount) OVER 
        (PARTITION BY product_id, store_id, EXTRACT(YEAR FROM sales_date) 
        ORDER BY sales_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd_sales_amount,
        SUM(sales_amount) OVER 
        (PARTITION BY product_id, store_id, TRUNC(sales_date, 'MM') 
        ORDER BY sales_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS mtd_sales_amount
    FROM
        sales
    WHERE
        sales_date <= p_date;
END calculate_and_insert_ytd_mtd_sales;
/

 

윈도우 함수를 사용하지 않고 GROUP BY를 통한 계산도 가능하지만 이 경우 가독성 문제 및 각 행마다 서브 쿼리를 사용하는 문제로 성능이 떨어질 수 있다.

CREATE OR REPLACE PROCEDURE calculate_and_insert_ytd_mtd_sales (
    p_date IN DATE
) IS
BEGIN
    -- 기존에 동일한 날짜 데이터가 있을 경우 삭제
    DELETE FROM ytd_mtd_sales WHERE sales_date = p_date;

    -- YTD 계산
    INSERT INTO ytd_mtd_sales (sales_date, product_id, store_id, ytd_sales_amount, mtd_sales_amount)
    SELECT
        p_date,
        product_id,
        store_id,
        (SELECT SUM(sales_amount)
         FROM sales s2
         WHERE s2.product_id = s1.product_id
           AND s2.store_id = s1.store_id
           AND s2.sales_date BETWEEN TRUNC(p_date, 'YEAR') AND p_date) AS ytd_sales_amount,
        (SELECT SUM(sales_amount)
         FROM sales s3
         WHERE s3.product_id = s1.product_id
           AND s3.store_id = s1.store_id
           AND s3.sales_date BETWEEN TRUNC(p_date, 'MONTH') AND p_date) AS mtd_sales_amount
    FROM
        sales s1
    WHERE
        sales_date <= p_date
    GROUP BY
        product_id, store_id;
END calculate_and_insert_ytd_mtd_sales;

결론

MTD, YTD에 대해서 다루어보았다. 처음 데이터 마트 집계 프로시저를 보았을 때 정신없어 보였던 쿼리가 MTD, YTD 쿼리였다. 무척이나 긴 쿼리들이 하는 일은 결국 얽힌 데이터 속에서 적절한 집계를 계산해 내는 것이었다. 쿼리를 작성하면서 빠뜨리는 게 없을까 조바심을 내게 만들던 테이블이었다. 그랬기에 이에 대해서 정리를 해보았다. 정리를 해보고 나니 그렇게까지 다루기 어려운 데이터는 아니라는 생각이 든다. 결국 정해진 규칙대로 묶어주면 될 뿐인 쿼리이니 말이다.