胖头鱼的技术专栏-476 26ai新特性实战：聚合过滤（20261012）

作者：胖头鱼的鱼缸（尹海文）
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 引入了符合 SQL 标准的 FILTER 子句，允许在聚合函数内部直接应用过滤条件，而不是在 WHERE 子句中进行全局过滤。

FILTER 子句使分析查询更清晰、更快速、更可靠。它让开发者能够在一次查询中同时计算带不同过滤条件的多个聚合值，而无需编写多个子查询或复杂的 CASE WHEN 表达式。

语法

aggregate_function FILTER (WHERE condition)

与传统方式的对比：

• 传统方式：
  SUM(CASE WHEN condition THEN value ELSE 0 END)
• FILTER 子句方式：
  SUM(value) FILTER (WHERE condition)

核心优势

• 语法简洁：比 CASE WHEN 更直观，代码可读性更好
• 性能优化：数据库优化器可更好地理解和优化 FILTER 子句
• 组合计算：一次查询中同时计算多个带不同条件的聚合值
• SQL 标准兼容：提升 SQL 代码的可移植性

使用场景

• 多维分析：一次查询中按不同条件统计，如计算总数、大额订单数、各地区汇总
• KPI 报表：统计达标率、不合格率等比例指标
• 漏斗分析：按阶段统计用户数量（访问、注册、下单、支付）
• 财务分析：统计不同金额区间的交易笔数和总额
• A/B 测试：对比实验组和对照组的平均值

实战演示

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

创建测试表并插入测试数据

CREATE TABLE filter_sales (
  product   VARCHAR2(30),
  region    VARCHAR2(30),
  amount    NUMBER,
  sale_date DATE);
  
INSERT INTO filter_sales VALUES ('Widget', 'East', 100, DATE '2026-01-15');
INSERT INTO filter_sales VALUES ('Widget', 'West', 150, DATE '2026-01-20');
INSERT INTO filter_sales VALUES ('Gadget', 'East', 200, DATE '2026-02-10');
INSERT INTO filter_sales VALUES ('Gadget', 'West', 120, DATE '2026-02-15');
INSERT INTO filter_sales VALUES ('Widget', 'East',  80, DATE '2026-03-01');
INSERT INTO filter_sales VALUES ('Gadget', 'West', 300, DATE '2026-03-05');
INSERT INTO filter_sales VALUES ('Widget', 'East', 250, DATE '2026-04-10');
INSERT INTO filter_sales VALUES ('Gadget', 'East', 180, DATE '2026-04-20');
COMMIT;

测试1：COUNT 过滤子句

SELECT product,
  COUNT(*)                                   AS total_orders,
  COUNT(*) FILTER (WHERE amount > 150)       AS large_orders,
  COUNT(*) FILTER (WHERE region = 'East')    AS east_orders
FROM filter_sales
GROUP BY product
ORDER BY product;

测试2：SUM 过滤子句

SELECT product,
  SUM(amount)                                   AS total_sales,
  SUM(amount) FILTER (WHERE region = 'East')    AS east_sales,
  SUM(amount) FILTER (WHERE amount > 150)       AS large_sales
FROM filter_sales
GROUP BY product
ORDER BY product;

测试3：AVG 过滤子句

SELECT product,
  ROUND(AVG(amount), 2)                             AS avg_all,
  ROUND(AVG(amount) FILTER (WHERE region = 'East'), 2)  AS avg_east,
  ROUND(AVG(amount) FILTER (WHERE region = 'West'), 2)  AS avg_west
FROM filter_sales
GROUP BY product
ORDER BY product;

测试4：多重条件组合过滤子句

SELECT product,
  COUNT(*) FILTER (WHERE amount > 150 AND region = 'West') AS large_west,
  SUM(amount) FILTER (WHERE amount > 150 AND region = 'West') AS large_west_total
FROM filter_sales
GROUP BY product
ORDER BY product;

测试5：分析窗口函数过滤子句

SELECT product, sale_date, amount,
  COUNT(*) OVER (PARTITION BY product ORDER BY sale_date
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  COUNT(*) FILTER (WHERE amount > 150)
    OVER (PARTITION BY product ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_large
FROM filter_sales
ORDER BY product, sale_date;

总结

本期对 Oracle AI Database 26ai，23.26.1 版本新引入的聚合过滤进行了完整介绍与实战演示。

老规矩，知道写了些啥。