MySQL 8.0 `ROLLUP` 与 `CUBE`：多维度聚合报表 - 智猿学院-IT界的百科全书

MySQL 8.0 `ROLLUP` 与 `CUBE`：多维度聚合报表，让数据在你手里跳舞💃🕺

大家好！我是你们的老朋友，人称“数据魔法师”的阿星。今天，我们要一起探索MySQL 8.0中两个非常强大的聚合函数：ROLLUP 和 CUBE。它们就像是数据报表界的“变形金刚”，能把你的数据从单调的表格，变成丰富多彩、多维度分析的宝藏！

开场白：告别沉闷，让数据动起来！

想想我们平时看的报表，是不是经常觉得缺少点什么？比如，你想知道不同地区、不同产品的总销售额，但报表只能告诉你每个地区的销售额，或者每个产品的销售额。你需要自己手动加加减减，才能得到想要的结果，简直是累觉不爱！

别担心，ROLLUP 和 CUBE 就是来拯救你的！它们可以自动帮你进行多维度的聚合计算，让你一眼就能看到数据的全貌，挖掘出隐藏在数据背后的秘密。它们就像两位技艺精湛的舞者，让你的数据在各种维度之间优雅地穿梭，跳出最美的华尔兹。

第一幕：ROLLUP – 从总览到细节，逐层深入的“金字塔”

ROLLUP，顾名思义，就是“向上汇总”。它会根据你指定的维度，逐层向上进行聚合，就像建造一座金字塔，从最细的颗粒度，逐步汇总到最顶端的总览。

想象一下： 你是一家电商平台的老板，想了解一下你家店铺的销售情况。你就可以用 ROLLUP 来创建一个报表，先显示每个地区的销售额，再显示所有地区的总销售额。

语法结构：

SELECT
    dimension1,
    dimension2,
    ...,
    aggregate_function(column)
FROM
    table_name
GROUP BY
    ROLLUP(dimension1, dimension2, ...);

重点： ROLLUP 会按照你指定的维度顺序进行汇总，从右向左，逐层减少维度。

举个栗子🌰：

假设我们有一张 sales 表，记录了不同地区（region）和不同产品类别（category）的销售额（amount）。

CREATE TABLE sales (
    region VARCHAR(20),
    category VARCHAR(20),
    amount DECIMAL(10, 2)
);

INSERT INTO sales (region, category, amount) VALUES
('North', 'Electronics', 1000.00),
('North', 'Clothing', 500.00),
('South', 'Electronics', 800.00),
('South', 'Clothing', 600.00),
('East', 'Electronics', 1200.00),
('East', 'Clothing', 700.00),
('West', 'Electronics', 900.00),
('West', 'Clothing', 400.00);

现在，我们想用 ROLLUP 来生成一个报表，显示每个地区、每个产品类别的销售额，以及总销售额。

SELECT
    region,
    category,
    SUM(amount) AS total_amount
FROM
    sales
GROUP BY
    ROLLUP(region, category);

结果：

region	category	total_amount
East	Clothing	700.00
East	Electronics	1200.00
East	NULL	1900.00	— 东部地区总销售额
North	Clothing	500.00
North	Electronics	1000.00
North	NULL	1500.00	— 北部地区总销售额
South	Clothing	600.00
South	Electronics	800.00
South	NULL	1400.00	— 南部地区总销售额
West	Clothing	400.00
West	Electronics	900.00
West	NULL	1300.00	— 西部地区总销售额
NULL	NULL	6100.00	— 所有地区总销售额

解读：

NULL 值表示该维度的总计。
ROLLUP(region, category) 先按照 region 和 category 进行分组，然后按照 region 进行汇总，最后计算总计。

小贴士： 你可以用 COALESCE 函数来把 NULL 值替换成更有意义的文本，比如 "Total"。

SELECT
    COALESCE(region, 'Total') AS region,
    COALESCE(category, 'Total') AS category,
    SUM(amount) AS total_amount
FROM
    sales
GROUP BY
    ROLLUP(region, category);

第二幕：CUBE – 全方位透视，360°无死角的“魔方”

CUBE 比 ROLLUP 更强大，它会计算所有可能的维度组合的聚合结果，就像一个魔方，你可以从任何角度观察数据。

想象一下： 你是一家电影院的经理，想了解一下不同电影类型（genre）和不同放映时间段（time_slot）的票房收入。CUBE 可以帮你生成一个报表，显示每个电影类型、每个放映时间段的票房收入，以及所有可能组合的票房收入，比如：

动作片在上午场的票房收入
喜剧片在下午场的票房收入
所有电影类型在所有放映时间段的总票房收入

语法结构：

SELECT
    dimension1,
    dimension2,
    ...,
    aggregate_function(column)
FROM
    table_name
GROUP BY
    CUBE(dimension1, dimension2, ...);

重点： CUBE 会计算所有维度组合的聚合结果，包括单个维度、多个维度和所有维度的总计。

举个栗子🌰：

假设我们有一张 movie_tickets 表，记录了不同电影类型（genre）和不同放映时间段（time_slot）的售票数量（tickets_sold）。

CREATE TABLE movie_tickets (
    genre VARCHAR(20),
    time_slot VARCHAR(20),
    tickets_sold INT
);

INSERT INTO movie_tickets (genre, time_slot, tickets_sold) VALUES
('Action', 'Morning', 100),
('Action', 'Afternoon', 150),
('Comedy', 'Morning', 80),
('Comedy', 'Afternoon', 120),
('Drama', 'Morning', 60),
('Drama', 'Afternoon', 90);

现在，我们想用 CUBE 来生成一个报表，显示每个电影类型、每个放映时间段的售票数量，以及所有可能组合的售票数量。

SELECT
    genre,
    time_slot,
    SUM(tickets_sold) AS total_tickets
FROM
    movie_tickets
GROUP BY
    CUBE(genre, time_slot);

结果：

genre	time_slot	total_tickets
Action	Afternoon	150
Action	Morning	100
Action	NULL	250	— 动作片总售票数量
Comedy	Afternoon	120
Comedy	Morning	80
Comedy	NULL	200	— 喜剧片总售票数量
Drama	Afternoon	90
Drama	Morning	60
Drama	NULL	150	— 剧情片总售票数量
NULL	Afternoon	360	— 下午场总售票数量
NULL	Morning	240	— 上午场总售票数量
NULL	NULL	600	— 总售票数量

解读：

NULL 值表示该维度的总计。
CUBE(genre, time_slot) 会计算以下组合的聚合结果：
- genre 和 time_slot
- genre
- time_slot
- 所有维度

小贴士： 当维度数量较多时，CUBE 会生成大量的聚合结果，可能会影响查询性能。因此，在使用 CUBE 时，要慎重考虑维度数量。

第三幕：GROUPING 函数 – 区分真实值和汇总值，让报表更清晰

在 ROLLUP 和 CUBE 的结果中，NULL 值表示汇总值，但有时 NULL 值也可能是数据本身的真实值。为了区分这两种情况，我们可以使用 GROUPING 函数。

想象一下： 你的公司有一些客户没有提供地区信息，所以在 sales 表中，他们的 region 字段是 NULL。如果你直接使用 ROLLUP，就无法区分这些客户的销售额，和所有地区的总销售额。

GROUPING 函数可以告诉你，某个维度是否是因为汇总而产生的 NULL 值。

语法结构：

GROUPING(dimension)

如果 dimension 是因为汇总而产生的 NULL 值，GROUPING(dimension) 返回 1。
如果 dimension 是数据本身的真实值，GROUPING(dimension) 返回 0。

举个栗子🌰：

我们修改一下之前的 sales 表，加入一些 region 为 NULL 的数据。

INSERT INTO sales (region, category, amount) VALUES
(NULL, 'Electronics', 500.00),
(NULL, 'Clothing', 300.00);

现在，我们使用 GROUPING 函数来区分真实的 NULL 值和汇总的 NULL 值。

SELECT
    COALESCE(region, 'Total') AS region,
    COALESCE(category, 'Total') AS category,
    SUM(amount) AS total_amount,
    GROUPING(region) AS region_grouping,
    GROUPING(category) AS category_grouping
FROM
    sales
GROUP BY
    ROLLUP(region, category);

结果（部分）：

region	category	total_amount	region_grouping	category_grouping
NULL	Clothing	300.00	0	0	— 真实的 NULL 值
NULL	Electronics	500.00	0	0	— 真实的 NULL 值
East	Clothing	700.00	0	0
East	Electronics	1200.00	0	0
East	NULL	1900.00	0	1	— 汇总的 NULL 值
North	Clothing	500.00	0	0
…	…	…	…	…
NULL	NULL	6900.00	1	1	— 汇总的 NULL 值

解读：

region_grouping = 0 表示 region 是数据本身的真实值。
region_grouping = 1 表示 region 是因为汇总而产生的 NULL 值。

小贴士： 你可以使用 CASE 表达式，根据 GROUPING 函数的结果，来显示不同的文本。

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN 'Total'
        ELSE COALESCE(region, 'Unknown Region')
    END AS region,
    CASE
        WHEN GROUPING(category) = 1 THEN 'Total'
        ELSE COALESCE(category, 'Unknown Category')
    END AS category,
    SUM(amount) AS total_amount
FROM
    sales
GROUP BY
    ROLLUP(region, category);

第四幕：GROUPING SETS – 灵活组合，随心所欲的“乐高积木”

GROUPING SETS 允许你指定多个分组集合，然后计算每个集合的聚合结果。它就像乐高积木，你可以自由组合不同的维度，生成你想要的报表。

想象一下： 你是一家广告公司的分析师，想了解不同广告渠道（channel）和不同广告活动（campaign）的点击量。你可能需要以下几种报表：

每个广告渠道的总点击量
每个广告活动的总点击量
所有广告渠道和广告活动的总点击量

使用 GROUPING SETS，你可以用一个查询语句生成所有这些报表。

语法结构：

SELECT
    dimension1,
    dimension2,
    ...,
    aggregate_function(column)
FROM
    table_name
GROUP BY
    GROUPING SETS ((dimension1, dimension2), (dimension1), (dimension2), ());

重点： GROUPING SETS 允许你指定多个分组集合，每个集合用括号括起来。空括号 () 表示计算总计。

举个栗子🌰：

假设我们有一张 ad_clicks 表，记录了不同广告渠道（channel）和不同广告活动（campaign）的点击量（clicks）。

CREATE TABLE ad_clicks (
    channel VARCHAR(20),
    campaign VARCHAR(20),
    clicks INT
);

INSERT INTO ad_clicks (channel, campaign, clicks) VALUES
('Facebook', 'Summer Sale', 1000),
('Facebook', 'Back to School', 800),
('Google', 'Summer Sale', 1200),
('Google', 'Back to School', 900),
('Twitter', 'Summer Sale', 700),
('Twitter', 'Back to School', 600);

现在，我们使用 GROUPING SETS 来生成一个报表，显示每个广告渠道、每个广告活动，以及所有可能组合的点击量。

SELECT
    channel,
    campaign,
    SUM(clicks) AS total_clicks
FROM
    ad_clicks
GROUP BY
    GROUPING SETS ((channel, campaign), (channel), (campaign), ());

结果：

channel	campaign	total_clicks
Facebook	Back to School	800
Facebook	Summer Sale	1000
Google	Back to School	900
Google	Summer Sale	1200
Twitter	Back to School	600
Twitter	Summer Sale	700
Facebook	NULL	1800	— Facebook 总点击量
Google	NULL	2100	— Google 总点击量
Twitter	NULL	1300	— Twitter 总点击量
NULL	Back to School	2300	— Back to School 总点击量
NULL	Summer Sale	2900	— Summer Sale 总点击量
NULL	NULL	5200	— 总点击量

解读：

GROUPING SETS ((channel, campaign), (channel), (campaign), ()) 会计算以下组合的聚合结果：
- channel 和 campaign
- channel
- campaign
- 所有维度

小贴士： GROUPING SETS 可以让你灵活地控制聚合的维度，生成你想要的报表。

第五幕：案例分析 – 电商销售数据多维分析

让我们用一个更复杂的案例来演示 ROLLUP 和 CUBE 的强大功能。

假设我们有一个电商平台的销售数据，包含以下字段：

order_id：订单ID
customer_id：客户ID
product_category：产品类别
region：地区
order_date：订单日期
amount：订单金额

我们想分析不同维度的销售情况，比如：

每个地区、每个产品类别的销售额
每个月、每个地区的销售额
每个客户的平均订单金额

我们可以使用 ROLLUP 和 CUBE 来生成各种报表，帮助我们了解销售趋势，优化营销策略。

示例SQL（部分）：

-- 使用 ROLLUP 分析每个地区、每个产品类别的销售额
SELECT
    region,
    product_category,
    SUM(amount) AS total_amount
FROM
    orders
GROUP BY
    ROLLUP(region, product_category);

-- 使用 CUBE 分析每个月、每个地区的销售额
SELECT
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    region,
    SUM(amount) AS total_amount
FROM
    orders
GROUP BY
    CUBE(YEAR(order_date), MONTH(order_date), region);

-- 使用 GROUPING SETS 分析每个客户的平均订单金额
SELECT
    customer_id,
    AVG(amount) AS avg_order_amount
FROM
    orders
GROUP BY
    GROUPING SETS ((customer_id), ());

总结：让数据报表不再枯燥！

ROLLUP 和 CUBE 是 MySQL 8.0 中强大的聚合函数，可以帮助你进行多维度的数据分析，生成丰富多彩的报表。它们就像两把锋利的宝剑，让你在数据的海洋中披荆斩棘，挖掘出隐藏的商业价值。

希望今天的讲解能帮助你更好地理解和使用 ROLLUP 和 CUBE，让你的数据报表不再枯燥，让你的数据分析更加高效！记住，数据不是冰冷的数字，它是一个个鲜活的故事，等待你去发现。

好了，今天的分享就到这里。感谢大家的聆听！下次再见！👋

MySQL 8.0 ROLLUP 与 CUBE：多维度聚合报表，让数据在你手里跳舞💃🕺

发表回复 取消回复

MySQL 8.0 `ROLLUP` 与 `CUBE`：多维度聚合报表，让数据在你手里跳舞💃🕺

发表回复取消回复