背景
场景:
- 聚合查询、窗口查询时, 对聚合的内容或窗口的内容本身有过滤条件诉求.
- 如: 每个分组排除噪点后的方差
- 某些分组排除噪点后的方差
挑战:
- 传统的方法需要使用case when 来进行过滤, 然而对于有上下文相关的记录使用case when无法支持, 例如求标准方差、平均值等需要收敛到子集空间进行计算时, case when结果不一致.
- 传统方法需要扫描多遍table
PG 解决方案:
语法简单, 同时只需要扫描一次table,而且结果不存在语意问题.
- agg filter
- window filter
- http://api.pgxn.org/src/trimmed_aggregates/
https://www.postgresql.org/docs/14/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
https://blog.crunchydata.com/blog/fast-flexible-summaries-with-aggregate-filters-and-windows
窗口过滤器
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
聚合过滤器
aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]
例子:
SELECT
b / 100 AS b_div_100,
stddev(value) FILTER (WHERE c = 'bee') AS bee_stddev,
stddev(value) FILTER (WHERE a > 900) AS a900_stddev
FROM sales
GROUP BY 1;
SELECT
100.0 * sum(value) FILTER (WHERE c = 'bee') / sum(value) AS bee_pct,
100.0 * sum(value) FILTER (WHERE a > 900) / sum(value) AS a900_pct
FROM sales;