1 시나리오

2 MSSQL 2005의 구현방법

3 결론

4 날짜에 따른 선입선출

다음과 같은 테이블 있다.

고객 yasicom은 bill 테이블과 같이 캐시가 충전되어 있다. 그리고 bill_policy테이블에는 어떤 요금부터 차감을 할 것인지에 대한 계산의 우선순위가 매겨져 있다. 또한 충전한 요금유형이 여러 개라면 먼저 충전된 캐시가 차감된다. 즉, 선입선출이다. 그러므로 다음과 같은 우선순위를 갖는다.

만약 17,000원짜리 아이템을 구매하였다면, 다음과 같이 차감되어야 한다.

1. 1번째 Row: 500 - 17000 = -16500
   
2. 2번째 Row: 2000 - 16500 = -14500
   
3. 3번째 Row: 500 - 14500 = -14000
   
4. 4번째 Row: 10000 - 14000 = -4000
   
5. 5번째 Row: 5000 - 4000 = 1000
   
   

즉, 17,000원 짜리 아이템을 구매했으면 충전된 캐시가 다음과 같이 차감(업데이트)되어야 한다.

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 @billlog table
(
		seq int
	,	cust_id varchar(20)
	,	cash int
	,	remain_cash int
	,	deduction_cash int
	,	charge_type varchar(20)
	,	calc_rank int
	,	create_dt datetime
)

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;
	
	or
	*/
	--로그로 남기는 것도 한 번에..
	--2005에서 되던가? 아.. 귀찮아.. 2008에서는 되니까.. 2005는 테스트 해보시길..

	update a
	set a.cash = b.remain_cash
	output
		deleted.seq 
	,	deleted.cust_id 
	,	deleted.cash
	,	b.remain_cash 
	,	b.deduction_cash 
	,	deleted.charge_type
	,	b.calc_rank
	,	deleted.create_dt
	into @billlog
	from bill a inner join cte b
	on a.seq = b.seq;
	
	select 
		seq 차감일련번호
	,	cust_id 고객ID
	,	cash 캐시
	,	remain_cash 남은캐시
	,	deduction_cash 앞으로차감해야할캐시
	,	@price + case when deduction_cash < 0 then deduction_cash else 0 end 차감된캐시
	,	charge_type 요금유형
	,	calc_rank 차감우선순위
	,	create_dt 캐시충전일시 
	from @billlog 
	order by calc_rank
end else
	select '캐시가부족하니까충전해유~';
go

--select * from bill;

계정의 잔액

차감로그

DBMS의 개선된 기능을 이용하면 많은 이렇게 짧은 코드로 차감에 대한 거의 모든 로직을 끝낼 수 있다. CTE를 사용하여 순차적인 계산을 했고, output절을 이용하여 로그를 남기는 것 지 해 보았다. 응용을 잘하면 개발시간이 많이 줄어들 것으로 보인다. CTE의 사용으로 부하가 좀 있으리라 예상된다고 할 수 있다. 진짜 그런가? 1개의 거래를 처리하기 위해서 발생되는 비용을 모두 따져보라. 아마도 비슷할 것이라 생각된다. 만약 우려가 된다면 CTE 사용으로 인한 부하만큼 하드웨어 용량산정 할 때 조금 더 빵빵하게 잡으면 된다. (요즘은 하드웨어 가격이 워낙 싸져서 하드웨어의 비약적인 발전을 이용하는 것도 좋은 솔루션이 될 수 있다.)

declare 
	@deduct int
,	@id char(12)

set @id = 'yasicom'
set @deduct = 41000

;with base
as
(
	select 
		a.seq
	,	a.id
	,	a.point
	,	row_number() over(partition by a.cn order by a.seq) calc_rank
	from stg.sales.Members_UserPoint_Log_T a
		left join stg.sales.Members_UserPoint_MoneyLog_T b
			on a.seq = b.seq
	where a.id = @id
	and a.seq >= 86
	and a.point >= 0
), cte
as
(
	select
		seq
	,	id
	,	point
	,	case when point - @deduct <= 0 then 0 else point - @deduct end residual_point
	,	case when point >= @deduct then @deduct else point - @deduct end residual_deduct_point
	,	case when point >= @deduct then @deduct - point else point - @deduct end deducted_point
	,	calc_rank
	,	1 level
	,	point acc_point
	from base
	where calc_rank = 1
	union all
	select --Recusive member
		a.seq
	,	a.id
	,	a.point
	,	case when a.point + b.residual_deduct_point <= 0 then 0 else a.point + b.residual_deduct_point end residual_point
	,	case when a.point + b.residual_deduct_point <= 0 then a.point + b.residual_deduct_point else 0 end residual_deduct_point
	,	a.point + b.residual_deduct_point deducted_point
	,	a.calc_rank
	,	b.level + 1
	,	a.point + b.acc_point acc_point
	from base a inner join cte b
	on a.calc_rank = b.level + 1
	and a.id = b.id
	--where a.point + b.deduct_point < 0 --where 절은 어디까지 update 해야 하는지 구분하는 부분
	--or a.point > b.deduct_point
)	
select 
	seq
,	id
,	@deduct total_deduct_point --차감되어야 할 총 포인트
,	point -- 지급받은 포인트
,	residual_point --차감 후 포인트
,	acc_point --지급받은 누적포인트
from cte
where point <> residual_point
from cte;

결과