PL/SQL: Как да напишем собствена агрегираща/аналитична функция?

PL/SQL как да напишем собствена агрегираща или аналитична функция

Автор: Дикран Хачикян
Често създаването на собствена агрегираща или аналитична функция се възприема като задача за много напреднали потребители, но надявам се, че тази статия ще ви убеди в това, че подобна задача може спокойно да се постави в категорията „обичайни“.

За да напишете собствена агрегираща функция са необходими четири елемента:

състояние
функция за обработка на следващ ред
функция която връща резултата
указание, че предишните три точки са агрегираща функция

В сървърите на Oracle първите три се реализират с интерфейса Data Cartridge, с помощта на който лесно могат да се създават разширения към съществуващата в сървъра функционалност, а четвъртият е стандартна DDL заявка с малко допълнение.

Да разгледаме прост пример, в който ще напишем агрегираща функция PRODUCT(), която умножава данните в група – такава няма сред стандартните. След подробните стъпки, вижте и видеото как да напишете собствена агрегираща функция на PL/SQL, която изчислява средна претеглена стойност.

Стъпка 1: Създаване на собствен обектен тип

Трябва да създадете собствен тип, който на Стъпка 3 ще се използва при създаването на функцията, която ще участва в заявките.

CREATE OR REPLACE TYPE ProductImpl AS OBJECT(
    product NUMBER, -- тук ще се натрупва резултата
    
    --статична функция за инициализация на контекста
    STATIC FUNCTION ODCIAggregateInitialize( ctx IN OUT ProductImpl )
        RETURN NUMBER,
        
    --функция, която ще се изпълнява за всеки един ред (итератор)
    --velue е стойността намираща се на реда, който в момента се обработва
    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ProductImpl, value IN NUMBER)
        RETURN NUMBER,
    
    --функция, която се използва за обединяване на резултатите при паралелно 
    --изпълнение
    --ctx2 - съдържа резултата от изпълнението в другата(-ите) нишки     
    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ProductImpl, ctx2 IN ProductImpl )
        RETURN NUMBER,
    
    --функция, която има за задача да върне резултата    
    MEMBER FUNCTION ODCIAggregateTerminate( self IN OUT ProductImpl, returnValue OUT NUMBER, flags IN NUMBER)
        RETURN NUMBER
);

CREATE OR REPLACE TYPE ProductImpl AS OBJECT(

product NUMBER, -- тук ще се натрупва резултата

--статична функция за инициализация на контекста

STATIC FUNCTION ODCIAggregateInitialize( ctx IN OUT ProductImpl )

RETURN NUMBER,

--функция, която ще се изпълнява за всеки един ред (итератор)

--velue е стойността намираща се на реда, който в момента се обработва

MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ProductImpl, value IN NUMBER)

RETURN NUMBER,

--функция, която се използва за обединяване на резултатите при паралелно

--изпълнение

--ctx2 - съдържа резултата от изпълнението в другата(-ите) нишки

MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ProductImpl, ctx2 IN ProductImpl )

RETURN NUMBER,

--функция, която има за задача да върне резултата

MEMBER FUNCTION ODCIAggregateTerminate( self IN OUT ProductImpl, returnValue OUT NUMBER, flags IN NUMBER)

RETURN NUMBER

);

Четирите функции от интерфейса на Data Cartridge и учатват в различните моменти от работата на агрегиращата функция.

ODCIAggregateInitialize() извършва началната инициализация на контекста.
ODCIAggregateIterate() е функцията, в която извършва основното изчисление.
Изпълнява се за всеки ред от групата като параметъра value е текущата стойност,
с която трябва да умножим.
ODCIAggregateMerge() се използва в случите, когато сървъра изпълнява действията
паралелно в няколко нишки. Функцията обединява резултатите от паралелните изчисления
ODCIAggregateTerminate() връща резултата

В четирите функции параметъра self е референция към текущия контекст.

Стъпка 2: Дефиниране на функциите в тялото на типа

Функциите от типа ProductImpl трябва да се дефинират и да се разпише действието им за постигане на крайния резултат.

CREATE OR REPLACE TYPE BODY ProductImpl IS
    STATIC FUNCTION ODCIAggregateInitialize( ctx IN OUT ProductImpl )
        RETURN NUMBER IS
        BEGIN
            ctx := ProductImpl(1);--инициализация на променливата product с 1
            RETURN ODCIConst.SUCCESS;
        END;
        
    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ProductImpl, value IN NUMBER)
        RETURN NUMBER IS
        BEGIN
    
            self.product := self.product * NVL(value, 1);
      
            RETURN ODCIConst.SUCCESS;
        END;
        
    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ProductImpl, ctx2 IN ProductImpl )
        RETURN NUMBER IS
        BEGIN
            --умножаваме резултата от другата нишка по това, което е изчислено
            --в текущата
            self.product := self.product * ctx2.product;
            RETURN ODCIConst.SUCCESS;
        END;
        
    MEMBER FUNCTION ODCIAggregateTerminate( self IN OUT ProductImpl, returnValue OUT NUMBER, flags IN NUMBER)
        RETURN NUMBER IS
        BEGIN
            --връщаме резултата
            returnValue := self.product;
            RETURN ODCIConst.SUCCESS;
        END;
END;

CREATE OR REPLACE TYPE BODY ProductImpl IS

STATIC FUNCTION ODCIAggregateInitialize( ctx IN OUT ProductImpl )

RETURN NUMBER IS

BEGIN

ctx := ProductImpl(1);--инициализация на променливата product с 1

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ProductImpl, value IN NUMBER)

RETURN NUMBER IS

BEGIN

self.product := self.product * NVL(value, 1);

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ProductImpl, ctx2 IN ProductImpl )

RETURN NUMBER IS

BEGIN

--умножаваме резултата от другата нишка по това, което е изчислено

--в текущата

self.product := self.product * ctx2.product;

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateTerminate( self IN OUT ProductImpl, returnValue OUT NUMBER, flags IN NUMBER)

RETURN NUMBER IS

BEGIN

--връщаме резултата

returnValue := self.product;

RETURN ODCIConst.SUCCESS;

END;

В конкретния пример, функциите връщат ODCIConst.SUCCESS, но при други функции, с сложни изчисления, проверки и др. може да се използва ODCIConst.ERROR или някоя от другите константи.

Стъпка 3: Създаване на функция, която ще се използва в заявките

CREATE OR REPLACE FUNCTION Product(value NUMBER) RETURN NUMBER
PARALLEL_ENABLE -- посочва, че може да се използва в паралелно работещи потоци 
AGGREGATE USING ProductImpl;

CREATE OR REPLACE FUNCTION Product(value NUMBER) RETURN NUMBER

PARALLEL_ENABLE -- посочва, че може да се използва в паралелно работещи потоци

AGGREGATE USING ProductImpl;

С това сме готови да тестваме работата на агрегиращата функция.

Стъпка 4: Тест

Ще използваме заявка с данни директно въведени в скрипта за да може по-лесно да променяме стойностите и да изследваме поведението на функцията при 0 u NULL стойности.

WITH qry AS (
    SELECT 'A' nm, 1 v FROM DUAL UNION ALL
    SELECT 'A' nm, 2 v FROM DUAL UNION ALL
    SELECT 'A' nm, 3 v FROM DUAL UNION ALL
    SELECT 'B' nm, 2 v FROM DUAL UNION ALL
    SELECT 'B' nm, 4 v FROM DUAL UNION ALL
    SELECT 'B' nm, 2 v FROM DUAL UNION ALL
    SELECT 'C' nm, 3 v FROM DUAL UNION ALL
    SELECT 'C' nm, 2 v FROM DUAL )
SELECT nm
, PRODUCT(v) Product
FROM qry
GROUP BY nm;

WITH qry AS (

SELECT 'A' nm, 1 v FROM DUAL UNION ALL

SELECT 'A' nm, 2 v FROM DUAL UNION ALL

SELECT 'A' nm, 3 v FROM DUAL UNION ALL

SELECT 'B' nm, 2 v FROM DUAL UNION ALL

SELECT 'B' nm, 4 v FROM DUAL UNION ALL

SELECT 'B' nm, 2 v FROM DUAL UNION ALL

SELECT 'C' nm, 3 v FROM DUAL UNION ALL

SELECT 'C' nm, 2 v FROM DUAL )

SELECT nm

, PRODUCT(v) Product

FROM qry

GROUP BY nm;

Полученият резултат е:

+----+---------+
| nm | Product |
+----+---------+
| A  | 6       |
+----+---------+
| B  | 16      |
+----+---------+
| C  | 6       |
+----+---------+

+----+---------+

| nm | Product |

+----+---------+

| A | 6 |

+----+---------+

| B | 16 |

+----+---------+

| C | 6 |

+----+---------+

Опитайте да промените част от стойностите с 0 или NULL и вижте дали полученият резултат съответства на това, което искате да получите накрая. В нашата реализация например, при NULL стойност се умножава по 1, но в друга ситуация може да се изисква NULL да се третира като 0 или да остане NULL, което ще доведе до NULL на цялото произведение.

Как ще изглежда аналитичният вариант на написаната функция?

Хубавата новина е, че функцията може да се използва и като аналитична без да се налага да променяме каквото и да е било в нея. Нека да тестваме:

WITH qry AS (
    SELECT 'A' nm, 1 v FROM DUAL UNION ALL
    SELECT 'A' nm, 2 v FROM DUAL UNION ALL
    SELECT 'A' nm, 3 v FROM DUAL UNION ALL
    SELECT 'B' nm, 2 v FROM DUAL UNION ALL
    SELECT 'B' nm, 4 v FROM DUAL UNION ALL
    SELECT 'B' nm, 2 v FROM DUAL UNION ALL
    SELECT 'C' nm, 3 v FROM DUAL UNION ALL
    SELECT 'C' nm, 2 v FROM DUAL )
SELECT nm
, v
, PRODUCT(v) OVER( PARTITION BY nm 
                   ORDER BY v 
                   ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) Product
FROM qry;

WITH qry AS (

SELECT 'A' nm, 1 v FROM DUAL UNION ALL

SELECT 'A' nm, 2 v FROM DUAL UNION ALL

SELECT 'A' nm, 3 v FROM DUAL UNION ALL

SELECT 'B' nm, 2 v FROM DUAL UNION ALL

SELECT 'B' nm, 4 v FROM DUAL UNION ALL

SELECT 'B' nm, 2 v FROM DUAL UNION ALL

SELECT 'C' nm, 3 v FROM DUAL UNION ALL

SELECT 'C' nm, 2 v FROM DUAL )

SELECT nm

, v

, PRODUCT(v) OVER( PARTITION BY nm

ORDER BY v

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) Product

FROM qry;

Полученият резултат е:

+----+---+---------+
| nm | v | Product |
+----+---+---------+
| A  | 1 | 1       |
+----+---+---------+
| A  | 2 | 2       |
+----+---+---------+
| A  | 3 | 6       |
+----+---+---------+
| B  | 2 | 2       |
+----+---+---------+
| B  | 2 | 4       |
+----+---+---------+
| B  | 4 | 8       |
+----+---+---------+
| C  | 2 | 2       |
+----+---+---------+
| C  | 3 | 6       |
+----+---+---------+

+----+---+---------+

| nm | v | Product |

+----+---+---------+

| A | 1 | 1 |

+----+---+---------+

| A | 2 | 2 |

+----+---+---------+

| A | 3 | 6 |

+----+---+---------+

| B | 2 | 2 |

+----+---+---------+

| B | 2 | 4 |

+----+---+---------+

| B | 4 | 8 |

+----+---+---------+

| C | 2 | 2 |

+----+---+---------+

| C | 3 | 6 |

+----+---+---------+

Пример с видео: Как да напишем агрегираща функция, която изчислява претеглена средна стойност?

Цялостен пример показващ стъпка по стъпка как да напишете собствена агрегираща функция на PL/SQL, която изчислява средна претеглена стойност

Искате да научите още за работата с бази данни?

Вижте какви обучения предстоят в сферата на SQL, PL/SQL, анализ на данни и работа с бази данни.

Научете повече

Ако Вие сте програмист или администратор на бази данни и искате да научите повече за PL/SQL и да подобрите работата си със сървърите на Oracle, този курс е за вас!

PL/SQL: Как да напишем собствена агрегираща/аналитична функция?

Стъпка 1: Създаване на собствен обектен тип

Стъпка 2: Дефиниране на функциите в тялото на типа

Стъпка 3: Създаване на функция, която ще се използва в заявките

Стъпка 4: Тест

Как ще изглежда аналитичният вариант на написаната функция?

Пример с видео: Как да напишем агрегираща функция, която изчислява претеглена средна стойност?

Искате да научите още за работата с бази данни?

Ако Вие сте програмист или администратор на бази данни и искате да научите повече за PL/SQL и да подобрите работата си със сървърите на Oracle, този курс е за вас!

Свързани статии