Oracle Tech Talk

All about Oracle Technologies

Friday, 25 September 2015

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

11:46 Posted by NTP , No comments
Bài viết này sẽ giới thiệu cách sử dụng từ khóa ITERATE trong mệnh đề MODEL.

Trong mệnh đề MODEL, chúng ta có thể sử dụng vòng lặp cho các biểu thức tính toán bằng cách sử dụng từ khóa ITERATE.
 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> ... )

Chúng ta xét câu lệ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)
    RULES
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000)
ORDER BY prd_type_id, year, month;
(Câu lệnh 1)
Câu lệnh trên sẽ thực thi biểu thức tính toán doanh số bán hàng của tháng 2/2003 3 lần.

1. Sử dụng với hàm ITERATION_NUMBER
Để biết được vòng lặp đang thực thi ở lần thứ mấy, Oracle cung cấp cho chúng ta hàm ITERATION_NUMBER. Giá trị bắt đầu của hàm này là 0 và được tăng dần lên 1 đơn vị. Chúng ta bổ sung vào câu lệnh 1 một cột nữa để kiểm tra sự hoạt động của hàm ITERATION_NUMBER.
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT prd_type_id,
       year,
       month,
       sales_amount,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) AS iterator)
    RULES
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000,
        iterator [2, 2003] = iterator[2, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)
Kết quả:
(Hình 1)
Chúng ta thấy rằng cột ITERATOR qua các lần lặp sẽ cho chúng ta biết được giá trị hiện tại của các lần lặp đó và có giá trị khởi đầu là 0 ở lần lặp đầu tiên.

Chúng ta cũng lưu ý về cách thức hoạt động của vòng lặp như sau:
  • Lần lặp thứ 1
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
  • Lần lặp thứ 2
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
  • Lần lặp thứ n
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
Xét câu lệnh sau để thấy cách hoạt động của vòng lặp:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT prd_type_id,
       year,
       month,
       sales_amount,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) AS iterator)
    RULES
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000,
        iterator [2, 2003] = iterator[2, 2003] || 'x',
        iterator [2, 2003] = iterator[2, 2003] || 'y')
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)
Kết quả:

(Hình 2)
2. Sử dụng với từ khóa UNTIL
Từ khóa UNTIL được sử dụng kèm theo mệnh đề điều kiện để kết thúc vòng lặp khi thỏa điều kiện. Chúng ta xét ví dụ 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,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) iterator)
    RULES
        ITERATE (12) UNTIL sales_amount[12, 2003] > 4500
        (sales_amount [12, 2003] =
                  sales_amount[ITERATION_NUMBER + 1, 2003]
                + sales_amount[ITERATION_NUMBER + 2, 2003],
        iterator [12, 2003] =
                iterator[12, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)
Kết quả:
(Hình 3)

Câu lệnh 4 có số lần lặp cho các biểu thức tính toán là 12 lần và sẽ dừng biểu thức tính toán khi thỏa điều kiện của biểu thức điều kiện: sales_amount[12, 2003] > 4500. Kết quả cho thấy với PRD_TYPE_ID là 1 thì ở lần lặp thứ 1 đã thỏa điều kiện trong khi PRD_TYPE_ID là 2 thì ở lần lặp thứ 3 mới thỏa.

Chú ý: các biểu thức tính toán sẽ được thực hiện ít nhất 1 lần trước khi xét đến biểu thức điều kiện được khai báo với từ khóa UNTIL.

3. Sử dụng với hàm PREVIOUS
Để tham chiếu với giá trị trước đó trong vòng lặp, chúng ta có thể dùng hàm PREVIOUS.
Câu lệnh:
 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,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) iterator)
    RULES
        ITERATE (12) UNTIL PREVIOUS (sales_amount[12, 2003]) > 4500
        (sales_amount [12, 2003] =
                  sales_amount[ITERATION_NUMBER + 1, 2003]
                + sales_amount[ITERATION_NUMBER + 2, 2003],
        iterator [12, 2003] =
                iterator[12, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 5)
Kết quả:
(Hình 4)
Chú ý: hàm PREVIOUS chỉ được sử dụng với từ khóa UNTIL, các vị trí khác khi dùng hàm này sẽ bị báo lỗi:
ORA-32618: incorrect use of MODEL PREVIOUS function

4. Các vấn đề khác
  • Số vòng lặp tối đa được cho phép ở phiên bản 12c là: 4,294,967,295 (4Gig - 1) lần.
  • Hàm ITERATION_NUMBER có thể được sử dụng trong biểu thức điều kiện của từ khóa UNTIL nhưng không được sử dụng trong PARTITION BY hoặc DIMENSION BY hoặc MEASURES.
  • Bản chất của việc lặp này là với mỗi giá trị duy nhất trong PARTITION BY Oracle sẽ lặp lại n lần bộ các biểu thức tính toán được khai báo.
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

Wednesday, 23 September 2015

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

13:09 Posted by NTP , No comments
Bài viết này sẽ giới thiệu về thứ tự thực hiện của các biểu thức tính toán được sử dụng trong 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> ... )

Qua các phần trước, chúng ta thấy rằng trong mệnh đề MODEL có rất nhiều biểu thức tính toán được khai báo. Các biểu thức tính toán này có thể phụ thuộc lẫn nhau, kết quả tính toán của 1 biểu thức có thể sẽ là đầu vào cho 1 biểu thức tính toán tiếp theo. Do vậy việc chọn cách sắp xếp thứ tự của các phép tính khác nhau sẽ cho ra những kết quả khác nhau.

Chúng ta xét tập dữ liệu sau:
1
2
3
4
5
6
SELECT a.prd_type_id,
       a.year,
       a.month,
       a.amount
  FROM all_sales a
 WHERE month IN (10, 11, 12);
(Câu lệnh 1)
Kết quả:
(Hình 1)
Với tập kết quả này, giả sử chúng ta cần thực hiện các biểu thức tính toán sau:
  • Cập nhật doanh số bán hàng của 12/2003 biết rằng doanh số mới này bằng bình quân doanh số bán hàng của 10/2003 và 11/2003
  • Cập nhật doanh số bán hàng của 11/2003 biết rằng doanh số mới này bằng 110% doanh số bán hàng của 10/2003
  • Cập nhật doanh số bán hàng của 10/2003 là 1000

Với các yêu cầu như trên, tùy thuộc vào thứ tự của các biểu thức tính toán và các từ khóa sử dụng kèm theo mà ta sẽ có các kết quả khác nhau. Chúng ta lần lượt xét các trường hợp câu lệnh sau:

TRƯỜNG HỢP 1: thứ tự của các biểu thức tính toán lần lượt cho tháng 10, tháng 11 và tháng 12
Câu lệnh:
 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 [10, 2003] =
                1000,
        sales_amount [11, 2003] =
                sales_amount[10, 2003] * 1.1,
        sales_amount [12, 2003] =
                  (  sales_amount[10, 2003]
                   + sales_amount[11, 2003])
                / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)
Kết quả:
(Hình 2)

Chúng ta thấy rằng theo thứ tự thì biểu thức tính toán cho tháng 10 sẽ được thực hiện trước, biểu thức tính toán cho tháng 11 sẽ lấy kết quả của tháng 10 vừa tính được nhân cho 110%, biểu thức tính toán cho tháng 12 sẽ lấy tổng kết quả của tháng 10 và tháng 11 vừa tính được rồi chia cho 2.

TRƯỜNG HỢP 2: thứ tự của các biểu thức tính toán lần lượt cho tháng 12, tháng 11 và tháng 10
Câu lệnh:
 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 [12, 2003] =
                  (  sales_amount[10, 2003]
                   + sales_amount[11, 2003])
                / 2,
        sales_amount [11, 2003] =
                sales_amount[10, 2003] * 1.1,
        sales_amount [10, 2003] =
                1000)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)
Kết quả:
(Hình 3)
Trường hợp này nhìn vào thứ tự của các biểu thức chúng ta thấy rằng biểu thức tính toán cho tháng 12 được thực hiện trước, khi đó giá trị của tháng 10 và tháng 11 chưa thay đổi. Tương tự cho biểu thức tính toán của tháng 11. Biểu thức tính toán cho tháng 10 cập nhật doanh số bán hàng về 1000.

Kết luận: Oracle mặc định sẽ thực hiện các biểu thức tính toán theo thứ tự được khai báo.

Tiếp theo chúng ta sẽ khảo sát về cách kết hợp thứ tự các biểu thức tính toán và các từ khóa AUTOMATIC ORDER | SEQUENTIAL ORDER.

TRƯỜNG HỢP 3: thứ tự của các biểu thức tính toán lần lượt cho tháng 12, tháng 11 và tháng 10; đồng thời sử dụng thêm các từ khóa AUTOMATIC ORDER | SEQUENTIAL ORDER.
Câu lệnh:
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)
    RULES AUTOMATIC ORDER
(
sales_amount[12, 2003] =
  (sales_amount[10, 2003] +
  sales_amount[11, 2003]) / 2,
sales_amount[11, 2003] = 
  sales_amount[10, 2003] * 1.1,
sales_amount[10, 2003] = 1000
)
ORDER BY prd_type_id, year, month;

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)
    RULES SEQUENTIAL ORDER
(
sales_amount[12, 2003] =
  (sales_amount[10, 2003] +
  sales_amount[11, 2003]) / 2,
sales_amount[11, 2003] = 
  sales_amount[10, 2003] * 1.1,
sales_amount[10, 2003] = 1000
)
ORDER BY prd_type_id, year, month;

Kết quả:
Khi sử dụng với từ khóa AUTOMATIC ORDER

Khi sử dụng với từ khóa SEQUENTIAL ORDER

Từ 2 kết quả trên chúng ta thấy rằng, khi sử dụng với:
  • AUTOMATIC ORDER: các biểu thức tính toán sẽ được sắp xếp lại theo thứ tự nhất định trước khi thực hiện tính toán.
  • SEQUENTIAL ORDER: các biểu thức tính toán sẽ được thực hiện tuần tư theo thứ tự được khai báo. Biểu thức tính toán nào được khai báo trước sẽ được tính trước, biểu thức nào khai báo sau sẽ được tính sau. Mỗi một trật tự biểu thức tính toán khác nhau sẽ cho các kết quả khác nhau.
Để hiểu rõ thêm về 2 cách dùng trên, chúng ta xét tiếp ví dụ sau:
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)
    RULES AUTOMATIC ORDER
(
sales_amount[2, 2004] =
  sales_amount[1, 2004] + 1000,
sales_amount[1, 2004] = 
  sales_amount[12, 2003] * 1.1, 
sales_amount[12, 2003] = 1000
)
ORDER BY prd_type_id, year, month;

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)
    RULES SEQUENTIAL ORDER
(
sales_amount[2, 2004] =
  sales_amount[1, 2004] + 1000,
sales_amount[1, 2004] = 
  sales_amount[12, 2003] * 1.1, 
sales_amount[12, 2003] = 1000 
)
ORDER BY prd_type_id, year, month;

Kết quả:
Khi sử dụng với từ khóa AUTOMATIC ORDER

Khi sử dụng với từ khóa SEQUENTIAL ORDER

Ở ví dụ này chúng ta thấy rằng khi sử dụng với AUTOMATIC ORDER thì mệnh đề này đảm bảo kết quả trả về không bị bất cứ giá trị NULL nào. Từ khóa này tạo ra 1 vòng lặp vô hạn để gán các giá trị tính toán vào đó. Chỉ đến khi nào kết quả trả về không có giá trị NULL nào thì vòng lặp này mới được xem là kết thúc. Chúng ta xét ví dụ sau để thấy được sự hoạt động của vòng lặp khi dùng với AUTOMATIC ORDER.
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)
    RULES AUTOMATIC ORDER
        (sales_amount [2, 2004] = sales_amount[1, 2004] + 1000,
        sales_amount [2, 2004] = 1000,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1,
        sales_amount [12, 2003] = 1000)
ORDER BY prd_type_id, year, month;

Câu lệnh trên sẽ báo lỗi:
ORA-32630: multiple assignment in automatic order MODEL
Nhưng nếu chúng ta thay bằng từ khóa SEQUENTIAL ORDER thì lỗi trên sẽ không xuất hiện.

Kết luận:
  • Khi sử dụng với từ khóa AUTOMATIC ORDER: Oracle sẽ dựa vào sự phụ thuộc của các biểu thức tính toán (biểu thức nào ít phụ thuộc nhất sẽ được thực hiện trước, kết quả của nó sẽ là đầu vào của biểu thức tiếp theo) để sắp xếp lại thứ tự các biểu thức chứ không dựa vào thứ tự khai báo và sẽ đảm bảo rằng kết quả trả về không có giá trị NULL nào. Với từ khóa này, Oracle không cho phép gán 1 biểu thức tính toán nhiều lần.
  • Khi sử dụng với từ khóa SEQUENTIAL ORDER: Oracle sẽ thực hiện tuần tự các biểu thức tính toán theo thứ tự được khai báo và không quan tâm đến sự phụ thuộc lẫn nhau của các biểu thức. Từ khóa này cho phép chúng ta gán 1 biểu thức tính toán được nhiều lần.
Lưu ý: khi chúng ta không khai báo sử dụng AUTOMATIC | SEQUENTIAL ORDER trong mệnh đề MODEL thì mặc định là từ khóa SEQUENTIAL ORDER sẽ được sử dụng.

Vậy có nên sử dụng AUTOMATIC ORDER trong tất cả các trường hợp không? Câu trả lời là tùy thuộc vào kết quả mà chúng ta muốn nhận. Nếu như chúng ta chắc chắn với thứ tự của các biểu thức thì chúng ta sẽ dùng SEQUENTIAL ORDER. Ngoài ra chúng ta cũng nên lưu ý là khi sử dụng với AUTOMATIC ORDER thì chúng ta sẽ mất thêm 1 khoảng chi phí về thời gian cho việc Oracle phải xây dựng và sắp xếp lại sự phụ thuộc của các biểu thức trước khi thực thi câu lệnh.
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

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