PL/SQL: Как да напишем собствена агрегираща/аналитична функция?
Автор: Дикран Хачикян
Често създаването на собствена агрегираща или аналитична функция се възприема като задача за много напреднали потребители, но надявам се, че тази статия ще ви убеди в това, че подобна задача може спокойно да се постави в категорията „обичайни“.
За да напишете собствена агрегираща функция са необходими четири елемента:
- състояние
- функция за обработка на следващ ред
- функция която връща резултата
- указание, че предишните три точки са агрегираща функция
В сървърите на Oracle първите три се реализират с интерфейса Data Cartridge, с помощта на който лесно могат да се създават разширения към съществуващата в сървъра функционалност, а четвъртият е стандартна DDL заявка с малко допълнение.
Да разгледаме прост пример, в който ще напишем агрегираща функция PRODUCT(), която умножава данните в група – такава няма сред стандартните. След подробните стъпки, вижте и видеото как да напишете собствена агрегираща функция на PL/SQL, която изчислява средна претеглена стойност.
Стъпка 1: Създаване на собствен обектен тип
Трябва да създадете собствен тип, който на Стъпка 3 ще се използва при създаването на функцията, която ще участва в заявките.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 трябва да се дефинират и да се разпише действието им за постигане на крайния резултат.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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; |
В конкретния пример, функциите връщат ODCIConst.SUCCESS, но при други функции, с сложни изчисления, проверки и др. може да се използва ODCIConst.ERROR или някоя от другите константи.
Стъпка 3: Създаване на функция, която ще се използва в заявките
|
1 2 3 |
CREATE OR REPLACE FUNCTION Product(value NUMBER) RETURN NUMBER PARALLEL_ENABLE -- посочва, че може да се използва в паралелно работещи потоци AGGREGATE USING ProductImpl; |
Стъпка 4: Тест
Ще използваме заявка с данни директно въведени в скрипта за да може по-лесно да променяме стойностите и да изследваме поведението на функцията при 0 u NULL стойности.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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; |
Полученият резултат е:
|
1 2 3 4 5 6 7 8 9 |
+----+---------+ | nm | Product | +----+---------+ | A | 6 | +----+---------+ | B | 16 | +----+---------+ | C | 6 | +----+---------+ |
Опитайте да промените част от стойностите с 0 или NULL и вижте дали полученият резултат съответства на това, което искате да получите накрая. В нашата реализация например, при NULL стойност се умножава по 1, но в друга ситуация може да се изисква NULL да се третира като 0 или да остане NULL, което ще доведе до NULL на цялото произведение.
Как ще изглежда аналитичният вариант на написаната функция?
Хубавата новина е, че функцията може да се използва и като аналитична без да се налага да променяме каквото и да е било в нея. Нека да тестваме:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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; |
Полученият резултат е:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
+----+---+---------+ | 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, която изчислява средна претеглена стойност
Искате да научите още за работата с бази данни?
Ако Вие сте програмист или администратор на бази данни и искате да научите повече за PL/SQL и да подобрите работата си със сървърите на Oracle, курсът PL/SQL за Oracle разработчици, програмисти и администратори на бази данни е за вас!
Научете повече