컬럼이 무슨 뜻인지 물어보고 나서 조건을 짰다

MySQL 에서 주문 통계를 뽑았는데 숫자가 안 맞는다는 말을 들었다. 조건이 틀린 것이었다.

Open Table of contents

원인 — 이름만 보고 짰다
물어보고 나서 알았다
세는 대상을 먼저 정했다
날짜와 검산
정리

원인 — 이름만 보고 짰다

처음 쓴 조건은 이랬다.

SELECT COUNT(*) FROM orders WHERE order_state = 'DONE';

DONE 이면 완료라고 생각했다. 그런데 order_state 에 어떤 값이 들어 있는지는 세어 보지도 않았다.

SELECT order_state, COUNT(*) FROM orders GROUP BY order_state;

READY      1,204
PAID      18,441
SHIPPED    8,220
DONE      42,118
CANCEL     2,904
REFUND       841
PARTIAL      312
HOLD          88

여덟 개였다. DONE 말고 PAID 와 SHIPPED 도 취소되지 않은 주문이라 매출에 들어가야 한다.

GROUP BY 로 값의 종류부터 세어 봤으면 조건을 그렇게 짜지 않았을 것이다.

물어보고 나서 알았다

각 상태가 무슨 뜻인지 담당자에게 물었다.

READY    주문서만 만들어진 상태. 결제 전
PAID     결제됨. 아직 안 보냄
SHIPPED  보냈음
DONE     구매 확정 (받고 나서 일정 기간 지남)
CANCEL   취소
REFUND   환불
PARTIAL  일부 취소. 남은 금액이 있다
HOLD     보류 (재고 없음 등)

PARTIAL 이 걸렸다. 일부 취소면 금액이 원래와 다르다.

SELECT total_amount, paid_amount FROM orders WHERE order_state='PARTIAL' LIMIT 3;

total_amount  paid_amount
50000         30000
28000         14000

total_amount 를 더하면 취소된 것까지 더한다. paid_amount 를 써야 맞는다.

이런 것은 코드를 아무리 읽어도 안 나온다. 이름의 유래와 예외는 사람에게 물어야만 알 수 있는 종류였다.

세는 대상을 먼저 정했다

숫자가 안 맞은 더 큰 이유는 매출이 한 가지가 아니었기 때문이다. 무엇을 매출이라 부르는지 물으니 답이 갈렸다.

결제된 금액       PAID + SHIPPED + DONE + PARTIAL 의 paid_amount
확정된 금액       DONE 의 paid_amount
환불 뺀 금액      위에서 REFUND 를 뺀다

셋 다 필요한 자리가 있었다. 하나로 정하는 것이 아니라 셋을 다 만들고 이름을 붙였다.

-- 결제 기준
SELECT SUM(paid_amount) FROM orders
WHERE order_state IN ('PAID','SHIPPED','DONE','PARTIAL') AND pay_date BETWEEN ? AND ?;

결제 기준 매출과 확정 기준 매출과 순매출로 이름이 붙으니 다음부터는 서로 어느 숫자를 말하는지 헷갈리지 않았다.

날짜와 검산

날짜 컬럼도 여럿이었다.

reg_date      주문서를 만든 날
pay_date      결제된 날
ship_date     보낸 날
done_date     확정된 날

5월 매출이 어느 날짜 기준인지가 또 갈렸다. 결제 기준이면 pay_date 다.

WHERE pay_date >= '2014-05-01' AND pay_date < '2014-06-01'

BETWEEN '2014-05-01' AND '2014-05-31' 로 쓰면 31일의 시각이 붙은 행이 빠진다. 끝 날짜가 그날 자정으로 읽히기 때문이다. DATE() 로 감싸 비교하는 방법도 있는데 그러면 인덱스를 못 쓴다.

내가 뽑은 값이 맞는지 다른 곳과도 맞춰 봤다.

내 쿼리             41,882,000
결제사 정산 내역     41,882,000

같았다. 안 맞았으면 어디가 다른지 봤어야 했다. 혼자 계산하고 끝내지 않고 맞춰 볼 것을 찾은 것이 마지막 확인이었다.

쿼리에는 왜 이 조건인지도 적었다.

/* 결제 기준 매출.
   PARTIAL 은 일부 취소된 것이라 total_amount 가 아니라 paid_amount 를 쓴다.
   REFUND 는 제외한다. 순매출은 별도 쿼리. */
SELECT SUM(paid_amount) ...

물어서 알아낸 상태 뜻도 문서로 남겼다. 다음 사람이 나처럼 이름만 보고 짜지 않게 하기 위해서인데 그때는 물어볼 사람이 없을 수도 있다.

정리

컬럼 이름만 보고 조건을 짜면 틀린다
GROUP BY 로 값의 종류를 먼저 세어 본다
각 값이 무슨 뜻인지 물어본다. 이름과 뜻이 다른 것이 있다
PARTIAL 처럼 금액이 달라지는 상태는 paid_amount 를 쓴다
세는 대상이 무엇인지부터 정한다. 매출이 하나가 아니었다
여러 기준이 다 필요하면 이름을 붙여 어느 것인지 드러나게 한다
날짜 컬럼도 여럿이다. 어느 날짜 기준인지 정한다
DATETIME 은 BETWEEN 대신 다음 날 미만으로 잡는다. DATE() 로 감싸면 인덱스를 못 쓴다
맞춰 볼 것을 찾는다. 혼자 계산하고 끝내지 않는다
알아낸 뜻을 문서로 남긴다. 다음 사람은 물어볼 데가 없을 수 있다