聚合函数支持KEEP子句

可获得性

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

agg：Aggregate Functions（聚合函数）

特性简介

KEEP是Oracle中的一个重要的分析函数，和DENSE_RANK结合使用，类似于OVER的语法结构。主要使用场景如下：取同一个分组下以某个字段排序，然后对指定字段取最大值和最小值。本特性对Oracle的KEEP语法进行兼容适配。

客户价值

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

特性描述

KEEP语法和DENSE_RANK FIRST/LAST结合使用，主要是对同一个分组下以某个字段排序后，获取排序后的第一组数据或最后一组数据，然后对该组数据的指定字段取最大值和最小值。

MogDB对Oracle的KEEP语法进行兼容，语法规则、执行结果和Oracle保持一致，执行过程中无core，无与Oracle不一致的异常报错，唯一区别是目前暂不支持包含KEEP子句的窗口函数，即如果查询语句中同时包含KEEP子句和OVER子句，直接报错退出。

特性约束

不支持向量化的agg算子。
不支持窗口函数。

语法描述

Oracle的KEEP语法定义如下：

MogDB的语法规则与上述Oracle的语法规则基本一致，唯一区别是MogDB不支持包含KEEP子句的窗口函数。

示例

-- 创建表tab_keep
DROP TABLE IF EXISTS tab_keep;
CREATE TABLE tab_keep (a INT, b INT, c INT, d INT, e INT, f INT, g INT);
-- 插入数据
INSERT INTO tab_keep VALUES (1, 1, 1, 1, 10, 100, 1000);
INSERT INTO tab_keep VALUES (1, 1, 1, 1, 10, 100, 2000);
INSERT INTO tab_keep VALUES (1, 1, 1, 1, 10, 200, 3000);
INSERT INTO tab_keep VALUES (1, 1, 1, 1, 20, 200, 4000);
INSERT INTO tab_keep VALUES (1, 1, 1, 2, 20, 300, 5000);
INSERT INTO tab_keep VALUES (1, 1, 2, 2, 20, 300, 6000);
INSERT INTO tab_keep VALUES (1, 2, 2, 2, 30, 400, 7000);
INSERT INTO tab_keep VALUES (1, 2, 2, 2, 30, 400, 8000);
INSERT INTO tab_keep VALUES (1, 2, 2, 3, 30, 500, 9000);
INSERT INTO tab_keep VALUES (1, 2, 2, 3, 40, 500, 10000);
INSERT INTO tab_keep VALUES (1, 2, 3, 3, 40, 600, 12000);
INSERT INTO tab_keep VALUES (1, 2, 3, 3, 40, 600, 13000);
INSERT INTO tab_keep VALUES (2, 3, 4, 4, 40, 400, 4000);
INSERT INTO tab_keep VALUES (2, 3, 4, 4, 40, 400, 5000);
INSERT INTO tab_keep VALUES (2, 3, 4, 4, 40, 500, 6000);
INSERT INTO tab_keep VALUES (2, 3, 4, 4, 50, 500, 7000);
INSERT INTO tab_keep VALUES (2, 3, 4, 5, 50, 600, 8000);
INSERT INTO tab_keep VALUES (2, 3, 5, 5, 50, 600, 9000);
INSERT INTO tab_keep VALUES (2, 4, 5, 5, 60, 700, 10000);
INSERT INTO tab_keep VALUES (2, 4, 5, 5, 60, 700, 11000);
INSERT INTO tab_keep VALUES (2, 4, 5, 6, 60, 800, 12000);
INSERT INTO tab_keep VALUES (2, 4, 5, 6, 70, 800, 13000);
INSERT INTO tab_keep VALUES (2, 4, 6, 6, 70, 900, 14000);
INSERT INTO tab_keep VALUES (2, 4, 6, 6, 70, 900, 15000);

-- 测试KEEP(DENSE_RANK FIRST)
SELECT a, MAX(g) KEEP(DENSE_RANK FIRST ORDER BY b) FROM tab_keep GROUP BY a ORDER BY 1, 2;
SELECT a, MIN(g) KEEP(DENSE_RANK FIRST ORDER BY b) FROM tab_keep GROUP BY a ORDER BY 1, 2;

-- 测试KEEP(DENSE_RANK LAST)
SELECT a, MAX(g) KEEP(DENSE_RANK LAST ORDER BY b) FROM tab_keep GROUP BY a ORDER BY 1, 2;
SELECT a, MIN(g) KEEP(DENSE_RANK LAST ORDER BY b) FROM tab_keep GROUP BY a ORDER BY 1, 2;

-- GROUP BY 子句中包含GROUPING SETS，ROLLUP，CUBE分组集合
SELECT a, b, c, MAX(g) KEEP(DENSE_RANK FIRST ORDER BY d) FROM tab_keep GROUP BY GROUPING SETS((a),(b), (c), (a, b), (a, b, c)) ORDER BY a, b, c;
SELECT a, b, c, MIN(g) KEEP(DENSE_RANK FIRST ORDER BY d) FROM tab_keep GROUP BY GROUPING SETS((a),(b), (c), (a, b), (a, b, c)) ORDER BY a, b, c;
SELECT a, b, c, MIN(g) KEEP(DENSE_RANK FIRST ORDER BY d) FROM tab_keep GROUP BY ROLLUP(a, b, c) ORDER BY a, b, c;
SELECT a, b, c, MIN(g) KEEP(DENSE_RANK FIRST ORDER BY d) FROM tab_keep GROUP BY CUBE(a, b, c) ORDER BY a, b, c;

-- 包含多个agg函数
SELECT a, b, c, MAX(g) KEEP(DENSE_RANK FIRST ORDER BY d), 
                MAX(f) KEEP(DENSE_RANK FIRST ORDER BY d), 
                MIN(g) KEEP(DENSE_RANK FIRST ORDER BY d),
                MIN(f) KEEP(DENSE_RANK FIRST ORDER BY d)
                FROM tab_keep GROUP BY GROUPING SETS((a),(b), (c), (a, b), (a, b, c)) ORDER BY a, b, c;
                
-- Order by子句中包含nulls first
SELECT a,  MAX(c) KEEP(DENSE_RANK FIRST ORDER BY b nulls FIRST) FROM tab_keep GROUP BY a ORDER BY 1, 2;
SELECT a,  MAX(c) KEEP(DENSE_RANK LAST ORDER BY b nulls FIRST) FROM tab_keep GROUP BY a ORDER BY 1, 2;

-- Order by子句中包含nulls last
SELECT a,  MAX(c) KEEP(DENSE_RANK FIRST ORDER BY b nulls LAST) FROM tab_keep GROUP BY a ORDER BY 1, 2;
SELECT a,  MAX(c) KEEP(DENSE_RANK LAST ORDER BY b nulls LAST) FROM tab_keep GROUP BY a ORDER BY 1, 2;