FILTER 子句
FILTER (WHERE condition)
FILTER (WHERE condition)
是聚合函数的可选子句，用于在聚合计算前过滤行，等价于在聚合内部加条件，比用
CASE WHEN
CASE WHEN
更简洁。
语法
aggregate_function(expr) FILTER (WHERE condition)
适用于所有聚合函数：
SUM
SUM
、
COUNT
COUNT
、
AVG
AVG
、
MAX
MAX
、
MIN
MIN
、
GROUP_CONCAT
GROUP_CONCAT
等。
与 CASE WHEN 的对比
-- 传统写法：CASE WHEN
SELECT
SUM(CASE WHEN status = 'paid' THEN amount END) AS paid_total,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_count
-- FILTER 写法：更简洁
SELECT
SUM(amount) FILTER (WHERE status = 'paid') AS paid_total,
COUNT(*) FILTER (WHERE status = 'paid') AS paid_count
FROM orders;
使用示例
-- 准备数据
CREATE TABLE doc_orders (id INT, amount INT, status STRING, region STRING);
INSERT INTO doc_orders VALUES
(1, 100, 'paid', 'north'),
(2, 200, 'pending', 'north'),
(3, 150, 'paid', 'south'),
(4, 300, 'paid', 'south'),
(5, 80, 'pending', 'north');
-- 同时统计不同条件下的聚合值
SELECT
SUM(amount) FILTER (WHERE status = 'paid') AS paid_total,
SUM(amount) FILTER (WHERE status = 'pending') AS pending_total,
COUNT(*) FILTER (WHERE status = 'paid') AS paid_count,
AVG(amount) FILTER (WHERE region = 'north') AS north_avg,
MAX(amount) FILTER (WHERE status = 'paid' AND amount > 100) AS max_large_paid
FROM doc_orders;
| paid_total | pending_total | paid_count | north_avg | max_large_paid |
|---|
| 550 | 280 | 3 | 126 | 300 |
与窗口函数结合
FILTER
FILTER
也可以与窗口函数结合使用：
SELECT
id,
amount,
SUM(amount) FILTER (WHERE status = 'paid') OVER (PARTITION BY region) AS region_paid_sum
FROM doc_orders;
注意事项
相关文档
