【SQL】COUNT... FILTER 的适用场景

一、引言

• 今天参加业务评审，见到一个不太一样的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 平稳过渡即可。