MySQL - ROLLUP：掌握數據匯總

你好，有志於MySQL的愛好者！今天，我們將踏上一段令人興奮的旅程，進入數據匯總的世界，使用ROLLUP子句。作為你友善的鄰居計算機老師，我將指導你逐步學習這個主題。別擔心你對編程是新手——我們會從基礎開始，逐步提升。所以，來一杯咖啡（或者如果你喜歡，來一杯茶），我們一起來深入探討吧！

MySQL - ROLLUP

MySQL的ROLLUP子句：數據分析的新好朋友

ROLLUP是什麼？

想像你正在計劃一個大型家庭聚會野餐。你有一份食物清單，包括它們的數量和價格。現在，如果你能快速看到每個食物類別的總成本，以及所有食物的總成本，那不是很好嗎？這正是MySQL中的ROLLUP所做的——它幫助你在不同數據層次上創建摘要行。

ROLLUP是GROUP BY子句的擴展。它允許你在一個查詢中生成多個層次的小計和總計。很棒吧？

基本語法

以下是使用ROLLUP的查詢的基本結構：

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

讓我們用一個簡單的例子來解析這個結構。想像我們有一個名為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);

現在，讓我們使用ROLLUP來獲取我們野餐開支的摘要：

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

結果可能如下所示：

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

在這個結果中，我們看到了每個類別的總成本，以及最後一行（類別列中為NULL）給我們所有類別的總計。那個NULL是ROLLUP表示“這是所有上面的總和”的方式。

在多個列上使用ROLLUP：深入探討

現在我們已經掌握了基礎，讓我們升級一個層次。ROLLUP可以在多個列上施展其魔法，創建子總計的層次結構。這就像按類別組織你的野餐物品，然後按特定物品組織，並在每個層次獲得總計。

讓我們修改我們的查詢，同時按類別和物品名稱進行分組：

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

這個查詢可能會產生如下結果：

+----------+------------+------------+
| 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 |
+----------+------------+------------+

這裡發生了什麼：

我們得到了每個類別中每個特定物品的總計。
我們得到了每個類別的子總計（當物品名稱為NULL時）。
我們在最後得到了總計（當類別和物品名稱都為NULL時）。

這就像在你的數據庫查詢結果中有一個迷你電子表格！

在客戶端程序中使用ROLLUP：整合所有知識

現在，讓我們在更真實的場景中運用我們的ROLLUP技巧。想像你正在為一家小型雜貨店開發一個簡單的庫存管理系統。你想要創建一個報告，顯示按部門、產品分類的銷售情況，然後給出總計。

首先，讓我們創建一個表並插入一些樣本數據：

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);

現在，讓我們使用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;

這個查詢將產生一個詳細報告，每個層次都有子總計。IFNULL函數用於將NULL值替換為更有意義的標籤。

這個查詢的工作原理如下：

它按部門、產品和季度對數據進行分組。
它計算每個組合的收入總計。
它使用ROLLUP創建每個層次的子總計。
它將NULL值替換為描述性標籤，以提高可讀性。

結果可能如下所示：

+-------------+------------+--------------+---------------+
| 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 |
+-------------+------------+--------------+---------------+

這個結果給我們提供了對我們銷售數據的全面觀察，每個產品、每個部門都有子總計，最後有總計。

有用的ROLLUP方法

以下是一張總結了一些有用的ROLLUP方法的表格：

方法	描述
WITH ROLLUP	與GROUP BY一起使用，生成小計和總計
IFNULL()	將NULL值替換為指定的值
GROUPING()	返回1對於由ROLLUP產生的NULL值，否則返回0
COALESCE()	返回列表中第一個非NULL值

記住，熟練才能精通！嘗試創建你自己的表並實驗ROLLUP查詢。這是一個強大的工具，可以為你的數據提供寶貴的見解。

就这样，夥伴們！你剛剛通過ROLLUP子句提升了你的MySQL技能。從簡單的摘要到複雜的層次報告，ROLLUP都能應對。繼續探索，繼續編程，最重要的是，繼續享受數據的樂趣！

Credits: Image by storyset