LightDB支持带有keep关键字的聚集函数

背景

聚集函数的作用是对查询结果按条件分组，然后对每个组内的所有记录进行汇总/统计，最后形成每组一个结果后输出。oracle中存在一种特殊的聚集函数，就是带有keep关键字的聚集函数，它的执行逻辑也是先对查询结果按条件分组，但不是直接对组内的所有记录进行汇总/统计，而是先将组内数据按照条件进行排序，选取最小或最大的一条或几条记录进行汇总/统计，最后形成每组一个结果后输出。

oracle中带keep关键字的聚集函数的语法如下（仅first和last支持，图片展示的是first）

LightDB实现带有keep的聚集函数功能

 LightDB从24.1版本开始也支持了带有keep关键字的聚集函数功能，仅在oracle兼容模式下生效，下面就来介绍和使用一下这个功能。

首先准备测试数据

create table t1(a int,b int,c int);

insert into t1 values(1,1,1),(1,2,1),(1,3,3),(1,4,3),(2,10,1),(2,20,1),(2,30,3),(2,40,3);

lightdb@oracle=# select * from t1;
 a | b  | c 
---+----+---
 1 |  1 | 1
 1 |  2 | 1
 1 |  3 | 3
 1 |  4 | 3
 2 | 10 | 1
 2 | 20 | 1
 2 | 30 | 3
 2 | 40 | 3
(8 rows)

可以看到测试数据按字段a分成了2组，分别是a=1和a=2的2组数据，接下来使用带有keep的聚集函数分别对2组数据进行聚集操作：

--将t1表数据按a字段分组，组内对c排序，取组内最小的一条或多条数据将其keep住
--最后对keep住的元组，对其执行sum聚集操作
--例如对于a=1的数据，按c排序后，b=1和b=2这2条记录的c列值都是1，是最小的，
--因此这2条记录会被keep住，对这2条记录的b列执行sum操作，最终结果即为3
select a,sum(b) keep(dense_rank first order by c) keep_sum,sum(b) from t1 group by a;
 a | keep_sum | sum 
---+----------+-----
 1 |        3 |  10
 2 |       30 | 100
(2 rows)


--dense_rank后紧跟last，说明是对组内排序好的元组取最大值并将其keep住
lightdb@oracle=# select a,sum(b) keep(dense_rank last order by c) keep_sum,sum(b) from t1 group by a;
 a | keep_sum | sum 
---+----------+-----
 1 |        7 |  10
 2 |       70 | 100
(2 rows)


--count/avg/min/max的用法也是类似的
select a,count(b) keep(dense_rank first order by c) keep_count,count(b) from t1 group by a;
 a | keep_count | count 
---+------------+-------
 1 |          2 |     4
 2 |          2 |     4
(2 rows)

select a,avg(b) keep(dense_rank first order by c) keep_avg,avg(b) from t1 group by a;
 a | keep_avg | avg 
---+----------+-----
 1 |      1.5 | 2.5
 2 |       15 |  25
(2 rows)

select a,min(b) keep(dense_rank first order by c) keep_min,min(b) from t1 group by a;
 a | keep_min | min 
---+----------+-----
 1 |        1 |   1
 2 |       10 |  10
(2 rows)

select a,max(b) keep(dense_rank first order by c) keep_max,max(b) from t1 group by a;
 a | keep_max | max 
---+----------+-----
 1 |        2 |   4
 2 |       20 |  40
(2 rows)

上述案例描述的是sum函数结合keep关键字的用法，目前LightDB支持对count、sum、avg、min和max聚集函数可以结合关键字keep使用。

使用限制说明

1. 在Oracle兼容模式下，目前只支持sum、count、avg、min和max这5个聚集函数使用带有keep关键字的用法。
2. 我们可以看到oracle中不仅可以对普通聚集函数使用keep，也可以对带有over子句的分析函数使用keep功能。但是LightDB 24.1版本仅支持对普通聚集函数使用keep，带有over子句的分析函数暂不支持keep功能。
3. 聚集函数使用最多的场景就是对数值类型进行聚集运算，目前仅对数值类型实现了带有keep功能的聚集函数，例如上面的例子中，sum(b)中的参数b列必须是数值类型。
4. 组内排序功能仅支持按单列或多列排序，不支持对表达式排序，例如上面例子中keep(dense_rank last order by c)，目前不支持keep(dense_rank last order by c+b)

以上使用限制特此说明。