digoal
2021-08-25
PostgreSQL , 聚合 , 窗口
视频回放: https://www.bilibili.com/video/BV1K3411B7p9/
场景:
- 聚合查询、窗口查询时, 对聚合的内容或窗口的内容本身有过滤条件诉求.
- 如: 每个分组排除噪点后的方差
- 某些分组排除噪点后的方差
挑战:
- 传统的方法需要使用 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;
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
