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

본문 바로가기

db

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

페이지 정보

작성자 서방님 댓글 0건 조회 33회 작성일 13-01-04 11:25

본문

출처 : 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;

 

이렇게 구현해 보았다.

댓글목록

등록된 댓글이 없습니다.

Total 447건 1 페이지
게시물 검색

회원로그인

접속자집계

오늘
139
어제
365
최대
592
전체
40,113

그누보드5
Copyright © seobangnim.com All rights reserved.
자바스크립트를 활성화 하세요![ 브라우저에서 자바스크립트를 활성화하는 방법 ]