【SQL】COUNT... FILTER 的适用场景
- 一、引言
- 二、FILTER 基础学习
- 2.1 语法解析
- 2.2 适用范围
- 2.2.1 主流数据库支持情况
- 2.3 技术优势
- 2.3.1 性能优化
- 2.3.2 等阶写法对比
- 2.4 适用场景
- 2.4.1 多维统计报表
- 2.4.2 动态数据过滤
- 2.4.3 复杂条件处理
- 三、总结
一、引言
- 今天参加业务评审，见到一个不太一样的SQL如下，在数据分析与数据库查询场景中，条件计数常用 CASE WHEN 实现，但 COUNT(*) FILTER (WHERE …) 很少见，觉得比较新奇。
- 由于日常大量使用的是Hive和Mysql偏多，随着业务场景变更，逐渐使用PostgreSQL多起来，便好奇的想了解这种写法，做个学习笔记。
- 本文将探讨交流其核心原理、适用场景及技术优势，帮助开发者优化查询性能并提升代码可读性。
# 突然感觉不常见的一种写法
SELECT count(*) FILTER (where s_type='0'),"s" FROM log_detail
WHERE ds = '20250414' GROUP BY "s"
二、FILTER 基础学习
2.1 语法解析
COUNT(*) FILTER (WHERE <condition>)
- **COUNT(*)**：统计所有行数（包括 NULL 值）。
- **FILTER (WHERE )**：动态过滤参与计数的记录，仅保留满足条件的行。
- 示例如上面提到的业务案例；
2.2 适用范围
- 主要适用于【遵循 SQL:2003 标准或支持扩展聚合函数语法的数据库系统】，具体支持的 SQL 类型及场景如下：
2.2.1 主流数据库支持情况
- PostgreSQL 及兼容生态、衍生系统如 Greenplum、Amazon Redshift 等也兼容该语法。
- SparkSQL 会自动将 COUNT(DISTINCT) 转换为 FILTER 表达式以提高性能。
- 例如，多列去重统计时，Spark 会通过 Expand 操作生成 gid 标记，再结合 FILTER 分条件聚合。
- SQLite：支持 FILTER 子句，常用于分组后按条件聚合。
- 部分 OLAP 引擎：ClickHouse、Doris 等列式存储数据库支持类似语法。
对于日常不使用上述数据库标准的同学，当个参考即可。
2.3 技术优势
2.3.1 性能优化
- 减少计算冗余：传统 CASE WHEN 需要遍历所有行并生成中间结果，而 FILTER 子句通过预过滤直接跳过无关数据内存和 CPU 消耗（实测性能提升可达 20%-30%）
- 优化执行计划：数据库引擎可针对过滤条件生成更优的索引扫描路径（如 Bitmap Index Scan）。
2.3.2 等阶写法对比
| 使用 FILTER 的写法 | 传统 CASE WHEN 写法 |
|---|---|
| COUNT(*) FILTER (WHERE s_type=‘0’) | COUNT(CASE WHEN s_type=‘0’ THEN 1 END) |
| SUM(amount) FILTER (WHERE status=‘paid’) | SUM(CASE WHEN status=‘paid’ THEN amount ELSE 0 END) |
2.4 适用场景
2.4.1 多维统计报表
- 单次查询生成多维度聚合指标，例如：
SELECT product_category,COUNT(*) FILTER (WHERE region='North') AS north_orders,COUNT(*) FILTER (WHERE region='South') AS south_orders
FROM sales
GROUP BY product_category;
2.4.2 动态数据过滤
- 结合时间窗口或动态参数实现灵活查询：
-- 统计近7天活跃用户中 VIP 占比
SELECT COUNT(*) FILTER (WHERE is_vip = true) / COUNT(*)::FLOAT AS vip_ratio
FROM users
WHERE last_login >= CURRENT_DATE - INTERVAL '7 days'2.4.3 复杂条件处理
- 支持嵌套子查询和窗口函数，例如：
SELECT user_id,COUNT(*) FILTER (WHERE s_type = '1' AND EXISTS (SELECT 1 FROM A WHERE ad_id = B.ad_id)) AS clicks
FROM B
GROUP BY user_id;
三、总结
COUNT(*) FILTER (WHERE …) 通过语法精简与执行优化，显著提升了条件统计的效率与可维护性。尤其在 PostgreSQL 和 Spark 生态中，是多维分析的推荐实践，对于需兼容传统数据库的场景，可通过 CASE WHEN 平稳过渡即可。
