데이터베이스 질문과 답변

데이터 합계 문제 질문입니다. 0 1 1,061

by 힘내라김대리 [SQLServer] 데이터합 [2019.06.13 19:52:08]

질문이 있어 글을 올립니다.

 

우선 데이터가 아래와 같이 있을때

 

포인트 적립이력 테이블명 : 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

 

by 마농 [2019.06.13 23:52:12]

1. 같은날 동일포인트가 들어왔다 빠지는 조건에는 오류가 있습니다.
 - 적립취소가 동일 날짜에만 발생된다고 장담할 수 없고.
 - 적립취소가 아닌데도 우연히 동일한 금액을 사용하는 경우가 발생할 수도 있습니다.
 - 적립취소에 해당하는 금액을 차감하는 방식을 써야 합니다.
2. CAST 는 왜 써야 하나요?
 - 해당 항목이 문자형 컬럼인가요?
 - 숫자형이라면 쓸 필요가 없고,
 - 문자형이라면 설계가 잘못된 거네요. 숫자형으로 변경을 권고합니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입