예시: 최근 3개월 주문의 평균 결제 금액 계산

이번 강의에서는 재밌는 실전 예제를 다뤄볼 거야.

평균 결제 금액(average check)은 한 번 구매할 때 고객이 평균적으로 얼마를 쓰는지 보여주는 메트릭이야. 이건 비즈니스에서 핵심 메트릭 중 하나고, 이걸로:

• 구매력 변화를 분석할 수 있고,
• 판매 트렌드를 찾을 수 있고,
• 마케팅 캠페인 효과도 평가할 수 있어.

문제 정의

우리한테 orders 테이블이 있는 데이터베이스가 있다고 생각해봐. 여기 주문들이 저장돼 있어. 우리의 목표는:

1. 최근 3개월 동안 들어온 주문의 평균 결제 금액을 계산하기.
2. 이 계산을 프로시저로 자동화하기.
3. 결과를 따로 테이블에 저장해서 나중에 분석할 수 있게 하기.

데이터베이스 확장: orders 테이블 구조

먼저 필요한 데이터가 들어있는 테이블이 있는지 확인하자. orders 테이블 구조는 대충 이렇게 생겼을 거야:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

• order_id — 주문의 고유 식별자.
• customer_id — 주문한 고객.
• order_date — 주문이 들어온 날짜.
• total_amount — 주문의 총 금액.

예시로 몇 개 데이터 넣어서 연습해보자:

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

평균 결제 금액 수동 계산

자동화하기 전에, 최근 3개월 평균 결제 금액을 구하는 기본 쿼리를 먼저 써보자. 현재 날짜(CURRENT_DATE)랑 AVG() 함수를 써서 평균을 구할 거야.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

여기서 무슨 일이 일어나냐면:

• AVG(total_amount) — total_amount의 평균을 구하는 집계 함수야.
• CURRENT_DATE - INTERVAL '3 months' — 최근 3개월 주문만 뽑아와.
• ROUND(..., 2) — 결과를 소수점 둘째 자리까지 반올림해.

쿼리 결과는 대충 이렇게 나올 거야:

프로시저로 자동화하기

이제 우리의 목표는 이 계산을 자동으로 해주는 프로시저를 만들고, 결과를 따로 로그 테이블에 남기는 거야. 먼저 분석 로그 저장용 테이블을 만들자.

log_analytics 테이블 만들기

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — 기록된 날짜와 시간.
• metric_name — 메트릭 이름(여기선 "averagecheck_3_months").
• metric_value — 계산된 메트릭 값.

프로시저 만들기

이제 프로시저를 만들어보자. 이 프로시저는:

1. 최근 3개월 평균 결제 금액을 계산하고,
2. 결과를 log_analytics 테이블에 저장해.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- 1단계: 평균 결제 금액 계산
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- 2단계: 결과 로그 남기기
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- 디버깅용 정보 출력(선택 사항)
    RAISE NOTICE '평균 결제 금액: %', avg_check;
END;
$$ LANGUAGE plpgsql;

이제 이 함수를 호출하면 자동으로 log_analytics 테이블에 결과가 저장돼:

SELECT calculate_average_check();

작업 스케줄러로 자동화하기

지난 강의에서 이미 작업 스케줄러를 설치했었지. Linux라면 pg_cron 확장이고, Windows나 macOS라면 시스템 스케줄러(cron이나 Task Scheduler)로 설정했을 거야. 이제 준비가 끝났으니, 프로시저를 스케줄에 연결해보자.

Linux에서 pg_cron을 쓴다면, 이 확장이 데이터베이스에 활성화되어 있는지 확인해:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(참고: pg_cron 설치랑 shared_preload_libraries 설정은 지난 시간에 이미 다뤘어.)

이제 calculate_average_check() 함수를 예를 들어 매일 자정에 실행하도록 예약할 수 있어:

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

설명:

• 'daily_avg_check' — 작업 이름;
• '0 0 * * *' — 매일 00:00에 실행하는 cron 표현식;
• $$ 안의 명령 — 실행될 SQL이야.

Windows나 macOS라면 pg_cron이 안 되거나(Windows는 아예 안 됨, macOS는 직접 빌드해야 함) 하니까, 이미 시스템 스케줄러를 설정했을 거야. 이제 SQL 파일만 연결하면 돼.

1. 쿼리 들어있는 파일 만들기:

   echo "SELECT calculate_average_check();" > /path/to/script.sql
   

2. psql로 파일을 스케줄에 맞춰 실행하기:

   • Linux/macOS에서:

       0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

     (crontab -e로 추가)
   • Windows Task Scheduler에서는:
     • psql.exe 경로 지정.
     • 인자에:
       -U postgres -d your_database -f "C:\path\to\script.sql"

이렇게 하면 어떤 시스템이든 프로시저가 자동으로 실행되고, log_analytics 테이블에 평균 결제 금액이 정기적으로 기록돼. 어떤 방법을 쓰는지 모르겠으면 지난 강의로 돌아가서 각 플랫폼별 설치랑 설정 방법을 다시 보면 돼.

결과 확인 및 분석

이제 결과가 어떻게 나왔는지 보자. log_analytics 테이블에서 데이터 조회해보자:

SELECT * FROM log_analytics ORDER BY log_date DESC;

예시 결과:

이제 평균 결제 금액 계산 로그가 다 남아있어! 이 데이터로 리포트 만들거나, 메트릭 변화 분석도 할 수 있지.

자주 하는 실수와 예방법

평균 결제 금액 계산용 분석 프로시저를 쓸 때 흔히 하는 실수들이 있어.

그 중 하나는 — 결과가 비어있는 걸 깜빡하는 거야. 최근 3개월에 주문이 없으면 AVG()가 NULL을 반환해서 로그 남길 때 문제 생길 수 있어. 이럴 땐 COALESCE()를 쓰면 돼:

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

또 다른 실수는 orders 테이블에 잘못된 데이터가 들어있는 거야. 예를 들면, 주문 금액이 음수거나 날짜가 이상한 경우. 데이터 정기적으로 점검하거나, 데이터베이스 레벨에서 제약조건(예: CHECK (total_amount > 0))을 추가하는 게 좋아.

축하해, 이제 최근 3개월 평균 결제 금액을 자동으로 계산하고 결과를 저장하는 완벽한 프로시저가 생겼어. 이건 PostgreSQL이랑 PL/pgSQL로 분석 작업을 자동화하는 수많은 예시 중 하나일 뿐이야. 다음 강의에서는 더 복잡한 분석 시나리오도 다뤄볼 거니까, 또 보자!