【重新发现PostgreSQL之美】- 48 聚合、窗口过滤器

背景

场景:

• 聚合查询、窗口查询时, 对聚合的内容或窗口的内容本身有过滤条件诉求.

• 如: 每个分组排除噪点后的方差
• 某些分组排除噪点后的方差

挑战:

• 传统的方法需要使用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;