SQL Academy · 课时

使用 CUBE 处理所有组合

Q: 「使用 CUBE 处理所有组合」这节课中我会学到什么？

一次性处理每种分组组合 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

一次性处理每种分组组合

使用 CUBE 处理所有组合 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是 CUBE？

SQL 中的 CUBE 扩展会根据列列表生成所有可能的分组组合。ROLLUP 创建层级，而 CUBE 生成小计的完整笛卡尔积，其中包括总计。

您可以把它理解为回答这样的问题：“请给出可以根据这些列计算出的所有小计。”

设置数据表

我们将使用一个 sales 表，按年份、区域和产品类别记录收入。这类多维数据正是 CUBE 的优势所在。

CREATE TABLE sales (
  year     INT,
  region   VARCHAR(20),
  category VARCHAR(20),
  revenue  NUMERIC(10,2)
);

INSERT INTO sales VALUES
  (2023, 'North', 'Electronics', 12000),
  (2023, 'North', 'Clothing',     8000),
  (2023, 'South', 'Electronics',  9500),
  (2023, 'South', 'Clothing',     6000),
  (2024, 'North', 'Electronics', 14000),
  (2024, 'North', 'Clothing',     9500),
  (2024, 'South', 'Electronics', 11000),
  (2024, 'South', 'Clothing',     7200);

您的第一个 CUBE 查询

语法很简单：将 GROUP BY 替换为 GROUP BY CUBE(...)，并在括号内列出各列。SQL 会将这些列的所有组合作为分组集合生成。

SELECT
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

CUBE 会生成多少个分组？

对于 n 列，CUBE 会生成 2ⁿ 个分组集合——列出列的每个子集各生成一个集合，其中包括空集合（总计）。

对于 3 列（年份、区域、类别），即 2³ = 8 个分组集合：每一列单独分组、每两列组合、三列一起分组，以及完全不分组。

-- 3 columns => 8 grouping sets:
-- (year, region, category)
-- (year, region)
-- (year, category)
-- (region, category)
-- (year)
-- (region)
-- (category)
-- () <- grand total
SELECT COUNT(*) AS row_count
FROM (
  SELECT year, region, category, SUM(revenue)
  FROM sales
  GROUP BY CUBE(year, region, category)
) sub;

NULL 标记汇总的维度

与 ROLLUP 一样，CUBE 使用 NULL 表示某列已进行汇总。如果结果行中的 region 为 NULL，则表示该行汇总了在其余列组合下的所有区域。

请使用 GROUPING(col) 区分真正的 NULL 值和聚合标记。

SELECT
  GROUPING(year)     AS g_year,
  GROUPING(region)   AS g_region,
  GROUPING(category) AS g_category,
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY g_year, g_region, g_category;

使用 COALESCE 让 NULL 更易读

为了让报表中的输出更易读，请将每个分组列包裹在 COALESCE 中，用描述性标签（如 'ALL'）替换聚合产生的 NULL。

SELECT
  COALESCE(CAST(year AS VARCHAR), 'ALL YEARS')     AS year,
  COALESCE(region,   'ALL REGIONS')                AS region,
  COALESCE(category, 'ALL CATEGORIES')             AS category,
  SUM(revenue)                                     AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

CUBE 与 ROLLUP——关键区别

ROLLUP(a, b, c) 只会沿一条层级路径创建小计：(a,b,c)、(a,b)、(a)、()。它遵循从左到右的顺序。

CUBE(a, b, c) 会创建每一个子集，包括 (a,c) 或仅有 (b) 这样的横向组合，而这些组合会被 ROLLUP 完全跳过。当您需要完整的多维分析而没有预先确定的层级时，请使用 CUBE。

-- ROLLUP: 4 grouping sets
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, region);

-- CUBE: 4 grouping sets for 2 columns (same count here)
-- but adds the (region) subtotal that ROLLUP omits
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY CUBE(year, region);

部分 CUBE

您可以将常规 GROUP BY 列与 CUBE 子列表混用。列在 CUBE(...) 外部列出时，会始终存在于每个分组集合中，只有其中的列会进行完整的组合处理。

-- year is fixed; CUBE only over region and category
SELECT
  year,
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY year, CUBE(region, category)
ORDER BY year, region, category;

使用 HAVING 筛选 CUBE 结果

HAVING 对 CUBE 输出结果的作用与普通 GROUP BY 完全相同。您可以筛除聚合值未达到阈值的分组行——例如，只保留总收入超过最低值的行。

SELECT
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(region, category)
HAVING SUM(revenue) > 15000
ORDER BY total_revenue DESC;

将 CUBE 与窗口函数结合

您可以将 CUBE 查询封装在 CTE 中，然后应用窗口函数，对结果中的行进行排名或比较。这是构建高管级仪表板的强大模式。

WITH cube_result AS (
  SELECT
    COALESCE(region,   'ALL') AS region,
    COALESCE(category, 'ALL') AS category,
    SUM(revenue) AS total_revenue
  FROM sales
  GROUP BY CUBE(region, category)
)
SELECT
  region,
  category,
  total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM cube_result
ORDER BY revenue_rank;

何时选择 CUBE

CUBE 适用于以下情况：

您需要针对临时分析或 OLAP 风格的报表，获取每一种小计组合。
您的分组列之间不存在自然的层级关系。
您希望让分析人员按他们选择的任意方式切分数据。

列数较多时应避免使用 CUBE——4 列就会生成 16 个分组集，5 列则生成 32 个。优先使用 ROLLUP 或显式的 GROUPING SETS，以便控制结果集的规模。

快速检查

GROUP BY CUBE(a, b, c, d) 会生成多少个不同的分组集？

课程回顾

在本课中，您学习了 CUBE 如何根据列列表生成所有可能的分组集组合，因此它非常适合多维报表。

要点：

GROUP BY CUBE(a, b, c) 会生成 2ⁿ 个分组集。
结果列中的 NULL 表示该维度已进行聚合；您可以使用 GROUPING() 对其进行识别。
COALESCE 会将聚合产生的 NULL 值转换为有意义的标签。
部分 CUBE（例如 GROUP BY year, CUBE(region, category)）会固定某些列，只对其余列执行立方组合。
完整的临时分析应使用 CUBE；当层级关系已知或列数较多时，优先使用 ROLLUP 或显式的 GROUPING SETS。

常见问题解答

「使用 CUBE 处理所有组合」课时是免费的吗？

是的 — 「使用 CUBE 处理所有组合」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「使用 CUBE 处理所有组合」这节课中我会学到什么？

一次性处理每种分组组合你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「使用 CUBE 处理所有组合」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy