胖头鱼的技术专栏-454 26ai新特性实战：GROUP BY ALL 子句（20260801）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database
PostgreSQL ACE
10年+数据库行业经验
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP，ITPUB认证专家
圈内拥有“总监”称号，非著 名社恐（社交恐怖分子）
全网同名：胖头鱼的鱼缸
ITPUB：yhw1809
除授权转载并标明出处外，均为“非法”抄袭

特性介绍

Oracle AI Database 23.26.1 引入了 GROUP BY ALL 子句，用于简化聚合查询的编写。GROUP BY ALL 会自动将 SELECT 列表中所有非聚合列作为分组依据，开发者无需手动列出每个分组列。

语法介绍

SELECT col1, col2, ..., AGG(colN), ...
FROM table_name
GROUP BY ALL;

行为细节：

• GROUP BY ALL 自动识别 SELECT 列表中所有不是聚合函数参数的非聚合列
• 它将所有非聚合列添加到 GROUP BY 子句中
• 等价于手写 GROUP BY col1, col2, col3, …
• 聚合列（如 SUM(amount)、COUNT(*)）自动排除在分组之外
• 表达式列（如 CASE WHEN、计算列）也会被自动分组
• 支持与 HAVING、ORDER BY 等其他子句组合使用

对比与优势

与传统 GROUP BY 对比：

• 传统写法：

SELECT product, region, SUM(amount)
FROM sales
GROUP BY product, region;

• GROUP BY ALL 写法：

SELECT product, region, SUM(amount)
FROM sales
GROUP BY ALL;

优势：

• 减少代码量，避免遗漏分组列
• 当修改 SELECT 列表时，GROUP BY 自动适应，减少维护负担
• 提高可读性，特别适合具有多列分组的复杂查询
• 避免因手动列举不匹配导致 ORA-00979 错误

注意事项

• 如果 SELECT 列表中只有聚合函数且无其他列，GROUP BY ALL 将所有行聚合为单行结果
• 如果 SELECT 列表中包含常量表达式，它们不会影响分组行为
• 窗口函数（如 RANK、ROW_NUMBER）的参数列会被视为非聚合列

适用场景

• 快速聚合查询：在临时分析中快速编写分组聚合，无需维护列列表
• 报表开发：SELECT 列频繁变动的分析查询场景
• 数据探索：在 SQL*Plus 或 SQLcl 中进行交互式数据探索
• 代码简洁化：减少复制粘贴错误，特别是在列数较多的表中
• 原型开发：快速验证聚合逻辑，稍后再精细化为明确的 GROUP BY

实战演示

同之前一样，使用用户NFTEST，已授权DB_DEVELOPER_ROLE角色，测试操作均在NFTEST用户下执行。

创建测试表并添加测试数据

CREATE TABLE groupby_sales (
  sale_id   NUMBER PRIMARY KEY,
  product   VARCHAR2(30),
  region    VARCHAR2(30),  category  VARCHAR2(20),
  amount    NUMBER,
  sale_date DATE);
  
INSERT INTO groupby_sales VALUES (1, 'Widget', 'East',  'Electronics', 100, DATE '2026-01-15');
INSERT INTO groupby_sales VALUES (2, 'Widget', 'West',  'Electronics', 150, DATE '2026-01-20');
INSERT INTO groupby_sales VALUES (3, 'Gadget', 'East',  'Electronics', 200, DATE '2026-02-10');
INSERT INTO groupby_sales VALUES (4, 'Gadget', 'West',  'Electronics', 120, DATE '2026-02-15');
INSERT INTO groupby_sales VALUES (5, 'Widget', 'East',  'Electronics',  80, DATE '2026-03-01');
INSERT INTO groupby_sales VALUES (6, 'Gadget', 'West',  'Electronics', 300, DATE '2026-03-05');
INSERT INTO groupby_sales VALUES (7, 'Tablet', 'East',  'Mobile',      250, DATE '2026-01-10');
INSERT INTO groupby_sales VALUES (8, 'Tablet', 'West',  'Mobile',      180, DATE '2026-02-20');
INSERT INTO groupby_sales VALUES (9, 'Phone',  'North', 'Mobile',      400, DATE '2026-03-10');
INSERT INTO groupby_sales VALUES (10,'Phone',  'North', 'Mobile',      350, DATE '2026-03-15');
COMMIT;

测试1：对比

SELECT product, region,
  SUM(amount) AS total_sales,
  COUNT(*) AS sale_count
FROM groupby_sales
GROUP BY product, region
ORDER BY total_sales DESC;
SELECT product, region,
  SUM(amount) AS total_sales,
  COUNT(*) AS sale_count
FROM groupby_sales
GROUP BY ALL
ORDER BY total_sales DESC;

测试2：包含多个非聚合列的GROUP BY ALL

SELECT product, region, category,
  SUM(amount) AS total_sales,
  AVG(amount) AS avg_sale,
  MIN(amount) AS min_sale,
  MAX(amount) AS max_sale
FROM groupby_sales
GROUP BY ALL
ORDER BY category, product, region;

测试3：仅使用聚合函数（无非聚合列）的GROUP BY ALL

SELECT
  COUNT(*)           AS total_sales_count,
  SUM(amount)        AS grand_total,
  AVG(amount)        AS average_sale,
  MIN(amount)        AS smallest_sale,
  MAX(amount)        AS largest_sale
FROM groupby_sales
GROUP BY ALL;

测试4：HAVING子句与GROUP BY ALL联用

SELECT product, region, SUM(amount) AS total_sales
FROM groupby_sales
GROUP BY ALLHAVING SUM(amount) > 25
0ORDER BY total_sales DESC;

测试5：使用表达式列的GROUP BY ALL

SELECT
  product,
  region,  CASE
    WHEN EXTRACT(MONTH FROM sale_date) <= 3 THEN 'Q1-2026'
    ELSE 'Q2-2026'
  END AS quarter,
  SUM(amount) AS total_sales,
  COUNT(*)    AS num_sales
FROM groupby_sales
GROUP BY ALL
ORDER BY product, region, quarter;

总结

本期对 Oracle AI Database 26ai，23.26.1 版本新引入的 GROUP BY ALL 子句进行了完整介绍与实战演示。

老规矩，知道写了些啥。