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

Example: SQL statements containing Agg(xxx) OVER(PARTITION BY xxx) and row_number() OVER (PARTITION BY xxx)

-- Prepare data.
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                             
----------------------------------------------------------------- 
 WindowAgg  (cost=135.70..169.74 rows=1945 width=8) 
   ->  Sort  (cost=135.70..140.56 rows=1945 width=8) 
         Sort Key: a 
         ->  Seq Scan on t  (cost=0.00..29.45 rows=1945 width=8) 
(4 rows)

gaussdb=# EXPLAIN SELECT a, row_number() OVER(partition by a) FROM t;
                           QUERY PLAN
-----------------------------------------------------------------
 WindowAgg  (cost=135.70..169.74 rows=1945 width=4)
   ->  Sort  (cost=135.70..140.56 rows=1945 width=4)
         Sort Key: a
         ->  Seq Scan on t  (cost=0.00..29.45 rows=1945 width=4)
(4 rows)

-- Drop.
gaussdb=# DROP TABLE t;

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