데이터베이스 질문과 답변
질문이 있어 글을 올립니다.
우선 데이터가 아래와 같이 있을때
| 포인트 적립이력 | 테이블명 : POINT_DETAIL | ||
| USER_ID | POINT | POINT_DESC | REG_TIMESTAMP |
| AAA | 5000 | 가입포인트 적립 | 2019-06-01 |
| AAA | 1000 | 상품구매 적립 | 2019-06-02 |
| AAA | 100 | 출석 적립 | 2019-06-02 |
| AAA | 5000 | 상품구매 적립 | 2019-06-03 |
| AAA | -5000 | 상품구매 적립 취소 | 2019-06-03 |
| AAA | 7000 | 상품구매 적림 | 2019-06-03 |
| AAA | -7000 | 상품구매 적립 취소 | 2019-06-03 |
| AAA | -100 | 이벤트참여 | 2019-06-03 |
| AAA | -1000 | 이벤트참여 | 2019-06-04 |
| AAA | 500 | 상품구매 적립 | 2019-06-10 |
| AAA | -1000 | 이벤트참여 | 2019-06-11 |
| 포인트 적립(SUM) | |||
| USER_ID | TOTAL_POINT | USED_POINT | REMAIN_POINT |
| AAA | 18600 | 14100 | 4500 |
여기에서 TOTAL_POINT의 내용은 적립한 금액을 모두 더한 값입니다.
USED_POINT 는 사용한 포인트(마이너스값)를 더한 값
REMAIN_POINT는 TOTAL_POINT값과 USED_POINT 값을 더한 값입니다.
<쿼리문>
SELECT A.USER_ID
,SUM(CASE WHEN CAST(A.POINT AS INT) > 0 THEN CAST(A.POINT AS INT) ELSE 0 END) AS TOTAL_POINT
,SUM(CASE WHEN CAST(A.POINT AS INT) < 0 THEN ABS(CAST(A.POINT AS INT)) ELSE 0 END ) AS USED_POINT
,SUM(CAST(A.POINT AS INT)) AS REMAIN_POINT
FROM POINT_DETAIL A
GROUP BY A.USER_ID
하지만 이력 데이터에서 같은 날짜에 같은 가격으로 적립과 취소가 발생하면
해당 포인트는 0 으로 처리해야합니다.
정답은 아래와 같이 나와야하는데
쿼리를 어떻게 만들어야 할까요?
| 포인트 적립(SUM) | |||
| USER_ID | TOTAL_POINT | USED_POINT | REMAIN_POINT |
| AAA | 6600 | 2100 | 4500 |
1. 같은날 동일포인트가 들어왔다 빠지는 조건에는 오류가 있습니다.
- 적립취소가 동일 날짜에만 발생된다고 장담할 수 없고.
- 적립취소가 아닌데도 우연히 동일한 금액을 사용하는 경우가 발생할 수도 있습니다.
- 적립취소에 해당하는 금액을 차감하는 방식을 써야 합니다.
2. CAST 는 왜 써야 하나요?
- 해당 항목이 문자형 컬럼인가요?
- 숫자형이라면 쓸 필요가 없고,
- 문자형이라면 설계가 잘못된 거네요. 숫자형으로 변경을 권고합니다.
WITH point_detail AS
(
SELECT 'AAA' user_id, 5000 point, '가입포인트 적립' point_desc, '2019-06-01' reg_timestamp
UNION ALL SELECT 'AAA', 1000, '상품구매 적립' , '2019-06-02'
UNION ALL SELECT 'AAA', 100, '출석 적립' , '2019-06-02'
UNION ALL SELECT 'AAA', 5000, '상품구매 적립' , '2019-06-03'
UNION ALL SELECT 'AAA', -5000, '상품구매 적립 취소', '2019-06-03'
UNION ALL SELECT 'AAA', 7000, '상품구매 적림' , '2019-06-03'
UNION ALL SELECT 'AAA', -7000, '상품구매 적립 취소', '2019-06-03'
UNION ALL SELECT 'AAA', -100, '이벤트참여' , '2019-06-03'
UNION ALL SELECT 'AAA', -1000, '이벤트참여' , '2019-06-04'
UNION ALL SELECT 'AAA', 500, '상품구매 적립' , '2019-06-10'
UNION ALL SELECT 'AAA', -1000, '이벤트참여' , '2019-06-11'
)
SELECT user_id
, ISNULL(SUM(CASE WHEN point > 0 OR point_desc LIKE '%적립 취소' THEN point END), 0) total_point
, ISNULL(SUM(CASE WHEN point < 0 AND point_desc NOT LIKE '%적립 취소' THEN -point END), 0) used_point
, SUM(point) remain_point
FROM point_detail
GROUP BY user_id
;
) 버튼을 클릭하여 작성 하시면 됩니다.