WindowAgg

Description

The WindowAgg operator is used to process tuple window aggregation. The functions and implementation modes of the WindowAgg operator are similar to those of the aggregate operator. The main difference is that the tuples processed by the WindowAgg operator are restricted to the same window, while the tuples processed by the aggregate operator are the entire table (GROUP BY division).

Typical Scenarios

The query statement contains window functions, such as row_number() OVER (PARTITION BY xxx) and avg(xxx) OVER (PARTITION BY xxx).

Examples

-- Prepare data.
gaussdb=#DROP TABLE IF EXISTS t;
gaussdb=#CREATE TABLE t(a int, b int, c int); 
CREATE TABLE 
gaussdb=#INSERT INTO t VALUES(generate_series(1, 10), generate_series(601, 610), generate_series(901, 910)); 
INSERT 0 10

-- Execution result.
gaussdb=#EXPLAIN SELECT a, avg(b) OVER(partition by a) FROM t; 
                             QUERY PLAN
---------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=14.18..14.82 rows=21 width=8)
   Node/s: All datanodes
   ->  WindowAgg  (cost=13.37..13.60 rows=21 width=8)
         ->  Sort  (cost=13.37..13.40 rows=21 width=8)
               Sort Key: a
               ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=8)
(6 rows)

gaussdb=# EXPLAIN SELECT a, row_number() OVER(partition by a) FROM t;
                             QUERY PLAN
---------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=14.18..14.82 rows=21 width=8)
   Node/s: All datanodes
   ->  WindowAgg  (cost=13.37..13.60 rows=21 width=8)
         ->  Sort  (cost=13.37..13.40 rows=21 width=8)
               Sort Key: a
               ->  Seq Scan on t  (cost=0.00..13.13 rows=20 width=8)
(6 rows)

-- Drop.
gaussdb=#DROP TABLE IF EXISTS t;

In the preceding example, the output of the WindowAgg operator is as follows.