【重新發現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;  

  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;