[BigQuery] 예제로 배우는 SQL LAG 함수

이번 글에서는 BigQuery에서 LAG 함수를 활용하여 주문 데이터 분석을 진행하겠습니다. BigQuery는 SQL 표준을 따르므로 대부분의 SQL 함수와 유사하게 사용할 수 있습니다.

■ 예제: 주문 데이터를 이용한 취소율 분석

회사에서는 다양한 주문을 처리하고 있으며, 이 주문 데이터는 BigQuery의 orders 및 order_status 테이블에 저장되어 있습니다. 데이터 분석가로서 각 주문의 취소율을 분석해야 합니다. 특히 이번 예제에서는 ‘pending’ 후 ‘cancelled’되는 건에 대해 집중해서 분석하고자 합니다.

orders 테이블은 각 주문에 대한 기본 정보를 포함하고 있습니다.

order_status 테이블은 각 주문의 상태 변경 이력을 포함하고 있습니다.

■ LAG 함수 소개

LAG 함수는 현재 행의 이전 행 값을 가져오는 윈도우 함수입니다. 데이터 분석에서 시계열 데이터나 상태 변화를 추적할 때 매우 유용합니다. LAG 함수의 기본 구문은 다음과 같습니다:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)

• column_name: 이전 값을 가져올 열
• offset: 얼마나 이전의 행을 참조할지 (기본값은 1)
• default_value: 이전 행이 없을 때 반환할 기본값 (옵션)
• PARTITION BY: 파티션을 나누는 기준
• ORDER BY: 행을 정렬하는 기준

■ ROW_NUMBER 함수 소개

ROW_NUMBER 함수는 결과 집합의 각 행에 고유한 번호를 할당하는 윈도우 함수입니다. PARTITION BY와 ORDER BY 절을 사용하여 특정 기준으로 행 번호를 매길 수 있습니다. ROW_NUMBER 함수의 기본 구문은 다음과 같습니다:

ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column)

• PARTITION BY: 파티션을 나누는 기준
• ORDER BY: 행을 정렬하는 기준

이 함수는 데이터 분석에서 각 행의 순서를 지정할 때 매우 유용합니다.

■ LAG 함수를 사용한 취소율 분석

이제 LAG 함수를 사용하여 각 주문의 상태 변화를 추적하고, 취소율을 계산하는 방법을 살펴보겠습니다.

첫 번째 취소 상태 추적

먼저 각 주문의 첫 번째 취소 상태를 찾기 위해 LAG와 ROW_NUMBER 함수를 사용합니다. 이 쿼리는 pending 상태 후에 첫 번째로 cancelled 상태가 나타나는 경우를 찾습니다.

WITH first_cancelled AS (
    SELECT
        order_id,
        status,
        status_date,
        LAG(status) OVER (PARTITION BY order_id ORDER BY status_date) AS prev_status,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY status_date) AS row_num
    FROM
        `project.dataset.order_status`
)
SELECT
    order_id,
    status,
    status_date,
    prev_status,
    row_num
FROM
    first_cancelled
WHERE
    status = 'cancelled' AND prev_status = 'pending' AND row_num = 2;

이 샘플 결과 테이블은 각 주문의 pending 상태 후 첫 번째로 cancelled 상태가 나타나는 레코드를 보여줍니다. 각 행에는 주문 ID, 현재 상태, 상태 변경 일자, 이전 상태, 그리고 행 번호가 포함되어 있습니다. LAG 함수는 이전 상태를 추적하고, ROW_NUMBER 함수는 각 주문의 상태 변경 이력에서 순서를 매깁니다. first_cancelled CTE의 결과는 다음과 같습니다.

취소율 계산

이제 각 주문의 총 금액과 취소된 금액을 계산하여 취소율을 구해보겠습니다.

WITH first_cancelled AS (
    SELECT
        order_id,
        status,
        status_date,
        LAG(status) OVER (PARTITION BY order_id ORDER BY status_date) AS prev_status,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY status_date) AS row_num
    FROM
        `project.dataset.order_status`
),
valid_cancelled AS (
    SELECT
        order_id,
        status,
        status_date
    FROM
        first_cancelled
    WHERE
        status = 'cancelled' AND prev_status = 'pending' AND row_num = 2
),
total_order_amount AS (
    SELECT
        order_id,
        SUM(total_amount) AS total_amount
    FROM
        `project.dataset.orders`
    GROUP BY
        order_id
),
cancelled_order_amount AS (
    SELECT
        o.order_id,
        o.total_amount
    FROM
        `project.dataset.orders` o
    JOIN
        valid_cancelled vc
    ON
        o.order_id = vc.order_id
)
SELECT
    t.order_id,
    t.total_amount AS total_order_amount,
    COALESCE(c.total_amount, 0) AS cancelled_order_amount,
    COALESCE(c.total_amount, 0) / t.total_amount AS cancellation_rate
FROM
    total_order_amount t
LEFT JOIN
    cancelled_order_amount c
ON
    t.order_id = c.order_id;

위의 쿼리는 각 주문의 총 금액과 취소된 금액을 계산하여 취소율을 구합니다. LAG 함수를 사용하여 pending 상태 후 첫 번째 cancelled 상태를 추적함으로써, 더 정확한 취소율을 계산할 수 있습니다.

■ 자주 발생하는 문제 및 해결 방법

• 성능 문제: LAG 함수는 윈도우 함수를 사용하므로 데이터 양이 많을 경우 성능 저하가 발생할 수 있습니다. 이 경우, 적절한 인덱싱과 파티셔닝을 통해 성능을 최적화할 수 있습니다.
• NULL 값 처리: 이전 행이 없을 경우 LAG 함수는 기본적으로 NULL 값을 반환합니다. 이 경우, default_value 파라미터를 사용하여 기본값을 설정할 수 있습니다.

LAG(status, 1, 'unknown') OVER (PARTITION BY an_id ORDER BY created_at) AS prev_status