SQL聚合函数研究与实现
王慧刚 2006-10-22
摘 要：本文主要讲述数据库SQL聚合函数和在HG SQLHUB中自定义聚合函数的实现。
关键字：HGSQL SQLHUB 聚合函数
概述
数据库函数分为两类：单行函数和多行函数。多行函数也叫聚合函数、组合函数。单行函数处理一条记录返回一个值，聚合函数处理多条记录返回一个值。
单行函数可以任意使用，聚合函数只能用在select列表、having条件、排序条件中，聚合函数的参数为数组，数据的大小为记录数。这种数组不是普通高级语言的数组，它是一种虚拟数组，当记录数大时，它会把数据写入硬盘中，内存中只是镜像，这样就能处理大量的数据。
编写聚合函数的重要意义
各种数据库都提供了聚合函数，共用的有count、sum、avg、max、min。分组统计是数据库的重要功能，聚合函数的多少决定了分组统计功能的强弱。Oracle相对来说提供的统计函数比较多，9i约有34个，但这并不能覆盖所有的需求。据我所知，除hgsql外，其它数据库不支持用户自定义聚合函数，或编写很困难。可能出于以下原因：
1、 聚合函数识别
数据库在处理查询时必须对聚合函数是已知的，因为在不存在group by子句的select查询时要根据是否存在聚合函数来判断是否是分组查询。Hgsql规定聚合函数名称必须以“$”开头，解决了这个问题。
2、 语法不易支持
因为聚合函数的参数是数组，必须有相应的语法来支持访问和处理。Hgsql通过使用rowcount系统变量、参数数据访问等做了语法支持。
在HGSQL中编写用户自定义聚合函数
下面举两个例子来说明如何在hgsql中编写聚合函数
例子一：
/*
* 名称：$strsum
* 功能：将p_col的值用p_sep做间隔符合并起来
* 参数：p_col：数据
* p_sep：间隔符
*/
create or replace procedure $strsum(p_col,p_sep) return varchar as
begin
v_res := '';
for i in 1 .. rowcount loop
if i > 1 then
v_res := v_res + p_sep;
end if;
v_res := v_res + p_col(i);
end loop;
return v_res;
end;
说明：
² v_res是一个变量，变量不需要声明，可以直接使用，变量的数据类型是动态的
² rowcount是系统保留变量，表示聚合函数处理的记录数，也就是参数数组大小
² p_col(i)表示取数组的第i个值
² p_set也是参数数组，参数数组可以直接使用，表示取第1个值
函数使用：
以员工表做演示，下面是员工表的数据：
+------+--------+--------+------+------------+------+------+------+
| 编号 | 姓名 | 职位 | 上级 | 出生日期 | 工资 | 奖金 | 部门 |
+------+--------+--------+------+------------+------+------+------+
| 0001 | 王东 | 职员 | 0013 | 1980-02-05 | 800 | 0 | 20 |
| 0002 | 李海 卫 | 销售 | 0006 | 1979-08-15 | 1600 | 300 | 30 |
| 0003 | 张空 | 销售 | 0006 | 1982-08-07 | 1250 | 500 | 30 |
| 0004 | 孔兵 | 经理 | 0009 | 1982-03-01 | 2975 | 0 | 20 |
| 0005 | 刘龙维 | 销售 | 0006 | 1982-10-13 | 1250 | 1400 | 30 |
| 0006 | 张云 | 经理 | 0009 | 1977-02-14 | 2850 | 0 | 30 |
| 0007 | 关心 | 经理 | 0009 | 1978-07-27 | 2450 | 0 | 10 |
| 0008 | 王芳 | 主管 | 0004 | 1981-10-18 | 3000 | 0 | 20 |
| 0009 | 刘程飞 | 总经理 | | 1982-01-17 | 5000 | 0 | 10 |
| 0010 | 张凯 | 销售 | 0006 | 1979-07-29 | 1500 | 200 | 30 |
| 0011 | 宋建 | 职员 | 0008 | 1980-01-23 | 1100 | 0 | 20 |
| 0012 | 李保 | 职员 | 0006 | 1982-04-11 | 950 | 0 | 30 |
| 0013 | 刘海星 | 主管 | 0004 | 1978-01-30 | 3000 | 0 | 20 |
| 0014 | 赵新 | 职员 | 0007 | 1982-10-07 | 1300 | 0 | 10 |
| 0015 | 刘固 | 顾问 | 0009 | 1980-01-22 | 3000 | 0 | 50 |
+------+--------+--------+------+------------+------+------+------+
查询SQL:
select 部门,$strsum(工资,',') from 员工 group by 部门 order by 部门;
执行结果：
+------+------------------------------+
| 部门 | $STRSUM(工资,',') |
+------+------------------------------+
| 10 | 2450,5000,1300 |
| 20 | 800,2975,3000,1100,3000 |
| 30 | 1600,1250,1250,2850,1500,950 |
| 50 | 3000 |
+------+------------------------------+
所有用户编写的聚合函数都可以用在交叉统计中，下面举例：
查询SQL：
select * from crossx(query('员工'),'部门','职位','$strsum(编号,'','') 编号') crossx
执行结果：
+--------+----------------+--------------------------+-------------------------------+------+----------------------------------------------------------------------------+
| F1 | F2 | F3 | F4 | F5 | F6 |
+--------+----------------+--------------------------+-------------------------------+------+----------------------------------------------------------------------------+
| 部门 | 10 | 20 | 30 | 50 | |
| 职位 | 编号 | 编号 | 编号 | 编号 | 编号 |
| 主管 | | 0008,0013 | | | 0008,0013 |
| 总经理 | 0009 | | | | 0009 |
| 经理 | 0007 | 0004 | 0006 | | 0004,0006,0007 |
| 职员 | 0014 | 0001,0011 | 0012 | | 0001,0011,0012,0014 |
| 销售 | | | 0002,0003,0005,0010 | | 0002,0003,0005,0010 |
| 顾问 | | | | 0015 | 0015 |
| | 0007,0009,0014 | 0001,0004,0008,0011,0013 | 0002,0003,0005,0006,0010,0012 | 0015 | 0001,0002,0003,0004,0005,0006,0007,0008,0009,0010,0011,0012,0013,0014,0015 |
+--------+----------------+--------------------------+-------------------------------+------+----------------------------------------------------------------------------+
例子二：
/*
* 名称：$disstrsum
* 功能：将p_col的非重复值用p_sep做间隔符合并起来
* 参数：p_col：数据
* p_sep：间隔符
*/
create or replace procedure $disstrsum(p_col,p_sep) return varchar as
begin
v_set := (select distinct f1 from p_col order by f1);
v_rowcount := rowcount(v_set);
v_res := '';
for i in 1 .. v_rowcount loop
if i > 1 then
v_res := v_res + p_sep;
end if;
v_res := v_res + v_set(i,1);
end loop;
return v_res;
end;
说明：
² 可以对数组参数执行查询，里面只有一个字段F1，p_col
² rowcount函数用于获取数据集的记录数量，性能要优于对数据集进行count计数
² v_set的中保存的是数据集，v_set(i,1)表示取第i条记录的第1个字段的值
函数使用：
select 部门,$disstrsum(工资,',') from 员工 group by 部门 order by 部门;
+------+-------------------------+
| 部门 | $DISSTRSUM(工资,',') |
+------+-------------------------+
| 10 | 1300,2450,5000 |
| 20 | 800,1100,2975,3000 |
| 30 | 950,1250,1500,1600,2850 |
| 50 | 3000 |
+------+-------------------------+
小结
基于Hgsql的聚合函数功能支持，用户可以编写更为复杂的聚合函数，在HGSQL网站上提供更多的聚合函数实例供下载。
参考文献：《HGSQL参考手册》
联系方式：sqlhub@163.com
210
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
