Oracle Tech Talk: Analysis

Showing posts with label Analysis. Show all posts

Tuesday, 13 October 2015

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

20:50 Posted by NTP Analysis , Database No comments

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

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

16:00 Posted by NTP Analysis , Database No comments

Bài viết này sẽ hướng dẫn cách sử dụng từ khóa UNIQUE DIMENSION và UNIQUE SINGLE REFERENCE

Cú pháp:

<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> ... )

1. Các cách sử dụng với từ khóa UNIQUE DIMENSION

Từ khóa UNIQUE DIMENSION được sử dụng khi chúng ta chắc chắn muốn Oracle kiểm tra việc trùng dữ liệu trước khi thực hiện việc tính toán các biểu thức trong mệnh đề MODEL.

Chúng ta giả lập ví dụ sau: thêm 1 dòng dữ liệu trùng vào bảng ALL_SALES

-- disable constraint for inserting duplicate values
ALTER TABLE all_sales DISABLE CONSTRAINT all_sales_pk;

INSERT INTO all_sales
VALUES (2003,
        12,
        2,
        21,
        2000);

COMMIT;

(Câu lệnh 1)

Chạy câu lệnh sau với từ khóa UNIQUE DIMENSION

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10, 11, 12)
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
     UNIQUE DIMENSION
    RULES
        (sales_amount [2003, 12] =
                (sales_amount[2003, 10] + sales_amount[2003, 11]) / 2)
ORDER BY prd_type_id, year, month;

(Câu lệnh 2)

Sau khi thực thi câu lệnh 2, chúng ta nhận được lỗi trả về như sau:
ORA-32638: Non unique addressing in MODEL dimensions

Lý do: trong bảng all_sales có 2 dòng có cùng thông tin về (year,month,prd_type_id,emp_id) có giá trị là (2003,12,2,21).

Cách sửa: đảm bảo tập kết quả của câu lệnh SELECT ban đầu (câu SELECT không bao gồm mệnh đề MODEL) không có chứa dữ liệu trùng. Cụ thể câu lệnh như sau:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10, 11)
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
     UNIQUE DIMENSION
    RULES
        (sales_amount [2003, 12] =
                (sales_amount[2003, 10] + sales_amount[2003, 11]) / 2)
ORDER BY prd_type_id, year, month;

(Câu lệnh 3)

Kết quả:

(Hình 1)

So sánh câu lệnh 2 và câu lệnh 3, chúng ta thấy rằng câu lệnh 3 mệnh đề WHERE không chứa tháng 12/2003 (dòng gây dữ liệu trùng).

Chú ý: Từ khóa UNIQUE DIMENSION là từ khóa mặc định được sử dụng nếu như chúng ta không khai báo trong mệnh đề MODEL.

2. Cách sử dụng với từ khoá UNIQUE SINGLE REFERENCE

Chúng ta chạy lại câu lệnh 2 và 3 nhưng thay từ khóa UNIQUE DIMENSION bằng UNIQUE SINGLE REFERENCE.

Câu lệnh:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10, 11, 12)

MODEL UNIQUE SINGLE REFERENCE
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
        (sales_amount [2003, 12] =
                (sales_amount[2003, 10] + sales_amount[2003, 11]) / 2)
ORDER BY prd_type_id, year, month;

(Câu lệnh 4)

Kết quả:

(Hình 2)

Câu lệnh:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10, 11)
MODEL UNIQUE SINGLE REFERENCE
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
        (sales_amount [2003, 12] =
                (sales_amount[2003, 10] + sales_amount[2003, 11]) / 2)
ORDER BY prd_type_id, year, month;

(Câu lệnh 5)

Kết quả:

(Hình 3)

Chúng ta thấy rằng khi sử dụng với từ khóa UNIQUE SINGLE REFERENCE thì ở cả câu lệnh 4 và 5 đều trả về kết quả (không báo lỗi ở câu lệnh này). So sánh hình 2 và hình 3 chúng ta thấy số lượng dòng của hình 2 nhiều hơn hình 3 do có dòng 6, 7 được lặp lại. Việc lặp lại này là do tập dữ liệu ban đầu (tập dữ liệu câu SELECT chưa có mệnh đề MODEL) có chứa 2 dòng trùng nên khi biểu thức tính toán sẽ thực hiện tính toán luôn cho 2 dòng này và trả kết quả về.

Chúng ta xét thêm ví dụ sau:

Câu lệnh:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (12)
MODEL UNIQUE SINGLE REFERENCE
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
        (sales_amount [2004, 1] = sales_amount[2003, 12] * 1.1)
ORDER BY prd_type_id, year, month;

(Câu lệnh 6)

Sau khi thực thi câu lệnh 6, chúng ta nhận được lỗi trả về như sau:
ORA-32638: Non unique addressing in MODEL dimensions

Lý do: trong bảng all_sales có 2 dòng chứa dữ liệu của tháng 12/2003 và biểu thức tính toán cho tháng 01/2004 có liên quan đến tháng 12/2003 này. Oracle sẽ không biết lấy dòng nào trong 2 dòng trùng nhau để tính toán nên sẽ báo lỗi.

3. Các kết luận
Với từ khóa UNIQUE DIMENSION

Oracle sẽ kiểm tra trùng tập dữ liệu của câu lệnh SELECT, nếu có thì Oracle sẽ báo lỗi.
Dòng trùng ở đây được hiểu là sự kết hợp của các cột được khai báo trong mệnh đề PARTITION BY và DIMENSION BY.

Với từ khóa UNIQUE SINGLE REFERENCE

Oracle sẽ bỏ qua việc kiểm tra trùng tập dữ liệu của câu lệnh SELECT.
Oracle chỉ kiểm tra dữ liệu trùng của mệnh đề bên phải biểu thức tính toán, nếu có thì Oracle sẽ báo lỗi.
Do bỏ qua việc kiểm tra trùng dữ liệu của tập SELECT nên hiệu năng với tập dữ liệu lớn khi sử dụng với từ khóa UNIQUE SINGLE REFERENCE sẽ nhanh hơn khi dùng với từ khóa UNIQUE DIMENSION.

4. Case Study khác

Xét câu lệnh sau (điều kiện là chưa chạy câu lệnh 1)

SELECT year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10,11,12)
MODEL
    PARTITION BY (year)
    DIMENSION BY (month)
    MEASURES (amount sales_amount)
     [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
    RULES
        (sales_amount [ANY] = sales_amount[CV()] * 1.1)
ORDER BY year, month;

(Câu lệnh 7)

Câu lệnh này khi sử dụng 1 trong 2 từ khóa đều báo lỗi
ORA-32638: Non unique addressing in MODEL dimensions

Lý do: dữ liệu trùng ở đây đều vi phạm ở cả dữ liệu câu SELECT trước khi áp mệnh đề MODEL lẫn trùng ở cả biểu thức tính toán trong mệnh đề MODEL.

Cách sửa: như đã nói ở phần trên, bộ các cột trong mệnh đề PARTITION BY và DIMENSION BY phải là duy nhất. Do vậy ở đây chúng ta thêm vào 1 cột để đảm bảo bộ các cột này là duy nhất. Câu lệnh được sửa lại như sau:

SELECT year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10,11,12)
MODEL
    PARTITION BY (year, rownum rn)
    DIMENSION BY (month)
    MEASURES (amount sales_amount)
     [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
    RULES
        (sales_amount [ANY] = sales_amount[CV()] * 1.1)
ORDER BY year, month;

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

PIVOT và UNPIVOT (Phần 3)

16:36 Posted by NTP Analysis , Database 2 comments

Bài viết này sẽ giới thiệu cách PIVOT với số lượng cột không giới hạn.

Ở Phần 1, chúng ta thấy rằng khi PIVOT dữ liệu thì chúng ta phải xác định được số lượng cột trả về thông qua việc khai báo số lượng cột trong mệnh đề IN. Khi bảng dữ liệu của chúng ta có thêm/bớt giá trị thì bắt buộc chúng ta phải điều chỉnh lại câu lệnh PIVOT.

Bài viết này sử dụng hàm PIVOT của tác giả Anton để PIVOT cột động. Tham khảo bài viết gốc tại đây.

1. Thiết lập môi trường:
- Tải script tạo hàm PIVOT tại đây.
- Chạy script để tạo hàm.

2. Các kịch bản test với hàm PIVOT:
Tham khảo Phần 1 để có dữ liệu và câu lệnh mẫu để so sánh với phần này.

a. Yêu cầu 1: Thống kê kết quả bán hàng theo sản phẩm
Câu lệnh:

SELECT *
  FROM cust_sales 
    PIVOT (SUM (sales)
       FOR product_id
           IN  ('Prod A' AS product_a,
                'Prod B' AS product_b,
                'Prod C' AS product_c));

SELECT * FROM TABLE (
   PIVOT (
       'select customer_id,

        month, product_id,

        sum(sales) sum_sales 
        from cust_sales 
        group by customer_id,

        month, product_id'));

(Câu lệnh 1a - Hàm của Oracle)

(Câu lệnh 1b- Hàm của Anton)

Kết quả:

(Kết quả câu lệnh 1a)

(Kết quả câu lệnh 1b)

b. Yêu cầu 2: Thống kê kết quả bán hàng theo tháng
Câu lệnh:

SELECT *
  FROM cust_sales 
    PIVOT (SUM (sales)
       FOR month
         IN ('Jan' AS january, 
             'Feb' AS february, 
             'Mar' AS march));

SELECT *
  FROM TABLE (
    PIVOT ('select customer_id, product_id, 
            month, sum(sales) sum_sales 
            from cust_sales 
            group by customer_id, 
            product_id, month'));

(Câu lệnh 2a - Hàm của Oracle)

(Câu lệnh 2b- Hàm của Anton)

Kết quả:

(Kết quả câu lệnh 2a)

(Kết quả câu lệnh 2b)

c. Yêu cầu 3: Thống kê kết quả bán hàng theo khách hàng
Câu lệnh:

SELECT *
  FROM cust_sales 
    PIVOT (SUM (sales)
      FOR customer_id
        IN ('Adam', 
            'Jones', 
            'Kanes'));

SELECT *
  FROM TABLE (
    PIVOT ('select month, product_id,
            customer_id, sum(sales) sum_sales 
            from cust_sales 
            group by month, product_id, 
            customer_id'));

(Câu lệnh 3a - Hàm của Oracle)

(Câu lệnh 3b- Hàm của Anton)

Kết quả:

(Kết quả câu lệnh 3a)

(Kết quả câu lệnh 3b)

3. Tổng kết:
Qua các ví dụ trên, chúng ta thấy rằng hàm PIVOT của Anton có các điểm lưu ý như sau:

Số lượng cột tối thiểu là 3
Cột thứ (n-1) chính là cột dùng để PIVOT
Cột thứ n nếu dùng các hàm tổng hợp (SUM, COUNT, AVG, ...) thì bắt buộc phải có Alias
Mệnh đề ORDER BY không có hiệu lực trong chuỗi SELECT

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

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

16:33 Posted by NTP Analysis , Database No comments

Bài viết này sẽ giới thiệu về từ khóa UPSERT | UPSERT ALL và UPDATE khi được sử dụng kèm theo với từ khóa RULES trong mệnh đề MODEL.

1. Từ khóa UPSERT

UPSERT là từ khóa viết tắt của UPdate và inSERT được sử dụng trong Oracle - nghĩa là trong tập dữ liệu gốc ban đầu nếu như tồn tại giá trị tính toán thì từ khóa này sẽ thực hiện UPdate dữ liệu, còn nếu như không tồn tại giá trị tính toán thì từ khóa này sẽ inSERT 1 dòng dữ liệu mới.

Chúng ta xét câu lệnh sau:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT
        (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;

(Câu lệnh 1)

Kết quả:

(Hình 1)

Chúng ta thấy rằng doanh số bán hàng của tháng 12/2003 được UPdate và doanh số bán hàng của tháng 01/2004 thì được inSERT.

* Kết hợp với từ khóa RETURN UPDATED ROWS
Câu lệnh:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT
        (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;

(Câu lệnh 2)

Kết quả:

(Hình 2)

Khi kết hợp với từ khóa RETURN UPDATED ROWS thì chỉ có các dòng được UPdate hoặc inSERT mới có trong tập kết quả trả về. Với Câu lệnh 1 thì từ khóa RETURN ALL ROWS mặc định được sử dụng (Xem thêm bài viết Các cách trả dữ liệu về với từ khóa RETURN)

Lưu ý: từ khóa UPSERT mặc định sẽ được sử dụng nếu như chúng ta không khai báo

2. Từ khóa UPDATE

Khi sử dụng với từ khóa này thì chỉ có các giá trị nào tồn tại trong tập dữ liệu gốc ban đầu mới được cập nhật. Các giá trị không tồn tại sẽ không được xử lý tính toán.

Câu lệnh:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPDATE
        (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;

(Câu lệnh 3)

Kết quả:

(Hình 3)

Tập dữ liệu gốc ban đầu trước khi tính toán chứa doanh số bán hàng của 2 tháng: 11/2003 và 12/2003. Khi tập này được sử dụng với từ khóa UPDATE thì chỉ có biểu thức tính toán sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1 được áp dụng, biểu thức còn lại không được áp dụng nên tập kết quả trả về sẽ không có dữ liệu của tháng 01/2004.

* Kết hợp với từ khóa RETURN UPDATED ROWS
Câu lệnh:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPDATE
        (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;

(Câu lệnh 4)

Kết quả:

(Hình 4)

3. Từ khóa UPSERT ALL
Chúng ta so sánh 2 câu lệnh sau:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT
     (sales_amount [11, 2004] = 
       sales_amount[11, 2003] * 1.1,
     sales_amount [12, 2004] = 
       sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month 
         IN (11, 12)
MODEL

    RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT
        (sales_amount [any, 2004] = 
           sales_amount[cv(), 2003] * 1.1)
ORDER BY prd_type_id, year, month;

(Câu lệnh 5)

(Câu lệnh 6)

Kết quả:

(Hình 5)

(Hình 6)

Khi viết câu lệnh 6, chúng ta muốn nó là cách viết gọn của câu lệnh 5 nhưng kết quả lại không như ta mong đợi, câu lệnh 5 cho kết quả như hình 5 còn với câu lệnh 6 thì không có dữ liệu trả về.

Câu lệnh 5 sử dụng Positional Notation trong khi câu lệnh 6 lại sử dụng Symbolic Notation (xem bài Cách truy xuất dữ liệu (Positional Cell Reference và Symbolic Cell Reference)). Trong khi đó từ khóa ANY thì luôn được sử dụng với Symbolic Notation (xem bài Cách sử dụng hàm CV, từ khóa ANY và IS ANY) và sẽ không có dòng mới được insert.

Vì vậy, với câu lệnh 6 muốn kết quả trả về như hình 5 chúng ta phải dùng từ khóa UPSERT ALL.
Câu lệnh:

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT ALL
        (sales_amount [any, 2004] = 
           sales_amount[cv(), 2003] * 1.1)
ORDER BY prd_type_id, year, month;

(Câu lệnh 7)

Chú ý: ngoài từ khóa ANY thì các phép so sánh (>, >=, <, <=) hay từ khóa IN cũng cho kết quả như ví dụ trên.

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)

Friday, 9 October 2015

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

Friday, 2 October 2015

PIVOT và UNPIVOT (Phần 3)

Tuesday, 29 September 2015

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