Thursday , 12 December 2024
HOT

Thống kê trong sql server bằng pivot table

Trong quá trình lấy số liệu, thiết lập bảng biểu và báo cáo, một trường hợp thường gặp là bạn phải hiển thị dữ liệu từ dạng dòng chuyển thành cột. Kiểu truy vấn như vậy gọi là Crosstab-Query.
Từ phiên bản SQL Server 2000 trở về trước, không có một lệnh nào trực tiếp hỗ trợ việc truy xuất như vậy. Đa số sử dụng các câu lệnh trung gian như CASE, CURSOR. Từ phiên bản SQL Server 2005, Microsoft đã giới thiệu một lệnh mới, là PIVOT để hỗ trợ việc truy xuất dữ liệu.
Bài viết này sẽ mô tả cách thức truy vấn crosstab trên bằng cả hai cách, dùng các câu lệnh trung gian và cả PIVOT.
Dữ liệu đầu vào : CSDL Northwind, bảng Orders
Đây là Sample Database có sẵn trên SQL Server 2000, ở các phiên bản SQL Server sau này, bạn phải tự down về chứ không có trong bộ cài nữa.

Bảng Orders có nhiều cột, tuy nhiên để lấy ví dụ cho bài viết này ta chỉ quan tâm đến 3 cột là:

OrderID ID của đơn hàng. Trong bảng Orders này thì OrderID là khóa chính
EmployeeID ID của nhân viên. Với 1 đơn hàng thì có 1 và chỉ 1 nhân viên bán ra đơn hàng đó
ShipVia Đơn hàng này được vận chuyển qua phương thức nào. Thuộc tính này chỉ có 3 giá trị là 1,2 và 3 đại diện cho 3 phương thức vận chuyển khác nhau.

Cách diễn giải 3 trường này có thể không khớp với mô tả CSDL Northwind của Microsoft nhưng do tôi lấy dữ liệu trong này để làm ví dụ nên cũng không cần quá quan trọng về vấn đề này.

Bài toán
Lập báo cáo số đơn hàng bán được bởi mỗi nhân viên, chia theo từng loại vận chuyển. Ví dụ:

EmployeeID ShipVia1 ShipVia2 ShipVia3
1 21 33 54
2 44 23 11

Đây là dữ liệu cần lấy để tạo báo cáo:

SELECT OrderID, EmployeeID, ShipVia FROM Orders


Cách xuất dữ liệu thông thường đơn giản chỉ truy vấn như sau:

SELECT EmployeeID,
       ShipVia,
       COUNT(1) So_luong
FROM   dbo.Orders
GROUP BY
       EmployeeID,
       ShipVia
ORDER BY
       EmployeeID


Tuy nhiên dạng kết quả như thế này không phù hợp với yêu cầu bài toán đưa ra. Mặc dù dựa vào số liệu này hoàn toàn có thể thiết kế được 1 bảng biểu theo yêu cầu.
Dạng báo cáo hiển thị kết quả từ dạng dòng (ở đây là các giá trị của ShipVia) thành dạng cột (tạo thành các cột ShipVia1, ShipVia2 và ShipVia3 tương ứng với 3 giá trị của ShipVia là 1,2 và 3) được gọi là Crosstab-Report. Và câu truy vấn để thực hiện gọi là Crosstab-Query.

Cách làm 1: Tạo Crosstab-Report bằng phát biểu CASE
Ở phiên bản SQL Server 2000 trở về trước, ta cần dùng phát biểu CASE để thực hiện

SELECT EmployeeID,
       COUNT(CASE ShipVia WHEN 1 THEN 1 END) ShipVia1,
       COUNT(CASE ShipVia WHEN 2 THEN 1 END) ShipVia2,
       COUNT(CASE ShipVia WHEN 3 THEN 1 END) ShipVia3,
       COUNT(1) Total
FROM   dbo.Orders
GROUP BY
       EmployeeID
ORDER BY
       EmployeeID

Kết quả

Câu truy vấn được thực hiện như sau:
– Ta thực hiện một lệnh GROUP BY theo các nhân viên.
– Tại mỗi một Group, tiến hành đếm số lượng các đơn hàng theo từng loại vận chuyển
– Dòng COUNT(1) Total cho phép ta tạo thêm 1 cột nữa, để tính tổng số đơn hàng của mỗi nhân viên bán ra. Dòng này là tùy chọn.
Ngoài ra khi dùng CASE kết hợp với GROUP BY, ta có thể sử dụng thêm một chức năng nữa, là tạo ra một dòng cuối cùng tổng kết (summary) các dòng trong bảng bằng cách đưa thêm lựa chọn WITH CUBE hoặc ROLLUP

SELECT EmployeeID,
       COUNT(CASE ShipVia WHEN 1 THEN 1 END) ShipVia1,
       COUNT(CASE ShipVia WHEN 2 THEN 1 END) ShipVia2,
       COUNT(CASE ShipVia WHEN 3 THEN 1 END) ShipVia3,
       COUNT(1) Total
FROM   dbo.Orders
GROUP BY
       EmployeeID WITH CUBE
ORDER BY EmployeeID


Như ta thấy, thực hiện câu lệnh trên sẽ tạo ra một dòng nữa có EmployeeID là NULL, và các giá trị trong dòng này là tổng các giá trị ở các dòng còn lại trong bảng kết quả.
Tuy nhiên có một điều bất tiện là khi thực hiện sắp xếp, giá trị NULL lại được đưa lên đầu.
Nếu làm việc trên Oracle, có thể bạn đã quen với mệnh đề ORDER BY có thêm thuộc tính NULL_FIRSTS hoặc NULL_LASTS để cho phép xếp giá trị NULL lên đầu tiên hoặc cuối cùng.
Vậy trên SQL Server thì sao? Ta có thể thực hiện như sau.

SELECT EmployeeID,
       COUNT(CASE ShipVia WHEN 1 THEN 1 END) ShipVia1,
       COUNT(CASE ShipVia WHEN 2 THEN 1 END) ShipVia2,
       COUNT(CASE ShipVia WHEN 3 THEN 1 END) ShipVia3,
       COUNT(1) Total
FROM   dbo.Orders
GROUP BY
       EmployeeID WITH CUBE
ORDER BY
       (
       CASE
              WHEN EmployeeID IS NULL THEN 1 ELSE 0
       END
       ), EmployeeID

Như thế nếu EmployeeID là NULL (dòng tổng) thì kết quả sẽ là 1, và được xếp ở cuối cùng. Ngược lại nếu không null, thì lại dựa vào EmployeeID để Order tiếp. Lệnh CASE thật là kì diệu phải không?

Cách làm 2: Dùng lệnh PIVOT
Lệnh PIVOT mới được đưa vào từ phiên bản SQL Server 2005:

SELECT EmployeeID,
       [1] ShipVia1,
       [2] ShipVia2,
       [3]ShipVia3,
       [1] + [2] + [3] Total
FROM   (
SELECT EmployeeID,
       ShipVia,
       OrderID
FROM   dbo.Orders
) SourceTable
PIVOT(COUNT(OrderID) FOR ShipVia IN ([1], [2], [3])) PivotTable
ORDER BY EmployeeID

Giải thích
Bạn có thể xem MSDN để hiểu rõ hơn, cũng như biết thêm các cách sử dụng nâng cao của PIVOT. Ở đây tôi chỉ giải thích ngắn gọn và giới thiệu cách sử dụng đơn giản nhất.
Đầu tiên, quan trọng nhất là tạo bảng SourceTable như trên. Đây là bảng dữ liệu đầu vào trước khi thực hiện PIVOT

(
SELECT EmployeeID,
       ShipVia,
       OrderID
FROM   dbo.Orders
) SourceTable

Tiếp theo là bảng PIVOT, cho phép tạo các hàm tính toán và thực hiện hoán chuyển hàng thành cột dựa trên dữ liệu đầu vào là bảng SourceTable

PIVOT(COUNT(OrderID) FOR ShipVia IN ([1], [2], [3])) PivotTable

Chú ý đoạn FOR ShipVia IN ([1], [2], [3]) ở đây là các giá trị cho thuộc tính ShipVia.

Kết luận
Cuối cùng ta có thể tổng kết lại như sau
– Dạng báo cáo Crosstab cung cấp cái nhìn trực quan hơn về dữ liệu trong một số trường hợp.
– Tuy nhiên khi phát sinh thêm cột dữ liệu cần phải thay đổi lại câu truy vấn. Điều này rõ ràng không thuận tiện bằng việc báo cáo xuất theo dạng dòng như trước.
– Tuy SQL Server cung cấp tính năng PIVOT từ bản 2005 nhưng rất nhiều người vẫn dùng cách CASE như ở phiên bản trước, trong đó có cả tôi. Lý do thì tùy mỗi người. Với tôi thì đó là sự đơn giản, rõ ràng, và rất thuận tiện để tạo các dòng tổng kết.

Nguồn: sqlviet[dot]com

Leave a Reply

Your email address will not be published. Required fields are marked *

*