Oracle Tech Talk

All about Oracle Technologies

Thursday 17 September 2015

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

16:14 Posted by NTP , No comments
Bài viết này sẽ hướng dẫn cách xử lý các trường hợp NULL trong mệnh đề MODEL.

Chúng ta xét lại ví dụ ở phần 4: 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ề đó.
 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 1)
Kết quả:
(Hình 1)
Dòng số 1 và dòng số 5 cho ta giá trị NULL do biểu thức sales_amount[CV () - 2, CV ()] không lấy được giá trị (tháng 12/2002 không có doanh số bán hàng).
Để xử lý các trường hợp biểu thức trả về giá trị NULL, ta khảo sát các biểu thức sau đây.

1. Sử dụng IS PRESENT
Yêu cầu: 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ề đó; trường hợp thiếu doanh số bán hàng của 1 hoặc 2 tháng thì doanh số bán hàng của tháng thiếu đó sẽ là 1000.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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] =
                  (  CASE
                         WHEN sales_amount[CV () - 1, CV ()] IS PRESENT
                         THEN
                             sales_amount[CV () - 1, CV ()]
                         ELSE
                             1000
                     END
                   + CASE
                         WHEN sales_amount[CV () - 2, CV ()] IS PRESENT
                         THEN
                             sales_amount[CV () - 2, CV ()]
                         ELSE
                             1000
                     END)
                / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)

2. Sử dụng PRESENTV
Cú pháp hàm như sau:
1
PRESENTV(cell, expr1, expr2)

Hàm trả về expr1 nếu như cell tìm thấy được giá trị, ngược lại sẽ trả về expr2.
Câu lệnh 2 được viết lại như sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
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] =
                  (  PRESENTV (sales_amount[CV () - 1, CV ()],
                               sales_amount[CV () - 1, CV ()],
                               1000)
                   + PRESENTV (sales_amount[CV () - 2, CV ()],
                               sales_amount[CV () - 2, CV ()],
                               1000))
                / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)

3. Sử dụng PRESENTNNV
Cú pháp hàm như sau:
1
PRESENTNNV(cell, expr1, expr2)

Hàm sẽ trả về expr1 nếu như cell tìm thấy được giá trị và giá trị này phải khác NULL, ngược lại sẽ trả về expr2.

Hàm PRESENTV và PRESENTNNV không khác nhau mấy về cách sử dụng. Để hiểu rõ cách 2 hàm này đối xử với giá trị NULL, chúng ta nhìn vào bảng so sánh sau:

Ô có tồn tại? Giá trị ô có NULL? PRESENTV PRESENTNNV
Y Not null expr1 expr1
Y Null expr1 expr2
N Not null expr2 expr2
N Null expr2 expr2

4. Sử dụng KEEP NAV | IGNORE NAV
Chúng ta trở lại câu lệnh 1.
(Hình 2)
Dòng 1 và 5 có kết quả trả về là NULL vì không lấy được giá trị của tháng 12/2002. Vậy trong trường hợp không lấy được giá trị của biểu thức tính toán và muốn bỏ qua luôn giá trị này thì ta sử dụng từ khóa IGNORE NAV, ngược lại ta sẽ sử dụng từ khóa KEEP NAV (mặc định sẽ là KEEP NAV).
Cách sử dụng như sau:
1
2
3
MEASURES (<cols>)
    [IGNORE NAV] | [KEEP NAV]
  [RULES... ]

Chúng ta viết lại câu lệnh 1 có sử dụng từ khóa IGNORE NAV:
 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)
     IGNORE NAV
        (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 4)
Kết quả câu lệnh:
(Hình 3)
Dòng 1 và 5 đã không bị giá trị NULL nữa. Biểu thức sales_amount[CV () - 2, CV ()] trả về giá trị là NULL nhưng do sử dụng với từ khóa IGNORE NAV nên kết quả của biểu thức này chuyển từ NULL sang 0 và do đó toàn bộ biểu thức bên phải vẫn tính toán được và có giá trị trả về.
* Chú ý: các giá trị trả về khi sử dụng từ khóa IGNORE NAV

0 khi cột tham chiếu lấy giá trị có kiểu dữ liệu là số (numeric)
'' khi cột tham chiếu lấy giá trị có kiểu dữ liệu là chuỗi (char/string)
01-JAN-2001 khi cột tham chiếu lấy giá trị có kiểu dữ liệu là ngày (date)
NULL khi cột tham chiếu lấy giá trị có kiểu dữ liệu là các kiểu dữ liệu còn lại

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