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

0 comments :

Post a Comment