Support for Nesting of Aggregate Functions

Availability

This feature is available since MogDB 5.0.6.

Introduction

Aggregate functions are used to calculate a single result from multiple input rows. This feature supports the nesting of aggregate functions, with up to 2 levels of nesting, where the statement is equivalent to calculating the result of the aggregate function again based on the result of the first layer of aggregate function operations.

Benefits

Enhances compatibility with Oracle, reduces the migration cost for applications, and improves product usability.

Constraints

The query statement must include a GROUP BY clause and supports all GROUP BY extended grouping functions, such as GROUPING SETS, ROLLUP, and CUBE functions.
The nesting level is limited to a maximum of 2 layers and does not support deeper nesting.
Projection columns cannot include table columns, even if they appear in the GROUP BY.
The ORDER BY in the context of nested aggregate functions has no practical significance; performing ORDER BY on expressions not in GROUP BY will not result in an error.
This feature is only applicable in A compatibility mode.

Example

-- Create base table
MogDB=# CREATE TABLE t1 (a INT, b INT, c INT, d INT);
CREATE TABLE

-- Nesting of aggregate functions
MogDB=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT sum(count(a)) FROM t1 GROUP BY a;
            QUERY PLAN
-----------------------------------
 Aggregate
   Output: sum((count(t1.a)))
   ->  HashAggregate
         Output: t1.a, count(t1.a)
         Group By Key: t1.a
         ->  Seq Scan on public.t1
               Output: t1.a
(7 rows)

-- Multiple aggregate functions
MogDB=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT sum(count(c)), min(count(c)) FROM t1 GROUP BY b;
                    QUERY PLAN
--------------------------------------------------
 Aggregate
   Output: sum((count(t1.c))), min((count(t1.c)))
   ->  HashAggregate
         Output: t1.b, count(t1.c)
         Group By Key: t1.b
         ->  Seq Scan on public.t1
               Output: t1.b, t1.c
(7 rows)

-- Different nesting levels of aggregate functions
MogDB=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT sum(count(a)), count(a) FROM t1 GROUP BY a;
                QUERY PLAN
-------------------------------------------
 Aggregate
   Output: sum((count(t1.a))), count(t1.a)
   ->  HashAggregate
         Output: t1.a, count(t1.a)
         Group By Key: t1.a
         ->  Seq Scan on public.t1
               Output: t1.a
(7 rows)

-- Complex aggregate functions
MogDB=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT LISTAGG(count(a), ',') WITHIN group(ORDER BY a) FROM t1 GROUP BY a;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate
   Output: listagg((count(t1.a)), ','::text ) WITHIN GROUP ( ORDER BY t1.a)
   ->  HashAggregate
         Output: t1.a, count(t1.a)
         Group By Key: t1.a
         ->  Seq Scan on public.t1
               Output: t1.a
(7 rows)

-- GROUP BY using CUBE function
MogDB=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT sum(count(a)), count(a) FROM t1 GROUP BY CUBE(a,b);
                QUERY PLAN
-------------------------------------------
 Aggregate
   Output: sum((count(t1.a))), count(t1.a)
   ->  GroupAggregate
         Output: t1.a, t1.b, count(t1.a)
         Group By Key: t1.a, t1.b
         Group By Key: t1.a
         Group By Key: ()
         Sort Key: t1.b
           Group By Key: t1.b
         ->  Sort
               Output: t1.a, t1.b
               Sort Key: t1.a, t1.b
               ->  Seq Scan on public.t1
                     Output: t1.a, t1.b
(14 rows)

Aggregate Functions, SELECT

Support for Nesting of Aggregate Functions

Availability

Introduction

Benefits

Constraints

Example

Related Pages