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

Saturday, 18 July 2015

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

17:19 Posted by NTP , No comments
Bài viết này sẽ giới thiệu cách trả về kết quả của 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> ... )

Mệnh đề MODEL cho phép chúng ta trả kết quả về theo 2 dạng sau:
  • Trả về tất cả các dòng
  • Chỉ trả về các dòng được cập nhật
(Hình 1)
1. Trả về tất cả các dòng:
Để trả về tất cả các dòng, ta dùng mệnh đề: RETURN ALL ROWS
Ví dụ:
 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 ALL ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [month IN (1, 6, 12), year]
            ORDER BY month, year =
                sales_amount[CV (month), CV (year)] * 1.2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 1)

Câu lệnh trên cập nhật lại giá trị các tháng 1, 6, 12 lên 120%. Kết quả trả về bao gồm tất cả các dòng không được cập nhật (tháng 2, 3, 4, 5, 7, 8, 9, 10, 11) lẫn các dòng được cập nhật (tháng 1, 6, 12).

2. Chỉ trả về các dòng được cập nhật
Chúng ta dùng mệnh đề: RETURN UPDATED ROWS
Cũng ví dụ như trên nhưng chúng ta chỉ muốn kết quả trả về là các dòng được cập nhật (tháng 1, 6, 12), chúng ta sẽ viết 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 IN (1, 6, 12), year]
            ORDER BY month, year =
                sales_amount[CV (month), CV (year)] * 1.2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)

3. Vấn đề khác
Chúng ta thực thi câu lệnh sau với 2 trường hợp dùng RETURN ALL ROWS và RETURN UPDATED ROWS.
 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
 WHERE month IN (1, 2, 3)
MODEL RETURN ALL/UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [1, 2004] =
                sales_amount[CV (month), CV (year) - 1] * 1.2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)

Chúng ta thấy rằng năm 2004 không có trong bảng all_sales, các dòng của năm 2004 được tạo mới bằng cách thực hiện tính toán các dòng của năm 2003.

Tóm lại RETURN UPDATED ROWS sẽ trả về các dòng được UPSERT (các dòng được UPdate trên các dòng hiện có và các dòng được inSERT thêm vào).

* Lưu ý: các ví dụ trên có sử dụng hàm CV(), hàm này viết tắt của Current Value - cho phép biểu thức bên phải tham chiếu đến giá trị tương ứng của biểu thức bên trái.
Chi tiết hàm CV xem tại đây
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, 4 July 2015

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

13:47 Posted by NTP , No comments
Bài viết này sẽ giới thiệu cách sử dụng mệnh đề MODEL.

1. Giới thiệu:
Mệnh đề MODEL được Oracle giới thiệu từ phiên bản Oracle 10g. Mệnh đề này cho phép chúng ta thực hiện việc tính toán ngay từ bên trong câu lệnh. Việc tính toán này tương tự như chúng ta thực hiện tính toán trong Excel vậy, nghĩa là chúng ta có thể tham chiếu đến 1 cột bất kỳ của 1 hàng bất kỳ để lấy giá trị ô đó thực hiện việc tính toán.

2. Tạo môi trường test:
Chúng ta tải script tạo bảng và dữ liệu mẫu tại đây

3. Giải quyết yêu cầu:
Chúng ta xem thử dữ liệu mẫu chúng ta đang có ở đây:

1
2
3
4
5
6
SELECT a.year,
       a.month,
       a.prd_type_id,
       a.emp_id,
       a.amount
  FROM all_sales a;
(Câu lệnh 1)
Kết quả câu lệnh:
YEAR
MONTH
PRD_TYPE_ID
EMP_ID
AMOUNT
2003
1
1
21
10034.84
2003
1
2
21
1034.84
2003
2
1
21
15144.65
2003
2
2
21
1544.65
2003
3
1
21
20137.83
2003
3
2
21
2037.83
2003
4
1
21
25057.45
2003
4
2
21
2557.45
2003
5
1
21
17214.56
2003
5
2
21
1714.56
2003
6
1
21
15564.64
2003
6
2
21
1564.64
2003
7
1
21
12654.84
2003
7
2
21
1264.84
2003
8
1
21
17434.82
2003
8
2
21
1734.82
2003
9
1
21
19854.57
2003
9
2
21
1854.57
2003
10
1
21
21754.19
2003
10
2
21
2754.19
2003
11
1
21
13029.73
2003
11
2
21
1329.73
2003
12
1
21
10034.84
2003
12
2
21
1034.84
(Bảng kết quả 1)

Với bảng kết quả trên, giả sử chúng ta có các yêu cầu sau:

 Yêu cầu 1: Cho biết doanh số của năm 2013 (như bảng kết quả 1) và doanh số dự kiến của 3 tháng cuối năm 2014 bằng 1.5 lần doanh số 3 tháng tương ứng của năm 2013
- Chúng ta có thể viết câu lệnh sau để thực hiện yêu cầu:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT a.year,
       a.month,
       a.prd_type_id,
       a.amount
  FROM all_sales a
UNION ALL
SELECT 2014,
       a.month,
       a.prd_type_id,
       a.amount * 1.5
  FROM all_sales a
 WHERE month IN (10, 11, 12);
(Câu lệnh 2)

- Chúng ta cũng có thể viết cách khác bằng cách sử dụng mệnh đề MODEL. Cách viết 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
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [10, 2004] = sales_amount[10, 2003] * 1.5,
        sales_amount [11, 2004] = sales_amount[11, 2003] * 1.5,
        sales_amount [12, 2004] = sales_amount[12, 2003] * 1.5)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)

Chúng ta thấy rằng cả 2 cách viết đều trả về cùng 1 kết quả.

Yêu cầu 2: Cho biết doanh số của năm 2013 (như bảng kết quả 1) và doanh số dự kiến của:
   + Tháng 10/2014 gấp 1.5 lần tháng 10/2013
   + Tháng 11/2014 gấp 2 lần tháng 11/2013
   + Tháng 12/2014 bằng tháng 10/2014 + tháng 11/2014
Chúng ta thấy với yêu cầu phức tạp như vậy với cách viết số 1 sẽ rất mất thời gian, khó thực hiện và đôi khi sẽ tốn Performance của hệ thống nhưng với cách viết số 2 thì ta có thể thực hiện dễ dàng 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
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [10, 2004] =
                sales_amount[10, 2003] * 1.5,
        sales_amount [11, 2004] =
                sales_amount[11, 2003] * 2,
        sales_amount [12, 2004] =
                sales_amount[10, 2004] + sales_amount[11, 2004])
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)

4. Các mệnh đề con được dùng trong mệnh đề MODEL

(Hình 1)

- Sau khi thực thi 1 câu lệnh SQL, Oracle sẽ trả về 1 tập kết quả (Result Set). Tập kết quả này sẽ được lưu vào vùng nhớ và cho phép mệnh để MODEL phân chia tập này ra thành mảng nhiều chiều. Như hình trên ta thấy rằng khối lập phương ở giữa có thể được cắt theo chiều ngang, chiều dọc hay cắt thành từng lớp. Với mảng nhiều chiều này, mệnh đề MODEL cho phép chúng ta định nghĩa các luật (RULE) để thực hiện việc tính toán trên đó.
- Chúng ta khảo sát cú pháp đơn giản của mệnh để MODEL:
1
2
3
4
5
6
MODEL
  [PARTITION BY (<cols>)]
  DIMENSION BY (<cols>)
  MEASURES (<cols>)
  [RULES]
  (<rule>, <rule>,.., <rule>)

Trong đó:
  • PARTITION BY: dùng để chia tập kết quả thành nhiều phân vùng. 
  • DIMENSION BY: dùng để chia nhỏ từng vùng thành các chiều dữ liệu khác nhau.
  • MEASURES: dùng để tính toán giá trị trong từng chiều dữ liệu.
  • RULES: quy luật / cách tính toán 
Chúng ta lấy ví dụ 1 tập tin Excel để hiểu rõ hơn các khái niệm này:
  • PARTITION: tương ứng với từng sheet trong 1 workbook (workbook là tập kết quả trả về) 
  • DIMENSION: tương ứng với các cột (column) hoặc các dòng (row) trong 1 sheet
  • MEASURES: tương ứng với từng ô trong 1 sheet
  • RULES: tương ứng với việc dùng hàm để tính toán các giá trị trong các ô bất kỳ
(Hình 2)
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