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
- 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
- Giới thiệu tổng quan
- Các cách trả dữ liệu về với từ khóa RETURN
- Cách truy xuất dữ liệu (Positional Cell Reference và Symbolic Cell Reference)
- Cách sử dụng hàm CV, từ khóa ANY và IS ANY
- 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
- 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
- Vòng lặp các biểu thức tính toán với từ khóa ITERATE
- Cách sử dụng từ khóa UPSERT | UPSERT ALL và UPDATE với từ khóa RULES
- Cách sử dụng từ khóa UNIQUE DIMENSION và UNIQUE SINGLE REFERENCE
- Cách sử dụng mệnh đề REFERENCE để tham chiếu dữ liệu
0 comments :
Post a Comment