분석 데이터 로그를 별도의 테이블에 저장하기

상상해봐, 너가 일주일간의 판매 리포트를 만들었어. 계산도 끝났고, 클라이언트도 만족했지. 근데 한 달 뒤에 누가 와서 "그때 그 리포트 다시 보여줄 수 있어?"라고 물어봐. 만약 미리 데이터를 저장 안 했으면, 직접 복구하거나 "못 해요"라고 답해야 해. 이거 진짜 불편하고, 네 평판에도 영향 줄 수 있어.

분석 데이터 로그는 몇 가지 중요한 문제를 해결해줘:

• 히스토리 저장: 특정 기간 동안의 핵심 메트릭(예: 수익, 주문 수)을 기록할 수 있어.
• 감사 및 진단: 뭔가 잘못됐을 때, 어떤 데이터가 기록됐는지 항상 확인할 수 있어.
• 데이터 비교: 타임스탬프를 추가하면, 지표가 시간에 따라 어떻게 변했는지 분석할 수 있어.
• 데이터 재사용: 저장된 메트릭은 다른 분석 작업에도 쓸 수 있어.

핵심 아이디어: log_analytics 테이블

분석 데이터 로그를 위해, 모든 핵심 지표를 저장하는 전용 테이블을 만들어. 새로운 결과가 생길 때마다 테이블에 새로운 행이 추가되는 거지. 어떻게 동작하는지 쉽게 이해하려면, 기본 시나리오부터 시작해보자.

테이블 구조 예시

log_analytics 테이블에는 리포트 데이터를 저장할 거야. 구조는 이래 (DDL — Data Definition Language):

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- 레코드의 유니크 아이디
    report_name TEXT NOT NULL, -- 리포트나 메트릭 이름
    report_date DATE DEFAULT CURRENT_DATE, -- 리포트가 해당되는 날짜
    category TEXT, -- 데이터 카테고리(예: 지역, 상품)
    metric_value NUMERIC NOT NULL, -- 메트릭 값
    created_at TIMESTAMP DEFAULT NOW() -- 로그 기록 날짜와 시간
);

• log_id: 레코드의 기본 아이디야.
• report_name: 리포트나 메트릭 이름, 예를 들어 "Weekly Sales" 같은 거.
• report_date: 메트릭이 해당되는 날짜. 예를 들어 10월 1일 판매면 2023-10-01이 들어가.
• category: 데이터를 지역별 등으로 그룹화할 때 사용해.
• metric_value: 리포트 메트릭의 숫자 값.
• created_at: 로그 기록 타임스탬프.

log_analytics에 데이터 기록 예시

예를 들어, "북부" 지역의 10월 수익을 계산했다고 해. 이 값을 어떻게 저장할까?

INSERT INTO log_analytics (report_name, report_date, category, metric_value)
VALUES ('Monthly Revenue', '2023-10-01', 'North', 15000.75);

결과:

로그 기록용 프로시저 만들기

솔직히, 매주나 매달 데이터를 직접 입력하는 건 불가능하지. 그래서 프로시저로 자동화하는 게 좋아.

수익 데이터를 로그로 남기는 간단한 프로시저를 만들어보자:

CREATE OR REPLACE FUNCTION log_monthly_revenue(category TEXT, revenue NUMERIC)
RETURNS VOID AS $$
BEGIN
    INSERT INTO log_analytics (report_name, report_date, category, metric_value)
    VALUES ('Monthly Revenue', CURRENT_DATE, category, revenue);
END;
$$ LANGUAGE plpgsql;

이제 log_monthly_revenue 프로시저는 두 개의 파라미터를 받아:

• category: 데이터 카테고리, 예를 들어 지역.
• revenue: 수익 값

이렇게 함수 호출해서 수익을 기록할 수 있어:

SELECT log_monthly_revenue('North', 15000.75);

결과는 INSERT로 직접 넣는 거랑 똑같아.

로그 구조에 대한 추가 아이디어

가끔 핵심 메트릭이 하나가 아니라 여러 개일 수도 있어. 예를 들어 주문 수나 평균 결제 금액 같은 추가 지표도 같이 기록하고 싶을 때가 있지.

테이블 구조를 이렇게 바꿔보자:

CREATE TABLE log_analytics_extended (
    log_id SERIAL PRIMARY KEY,
    report_name TEXT NOT NULL,
    report_date DATE DEFAULT CURRENT_DATE,
    category TEXT,
    metric_values JSONB NOT NULL, -- 메트릭을 JSONB로 저장
    created_at TIMESTAMP DEFAULT NOW()
);

여기서 중요한 건 여러 메트릭을 한 필드에 JSONB 타입으로 저장한다는 거야.

확장 테이블에 기록 예시

예를 들어, 수익, 주문 수, 평균 결제 금액 세 가지 메트릭을 동시에 저장하고 싶어. 쿼리는 이렇게:

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Monthly Revenue',
    'North',
    '{"revenue": 15000.75, "orders": 45, "avg_check": 333.35}'::jsonb
);

결과:

로그 활용 예시: 수익 분석

모든 지역의 10월 총 수익을 알고 싶다고 해. 쿼리는 이렇게:

SELECT SUM((metric_values->>'revenue')::NUMERIC) AS total_revenue
FROM log_analytics_extended
WHERE report_date BETWEEN '2023-10-01' AND '2023-10-31';

로그 활용 예시: 지역별 트렌드

지역별 수익 변화를 분석해보자:

SELECT category, report_date, (metric_values->>'revenue')::NUMERIC AS revenue
FROM log_analytics_extended
ORDER BY category, report_date;

흔한 실수 처리하기

분석 데이터 로그를 남길 때 실수할 수 있는 부분이 있어. 그 실수들과 예방법을 알아보자.

• 실수: 카테고리나 날짜를 빼먹음. 테이블에 DEFAULT CURRENT_DATE처럼 기본값을 지정하는 게 좋아.
• 실수: 중복 레코드. 중복을 막으려면 유니크 인덱스를 추가해:

  CREATE UNIQUE INDEX unique_log_entry
  ON log_analytics (report_name, report_date, category);
  

• 실수: 0으로 나누는 메트릭 계산. 항상 나누는 값이 0인지 체크해! NULLIF를 써봐:

  SELECT revenue / NULLIF(order_count, 0) AS avg_check FROM orders;
  

실제 프로젝트에서의 활용

분석 데이터 로그는 다양한 분야에서 유용해:

• 리테일: 상품 카테고리별 수익과 판매 추적.
• 서비스: 서버나 앱의 부하 분석.
• 금융: 트랜잭션과 지출 관리.

이런 데이터는 단순히 "무슨 일이 있었는지" 설명하는 데 그치지 않고, 로그에서 보이는 걸 바탕으로 의사결정도 할 수 있게 해줘. 이제 PostgreSQL에서 분석 데이터 히스토리를 어떻게 기록하는지 알았지? 좋아, 앞으로 더 유용한 내용이 기다리고 있어!