Aggregate Functions Support Keep Clause

Availability

This feature is available since MogDB 5.0.0.

Introduction

KEEP is an important analytic function in Oracle, and DENSE_RANK used in combination, similar to the syntax structure of OVER. The main use scenarios are as follows: take the same grouping to a certain field sorting, and then take the maximum and minimum value of the specified field. This feature is compatible with Oracle's KEEP syntax.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Description

KEEP syntax and DENSE_RANK FIRST/LAST used in conjunction, mainly for the same grouping of a certain field sorting, access to the sorted first group of data or the last group of data, and then the group of data on the specified field to take the maximum and minimum values.

MogDB on Oracle's KEEP syntax compatibility, syntax rules, execution results and Oracle to maintain consistency, no core in the execution process, no inconsistency with the Oracle exception error, the only difference is that currently does not support the window function contains KEEP clauses, that is, if the query statement contains both KEEP clauses and OVER clauses, the direct error to exit! The only difference is that there is no support for window functions containing KEEP clauses.

Constraints

The vectored aggregate operator is not supported.
Window functions are not supported.

Syntax Description

Oracle's KEEP syntax is defined as follows:

The syntax rules of MogDB are basically the same as those of Oracle described above, the only difference being that MogDB does not support window functions that contain KEEP clauses.

Example

-- Create table 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 data
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);

-- Test 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;

-- Test 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 clause with GROUPING SETS, ROLLUP, CUBE grouping sets
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;

-- Contains multiple aggregate functions
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 clause contains 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 clause contains 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;