Oracle Tech Talk

All about Oracle Technologies

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