Oracle Tech Talk

All about Oracle Technologies

Saturday, 15 August 2015

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

09:50 Posted by NTP , No comments
Bài viết này sẽ hướng dẫn cách sử dụng hàm CV(), từ khóa ANY và IS ANY

1. Cách sử dụng hàm CV()
Chúng ta xét yêu cầu sau: cập nhật doanh số bán hàng của 3 tháng cuối năm lên 110%
 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 [10, 2003] = sales_amount[10, 2003] * 1.1,
        sales_amount [11, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [12, 2003] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 1)

Chúng ta có thể viết lại câu lệnh trên bằng cách kết hợp BETWEEN ... AND và hàm CV 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 BETWEEN 10 AND 12, year]
            ORDER BY month =
                sales_amount[CV (), CV ()] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)

Hàm CV cho phép chúng ta lấy giá trị hiện tại của các cột được khai báo trong DIMENSION BY của mệnh đề MODEL để thực hiện tính toán (CV viết tắt của chữ Current Value).

Chú ý: hàm CV luôn được sử dụng bên phải của biểu thức tính toán, chúng ta có thể sử dụng hàm CV không có tham số hoặc có tham số. Khi sử dụng không có tham số Oracle sẽ hiểu theo thứ tự tương ứng với các cột trong DIMENSION BY.
Cách viết sau sử dụng hàm CV có tham số là tên cột trong DIMENSION BY và kết quả trả về tương tự như cách viết trong câu lệnh 2.
 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 BETWEEN 10 AND 12, year]
            ORDER BY month =
                sales_amount[CV (month), CV (year)] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)

2. Cách sử dụng từ khóa ANY và IS ANY
Chúng ta xét yêu cầu sau:  cập nhật doanh số bán hàng của tất cả các tháng của tất cả các năm lên 110%
 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, year] = sales_amount[CV (), CV ()] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)

Chúng ta có thể dùng từ khóa ANY  hoặc IS ANY để viết lại câu lệnh 4 như sau:
 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 is any, any] = sales_amount[CV (), CV ()] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 5)

Từ khóa ANY / IS ANY được dùng để truy xuất vào tất cả các ô (bao gồm cả các ô có giá trị NULL) của cột được khai báo trong DIMENSION BY. Khi sử dụng IS ANY chúng ta phải xác định tên cột, còn từ khóa ANY khi được sử dụng sẽ ngầm được hiểu theo thứ tự của các cột được khai báo trong DIMENSION BY.
Chú ý: từ khóa ANY / IS ANY luôn được sử dụng bên trái của biểu thức tính toán và chỉ UPDATE (không INSERT) giá trị mới vào mảng.

3. Sử dụng hàm CV để thực hiện các phép tính liên kết các dòng với nhau
Giả sử chúng ta có yêu cầu sau: cập nhật doanh số bán hàng của các tháng 2, 5, 8, 11 biết rằng doanh số bán hàng của các tháng này bằng bình quân doanh số bán hàng của 2 tháng liền kề đó.
(Hình 1)
Hình trên mô phỏng cách dùng hàm trong excel để thực hiện yêu cầu tính toán.
Câu lệnh thực hiện yêu cầu trên như sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
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 (2,
                                 5,
                                 8,
                                 11),
                       year IS ANY] = 
                  (  sales_amount[CV () - 1, CV ()]
                   + sales_amount[CV () - 2, CV ()])
                / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 6)
Kết quả như sau:
(Hình 2)
Chúng ta thấy rằng kết quả trả về ở các tháng là 2 sẽ có giá trị là NULL vì trong tập dữ liệu tháng 12 năm trước không có giá trị nên phép tính không thực hiện được.
Qua ví dụ trên, chúng ta cũng thấy được cách sử dụng hàm CV rất linh hoạt.
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

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