Cách sử dụng mệnh đề MODEL (Phần 10) ~ Oracle Tech Talk

Bài viết này sẽ giới thiệu cách sử dụng mệnh đề REFERENCE trong mệnh đề MODEL để tham chiếu dữ liệu từ các bảng dữ liệu khác.

Cú pháp tổng quát của mệnh đề MODEL

<prior clauses of SELECT statements>
MODEL [main] [RETURN {ALL|UPDATED} ROWS]
  [reference models]
  [PARTITION BY (<cols>)]
  DIMENSION BY (<cols>)
  MEASURES (<cols>)
    [IGNORE NAV] | [KEEP NAV]
    [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
  [RULES
    [UPSERT | UPDATE]
    [AUTOMATIC ORDER | SEQUENTIAL ORDER]
    [ITERATE (n) [UNTIL <condition>]]
    ( <cell_assignment> = <expression> ... )

Trong tất cả các bài viết trước, chúng ta đã khảo sát qua cách sử dụng mệnh đề MODEL với các từ khóa kèm theo và các bài này chỉ xoay quanh mệnh đề MODEL đơn (single) hay còn gọi là mệnh đề MODEL chính (main). Bài viết này sẽ giới thiệu cách sử dụng MAIN MODEL và REFERENCE MODEL. REFERENCE MODEL được đề cập trong mệnh đề MODEL đóng vai trò như là các bảng tham chiếu (lookup table) cho mệnh đề MAIN MODEL.

Mệnh đề REFERENCE (reference models) gồm các thành phần sau:

REFERENCE model_name ON (query) 
   DIMENSION BY (cols) 
   MEASURES (cols) 
      [reference options]

Trong đó:

model_name: tên định danh cho REFERENCE MODEL. Tên này sẽ được dùng với dấu chấm và tên cột để tham chiếu giá trị trong biểu thức tính toán của MAIN MODEL
query: câu lệnh SELECT để truy vấn tập dữ liệu làm bảng tham chiếu (lookup table)
DIMENSION BY và MEASURES: tương tự như cách dùng với MAIN MODEL ở các bài viết trước
reference options: các tùy chọn khác cho REFERENCE MODEL như KEEP NAV | IGNORE NAV, ...

1. Dẫn nhập

Giả sử chúng ta có mẫu dữ liệu sau trong Excel:

(Hình 1)

Các bảng dữ liệu như hình 1 gồm có:

ALL_SALES: chứa doanh số bán hàng theo ID loại hàng hóa (prd_type_id) theo từng tháng trong năm.
PROMOTION: chứa % tỉ lệ khuyến mãi theo ID loại hàng hóa.
SEASON_PROMOTION: chứa % tỉ lệ khuyến mãi theo ID loại hàng hóa theo mùa (tháng trong năm).

Với các bảng dữ liệu trên, giả sử chúng ta có yêu cầu sau:

Yêu cầu 1: dựa vào bảng dữ liệu PROMOTION, thêm 1 cột trong bảng dữ liệu ALL_SALES cho biết số tiền khách hàng được hưởng khuyến mãi theo loại ID hàng hóa.
Yêu cầu 2: dựa vào bảng dữ liệu SEASON_PROMOTION, thêm 1 cột vào bảng dữ liệu ALL_SALES cho biết số tiền khách hàng được hưởng khuyến mãi theo loại ID hàng hóa.

Phân tích yêu cầu:

Yêu cầu 1: nhìn vào bảng dữ liệu PROMOTION chúng ta thấy rằng chúng ta có thể dùng hàm VLOOKUP của Excel để giải quyết yêu cầu 1 này. Cụ thể:

(Hình 2)
Yêu cầu 2: nhìn vào bảng dữ liệu SEASON_PROMOTION và ALL_SALES chúng ta thấy rằng việc lookup giữa 2 bảng này là việc lookup nhiều điều kiện. Có nhiều cách để giải quyết yêu cầu này, nhưng trong phạm vi bài viết này chúng ta sẽ dùng hàm SUMPRODUCT để giải quyết yêu cầu 2. Cụ thể:

(Hình 3)

2. Sử dụng REFERENCE MODEL để giải quyết các yêu cầu tương tự như trên

Như đã giới thiệu ở phần đầu, mệnh đề REFERENCE cho phép chúng ta tạo ra các bảng lookup để tham chiếu và kết hợp với mệnh đề MAIN MODEL để thực hiện các biểu thức tính toán. Như hình 1 thì các bảng PROMOTION và SEASON_PROMOTION chính là 2 bảng dùng để tham chiếu.

Với yêu cầu 1: bảng ALL_SALES tham chiếu với bảng PROMOTION qua 1 cột chung là PRD_TYPE_ID.
Với yêu cầu 2: bảng ALL_SALES tham chiếu với bảng SEASON_PROMOTION qua 3 cột chung là PRD_TYPE_ID, YEAR và MONTH.

Chúng ta chạy nhóm câu lệnh sau để tạo 2 bảng PROMOTION và SEASON_PROMOTION đồng thời chèn thêm dữ liệu mẫu như hình 1.

CREATE TABLE promotion
(
    prd_type_id   NUMBER,
    rate          NUMBER
);
--
CREATE TABLE season_promotion
(
    prd_type_id   NUMBER,
    year          NUMBER,
    month         NUMBER,
    rate          NUMBER
);

INSERT INTO promotion
VALUES(1,0.43);
INSERT INTO promotion 
VALUES(2,0.52);
--
INSERT INTO prd_type_promo_season 
VALUES(1,2003,10,0.52);
INSERT INTO prd_type_promo_season 
VALUES(1,2003,12,0.54);
INSERT INTO prd_type_promo_season 
VALUES(2,2003,10,0.53);
INSERT INTO prd_type_promo_season 
VALUES(2,2003,11,0.55);
INSERT INTO prd_type_promo_season 
VALUES(2,2003,12,0.56);
--
COMMIT;

(Nhóm câu lệnh 1)

Sử dụng REFERENCE MODEL để giải quyết 2 yêu cầu trên. Câu lệnh:

SELECT prd_type_id, year, month, amount_no_promo, 
       amount_promotion, amount_season_promotion
  FROM all_sales a
 WHERE month IN (10, 11, 12)
MODEL
REFERENCE t1_ref ON (SELECT prd_type_id, rate 
                     FROM promotion)
        DIMENSION BY (prd_type_id)
        MEASURES (rate r1)
REFERENCE t2_ref ON (SELECT prd_type_id, year, month, rate 
                     FROM season_promotion)
        DIMENSION BY (prd_type_id, year, month)
        MEASURES (rate r2)
        IGNORE NAV
    MAIN t_main
    DIMENSION BY (prd_type_id, year, month)
    MEASURES (amount amount_no_promo, 
              0 AS amount_promotion, 
              0 AS amount_season_promotion)
    RULES UPSERT
        (amount_promotion[ANY,ANY,ANY] = 
              amount_no_promo[CV(prd_type_id),CV(),CV()] * 
              t1_ref.r1[CV(prd_type_id)],
         amount_season_promotion[ANY,ANY,ANY] = 
              amount_no_promo[CV(prd_type_id),CV(),CV()] * 
              t1_ref.r2[CV(prd_type_id),CV(year),CV(month)]
        )
ORDER BY prd_type_id, year,month;

(Câu lệnh 1)

- Dòng 6 và 10 cho phép chúng ta tham chiếu đến 2 bảng PROMOTION và SEASON_PROMOTION thông qua 2 alias là t2_ref và t2_ref để lấy giá trị r1 và r2.
- Dòng 23 và 26 là 2 dòng để chúng ta lấy r1 và r2 để thực hiện tính toán.

Kết quả:

(Hình 4)

3. Các lưu ý quan trọng

Câu SELECT của mệnh đề REFERENCE không thể kết với câu SELECT của mệnh đề MAIN.
Không được sử dụng từ khóa PARTITION BY trong mệnh đề REFERENCE.
Mệnh đề REFERENCE chỉ dùng thể tham chiếu giá trị (read-only), chúng ta không thể UPDATE/UPSERT các giá trị trong tập kết quả của mệnh đề REFERENCE.
Mệnh đề MODEL chỉ có duy nhất 1 MAIN MODEL nhưng có thể có nhiều REFERENCE MODEL.

Các bài viết có liên quan

Oracle Tech Talk

All about Oracle Technologies

Tuesday 13 October 2015

Cách sử dụng mệnh đề MODEL (Phần 10)

0 comments :

Post a Comment