WindowAgg

名称

窗口聚合算子

介绍

WindowAgg算子用于处理窗口函数，窗口函数是在满足某种条件的一系列数据(元组)上执行的特殊函数。

GROUP BY子句可对数据进行分组，但无法投影非分组属性而在实际的使用中会需要投影非分组属性，可使用WindowAgg算子

举例

--清理环境
DROP TABLE t1;

--创建环境
CREATE TABLE t1(class_id int, gender text,num int);
INSERT INTO t1 VALUES(1,'male',20);
INSERT INTO t1 VALUES(1,'female',16);
INSERT INTO t1 VALUES(2,'male',17);
INSERT INTO t1 VALUES(2,'female',18);

EXPLAIN ANALYZE SELECT class_id,gender,num,cast(num as float)/SUM(num) OVER (PARTITION BY class_id) as pct  FROM t1;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=622.40..847.68 rows=8192 width=40) (actual time=0.39..0.50 rows=4 loops=1)
   ->  Sort  (cost=622.40..642.88 rows=8192 width=40) (actual time=0.27..0.27 rows=4 loops=1)
         Sort Key: CLASS_ID
         ->  Seq Scan on T1  (cost=0.00..89.92 rows=8192 width=40) (actual time=0.09..0.15 rows=4 loops=1)
 Planning Time: 0.33 msec
 Execution Time: 0.43 msec
(6 rows)