Group

Description

The group operator is used to process the GROUP BY clause and group the lower-layer sorted tuples. The result after grouping by group key is returned.

Typical Scenarios

Grouping: queries the number of different values in a column. The function of this operation is similar to that of DISTINCT.

Examples

The query statement contains the GROUP BY clause.

-- Prepare data.
gaussdb=#DROP TABLE IF EXISTS student;
gaussdb=#CREATE TABLE student(id integer, class_id integer, grade number); 
CREATE TABLE 
gaussdb=#INSERT INTO student VALUES(generate_series(1,50), 1, floor(100 * random())); 
INSERT 0 50 
gaussdb=#INSERT INTO student VALUES(generate_series(51,100), 2, floor(100 * random())); 
INSERT 0 50 
gaussdb=#INSERT INTO student VALUES(generate_series(101,150), 3, floor(100 * random())); 
INSERT 0 50 
gaussdb=#INSERT INTO student VALUES(generate_series(151,200), 3, floor(100 * random())); 
INSERT 0 50

-- Execution result.
gaussdb=#SET enable_hashagg = off;
SET
gaussdb=#EXPLAIN SELECT class_id FROM student GROUP BY class_id ORDER BY class_id; 
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Group  (cost=13.37..13.44 rows=26 width=4)
   Group By Key: class_id
   ->  Sort  (cost=15.27..15.33 rows=26 width=4)
         Sort Key: class_id
         ->  Streaming (type: GATHER)  (cost=14.18..14.65 rows=26 width=4)
               Node/s: All datanodes
               ->  Group  (cost=13.37..13.44 rows=26 width=4)
                     Group By Key: class_id
                     ->  Sort  (cost=13.37..13.40 rows=21 width=4)
                           Sort Key: class_id
                           ->  Seq Scan on student  (cost=0.00..13.13 rows=20 width=4)
(11 rows)

-- Drop.
gaussdb=#DROP TABLE student;