Oracle Tech Talk

All about Oracle Technologies

Saturday, 18 July 2015

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

17:19 Posted by NTP , No comments
Bài viết này sẽ giới thiệu cách trả về kết quả của mệnh đề MODEL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
<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]
  [RULES
    [UPSERT | UPDATE]
    [AUTOMATIC ORDER | SEQUENTIAL ORDER]
    [ITERATE (n) [UNTIL <condition>] ]
    ( <cell_assignment> = <expression> ... )

Mệnh đề MODEL cho phép chúng ta trả kết quả về theo 2 dạng sau:
  • Trả về tất cả các dòng
  • Chỉ trả về các dòng được cập nhật
(Hình 1)
1. Trả về tất cả các dòng:
Để trả về tất cả các dòng, ta dùng mệnh đề: RETURN ALL ROWS
Ví dụ:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN ALL ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [month IN (1, 6, 12), year]
            ORDER BY month, year =
                sales_amount[CV (month), CV (year)] * 1.2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 1)

Câu lệnh trên cập nhật lại giá trị các tháng 1, 6, 12 lên 120%. Kết quả trả về bao gồm tất cả các dòng không được cập nhật (tháng 2, 3, 4, 5, 7, 8, 9, 10, 11) lẫn các dòng được cập nhật (tháng 1, 6, 12).

2. Chỉ trả về các dòng được cập nhật
Chúng ta dùng mệnh đề: RETURN UPDATED ROWS
Cũng ví dụ như trên nhưng chúng ta chỉ muốn kết quả trả về là các dòng được cập nhật (tháng 1, 6, 12), chúng ta sẽ viết như sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [month IN (1, 6, 12), year]
            ORDER BY month, year =
                sales_amount[CV (month), CV (year)] * 1.2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)

3. Vấn đề khác
Chúng ta thực thi câu lệnh sau với 2 trường hợp dùng RETURN ALL ROWS và RETURN UPDATED ROWS.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (1, 2, 3)
MODEL RETURN ALL/UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [1, 2004] =
                sales_amount[CV (month), CV (year) - 1] * 1.2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)

Chúng ta thấy rằng năm 2004 không có trong bảng all_sales, các dòng của năm 2004 được tạo mới bằng cách thực hiện tính toán các dòng của năm 2003.

Tóm lại RETURN UPDATED ROWS sẽ trả về các dòng được UPSERT (các dòng được UPdate trên các dòng hiện có và các dòng được inSERT thêm vào).

* Lưu ý: các ví dụ trên có sử dụng hàm CV(), hàm này viết tắt của Current Value - cho phép biểu thức bên phải tham chiếu đến giá trị tương ứng của biểu thức bên trái.
Chi tiết hàm CV xem tại đây
Các bài viết có liên quan
  1. Giới thiệu tổng quan
  2. Các cách trả dữ liệu về với từ khóa RETURN
  3. Cách truy xuất dữ liệu (Positional Cell Reference và Symbolic Cell Reference)
  4. Cách sử dụng hàm CV, từ khóa ANY và IS ANY
  5. Cách xử lý các giá trị NULL qua các từ khóa IS PRESENT | KEEP NAV | IGNORE NAV và các hàm PRESENTV | PRESENTNNV
  6. Quy định về trật tự tính toán của các biểu thức qua từ khóa AUTOMATIC ORDER | SEQUENTIAL ORDER
  7. Vòng lặp các biểu thức tính toán với từ khóa ITERATE
  8. Cách sử dụng từ khóa UPSERT | UPSERT ALL và UPDATE với từ khóa RULES
  9. Cách sử dụng từ khóa UNIQUE DIMENSION và UNIQUE SINGLE REFERENCE
  10. Cách sử dụng mệnh đề REFERENCE để tham chiếu dữ liệu

0 comments :

Post a Comment