MySQL - ROLLUP: Th chủng.Data Aggregation

Xin chào các bạn đam mê MySQL! Hôm nay, chúng ta sẽ bắt đầu một hành trình thú vị vào thế giới của việc tổng hợp dữ liệu với clause ROLLUP. Là một giáo viên máy tính gần gũi, tôi sẽ hướng dẫn các bạn qua chủ đề này từng bước một. Đừng lo lắng nếu bạn mới bắt đầu học lập trình - chúng ta sẽ bắt đầu từ cơ bản và dần dần nâng cao. Hãy nhâm nhi một tách cà phê (hoặc trà, nếu bạn thích vậy), và cùng nhau bắt đầu nhé!

MySQL - ROLLUP

The MySQL ROLLUP Clause: Người Bạn Mới Tốt Nhất Trong Phân Tích Dữ Liệu

What is ROLLUP?

Hãy tưởng tượng bạn đang lên kế hoạch cho một buổi dã ngoại gia đình lớn. Bạn có danh sách tất cả các món ăn, số lượng và giá cả. Liệu có phải bạn muốn nhanh chóng xem tổng chi phí cho mỗi nhóm thực phẩm, cũng như tổng chi phí cho tất cả mọi thứ? Đó chính xác là điều mà ROLLUP làm trong MySQL - nó giúp bạn tạo ra các hàng tóm tắt ở các mức độ khác nhau của dữ liệu.

ROLLUP là một mở rộng của clause GROUP BY. Nó cho phép bạn tạo ra nhiều mức tổng tạm và một tổng cộng trong một truy vấn duy nhất. Thật tuyệt vời phải không?

Basic Syntax

Dưới đây là cấu trúc cơ bản của một truy vấn sử dụng ROLLUP:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ... WITH ROLLUP;

Hãy phân tích này với một ví dụ đơn giản. Giả sử chúng ta có một bảng叫做 picnic_items:

CREATE TABLE picnic_items (
item_name VARCHAR(50),
category VARCHAR(50),
quantity INT,
price DECIMAL(10, 2)
);

INSERT INTO picnic_items VALUES
('Sandwich', 'Food', 20, 5.99),
('Soda', 'Drink', 30, 1.99),
('Chips', 'Snack', 15, 2.50),
('Water', 'Drink', 40, 0.99),
('Cookies', 'Snack', 25, 3.50);

Bây giờ, hãy sử dụng ROLLUP để nhận được tóm tắt chi phí dã ngoại của chúng ta:

SELECT category, SUM(quantity * price) AS total_cost
FROM picnic_items
GROUP BY category WITH ROLLUP;

Kết quả có thể trông như thế này:

+----------+------------+
| category | total_cost |
+----------+------------+
| Drink    |     159.60 |
| Food     |     119.80 |
| Snack    |     125.00 |
| NULL     |     404.40 |
+----------+------------+

Trong kết quả này, chúng ta thấy tổng chi phí cho mỗi nhóm, và hàng cuối cùng (với giá trị NULL trong cột category) cho chúng ta tổng cộng cho tất cả các nhóm kết hợp.

ROLLUP on Multiple Columns: Diving Deeper

Bây giờ我们已经 có základ, hãy nâng cấp nó lên một chút. ROLLUP có thể hoạt động trên nhiều cột, tạo ra một hierachy của các tổng tạm. Nó giống như tổ chức các vật phẩm dã ngoại của bạn theo nhóm, sau đó theo từng vật phẩm cụ thể, và nhận được tổng ở mỗi mức.

Hãy thay đổi truy vấn của chúng ta để nhóm theo cả category và item_name:

SELECT category, item_name, SUM(quantity * price) AS total_cost
FROM picnic_items
GROUP BY category, item_name WITH ROLLUP;

Truy vấn này có thể tạo ra kết quả như sau:

+----------+------------+------------+
| category | item_name  | total_cost |
+----------+------------+------------+
| Drink    | Soda       |      59.70 |
| Drink    | Water      |      39.60 |
| Drink    | NULL       |      99.30 |
| Food     | Sandwich   |     119.80 |
| Food     | NULL       |     119.80 |
| Snack    | Chips      |      37.50 |
| Snack    | Cookies    |      87.50 |
| Snack    | NULL       |     125.00 |
| NULL     | NULL       |     344.10 |
+----------+------------+------------+

Đây là những gì đang xảy ra:

Chúng ta nhận được tổng cho mỗi vật phẩm cụ thể trong mỗi nhóm.
Chúng ta nhận được tổng tạm cho mỗi nhóm (nơi item_name là NULL).
Chúng ta nhận được tổng cộng ở cuối (nơi cả category và item_name đều là NULL).

Nó giống như có một bảng tính mini ngay trong kết quả truy vấn cơ sở dữ liệu của bạn!

Rollup Using Client Program: Bringing It All Together

Bây giờ, hãy áp dụng kỹ năng ROLLUP của chúng ta vào một tình huống thực tế hơn. Hãy tưởng tượng bạn đang phát triển một hệ thống quản lý tồn kho đơn giản cho một cửa hàng tạp hóa nhỏ. Bạn muốn tạo một báo cáo cho thấy doanh số theo phòng ban, sản phẩm, và sau đó cung cấp tổng cộng.

Đầu tiên, hãy tạo bảng của chúng ta và chèn một số dữ liệu mẫu:

CREATE TABLE sales (
department VARCHAR(50),
product VARCHAR(50),
quarter INT,
revenue DECIMAL(10, 2)
);

INSERT INTO sales VALUES
('Electronics', 'Laptop', 1, 1200.00),
('Electronics', 'Smartphone', 1, 800.00),
('Electronics', 'Laptop', 2, 1500.00),
('Clothing', 'T-Shirt', 1, 200.00),
('Clothing', 'Jeans', 2, 500.00),
('Grocery', 'Bread', 1, 50.00),
('Grocery', 'Milk', 1, 30.00),
('Grocery', 'Eggs', 2, 40.00);

Bây giờ, hãy tạo một báo cáo tổng quát sử dụng ROLLUP:

SELECT
IFNULL(department, 'Total') AS department,
IFNULL(product, 'Subtotal') AS product,
IFNULL(quarter, 'All Quarters') AS quarter,
SUM(revenue) AS total_revenue
FROM
sales
GROUP BY
department, product, quarter WITH ROLLUP;

Truy vấn này sẽ tạo ra một báo cáo chi tiết với tổng tạm ở mỗi mức. Chức năng IFNULL được sử dụng để thay thế các giá trị NULL bằng các nhãn có ý nghĩa hơn.

Dưới đây là những gì truy vấn này làm:

Nó nhóm dữ liệu theo phòng ban, sản phẩm và quý.
Nó tính tổng doanh thu cho mỗi sự kết hợp.
Nó sử dụng ROLLUP để tạo tổng tạm ở mỗi mức.
Nó thay thế các giá trị NULL bằng các nhãn mô tả để dễ đọc hơn.

Kết quả có thể trông như thế này:

+-------------+------------+--------------+---------------+
| department  | product    | quarter      | total_revenue |
+-------------+------------+--------------+---------------+
| Clothing    | Jeans      | 2            |        500.00 |
| Clothing    | Jeans      | All Quarters |        500.00 |
| Clothing    | T-Shirt    | 1            |        200.00 |
| Clothing    | T-Shirt    | All Quarters |        200.00 |
| Clothing    | Subtotal   | All Quarters |        700.00 |
| Electronics | Laptop     | 1            |       1200.00 |
| Electronics | Laptop     | 2            |       1500.00 |
| Electronics | Laptop     | All Quarters |       2700.00 |
| Electronics | Smartphone | 1            |        800.00 |
| Electronics | Smartphone | All Quarters |        800.00 |
| Electronics | Subtotal   | All Quarters |       3500.00 |
| Grocery     | Bread      | 1            |         50.00 |
| Grocery     | Bread      | All Quarters |         50.00 |
| Grocery     | Eggs       | 2            |         40.00 |
| Grocery     | Eggs       | All Quarters |         40.00 |
| Grocery     | Milk       | 1            |         30.00 |
| Grocery     | Milk       | All Quarters |         30.00 |
| Grocery     | Subtotal   | All Quarters |        120.00 |
| Total       | Subtotal   | All Quarters |       4320.00 |
+-------------+------------+--------------+---------------+

Kết quả này cho chúng ta một cái nhìn tổng quát về dữ liệu doanh số của chúng ta, với tổng tạm cho mỗi sản phẩm, mỗi phòng ban, và một tổng cộng ở cuối.

Useful ROLLUP Methods

Dưới đây là bảng tóm tắt một số phương pháp hữu ích khi làm việc với ROLLUP:

Method	Description
WITH ROLLUP	Sử dụng với GROUP BY để tạo tổng tạm và tổng cộng
IFNULL()	Thay thế các giá trị NULL bằng giá trị chỉ định
GROUPING()	Trả về 1 cho các giá trị NULL được tạo ra bởi ROLLUP, 0 nếu không
COALESCE()	Trả về giá trị đầu tiên không phải NULL trong danh sách

Nhớ rằng, thực hành là cách tốt nhất để trở thành thạo! Hãy thử tạo bảng của riêng bạn và thử nghiệm với các truy vấn ROLLUP. Nó là một công cụ mạnh mẽ có thể cung cấp những thông tin quý giá về dữ liệu của bạn.

Và thế là bạn đã nâng cao kỹ năng MySQL của mình với clause ROLLUP. Từ việc tóm tắt đơn giản đến các báo cáo hierachy phức tạp, ROLLUP có thể giúp bạn. Hãy tiếp tục khám phá, tiếp tục lập trình, và quan trọng nhất, hãy tiếp tục vui vẻ với dữ liệu!

Credits: Image by storyset