데이터베이스 질문과 답변
안녕하세요 이제 날씨도 많이 선선해지고 가을이 벌써 다가온것 같습니다.
이럴때일수록 감기 조심하셔요.
다름이 아니라 지난번 여러 회원분들께서 조언해주신덕에 SQL 공부를 하던 와중 집계함수 사용과 관련한 어려움이 있어 고견을 얻고자
영어로 검색하거나 구루비에서 선례를 찾아보려 했지만 도저히 찾을 수 없어 거의 일주일간 고심하다 부족함을 느끼고 이렇게 질문을 드리게 되었습니다.
구성되는 구조는 다음과 같습니다.
1. 실시간 환율과 거래금액을 데이터 베이스에 저장합니다. (거래 데이터 베이스)
실시간으로 전송받은 환율은 rate 컬럼에 저장되며 고객이 환전소로 환전을 신청하는 금액은 amount에 저장됩니다.
margin 컬럼은 실시간환율에서 거래 마진을 남기기 위해 회원별로 정해진 거래 시점의 마진율을 기록하여 추후 정산합니다.
afflicate_id 컬럼은 영업사원의 아이디 인데 영업사원은 각각 고유의 커미션 지급율을 가지고 있습니다.
| id | user_id | rate | request_amount | margin | status | afflicate_id | created_at | updated_at |
| 1 | 홍길동 | 1135.5 | 10000000 | 10 | 대기 | 김철수 | 2021-09-08 12:22:23 | |
| 2 | 홍길자 | 1138.2 | 100000 | 12 | 완료 | 강준호 | 2021-08-30 17:30:21 | 2021-08-30 17:33:11 |
2. 영업사원 아이디는 다음과 같이 구성됩니다.
commitions 컬럼은 현재 누적 커미션요금을 말합니다.
commition_rate 컬럼은 회원별 고객을 유치하여 거래 데이터베이스의 status 컬럼이 완료 처리가 되었을경우
request_amount에 대한 margin에서 commition_rate 컬럼에 입력된 백분율 값 만큼 commitions로 적립하기 위해 존재합니다.
예 : 거래데이터베이스 의 id 2번 홍길자의 경우 강준호의 commitions
( ( 100000 / 1138.2 ) - ( 100000 / 1138.2 * ( 12 / 100) ) * 40 / 100 ) = 4.2168
| id | user_id | commitions | commition_rate | status | created_at |
| 1 | 김철수 | 12300 | 40 | 정상 | 2021-07-30 |
| 2 | 강준호 | 10120 | 32 | 정상 | 2021-06-23 |
3. 결과적으로 조인과 같은 작업을 거쳐 나와야 할 테이블은 다음과 같은 형태입니다.
| 거래일시 | 총 받은금액 | 거래건수 | 총 출금액 | 처리상태 | 거래마진 | 영업사원 지급수수료 | 최종 마진 |
| 2021-09-08 | 10000000 | 1 | 8,806.69 | 대기 | |||
| 2021-08-30 | 100000 | 32 | 18612.26 | 완료 | 1000.54 | 400.21 | 600.33 |
제가 작성한 쿼리문은 거래 데이터 베이스 테이블에 회원 테이블에 존재하는 고유 아이디를 작성하고
단순 LEFT JOIN 하여 SUM( (tb.request_amount / tb.rate) * (ab.commition_rate / 100) ) as user_commitions 와 같은 형태로
SELECT 문을 작성해서 매 거래별 결과값은 정상적으로 출력하였습니다.
하지만 개별 결과값에 대해서는 정상적으로 출력이 되나 거래일시로 월별 일별 주별 통계를 내기위해 GROUP BY를 하는경우
정상적으로 출력이 되는 날짜가 있고 아닌 날짜가 있어 일일히 계산을 해 본결과,
정상적으로 출력이 되는 날짜는 계산을 위한 커미션요율 또는 환율이 동일한 경우 (하루에 한 영업사원만 거래가 있는경우) 에 정상적으로 출력이 되고,
여러 영업사원이 거래를 할 경우 정상적으로 출력되지 않는것으로 확인하여 OVER() 함수로 commition 테이블을 보니 나중에 존재하는 commition 요율이
다른 커미션들을 다 대체를 하는 것을 확인 하였습니다..
결국 지금 떠오르는 생각은 OVER() 함수로 각각 다른 값을 가진 요율 컬럼을 지정하여 1차 계산이후 일자별로 출력을 해야할것 같은데
좋은 해결방안이 없어 서브쿼리로 수를 맞추자니 맞출수 없는 계산식도 있고 쿼리가 복잡해 지는 느낌이 들고,
사실 오늘 OVER() 함수로 시도를 해봤지만 정상적으로 결과값을 만들수 없어 지푸라기라도 잡는 심정으로 고견을 구합니다.
저런 경우와 같이
결과 테이블의 한 행에 존재하는 컬럼끼리 계산을 해야하는 상황에서 각각 다른 요율을 가지고 있는 컬럼을 GROUP BY 하여 통계를 내야할경우,
각각 다른 요율을 가지고 있는 컬럼의 원래 고유 값들을 유지한 채 집계를 하는 방법이 궁금합니다.
이른 새벽인데 질문 글을 쓰며 다시금 더 공부를 해야겠다는 생각이 드네요..
오늘 하루도 좋은 하루되시길 바랍니다.
긴 글 읽어주셔서 감사합니다.
1. 사용하신 쿼리를 올려주셔야 뭐가 잘못인지 판단이 가능하죠.
- 정상쿼리(개별 계산 쿼리)와
- 오류쿼리(기간 집계 쿼리)를 올려 주세요.
2. 추측성 답변
- 개별집계 쿼리를 가지고 그대로 집계를 하셨다면? 문제될 일이 없을 것 같습니다.
- 그대로 집계한게 아니라 중간에 안해도 될 불필요 작업을 한게 아닐까? 생각되네요.
마농님 안녕하세요
지금 출근해서 말씀 확인했습니다. 감사합니다.
자면서도 어떻게 해결해야할지 생각했어요.
오늘 한번더 스스로 해보고 질문에 부족한 쿼리를 포함해 다시 질문 올리겠습니다.
그리고 이건 질문 외적인 이야긴데요.
그루비 질문글을 보다보니까 저도 답변을 달수 있는 글들이 보여서요.
혹시 답변을 달고싶을때 그때마다 데이터베이스에 실제 테이블을 생성하지 않고 간단하게
임시 테스트 해볼수 있는 방법이 있나요?
혹시 존재한다면 함수명만 알려주시면 제가 스스로 찾아서 공부 해 보겠습니다.
오늘은 비가 오지 않네요. 오늘 하루도 즐거운 하루되세요 감사합니다.
-- 간단한 테스트는 WITH 구문 이용 하세요. -- WITH test AS ( SELECT 1 v FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual ) SELECT SUM(v) FROM test WHERE v > 1 ;
) 버튼을 클릭하여 작성 하시면 됩니다.