达梦数据库自定义分析函数

本章介绍达梦数据库自定义分析函数。

分析函数主要用于计算基于组的某种聚合值。DM 分析函数为用户分析数据提供了一种更加简单高效的处理方式。如果不使用分析函数，则必须使用连接查询、子查询或者视图，甚至复杂的存储过程实现。引入分析函数后，只需要简单的 SQL 语句，并且执行效率方面也有大幅提高。与集函数的主要区别是，分析函数对于每组返回多行，而集函数对于每个分组只返回一行。多行形成的组称为窗口，窗口决定了执行当前行的计算范围，窗口的大小可以由组中定义的行数或者范围值滑动。

达梦数据库MEDIAN函数不支持窗口函数，本文参考iteye_11541所发布的文章关于Oracle中的实现，在达梦数据库中自定义函数实现MEDIAN的窗口函数，实现median(中位值)聚合函数的功能：对输入的一组数排序，当元素数量为奇数时，返回中间元素的值；当元素数量为偶数时，返回中间两个元数的平均值。

实现步骤

1.1、创建定义数值数组类型：

create or replace type t_number_array is varray(100) of number;

1.2、定义分析函数类型

--包头
create or replace type t_med
as object
        (
                numbers t_number_array,
                --初始化
                
        static function odciaggregateinitialize
                (
                        this in out t_med)
                return number,
                --遍历 member
        function odciaggregateiterate
                (
                        self in out t_med,
                        value number)
                return number,
                --结束 member
        function odciaggregateterminate
                (
                        self in t_med,
                        returnvalue out number,
                        flags in        number)
                return number,
                --合并 member
        function odciaggregatemerge
                (
                        self in out t_med,
                        ctx2 in t_med)
         return number 
 );
--包体
CREATE OR REPLACE TYPE BODY t_med
IS
        --初始化
    STATIC FUNCTION odciaggregateinitialize
        (
          this IN OUT t_med)
        RETURN NUMBER
IS
BEGIN
        this := t_med(t_number_array());
        RETURN odciconst.success;
END;
--遍历 MEMBER
FUNCTION odciaggregateiterate
        (
                SELF IN OUT t_med,
                VALUE NUMBER)
        RETURN NUMBER
IS
        --将元素按倒序,插入到数组中 --新元素将要插入的位置
        i_loc1 INTEGER;
        --移位时的数组指针
        i_loc2 INTEGER;
BEGIN
        --空值不处理
        IF VALUE IS NULL THEN
                RETURN odciconst.success;
        END IF;
        --假定初始的位置是最后
        i_loc1 := self.numbers.count + 1;
        FOR i                       IN 1 .. self.numbers.count
        LOOP
                IF VALUE        > self.numbers(i) THEN
                        i_loc1 := i;
                        GOTO outer1;
                END IF;
        END LOOP;
        <<outer1>>
        --数组扩充一个元素
        self.numbers.extend;
        i_loc2 := self.numbers.count;
        --插入位置的元素后移
        WHILE i_loc2 > i_loc1
        LOOP
                self.numbers(i_loc2) := self.numbers(i_loc2 - 1);
                i_loc2               := i_loc2              - 1;
        END LOOP;
        --新元素填入
        self.numbers(i_loc1) := VALUE;
        RETURN odciconst.success;
END;
--结束MEMBER
FUNCTION odciaggregateterminate
        (
                SELF IN t_med,
                returnvalue OUT NUMBER,
                flags IN        NUMBER)
        RETURN NUMBER
IS
BEGIN
        IF self.numbers.count = 0 THEN
                returnvalue  := NULL;
        ELSE
                IF self.numbers.count MOD 2 = 0 THEN
                        --元素数量是偶数,返回中间两个元素的平均值
                        returnvalue := (self.numbers(self.numbers.count / 2 + 1) + self.numbers(self.numbers.count / 2)) / 2;
                ELSE
                        --元素数量是奇数,返回中间元素
                        returnvalue := self.numbers((self.numbers.count - 1) / 2 + 1);
                END IF;
        END IF;
        RETURN odciconst.success;
END;
--合并 MEMBER
FUNCTION odciaggregatemerge
        (
                SELF IN OUT t_med,
                ctx2 IN t_med)
        RETURN NUMBER
IS
BEGIN
        NULL;
        RETURN odciconst.success;
END;
END;

1.3、定义分析函数

create or replace function f_med(p_value number) return number aggregate using t_med;

创建测试表：

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR(10),
                  JOB VARCHAR(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
 
INSERT INTO EMP VALUES (1,'SMITH','CLERK',800,20);
INSERT INTO EMP VALUES (2,'ALLEN','SALESMAN',1600,30);
INSERT INTO EMP VALUES (3,'WARD','SALESMAN',1250,30);
INSERT INTO EMP VALUES (4,'JONES','MANAGER',2975,20);
INSERT INTO EMP VALUES (5,'MARTIN','SALESMAN',1250,30);
INSERT INTO EMP VALUES (6,'BLAKE','MANAGER',2850,30);
INSERT INTO EMP VALUES (7,'CLARK','MANAGER',2850,10);
INSERT INTO EMP VALUES (8,'SCOTT','ANALYST',3000,20);
INSERT INTO EMP VALUES (9,'KING','PRESIDENT',3000,10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN',1500,30);
INSERT INTO EMP VALUES (11,'ADAMS','CLERK',1500,20);
COMMIT;

测试一下：

SELECT JOB,SAL,f_med(SAL) over(PARTITION BY JOB) FROM EMP;