PL/pgSQL로 분석 리포트 만들기

분석 리포트는 데이터를 체계적으로 보여주는 거라서, 의사결정할 때 도움돼. 예를 들면:

• 매니저들은 지난달 매출이 얼마였는지 보고 싶어해.
• 애널리스트들은 시장 트렌드를 찾고 있어.
• 개발자들은 앱 성능을 모니터링하고 있어.

상상해봐, 네가 엄청 큰 레스토랑의 셰프라고 치자. 어떤 메뉴가 제일 잘 나가는지 알려면 리포트가 필요하지. PostgreSQL은 네 레시피랑 주문 데이터베이스고, PL/pgSQL(프로시저)은 주방에서 주문 분석을 자동화해주는 네 조수야.

분석 리포트 기본 구조

분석 리포트는 데이터를 집계, 필터, 정렬, 순서대로 보여줘서 쓸모 있는 정보를 뽑아내는 도구야. 보통 리포트 구조는 이런 단계로 되어 있어:

1. 데이터 준비: 테이블에서 정보 뽑고, 필터링하고, 전처리하기.
2. 데이터 집계: 메트릭 계산(평균 결제금액, 전체 매출 등).
3. 포매팅: 보기 좋게 정렬해서 보여주기.
4. 결과 출력: 리포트를 사용자한테 보여주거나 테이블에 저장하기.

이 단계들은 전부 PL/pgSQL 프로시저로 만들 수 있어.

분석 리포트용 프로시저 만들기

기본적인 분석 리포트 만드는 예시를 볼게. 예를 들어 orders라는 테이블에 주문 데이터가 있다고 하자:

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

우리 목표: 지정한 달의 매출 합계 리포트 만들기. 즉, 보고 싶은 건:

• 월.
• 그 달의 전체 매출 합계.

프로시저 구조

우리 프로시저 플랜은 이래(겁먹지 마, PL/pgSQL 프로그래밍 별거 아냐):

1. 입력 파라미터로 월을 받기.
2. orders 테이블에서 그 달 데이터 뽑기.
3. 전체 매출 합계 계산하기.
4. 결과 리턴하기.

프로시저 구현

코드 예시:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- 지정한 달 데이터 뽑아서 집계하기
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY 1;
END;
$$ LANGUAGE plpgsql;

1. 입력 파라미터: p_month — 날짜야. 이걸로 월별로 데이터 필터링할 거야.
2. RETURN QUERY: 이건 마법 같은 거라, 프로시저에서 바로 데이터 리턴해줘.
3. DATE_TRUNC: order_date를 월의 시작으로 자를 때 써.
4. SUM: 주문 합계 계산하는 집계 함수야.
5. GROUP BY: 월별로 데이터 묶어. 리포트는 월 단위로 만드니까.

이제 함수 호출해보자:

SELECT * FROM monthly_sales_report('2023-08-01');

결과는 이런 식이야:

이 함수가 기본이야. 이제 좀 더 복잡하게 가보자!

더 복잡한 리포트 만들기

이번엔 매출을 고객별로 쪼개고 싶다고 해보자. 즉, 리포트에:

• 고객
• 월
• 그 달 고객 주문 합계

프로시저를 바꿔보자

CREATE OR REPLACE FUNCTION customer_monthly_report(p_month DATE)
RETURNS TABLE (
    customer_id INT,
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.customer_id,
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY o.customer_id, DATE_TRUNC('month', o.order_date);
END;
$$ LANGUAGE plpgsql;

이제 프로시저 호출:

SELECT * FROM customer_monthly_report('2023-08-01');

결과는 대충 이렇게 나와:

임시 테이블 사용하기

복잡한 리포트 만들 때 임시 테이블이 유용할 때가 있어. 예를 들어 중간 데이터를 처리해야 할 때.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- 임시 테이블 만들기
    CREATE TEMP TABLE temp_sales AS
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', p_month)
    GROUP BY customer_id, DATE_TRUNC('month', order_date);

    -- 이 테이블로 추가 계산이나 조작하기
    -- 예를 들어, 주문 합계 기준 상위 3명 고객 출력
    RAISE NOTICE '월 %의 TOP-3 고객:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE '고객: %, 합계: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

여기서 임시 테이블 temp_sales는 중간 결과 저장용이야.

꿀팁

1. 최적화: 인덱스 써서 데이터 조회 빠르게 해.
2. 0으로 나누기 에러: 항상 나누는 값 체크해서 리포트 망가뜨리지 마.
3. 날짜 포매팅: TO_CHAR 같은 함수로 보기 좋게 출력해.

너무 졸리지 않았길! 앞으로 더 복잡하고 재밌는 문제들이 기다리고 있으니까, 긴장 풀지 마!

1

설문조사/퀴즈

애널리틱스를 위한 프로시저, 레벨 59, 레슨 4

사용 불가능

애널리틱스를 위한 프로시저

애널리틱스를 위한 프로시저

열림