Oracle Tech Talk

All about Oracle Technologies

Saturday, 1 August 2015

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

14:02 Posted by NTP , No comments
Bài viết này sẽ hướng dẫn cách thức truy cập đến các giá trị trong ô trong mệnh đề MODEL.

A. Tham chiếu đến ô và giá trị của ô:
1. Truy xuất đến 1 ô và UPSERT giá trị của ô (Positional Cell Reference / Positional Notation)
Yêu cầu 1: cập nhật doanh số bán hàng của tháng 1 năm 2003 là 1000 và doanh số bán hàng của tháng 2 năm 2003 là 1300.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
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 [1, 2003] = 1000,
        sales_amount [2, 2003] = 1300)
ORDER BY prd_type_id, year, month;
(Câu lệnh 1)

Yêu cầu 2: dự báo doanh số bán hàng tháng 1 năm 2004 tăng 110% so với cùng kỳ năm 2003.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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 [1, 2004] = sales_amount[1, 2003] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)
Chú ý: nếu chúng ta muốn tạo các ô mới để dự báo cho tương lai, bắt buộc chúng ta phải sử dụng "Positional Cell Reference" hoặc câu lệnh FOR.

2. Truy xuất đến nhiều ô và UPDATE giá trị của các ô (Symbolic Cell Reference / Symbolic Notation)
Yêu cầu 1: cập nhật doanh số bán hàng của tháng 10, 11 và 12 năm 2003 là 2000
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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 > 9, year] = 2000)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)
Câu lệnh trên sử dụng "Symbolic Cell Reference" - cho phép chúng ta truy xuất và UPSERT đồng thời nhiều ô. Với "Symbolic Cell Reference" chúng ta có thể sử dụng các toán tử điều kiện như: <, >, IN, BETWEEN ... AND

Yêu cầu 2: cập nhật doanh số bán hàng của các tháng lẻ năm 2003 lên 110%
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
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,
                                 3,
                                 5,
                                 7,
                                 9,
                                 11),
                       year]
            ORDER BY month =
                sales_amount[CV (), CV ()] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)
Chú ý: khác với "Positional Cell Reference", "Symbolic Cell Reference" chỉ cho phép chúng ta UPDATE chứ không INSERT.

 3. Kết hợp Positional và Symbolic Cell Reference
Yêu cầu: cập nhật doanh số bán hàng năm 2003 như sau:
  • Từ tháng 1 đến tháng 10: tăng 110%
  • Tháng 11: 1000
  • Tháng 12: gấp 1.5 lần doanh số tháng 11
(Hình 1)
Câu lệnh như sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
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 BETWEEN 1 AND 10, year] order by month =
                sales_amount[CV (), CV ()] * 1.2,
        sales_amount [11, 2003] =
                1000,
        sales_amount [12, 2003] =
                sales_amount[11, 2003] * 1.5)
ORDER BY prd_type_id, year, month;
(Câu lệnh 5)

 B. Tham chiếu nhiều ô bên phải của biểu thức tính toán:
Chúng ta có thể sử dụng hàm ở biểu thức bên phải để thực hiện tính toán.
Yêu cầu: cập nhật doanh thu bán hàng của tháng 12/2003 biết rằng doanh thu này bằng bình quân doanh thu của 11 tháng trước đó cộng thêm 100.
 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 [12, 2003] =
                  100
                + ROUND (AVG (sales_amount)[month BETWEEN 1 AND 11, 2003], 2))
ORDER BY prd_type_id, year, month;
(Câu lệnh 6)

 C. Sử dụng vòng lặp FOR để truy xuất dữ liệu trong ô:
Chúng ta có thể sử dụng vòng lặp FOR để truy xuất dữ liệu trong các ô. Câu lệnh 5 có thể viết lại bằng cách sử dụng vòng lặp FOR và cho kết quả tương tự.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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 [FOR month FROM 1 TO 10 INCREMENT 1, year]
            ORDER BY month =
                sales_amount[CV (), CV ()] * 1.2,
        sales_amount [11, 2003] =
                1000,
        sales_amount [12, 2003] =
                sales_amount[11, 2003] * 1.5)
ORDER BY prd_type_id, year, month;
(Câu lệnh 7)

Cú pháp của vòng lặp FOR như sau:
1
2
FOR dimension FROM <value1> TO <value2> 
    [INCREMENT | DECREMENT] <value3>

Chú ý: vòng lặp FOR chỉ được sử dụng ở biểu thức bên trái, KHÔNG được sử dụng ở biểu thức bên phải và được sử dụng trong trường hợp thêm mới (INSERT) ô.
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