MogDB 支持聚集函数嵌套

可获得性

本特性自MogDB 5.0.6版本开始引入。

特性简介

聚集函数用于从多个输入行中计算出一个结果。本特性支持聚集函数嵌套运算，最多支持2层嵌套，语句等价于在第一层聚集函数运算结果的基础上再次计算聚集函数结果。

客户价值

增强MogDB与Oracle的兼容性，减少应用程序的迁移代价，提升产品易用性。

特性约束

• 查询语句必须包含GROUP BY子句，支持所有GROUP BY扩展分组函数，如GROUPING SETS、ROLLUP和CUBE函数。
• 嵌套层数最多支持2层，不支持更深层次嵌套。
• 投影列不可以包含表的列，即使该列出现在GROUP BY中。
• 聚集函数嵌套场景下的ORDER BY无实际意义，对不是GROUP BY中的表达式做ORDER BY不会报错。
• 本特性仅适用于A兼容模式。

示例

-- 创建基表
MogDB=# CREATE TABLE t1 (a INT, b INT, c INT, d INT);
CREATE TABLE

-- 聚集函数嵌套
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)

-- 多个聚集函数
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)

-- 不同嵌套层数的聚集函数
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)

-- 复杂聚集函数
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使用CUBE函数
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)

相关页面

聚集函数、SELECT