빌링의 차감 및 선입선출 방법
작성일 13-01-04 11:25
페이지 정보
작성자서방님 조회 121회 댓글 0건본문
출처 : http://dbstroy.egloos.com/689809
시나리오<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
다음과 같은 테이블 있다.
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" />
고객 yasicom은 bill 테이블과 같이 캐시가 충전되어 있다. 그리고 bill_policy테이블에는 어떤 요금부터 차감을 할 것인지에 대한 계산의 우선순위가 매겨져 있다. 또한 충전한 요금유형이 여러 개라면 먼저 충전된 캐시가 차감된다. 즉, 선입선출이다. 그러므로 다음과 같은 우선순위를 갖는다.
만약 17,000원짜리 아이템을 구매하였다면, 다음과 같이 차감되어야 한다.
1번째 Row: 500 - 17000 = -16500
2번째 Row: 2000 - 16500 = -14500
3번째 Row: 500 - 14500 = -14000
4번째 Row: 10000 - 14000 = -4000
5번째 Row: 5000 - 4000 = 1000
즉, 17,000원 짜리 아이템을 구매했으면 충전된 캐시가 다음과 같이 차감(업데이트)되어야 한다.
MSSQL 2005의 구현방법
2000버전의 MSSQL Server는 이러한 구현을 하려면 매우 우울했었다. 많은 방법을 생각해보았지만 커서만한 솔루션이 없었으니까 말이다. 어찌되었던 2005 버전에서는 CTE를 사용하는 재귀쿼리를 이용하여 선입선출 및 차감 Row단위의 접근이 어느 정도 가능해졌다. 뭐 집합적으로 가능해졌다고 할 수도 있겠으나 집합적이다 라고 말하기도 조금 뭣하다. 어찌되었건 다음과 같이 구현해 보았다.
|
use tempdb go
if object_id('bill_policy') is not null drop table bill_policy create table bill_policy( charge_type varchar(20) , calc_rank tinyint )
if object_id('bill') is not null drop table bill create table bill( seq int identity(1,1) primary key , cust_id varchar(20) , cash int , charge_type varchar(20) , create_dt datetime )
insert bill_policy values('이벤트', 1) insert bill_policy values('상품권', 2) insert bill_policy values('실캐시', 3) insert bill_policy values('포인트', 4)
insert bill values('yasicom', 500, '이벤트', '20070811') insert bill values('yasicom', 5000, '실캐시', '20070701') insert bill values('yasicom', 10, '포인트', '20070811') insert bill values('yasicom', 2000, '이벤트', '20070912') insert bill values('yasicom', 500, '상품권', '20070830') insert bill values('yasicom', 10000, '실캐시', '20070613')
select * from bill select * from bill_policy
select b.cust_id , b.cash , b.create_dt , a.charge_type , row_number() over(order by a.calc_rank, b.create_dt) calc_rank from bill_policy a inner join bill b on a.charge_type = b.charge_type
--만약 1,7000원 짜리item을 구매한다면.. declare @price int , @cust_id varchar(20); set @cust_id = 'yasicom' set @price = 17000; --세미콜론(;)을 꼭 찍어줘야 한다. 그렇지 않으면 문법에러라고 뻘건 글씨봐야 한다.
if (select isnull(sum(cash), 0) from bill where cust_id = @cust_id) >= @price begin with base(seq, cust_id, cash, create_dt, charge_type, calc_rank) as ( select b.seq , b.cust_id , b.cash , b.create_dt , a.charge_type , row_number() over(order by a.calc_rank, b.create_dt) calc_rank from bill_policy a inner join bill b on a.charge_type = b.charge_type ), cte(seq, cust_id, cash, remain_cash, deduction_cash, create_dt, charge_type, calc_rank, level) as ( select –-Anchor member seq , cust_id , cash , case when cash - @price <= 0 then 0 else cash - @price end remain_cash , cash - @price deduction_cash , create_dt , charge_type , calc_rank , 1 as level from base where calc_rank = 1 union all select –-Recusive member a.seq , a.cust_id , a.cash , case when a.cash + b.deduction_cash <= 0 then 0 else a.cash + b.deduction_cash end remain_cash , a.cash + b.deduction_cash deduction_cash , a.create_dt , a.charge_type , a.calc_rank , b.level + 1 from base a inner join cte b on a.calc_rank = b.level + 1 where a.cash + b.deduction_cash < 0 --where 절은 어디까지 update 해야 하는지 구분하는 부분 or a.cash > b.deduction_cash --where 절을 빼면 마지막 순서의 Row는 남은 캐시 합계가 된다. ) update a set a.cash = b.remain_cash from bill a inner join cte b on a.seq = b.seq; /* select seq 차감일련번호 , cust_id 고객ID , cash 캐시 , remain_cash 남은캐시 , deduction_cash 차감해야할캐시 , charge_type 요금유형 , calc_rank 차감우선순위 , create_dt 캐시충전일시 from cte; */ end else select '캐시가부족하니까충전해유~'; go
select * from bill;
|
이렇게 구현해 보았다.
댓글목록
등록된 댓글이 없습니다.