화물수량별 차등운임 계산

화물수량별 차등운임을 계산해보자.

테스트를 위해 아래와 같이 테이블을 생성하자. 화물수량이 1~50은 30, 51~100은 20, 101~999는 10의 운임이 적용된다.

-- 1
DROP TABLE 운송 PURGE;
DROP TABLE 운임 PURGE;

CREATE TABLE 운송 (운송번호 NUMBER, 화물유형 NUMBER, 운송수량 NUMBER);
CREATE TABLE 운임 (화물유형 NUMBER, 최소수량 NUMBER, 최대수량 NUMBER, 화물운임 NUMBER);

INSERT INTO 운송 VALUES (1, 1,  90);
INSERT INTO 운송 VALUES (2, 1, 120);
INSERT INTO 운임 VALUES (1,   1,  50, 30);
INSERT INTO 운임 VALUES (1,  51, 100, 20);
INSERT INTO 운임 VALUES (1, 101, 999, 10);
COMMIT;

아래 쿼리로 화물수량별 차등운임을 계산할 수 있다.

-- 2
SELECT a.*
     , (SELECT SUM (LEAST (a.운송수량 - x.최소수량 + 1
                         , x.최대수량 - x.최소수량 + 1) * x.화물운임)
          FROM 운임 x
         WHERE x.화물유형  = a.화물유형
           AND x.최소수량 <= a.운송수량) AS 화물운임
  FROM 운송 a;
 
운송번호 화물유형 운송수량 화물운임
-------- -------- -------- --------
       1        1       90     2300 -- (50 * 30) + (40 * 20) = 2300
       2        1      120     2700 -- (50 * 30) + (50 * 20) + (20 * 10) = 2700
 
2 행이 선택되었습니다.