빌링의 차감 및 선입선출 방법

Contents

1 시나리오

2 MSSQL 2005의 구현방법

[edit]

1 시나리오 #

다음과 같은 테이블 있다.

고객 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원 짜리 아이템을 구매했으면 충전된 캐시가 다음과 같이 차감(업데이트)되어야 한다.

[edit]

2 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
 

* IE에서 소스 복사시 줄바꿈 안됨. MS-Word 등에 붙여 넣으세요. 줄바꿈 되는 브라우저: Chrome, Opera, Safari

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
 

* IE에서 소스 복사시 줄바꿈 안됨. MS-Word 등에 붙여 넣으세요. 줄바꿈 되는 브라우저: Chrome, Opera, Safari

--만약 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;
 

* IE에서 소스 복사시 줄바꿈 안됨. MS-Word 등에 붙여 넣으세요. 줄바꿈 되는 브라우저: Chrome, Opera, Safari


출 처 : http://databaser.net/moniwiki/wiki.php/%EB%B9%8C%EB%A7%81%EC%9D%98%EC%B0%A8%EA%B0%90%EB%B0%8F%EC%84%A0%EC%9E%85%EC%84%A0%EC%B6%9C%EB%B0%A9%EB%B2%95