Hướng dẫn SQL & XQuery cho IBM DB2, Phần 4: Phân tích dữ liệu

Sử dụng các truy vấn nâng cao để phân tích dữ liệu

Hướng dẫn này mô tả cách viết các truy vấn có yêu cầu phân tích dữ liệu cơ bản. Phần lớn các truy vấn chứa đựng các tính toán tuần tự, hay là những tính toán hoạt động trên một tập hợp có sắp thứ tự của các hàng -– những truy vấn thường gặp trong khi phân tích kinh doanh. Các chức năng xử lý phân tích trực tuyến (OLAP) tạo ra khả năng trả về các thông tin xếp hạng, đánh số hàng, và các hàm cột hiện có như một giá trị vô hướng trong một kết quả truy vấn. Hướng dẫn này là Phần 4 của loạt tài liệu hướng dẫn SQL & XQuery cho IBM® DB2®.

Pat Moffatt, Giám đốc Chương trình quản lý thông tin, Sáng kiến học đường, IBM

Pat Moffatt là giám đốc Chương trình quản lý thông tin thuộc Sáng kiến học đường IBM. Thông qua chương trình sáng kiến học đường này, bà muốn làm ra sẵn những nguồn tài nguyên thích hợp phục vụ giảng dạy về quản lý thông tin để giúp các giảng viên đại học tích hợp chủ đề phần mềm quản lý thông tin vào trong chương trình đào tạo của họ



Bruce Creighton, Chuyên viên lập kế hoạch các phần kỹ năng, IBM

Bruce Creighton là chuyên viên lập kế hoạch các phần kỹ năng trong khoa Phát triển và Lập kế hoạch đào tạo Quản lý thông tin. Với vai trò này, ông lập kế hoạch đầu tư về nội dung đào tạo và cân bằng đầu tư giữa các lĩnh vực mà IBM có thu lợi nhuận với các lĩnh vực mà tại đó yêu cầu về phát triển các kỹ năng là quan trọng, cần cung cấp đào tạo miễn phí



Jessica Cao, Chuyên viên phát triển các công cụ đào tạo, IBM

Jessica Cao là một sinh viên ngành Nghệ thuật - Khoa học và ngành Khoa học máy tính tại Trường đại học McMaster. Chị sẽ tốt nghiệp văn bằng kép hạng danh dự vào tháng Tư 2009. Jessica đang làm việc trong chương trình hỗ trợ và lập kế hoạch kênh kỹ năng quản lý thông tin DB2 của phòng thí nghiệm IBM Toronto, nhằm chuyên chú sâu hơn vào lập trình, biên tập và kỹ năng viết



20 05 2009

Trước khi bạn bắt đầu

Về loạt bài này

Loạt tài liệu hướng dẫn này dạy các chủ đề SQL từ cơ bản đến nâng cao và các chủ đề XQuery cơ bản. Nó cũng chỉ ra cách diễn đạt các câu hỏi nghiệp vụ thường gặp bằng các truy vấn cơ sở dữ liệu sử dụng các câu truy vấn SQL hay các câu truy vấn XQuery. Những người phát triển ứng dụng và những người quản trị cơ sở dữ liệu có thể sử dụng hướng dẫn này để nâng cao các kỹ năng truy vấn cơ sở dữ liệu của mình. Các thành viên tham gia sáng kiến học đường IBM có thể sử dụng loạt hướng dẫn này như một phần trong chương trình giảng dạy cơ sở dữ liệu của họ.

Tất cả các ví dụ trong bài này dựa trên Aroma, một cơ sở dữ liệu mẫu có chứa các dữ liệu doanh thu về các sản phẩm cà phê và chè được bán trong các cửa hàng khắp nước Mỹ. Mỗi ví dụ gồm có ba phần:

  • Một câu hỏi kinh doanh, được diễn đạt bằng ngôn ngữ hàng ngày
  • Một hoặc nhiều ví dụ truy vấn, được biểu diễn bằng SQL hay Xquery
  • Một bảng các kết quả được cơ sở dữ liệu trả về

Cách trình bày hướng dẫn này cho phép các học viên tìm hiểu về ngôn ngữ SQL và XQuery. Cũng như với bất kỳ việc học tập nào, việc bổ sung thêm các bài tập thực hành là rất quan trọng. Dữ liệu và các định nghĩa bảng tạo điều kiện thuận lợi cho các bài tập thực hành này.

Đối với các sinh viên sử dụng tài liệu này như một phần của khoá học, hãy nhận từ thầy hướng dẫn của mình các chỉ dẫn để kết nối tới cơ sở dữ liệu Aroma và tìm hiểu mọi sự khác biệt giữa hướng dẫn và cài đặt tại máy cục bộ của mình.

Hướng dẫn này được viết cho Express-C 9 DB2 trên nền UNIX®, Linux® và Windows® (trước đây gọi là Viper).

Về hướng dẫn này

Hướng dẫn này mô tả cách viết các truy vấn có yêu cầu một số cách phân tích dữ liệu. Nhiều câu hỏi có chứa các tính toán tuần tự, hay các tính toán hoạt động trên một tập hợp có sắp xếp thứ tự của các hàng, các câu hỏi thường gặp trong khi phân tích hoạt động kinh doanh :

  • Tổng số luỹ kế (hay tổng số dịch chuyển) theo tháng là bao nhiêu?
  • Trung bình dịch chuyển theo tuần là bao nhiêu?
  • Kết quả xếp hạng các số doanh thu hàng tháng như thế nào?
  • Tỷ số doanh thu hiện tại hàng tháng với doanh thu hàng năm là bao nhiêu?

DB2 của IBM cung cấp một cách hiệu quả để trả lời các câu hỏi loại này khi sử dụng các hàm SQL OLAP tiêu chuẩn có trong DB2 9. Các chức năng xử lý phân tích trực tuyến (OLAP) tạo ra khả năng trả về các thông tin xếp hạng, đánh số hàng, và các hàm cột hiện có như một giá trị vô hướng trong một kết quả truy vấn. Một hàm OLAP có thể được chứa trong các biểu thức trong một danh sách chọn lựa hay mệnh đề ORDER BY của một câu lệnh chọn.

Hướng dẫn này gồm có một loạt các ví dụ với truy vấn kinh doanh và cú pháp kết hợp được trình bày trong mỗi trường hợp. Trước khi thực hiện các truy vấn này, hãy đọc các mô tả toàn bộ các chức năng OLAP trong SQL Reference Guide (Hướng dẫn Tham khảo SQL) .

Hướng dẫn này cũng cho thấy cách sử dụng các hàm vô hướng để tính toán và trích ra các thông tin ngày tháng, ví dụ như tên ngày trong tuần và tên tháng, từ các cột DATE (ngày tháng).

Nhiều truy vấn trong chương này dựa vào các tổng gộp doanh thu. Do các bảng Sales (Doanh thu) lưu giữ các tổng số hằng ngày, nên thiết kế cơ sở dữ liệu của bạn có thể bao gồm các bảng tổng gộp để trả lời các truy vấn này.

Kết nối tới một cơ sở dữ liệu

Bạn cần kết nối tới một cơ sở dữ liệu trước khi có thể sử dụng các câu lệnh SQL để truy vấn hay xử lý dữ liệu. Lệnh CONNECT kết hợp một kết nối cơ sở dữ liệu với một tên người sử dụng.

Thông qua người hướng dẫn tìm ra tên cơ sở dữ liệu bạn cần nối tới. Đối với loạt bài này, tên cơ sở dữ liệu là aromadb.

Để kết nối tới cơ sở dữ liệu aromadb, gõ lệnh sau đây vào trong bộ xử lý dòng lệnh DB2:

CONNECT TO aromadb USER userid USING password

Thay thế "userid" và "password" bằng số ID và mật khẩu của người sử dụng mà bạn đã nhận được từ thầy hướng dẫn. Nếu máy không hỏi userid và password thì chỉ cần gõ vào lệnh sau đây:

CONNECT TO aromadb

Thông điệp sau báo cho bạn biết rằng đã tạo kết nối thành công:

Database Connection Information
Database server      = DB2/NT 9.0.0
SQL authorization ID = USERID
Local database alias = AROMADB

Khi đã kết nối được, bạn có thể bắt đầu sử dụng cơ sở dữ liệu.


Các tổng số luỹ kế

Câu hỏi

Các số doanh thu hằng ngày của mặt hàng cà phê Roma Aroma trong tháng Giêng 2006 là gì? Các tổng con lũy kế theo Đôla và các số lượng bán ra trong tháng này là gì?

Truy vấn OLAP

SELECT date, SUM(dollars) AS total_dollars,
    SUM(SUM(dollars)) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING) AS run_dollars,
    SUM(quantity) AS total_qty,
    SUM(SUM(quantity)) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING) AS run_qty
	FROM aroma.period a, aroma.sales b, aroma.product c
	WHERE   a.perkey = b.perkey
	    AND c.prodkey = b.prodkey
	    AND c.classkey = b.classkey
	    AND year = 2006
	    AND month = 'JAN'
	    AND prod_name = 'Aroma Roma'
	GROUP BY date
	ORDER BY date;

Kết quả

DateTotal_DollarsRun_DollarsTotal_QtyRun_Qty
2006-01-02855.50855.50118118
2006-01-03536.501392.0074192
2006-01-04181.251573.2525217
2006-01-05362.501935.7550267
2006-01-06667.002602.7592359
2006-01-07659.753262.5091450
2006-01-08309.503572.0054504
2006-01-09195.753767.7527531
2006-01-10420.504188.2558589
2006-01-11547.504735.7578667
2006-01-12536.505272.2574741
2006-01-13638.005910.2588829
2006-01-141057.506967.75150979
2006-01-15884.507852.251221101
2006-01-16761.258613.501051206
2006-01-17455.509069.00661272
2006-01-18768.509837.501061378
2006-01-19746.7510584.251031481
2006-01-20261.0010845.25361517
2006-01-21630.7511476.00871604
2006-01-22813.7512289.751151719
...

Hàm OLAP SUM

Sự có mặt của mệnh đề OVER() phân biệt một hàm tập hợp đơn giản (SUM, MIN, MAX, COUNT, AVG) với một hàm gộp OLAP.

Hàm OLAP SUM tạo ra các tổng dịch chuyển khi khung cửa sổ chỉ rõ:

ROWS UNBOUNDED PRECEDING

Chỉ thị này ra lệnh cho hệ thống thực hiện hàm OLAP, trong trường hợp này là hàm SUM, trên tất cả các hàng đứng trước trong tập hợp kết quả. Bạn có thể cũng chỉ rõ một tập con của các hàng khi sử dụng các giới hạn khác, ví dụ như GROUP-BETWEEN. Để biết thông tin chi tiết về các khung cửa sổ, xem SQL Reference Guide.

Mệnh đề ORDER BY trong OLAP là then chốt. Đặc tả này bảo đảm rằng các hàng là đầu vào của hàm OLAP SUM được sắp xếp đúng thứ tự (theo ngày tháng Date, trong trường hợp này). Nếu bạn quên sử dụng chỉ thị này, các hàng đầu vào có thể xếp sai thứ tự lôgíc và kết quả của bạn về tổng dịch chuyển sẽ không có nghĩa. Mệnh đề ORDER BY cuối cùng trong truy vấn chỉ tác động đến cách hiển thị tập kết quả; nó đứng riêng và tách biệt với mệnh đề ORDER BY của hàm OLAP.

Hàm OLAP ROW_NUMBER

Bạn thậm chí có thể sử dụng các hàm OLAP cho các nhiệm vụ đơn giản như là cung cấp số lượng dòng trong một tập hợp kết quả như trong lệnh:

SELECT ROW_NUMBER() OVER() AS row_num, order_no, price
FROM aroma.orders;
ROW_NUMORDER_NOPRICE
136001200.46
236011535.94
33602780.00
...

Bắt đầu lại các tổng số luỹ kế

Câu hỏi

Các số doanh thu của Roma Aroma lũy kế trong mỗi tuần của Tháng giêng 2006 là gì?

Truy vấn OLAP

SELECT date, SUM(dollars) AS total_dollars,
	    SUM(SUM(dollars)) OVER(PARTITION BY week ORDER BY date
	    ROWS UNBOUNDED PRECEDING) AS run_dollars,
	    SUM(quantity) AS total_qty,
	    SUM(SUM(quantity)) OVER(PARTITION BY week ORDER BY date
	    ROWS UNBOUNDED PRECEDING) AS run_qty
	FROM aroma.period a, aroma.sales b, aroma.product c
	WHERE   a.perkey = b.perkey
	    AND c.prodkey = b.prodkey
	    AND c.classkey = b.classkey
	    AND year = 2006
	    AND month = 'JAN'
	    AND prod_name = 'Aroma Roma'
	GROUP BY week, date
	ORDER BY week, date;

Kết quả

DateTotal_DollarsRun_DollarsTotal_QtyRun_Qty
2006-01-02855.50855.50118118
2006-01-03536.501392.0074192
2006-01-04181.251573.2525217
2006-01-05362.501935.7550267
2006-01-06667.002602.7592359
2006-01-07659.753262.5091450
2006-01-08309.503572.0054504
2006-01-09195.75195.752727
2006-01-10420.50616.255885
2006-01-11547.501163.7578163
2006-01-12536.501700.2574237
2006-01-13638.002338.2588325
2006-01-141057.503395.75150475
2006-01-15884.504280.25122597
2006-01-16761.25761.25105105
2006-01-17455.501216.7566171
2006-01-18768.501985.25106277
2006-01-19746.752732.00103380
2006-01-20261.002993.0036416
2006-01-21630.753623.7587503
2006-01-22813.754437.50115618
...

Các phân vùng cửa sổ OLAP

Mệnh đề OLAP PARTITION BY bên trong mệnh đề OVER() cung cấp một phương tiện bắt đầu lại các tính toán khi các giá trị trong các cột được phân vùng thay đổi. Bạn có thể phân vùng các tính toán OLAP bằng một hoặc nhiều cột.

Trong truy vấn này bạn không tính đến WEEK như một cột được hiển thị mặc dù bạn sử dụng nó trong mệnh đề PARTITION BY. Hệ thống nhận được số tuần từ cột DATE. Tuy nhiên, các kết quả truy vấn của bạn có thể dễ dàng diễn giải hơn, nếu bạn đưa giá trị WEEK vào trong danh sách lựa chọn của bạn và định dạng bảng kết quả giống như kết quả khác.

Dưới đây là truy vấn được cập nhật để tính đến cột week trong lệnh chọn và kết quả xuất ra.

SELECT date, SUM(dollars) AS total_dollars,
		SUM(SUM(dollars)) OVER (PARTITION BY week ORDER BY date
	ROWS UNBOUNDED PRECEDING) AS run_dollars,
	    SUM(quantity) AS total_qty,
	    SUM(SUM(quantity)) OVER(PARTITION BY week ORDER BY date
	ROWS UNBOUNDED PRECEDING) AS run_qty, week
	FROM aroma.period a, aroma.sales b, aroma.product c
	WHERE   a.perkey = b.perkey
	    AND c.prodkey = b.prodkey
	    AND c.classkey = b.classkey
	    AND year = 2006
	    AND month = 'JAN'
	    AND prod_name = 'Aroma Roma'
	GROUP BY week, date
	ORDER BY week, date

Kết quả

DATETOTAL_DOLLARSRUN_DOLLARSTOTAL_QTYRUN_QTYWEEK
2006-01-02855.50855.501181182
2006-01-03536.501392.00741922
2006-01-04181.251573.2525 2172
2006-01-05362.501935.75502672
2006-01-06667.002602.75923592
2006-01-07659.753262.50914502
2006-01-08309.503572.00545042
2006-01-09195.75195.7527273
2006-01-10420.50616.2558853
2006-01-11547.501163.75781633
2006-01-12536.501700.25742373
2006-01-13638.002338.25883253
2006-01-141057.503395.751504753
2006-01-15884.504280.251225973
2006-01-16761.25761.251051054
2006-01-17455.501216.75661714
2006-01-18768.501985.251062774
2006-01-19746.752732.001033804
2006-01-20261.002993.00364164
2006-01-21630.753623.75875034
2006-01-22813.754437.501156184
...

Sử dụng các toán tử số học

Câu hỏi

Giá bán trung bình của mỗi sản phẩm trong năm 2004 là bao nhiêu? Tính trung bình bằng tổng doanh thu theo Đôla chia cho tổng số lượng bán ra.

Ví dụ truy vấn

SELECT prod_name, SUM(dollars) AS total_sales, SUM(quantity) AS total_qty,
	       DEC(sum(dollars)/sum(quantity), 7, 2) AS price
	FROM aroma.product a, aroma.sales b, aroma.period c
	WHERE   a.prodkey = b.prodkey
	    AND a.classkey = b.classkey
	    AND c.perkey = b.perkey
	    AND year = 2004
	GROUP BY prod_name
	ORDER BY price;

Kết quả

Prod_NameTotal_SalesTotal_QtyPrice
Gold Tips38913.75115633.36
Special Tips38596.00113903.38
Earl Grey41137.00113643.61
Assam Grade A39205.00107673.64
Breakfast Blend42295.50108803.88
English Breakfast44381.00107374.13
Irish Breakfast48759.00110944.39
Coffee Mug1054.002134.94
Darjeeling Number 162283.25115395.39
Ruby's Allspice133188.50234445.68
Assam Gold Blend71419.00116366.13
Colombiano188474.50275486.84
Aroma Roma203544.00283447.18
La Antigua197069.50268267.34
Veracruzano201230.00264697.60
Expresso XO224020.00285587.84
Aroma baseball cap15395.3519537.88
Lotta Latte217994.50269948.07
Cafe Au Lait213510.00263408.10
Aroma Sounds Cassette5206.006208.39
Xalapa Lapa251590.00292938.58
NA Lite231845.00258848.95
Demitasse Ms282385.25287439.82
Aroma t-shirt20278.50187010.84
Travel Mug1446.3513310.87
Darjeeling Special127207.001093111.63
Spice Sampler6060.0050512.00
Aroma Sounds CD7125.0055012.95
French Press, 2-Cup3329.8022414.86
Spice Jar4229.0023517.99
French Press, 4-Cup3323.6516719.90
Tea Sampler13695.0055024.90
...

Sử dụng các phép toán số học : ( ), +, -, *, /

Bạn có thể thực hiện các phép toán số học bên trong một danh sách lựa chọn hay bên trong một điều kiện tìm kiếm. Một tập hợp đầy đủ các toán tử số học được liệt kê trong bảng sau. Thứ tự mức ưu tiên đánh giá từ cao nhất tới thấp nhất (đỉnh bảng đến đáy bảng) và, bên trong một mức đã cho, từ trái sang phải, trong bảng:

Phép toánTên
( )Thứ tự đánh giá bắt buộc
+, -Dương và âm
*, /Nhân và chia
+, -Cộng và trừ

Nếu bạn có bất kỳ nghi ngờ nào về thứ tự đánh giá đối với một biểu thức đã cho, hãy nhóm biểu thức lại bằng dấu ngoặc đơn. Ví dụ, máy chủ đánh giá (4+ 3* 2) là 10 nhưng lại đánh giá biểu thức đã nhóm (( 4+ 3)* 2) là 14.

Các lưu ý về cách dùng

Hàm DEC được dùng để loại bỏ tất cả, chỉ để lại hai chữ số thập phân trong mỗi giá trị Price (Giá):

dec(sum(dollars)/sum(quantity), 7, 2) AS price

Để biết thêm thông tin về hàm DEC và các chức năng định dạng các kiểu dữ liệu khác, xem SQL Reference Guide.


So sánh các tổng dịch chuyển bằng OLAP

Câu hỏi

So sánh giữa các vùng phía Tây và phía Nam về mặt các tổng dịch chuyển doanh thu hàng ngày thế nào?

Truy vấn OLAP

SELECT t1.date, sales_cume_west, sales_cume_south, 
                sales_cume_west - sales_cume_south 
                AS west_vs_south
	FROM
	    (SELECT date, SUM(dollars) AS total_sales,
	        SUM(SUM(dollars)) OVER(ORDER BY date
	        ROWS UNBOUNDED PRECEDING) AS sales_cume_west
	    FROM aroma.market a,
	         aroma.store b,
	         aroma.sales c,
	         aroma.period d
	    WHERE   a.mktkey = b.mktkey
	        AND b.storekey = c.storekey
	        AND d.perkey = c.perkey
	        AND year = 2006
	        AND month = 'MAR'
	        AND region = 'West'
	    GROUP BY date) AS t1 JOIN
	    (SELECT date, SUM(dollars) AS total_sales,
	        SUM(SUM(dollars)) OVER(ORDER BY date
	        ROWS UNBOUNDED PRECEDING) AS sales_cume_south
	    FROM aroma.market a,
	         aroma.store b,
	         aroma.sales c,
	         aroma.period d
	    WHERE   a.mktkey = b.mktkey
	        AND b.storekey = c.storekey
	        AND d.perkey = c.perkey
	        AND year = 2006
	        AND month = 'MAR'
	        AND region = 'South'
	    GROUP BY date) AS t2
	ON t1.date = t2.date
	ORDER BY date;

Kết quả

DATESales_Cume_WestSales_Cume_SouthWEST_VS_SOUTH
2006-03-012529.252056.75472.50
2006-03-026809.004146.752662.26
2006-03-039068.756366.552702.20
...
2006-03-29100513.8562891.3537622.50
2006-03-30104267.4065378.7538888.65
2006-03-31107222.1568100.7539121.40

Mệnh đề OLAP ORDER BY

Một lợi thế của cách tiếp cận OLAP đối với tính toán chuỗi dịch chuyển là khả năng đặt chúng bên trong các truy vấn phụ. Việc sắp xếp thứ tự OLAP là một phần của chính bản thân hàm, và mỗi hàm OLAP có mệnh đề ORDER BY riêng của mình, độc lập với mệnh đề ORDER BY nằm cuối, thuộc truy vấn.

Trong truy vấn này mệnh đề ORDER BY DATE có trong mỗi hàm OLAP để bảo đảm các giá trị đúng được dùng để tính toán các tổng dịch chuyển. Tại cuối truy vấn có thêm một mệnh đề ORDER BY DATE nữa để điều khiển cách hiển thị tập hợp kết quả.

Các chú ý với truy vấn

Việc so sánh dịch chuyển giữa doanh thu của phía Tây và phía Nam được tính toán khi sử dụng phép tính số học đơn giản trong lệnh SELECT.


Trung bình trượt

Số tiền bán hàng dao động theo thời gian; khi dao động quá mạnh, chúng sẽ che lấp các xu thế dài hạn, nằm ngầm bên dưới. Các giá trị trung bình trượt được dùng để san bằng các ảnh hưởng của các dao động này. Ví dụ, giá trị trung bình trượt theo ba tuần là kết quả chia tổng gộp của ba tuần liên tiếp gần nhất cho ba.

Câu hỏi

Giá trị trung bình trượt ba tuần của doanh thu sản phẩm tại các cửa hàng ở San Jose và Miami trong quý ba năm 2005 là bao nhiêu?

Truy vấn OLAP

SELECT city, week, SUM(dollars) AS sales,
	    DEC(AVG(SUM(dollars)) OVER(partition by city
	    ORDER BY city, week ROWS 2 PRECEDING),7,2) AS mov_avg,
	    SUM(SUM(dollars)) OVER(PARTITION BY city
	    ORDER BY week ROWS unbounded PRECEDING) AS run_sales
	FROM aroma.store a,
	     aroma.sales b,
	     aroma.period c
	WHERE   a.storekey = b.storekey
	    AND c.perkey = b.perkey
	    AND qtr = 'Q3_05'
	    AND city IN ('San Jose', 'Miami')
	GROUP BY city, week;

Trong tập hợp kết quả sau đây, chú ý rằng các giá trị trung bình trong hai hàng đầu tiên với mỗi thành phố không phải là giá trị trung bình trượt ba tuần vì không có đủ 3 dữ liệu để hoàn thành một tính toán như vậy. Thay vào đó, hai giá trị trung bình ấy được tính toán đối với hàng đầu tiên (giá trị trung bình một tuần) và đối với hàng đầu tiên và hàng thứ hai (giá trị trung bình hai tuần) một cách tương ứng.

Kết quả

CityWeekSalesMov_avgRun_sales
Miami271838.551838.551838.55
Miami284482.153160.356320.70
Miami294616.70 3645.8010937.40
Miami304570.35 4556.4015507.75
Miami314681.95 4623.0020189.70
...
Miami385500.25 5235.0049493.35
Miami394891.40 5346.7154384.75
Miami403693.80 4695.1558078.55
...
San Jose273177.553177.553177.55
San Jose285825.804501.679003.35
San Jose298474.805826.0517478.15
San Jose307976.607425.7325454.75
San Jose317328.657926.6832783.40
San Jose326809.757371.6639593.15
San Jose337116.357084.9146709.50
...

Hàm DEC dùng để định nghĩa có bao nhiêu chữ số sẽ được hiển thị trong giá trị được trả về cho cột Mov_Avg . Để biết các chi tiết về hàm này, xem SQL Reference Guide.

Hàm OLAP AVG

Hàm OLAP AVG được sử dụng cùng với khung cửa sổ sau đây:

ROWS n PRECEDING

ở đây n là một số đại diện cho hệ số san bằng muốn có. Trong truy vấn trước đây giá trị này nói chung được thiết lập bằng từ khóa “unbounded” (không giới hạn). Trong truy vấn này, vì bạn muốn tính giá trị trung bình trượt theo 3 hàng, bạn chèn giá trị 2, để ngụ ý hàng hiện tại cộng với hai hàng trước.

Mệnh đề OLAP ORDER BY bảo đảm rằng hàm AVG được áp dụng vào chuỗi dịch chuyển đúng của các hàng (trong trường hợp này là WEEK). Mệnh đề PARTITION BY xác định giá trị mà theo nó hàm AVG sẽ được bắt đầu lại (trong trường hợp này là CITY).


Các tổng trượt

Câu hỏi

Tổng trượt theo từng bảy ngày của số lượng cà phê Demitasse Ms được bán trong Tháng Ba 2006 là gì?

Truy vấn OLAP

SELECT date, SUM(quantity) AS day_qty,
	    DEC(SUM(SUM(quantity)) OVER(ORDER BY date
	    ROWS 6 PRECEDING),7,2) AS mov_sum
	FROM aroma.sales a, aroma.period b, aroma.product c
	WHERE   b.perkey = a.perkey
	    AND c.classkey = a.classkey
	    AND c.prodkey = a.prodkey
	    AND year = 2006
	    AND month = 'MAR'
	    AND prod_name = 'Demitasse Ms'
	GROUP BY date
	ORDER BY date;

Tập hợp kết quả OLAP sau đây có chứa các tổng số dịch chuyển cho tất cả các hàng. Giống như trong truy vấn trung bình trượt trước đây, các giá trị thực tế của sáu hàng đầu tiên không hiển thị tổng trượt bảy ngày như mong muốn; bạn cần có bảy hàng để hiển thị chính xác giá trị đó.

Kết quả

DateDay_QtyMov_Sum
2006-03-016565.00
2006-03-021984.00
2006-03-0392176.00
2006-03-0491267.00
2006-03-05106373.00
2006-03-0692465.00
2006-03-07102567.00
2006-03-0821523.00
2006-03-0974578.00
2006-03-1081567.00
2006-03-1177553.00
2006-03-12127574.00
2006-03-13169651.00
2006-03-1431580.00
2006-03-1556615.00
2006-03-1640581.00
2006-03-1784584.00
2006-03-1834541.00
2006-03-19128542.00
2006-03-2097470.00
2006-03-2150489.00
2006-03-22147580.00
...

Một hàm tổng dịch chuyển, giống như trung bình dịch chuyển, được dùng để san bằng các ảnh hưởng của các dao động. Ví dụ, một tổng trượt bảy ngày được tính toán bằng cách lấy tổng bảy ngày liên tiếp.

Hàm vô hướng DEC được dùng để định nghĩa các giá trị thập phân được trả về cho cột Mov_Sum . Để biết các chi tiết về hàm này, xem SQL Reference Guide. Chú ý rằng trong ví dụ này, DEC làm cho các kết quả được hiển thị dưới dạng thập phân dù cho dữ liệu cơ sở có định dạng số nguyên.

Hàm OLAP SUM

Hàm OLAP SUM tạo ra các tổng trượt khi khung cửa sổ chỉ rõ:

ROWS n PRECEDING

Cũng có thể sử dụng từ khóa FOLLOWING .

Mệnh đề ORDER BY trong OLAP là then chốt. Đặc tả này bảo đảm rằng các hàng là đầu vào của hàm OLAP SUM được sắp xếp thứ tự đúng (theo Date tăng dần, trong trường hợp này).


Xếp hạng dữ liệu

Câu hỏi

Xếp hạng theo tổng doanh thu đôla trong Tháng Ba năm 2005 của các cửa hàng ở vùng phía Tây là như thế nào?

Truy vấn OLAP

SELECT store_name, district, SUM(dollars) AS total_sales,
	    RANK() OVER(ORDER BY SUM(dollars) DESC) AS sales_rank
	FROM aroma.market a,
	     aroma.store b,
	     aroma.sales c,
	     aroma.period d
	WHERE   a.mktkey = b.mktkey
	    AND b.storekey = c.storekey
	    AND d.perkey = c.perkey
	    AND year = 2005
	    AND month = 'MAR'
	    AND region = 'West'
	GROUP BY store_name, district;

Kết quả

Store_NameDistrictTotal_SalesSales_Rank
Cupertino Coffee SupplySan Francisco18670.501
Java Judy'sLos Angeles18015.502
Beaches BrewLos Angeles18011.553
San Jose Roasting CompanySan Francisco17973.904
Instant CoffeeSan Francisco15264.505
Roasters, Los GatosSan Francisco12836.506

Các tổng số hằng ngày từ các bảng Sales thoả mãn các điều kiện tìm kiếm trong mệnh đề WHERE được cộng lại, sau đó được xếp hạng.

Hàm OLAP RANK

Cơ sở dữ liệu IBM DB2 hỗ trợ một tập hợp các hàm xếp hạng OLAP, gồm có RANK() và DENSE_RANK(). Các hàm này không yêu cầu bất kỳ đối số nào; thay vào đó, mệnh đề OLAP ORDER BY định nghĩa cột hay biểu thức để dựa vào đó mà xếp hạng. Chú ý rằng thứ tự sắp xếp mặc định đối với các hàm OLAP là tăng dần (ASC), thường không phải là cách xếp hạng mong muốn cho các truy vấn kinh doanh. Bạn phải chỉ rõ từ khóa DESC trong mệnh đề OLAP ORDER BY để gán các thứ hạng từ cao đến thấp (ở đây 1 là giá trị cao nhất).

Hàm DENSE_RANK khác với với RANK ở một chi tiết cụ thể: khi bạn sử dụng hàm RANK, nếu có sự ngang bằng giữa hai hoặc nhiều hàng thì giá trị thứ hạng sẽ nhảy qua xuống dưới. Nếu hai hàng ngang bằng cùng xếp hạng là 2, vị trí tiếp theo sẽ là 4 (như là 1,2,2,4). Khi bạn sử dụng DENSE_RANK, không có một khe hở nào trong chuỗi các giá trị thứ hạng. Ví dụ, nếu hai hàng được xếp ở vị trí 8, vị trí tiếp theo vẫn là 9, như chỉ ra trong ví dụ sau:

SELECT prod_name, dollars, DENSE_RANK() OVER(ORDER BY dollars DESC) AS dense_rank,
	    RANK() OVER(ORDER BY dollars DESC) AS tie_rank
	FROM aroma.product a,
	     aroma.sales b,
	     aroma.period c
	WHERE   a.prodkey = b.prodkey
	    AND a.classkey = b.classkey
	    AND c.perkey = b.perkey
	    AND date = '01-03-2006';

Kết quả

PROD_NAMEDOLLARSDENSE_RANKTIE_RANK
Espresso Machine Italiano499.7511
Cafe Au Lait392.0022
Veracruzano360.0033
Lotta Latte328.0044
NA Lite306.0055
Colombiano283.5066
Darjeeling Special207.0077
Colombiano202.5088
Colombiano202.5088
Expresso XO201.50910
Xalapa Lapa195.501011
...

Sử dụng các tính toán DATE

Câu hỏi

Tính toán một ngày tháng ở trước 90 ngày và sau 90 ngày so với một ngày tháng đã cho.

Ví dụ truy vấn

SELECT date - 90 DAYS AS due_date,
	    date AS cur_date,
	    date + 90 DAYS AS past_due
	FROM aroma.period
	WHERE year = 2004
	    AND month = 'JAN';

Kết quả

Due_DateCur_DatePast_Due
2003-10-03 2004-01-01 2004-03-31
2003-10-04 2004-01-02 2004-04-01
2003-10-05 2004-01-03 2004-04-02
...

Tăng hoặc giảm các ngày tháng

Bạn có thể hoặc thêm vào hay xoá đi một ngày tháng. Để làm như vậy bạn phải chỉ rõ thông tin sau đây:

  • Giá trị sẽ được tăng hay giảm (tên cột hay biểu thức datetime)
  • Thành phần sẽ áp dụng (các) cách tăng. Thành phần này có thể là ngày, tháng, năm hay một kết hợp của các giá trị này.
  • Giá trị mức tăng lên dương hay âm

Về truy vấn

Ví dụ truy vấn tính toán một ngày tháng ở trước 90 ngày và sau 90 ngày so với một ngày tháng đã cho. Hàm này trả về giá trị theo định dạng ngày tháng thời gian ANSI SQL 92.

Cần phải biết chắc chắn bạn muốn điều chỉnh giá trị nào. Ví dụ, trong hầu hết các trường hợp, giá trị "90 ngày " không giống với "3 tháng". Nếu bạn thay đổi câu hỏi trên thành 3 tháng, khi đó, bạn sẽ có khả năng nhận được một tập hợp kết quả khác:

SELECT date - 3 MONTHS AS due_date,
	    date AS cur_date,
	    date + 3 MONTHS AS past_due
	FROM aroma.period
	WHERE year = 2004
	    AND month = 'JAN';

Kết quả

Due_DateCur_DatePast_Due
2003-10-01 2004-01-01 2004-04-01
2003-10-02 2004-01-02 2004-04-02
2003-10-03 2004-01-03 2004-04-03
...

Những sự không ăn khớp này có thể trở nên khó hiểu nếu bạn không cẩn thận. Bạn cần phải chú ý đến các trường hợp đặc biệt như năm nhuận để bảo đảm chắc chắn rằng bạn nhận được các thông tin cần thiết.

Bạn cũng có thể điều chỉnh cách kết hợp của các giá trị, như trong

SELECT date - 3 MONTHS - 4 DAYS AS due_date,
	    date AS cur_date,
	    date + 3 MONTHS + 4 DAYS AS past_due
	FROM aroma.period
	WHERE year = 2004
	    AND month = 'JAN';

Kết quả

Due_DateCur_DatePast_Due
2003-09-27 2004-01-01 2004-04-05
2003-09-28 2004-01-02 2004-04-06
2003-09-29 2004-01-03 2004-04-07
...

Bạn có thể điều chỉnh tương tự các phần riêng lẻ của bất kỳ kiểu dữ liệu TIME nào (HOURS, MINUTES, và/ hoặc SECONDS) hay bất kỳ kiểu dữ liệu TIMESTAMP nào (YEARS, MONTHS, DAYS, HOURS, MINUTES, và/ hoặc SECONDS). Để biết thêm thông tin về phép tính số học DATE, TIME, và TIMESTAMP, xem SQL Reference Guide.


Sử dụng mệnh đề HAVING để loại trừ các nhóm

Câu hỏi

Các sản phẩm nào có doanh thu tổng nhỏ hơn USD 25.000 trong năm 2005?

Ví dụ truy vấn

SELECT prod_name, sum(dollars) AS total_sales
	FROM aroma.product a, aroma.sales b, aroma.period c
	WHERE   a.prodkey = b.prodkey
	    AND a.classkey = b.classkey
	    AND c.perkey = b.perkey
	    AND year = 2005
	GROUP BY prod_name
	HAVING sum(dollars) < 25000 ORDER BY total_sales DESC;

Kết quả

Prod_NameTotal_Sales
Aroma t-shirt21397.65
Espresso Machine Royale18119.80
Espresso Machine Italiano17679.15
Coffee Sampler16634.00
Tea Sampler14907.00
Aroma baseball cap13437.20
Aroma Sheffield Steel Teapot8082.00
Spice Sampler7788.00
Aroma Sounds CD5937.00
Aroma Sounds Cassette5323.00
French Press, 4-Cup4570.50
Spice Jar4073.00
French Press, 2-Cup3042.75
Travel Mug1581.75
Easter Sampler Basket1500.00
Coffee Mug1258.00
Christmas Sampler1230.00

Điều kiện trên các nhóm: mệnh đề HAVING

Mặc dù việc phân chia dữ liệu thành các nhóm làm giảm lượng thông tin trả về, các truy vấn thường vẫn còn gửi về nhiều thông tin hơn yêu cầu của bạn. Bạn có thể sử dụng mệnh đề HAVING để loại trừ các nhóm không thỏa mãn một điều kiện đã chỉ rõ, ví dụ như các tổng số Đôla bé hơn hay lớn hơn một số đã cho.

Truy vấn này tính toán tổng thu nhập bán hàng với từng sản phẩm trong năm 2005, rồi chỉ giữ lại các sản phẩm nào có tổng thu nhập dưới $25.000.

Cú pháp của mệnh đề HAVING

SELECT column name(s)
	FROM table name(s)
	[WHERE search_condition]
	[GROUP BY group_list]
	[HAVING condition]
	[ORDER BY order_list];
conditionMột điều kiện SQL có thể chứa các hàm tập hợp.

Mệnh đề HAVING khác với mệnh đề WHERE về các mặt sau.

Mệnh đề WHERE Mệnh đề HAVING
Làm việc theo các hàng dữ liệu trước khi tạo nhóm.Làm việc theo tập hợp kết quả sau khi tạo nhóm.
Không thể biểu diễn các điều kiện qua các hàm tập hợp (ví dụ SUM hay AVG), nhưng có thể sử dụng các bí danh cột cho các biểu thức không tập hợp.Có thể biểu diễn các điều kiện qua hàm tập hợp hoặc bí danh cột bất kỳ .

Các lưu ý về cách dùng

Bất kỳ hàm tập hợp nào cũng có thể được sử dụng trong một điều kiện của mệnh đề HAVING. Một truy vấn có mệnh đề HAVING phải chứa một mệnh đề GROUP BY, trừ khi danh sách lựa chọn chỉ chứa các hàm tập hợp. Ví dụ:

SELECT MIN(prodkey), MAX(classkey)
	FROM aroma.product
	HAVING MIN(prodkey) = 0;

Tóm tắt

Tóm tắt

Hướng dẫn này là phần 4 trong loạt bài, nó đã mô tả cách:

  • Sử dụng các hàm SQL OLAP để thực hiện phân tích dữ liệu
  • Sử dụng các hàm vô hướng DATE và phép tính số học DATE để tính toán và lấy ra thông tin từ các cột DATETIME

Các hàm giải tích

Các hàm SQL OLAP có thể được dùng để trả lời một phạm vi rộng các câu hỏi kinh doanh như yêu cầu xếp hạng, tính tỷ lệ, tính tổng và các giá trị trung bình dịch chuyển, vân vân. Các ví dụ trong hướng dẫn này chỉ tập trung vào một số tính toán mà một hàm OLAP có thể xử lý. Nói chung, các hàm OLAP khá linh hoạt, và làm đơn giản đáng kể các câu lệnh SQL phức tạp mà bạn sẽ phải viết nếu thiếu chúng.


Tải về

Mô tảTênKích thước
Cơ sở dữ liệu AromaAroma_Data.zip1MB

Tài nguyên

Học tập

Lấy sản phẩm và công nghệ

  • Tải xuống DB2 Express-C, một phiên bản DB2 Express miễn phí để phát triển và triển khai các ứng dụng .

Thảo luận

Bình luận

developerWorks: Đăng nhập

Các trường được đánh dấu hoa thị là bắt buộc (*).


Bạn cần một ID của IBM?
Bạn quên định danh?


Bạn quên mật khẩu?
Đổi mật khẩu

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Ở lần bạn đăng nhập đầu tiên vào trang developerWorks, một hồ sơ cá nhân của bạn được tạo ra. Thông tin trong bản hồ sơ này (tên bạn, nước/vùng lãnh thổ, và tên cơ quan) sẽ được trưng ra cho mọi người và sẽ đi cùng các nội dung mà bạn đăng, trừ khi bạn chọn việc ẩn tên cơ quan của bạn. Bạn có thể cập nhật tài khoản trên trang IBM bất cứ khi nào.

Thông tin gửi đi được đảm bảo an toàn.

Chọn tên hiển thị của bạn



Lần đầu tiên bạn đăng nhập vào trang developerWorks, một bản trích ngang được tạo ra cho bạn, bạn cần phải chọn một tên để hiển thị. Tên hiển thị của bạn sẽ đi kèm theo các nội dung mà bạn đăng tải trên developerWorks.

Tên hiển thị cần có từ 3 đến 30 ký tự. Tên xuất hiện của bạn phải là duy nhất trên trang Cộng đồng developerWorks và vì lí do an ninh nó không phải là địa chỉ email của bạn.

Các trường được đánh dấu hoa thị là bắt buộc (*).

(Tên hiển thị cần có từ 3 đến 30 ký tự)

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Thông tin gửi đi được đảm bảo an toàn.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=70
Zone=Information Management, SOA và dịch vụ Web
ArticleID=383238
ArticleTitle=Hướng dẫn SQL & XQuery cho IBM DB2, Phần 4: Phân tích dữ liệu
publish-date=05202009