Comments 29
Спасибо за статью.
Еще бы пример как с производительностью у пользовательских функций, хотя бы в сравнении с какой либо стандартной.
Еще бы пример как с производительностью у пользовательских функций, хотя бы в сравнении с какой либо стандартной.
+2
Если сравнивать свою функцию, написанную на PL/SQL (тем более на PL/pgSQL) со стандартной, то все будет довольно печально. Так что если есть стандартная функция, то изобретать велосипед не надо (:
Если написать свою на C в PostgreSQL, то будет ровно одно и то же.
А вот если написать свою в Oracle на чем-нибудь компилируемом, то не знаю, не пробовал. Предполагаю, что будет чуть проигрывать.
Если написать свою на C в PostgreSQL, то будет ровно одно и то же.
А вот если написать свою в Oracle на чем-нибудь компилируемом, то не знаю, не пробовал. Предполагаю, что будет чуть проигрывать.
0
Буквально на прошлой неделе решал задачу корректного отображения, например, доли от итоговой суммы. Решил делать через агрегатные функции.
Код
-- Объявление агрегатной функции
CREATE AGGREGATE calc.percent(numeric[]) (
SFUNC=calc.part_add,
STYPE=numeric[],
FINALFUNC="calc.part_divide_percent",
INITCOND='{0, 0}'
);
-- Обработка очередной строки
CREATE OR REPLACE FUNCTION calc.part_add(
numeric[],
numeric[])
RETURNS numeric[] AS
$BODY$
select array[$1[1] + coalesce($2[1],0), $1[2] + coalesce($2[2],0)]
$BODY$
LANGUAGE sql VOLATILE
COST 100;
-- Выдача результата
CREATE OR REPLACE FUNCTION calc.part_divide_percent(numeric[])
RETURNS numeric AS
$BODY$
select case when not coalesce($1[2],0) = 0 then $1[1] / $1[2] * 100 else null end
$BODY$
LANGUAGE sql VOLATILE
COST 100;
+1
Точнее, в этом примере считается итоговый процент отношения одного показателя к другому.
0
Ага, можно так. Мне, правда, не очень нравится этот трюк с массивом: экономим на создании типа (а зачем?), но ведь код получается непонятный.
Кстати, как вариант, можно было бы обойтись обычной суммой: sum(...) / nullif(sum(...), 0)
.
А для отношения доли к сумме в Oracle даже есть стандартная функция ratio_to_report
.
0
Поясню. Трюк с массивом и использование самописной агрегатной функции нужны были для того, чтобы сделать интерфейс администратора для настройки этого приложения. Таким образом, в админке можно написать формулу расчёта показателя:
Эта формула легко регулярными выражениями приводится к виду
Так как подобных функций набирается как минимум с десяток и в них может быть более 2 параметров (присутствуют более сложные методики расчёта), решил не создавать типы для каждой из функций (впрочем, теперь это моё решение не кажется таким однозначным).
В результате:
=percent(PCurrent, PTotal)
Эта формула легко регулярными выражениями приводится к виду
calc.percent(array[indicators->>'PCurrent', indicators->>'PTotal'])
, который подставляется в текст создаваемого представления.Так как подобных функций набирается как минимум с десяток и в них может быть более 2 параметров (присутствуют более сложные методики расчёта), решил не создавать типы для каждой из функций (впрочем, теперь это моё решение не кажется таким однозначным).
В результате:
- даём пользователю возможность ввести человекопонятную формулу без необходимости проверки на 0/null
- можем проверить её корректность на этапе при сохранении
- разрешаем использовать только те агрегатные функции, которые доступны в схеме calc
0
Идея понятная, цель благая.
В плане «поворчать» могу обратить внимание на пару моментов.
Во-первых, все абстракции текут. Например, стоит ошибиться с названием параметра и получим странное поведение (молчаливый null вместо сообщения об ошибке) — если, конечно, это не было предусмотрено в дополнение к регулярке.
Ну и во-вторых, с отдельными типами эта конструкция работала бы ничуть не сложнее и не хуже (:
В плане «поворчать» могу обратить внимание на пару моментов.
Во-первых, все абстракции текут. Например, стоит ошибиться с названием параметра и получим странное поведение (молчаливый null вместо сообщения об ошибке) — если, конечно, это не было предусмотрено в дополнение к регулярке.
Ну и во-вторых, с отдельными типами эта конструкция работала бы ничуть не сложнее и не хуже (:
0
как потом дебажить эти функции?
0
Заметил особенность реализации аналитических функций в Oracle. Если в OVER() используем ORDER BY, то в ODCIAggregateTerminate зайдёт только при смене значения в ORDER BY. Надо об этом помнить, а то можно получить совсем неожиданные результаты.
Пример:
Функция GROUP_NUMBER — назначает номер группы при смене значения
Запрос
Возвращает неожиданный результат
А вот ожидаемый результат
Пример:
Функция GROUP_NUMBER — назначает номер группы при смене значения
GROUP_NUMBER
CREATE OR REPLACE TYPE GROUP_NUMBER_IMPL as OBJECT (
PREV_VAL VARCHAR2(100)
, CUR_GROUP_NUMBER NUMBER
, STATIC FUNCTION ODCIAggregateInitialize(SCTX IN OUT GROUP_NUMBER_IMPL) RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT GROUP_NUMBER_IMPL, VALUE IN VARCHAR2) RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateTerminate(SELF IN GROUP_NUMBER_IMPL, RETURN_VAL OUT NUMBER, FLAGS IN NUMBER) RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateMerge(self IN OUT GROUP_NUMBER_IMPL, CTX2 IN GROUP_NUMBER_IMPL) RETURN NUMBER
);
/
create or replace type body GROUP_NUMBER_IMPL IS
static FUNCTION ODCIAggregateInitialize(SCTX IN OUT GROUP_NUMBER_IMPL) RETURN NUMBER
is
begin
SCTX := GROUP_NUMBER_IMPL('', 0);
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT GROUP_NUMBER_IMPL, VALUE IN VARCHAR2) RETURN NUMBER is
begin
if NVL(value, 'ЪъЪ') != NVL(self.PREV_VAL, 'ЪъЪ') OR self.CUR_GROUP_NUMBER = 0 THEN
self.CUR_GROUP_NUMBER := self.CUR_GROUP_NUMBER + 1;
self.PREV_VAL := value;
end if;
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateTerminate(SELF IN GROUP_NUMBER_IMPL, RETURN_VAL OUT NUMBER, FLAGS IN NUMBER) RETURN NUMBER is
begin
RETURN_VAL := self.CUR_GROUP_NUMBER;
return ODCIConst.Success;
end;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT GROUP_NUMBER_IMPL, CTX2 IN GROUP_NUMBER_IMPL) RETURN NUMBER is
begin
self.CUR_GROUP_NUMBER := self.CUR_GROUP_NUMBER + CTX2.CUR_GROUP_NUMBER;
end;
end;
/
CREATE OR REPLACE FUNCTION GROUP_NUMBER(INPUT VARCHAR2) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING GROUP_NUMBER_IMPL;
/
Запрос
SELECT GROUP_NUMBER(O.OBJECT_TYPE) OVER (ORDER BY O.LAST_DDL_TIME, ROWNUM) ГР
, O.OBJECT_TYPE
, TO_CHAR(O.LAST_DDL_TIME, 'DD.MM.YYYY HH24:MI:SS') LAST_DDL_TIME
FROM (
SELECT O.*
FROM ALL_OBJECTS O
WHERE ROWNUM < 20
) O
;
Возвращает неожиданный результат
ГР OBJECT_TYPE LAST_DDL_TIME
---------- ------------------- -------------------
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 TABLE 13.08.2009 23:00:54
8 CLUSTER 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 TABLE 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 TABLE 13.08.2009 23:00:54
8 TABLE 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
9 TABLE 13.08.2009 23:09:45
9 TABLE 13.08.2009 23:18:04
19 rows selected
А вот ожидаемый результат
SELECT GROUP_NUMBER(O.OBJECT_TYPE) OVER (ORDER BY O.LAST_DDL_TIME, ROWNUM) ГР
, O.OBJECT_TYPE
, TO_CHAR(O.LAST_DDL_TIME, 'DD.MM.YYYY HH24:MI:SS') LAST_DDL_TIME
FROM (
SELECT O.*
FROM ALL_OBJECTS O
WHERE ROWNUM < 20
) O
;
ГР OBJECT_TYPE LAST_DDL_TIME
---------- ------------------- -------------------
1 INDEX 13.08.2009 23:00:54
2 TABLE 13.08.2009 23:00:54
3 CLUSTER 13.08.2009 23:00:54
4 INDEX 13.08.2009 23:00:54
5 TABLE 13.08.2009 23:00:54
6 INDEX 13.08.2009 23:00:54
6 INDEX 13.08.2009 23:00:54
6 INDEX 13.08.2009 23:00:54
6 INDEX 13.08.2009 23:00:54
7 TABLE 13.08.2009 23:00:54
7 TABLE 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
8 INDEX 13.08.2009 23:00:54
9 TABLE 13.08.2009 23:09:45
9 TABLE 13.08.2009 23:18:04
19 rows selected
+2
Копипаст подвёл. Первый запрос должен выглядеть так. Т.е. без ROWNUM в ORDER BY
SELECT GROUP_NUMBER(O.OBJECT_TYPE) OVER (ORDER BY O.LAST_DDL_TIME) ГР
, O.OBJECT_TYPE
, TO_CHAR(O.LAST_DDL_TIME, 'DD.MM.YYYY HH24:MI:SS') LAST_DDL_TIME
FROM (
SELECT O.*
FROM ALL_OBJECTS O
WHERE ROWNUM < 20
) O
;
+1
Есть такое дело, тоже натыкался. Спасибо, что напомнили, это важный момент.
И PostgreSQL себя точно так же ведет, кстати.
И PostgreSQL себя точно так же ведет, кстати.
0
И это правильно, иначе попробуйте представить себе что должен был бы тогда возвращать SUM()OVER() в следующем запросе:
Если бы мы хотели добавить агрегировать и при изменении N то должны были бы и показать/определить порядок сортировки при этой агрегации, например:
with o(c, n) as (
select 'A', 1 from dual union all
select 'A', 2 from dual union all
select 'A', 3 from dual union all
select 'B', 1 from dual union all
select 'C', 1 from dual union all
select 'C', 2 from dual union all
select 'C', 2 from dual union all
select 'C', 3 from dual union all
select 'C', 4 from dual
)
SELECT c,n
,sum(n)over(order by c) n_summ
FROM O;
Если бы мы хотели добавить агрегировать и при изменении N то должны были бы и показать/определить порядок сортировки при этой агрегации, например:
sum(n)over(order by c, n desc) n_summ
0
Это ожидаемый результат, из доки:
У такого окна изменение LAST_DDL_TIME и есть последний шаг агрегации. Функция-то все-таки агрегатная, если бы она возвращала каждый раз разные значения, это уже не агрегатной функцией было бы.
Кстати, а чем не подошел dense_rank?
Invoked by Oracle as the last step of aggregate computation.
У такого окна изменение LAST_DDL_TIME и есть последний шаг агрегации. Функция-то все-таки агрегатная, если бы она возвращала каждый раз разные значения, это уже не агрегатной функцией было бы.
Кстати, а чем не подошел dense_rank?
dense_rank()OVER (ORDER BY O.LAST_DDL_TIME,O.OBJECT_TYPE) grp
0
dense_rank делает совсем не то
0
А что именно вы хотите-то? Ваш пример недетерминирован. А из словесного описания функции как раз напрашивается dense_rank.
0
Сейчас постараюсь придумать живой пример…
Например, есть навигационные данные с транспортного средства. Необходимо выбрать все интервалы превышения скорости и проанализировать их: время превышения, средняя скорость, пробег и т.д.
Запрос будет выглядеть примерно так:
Стандартно присвоение группы делается 2мя аналитическими надзапросами LAG + SUM. А здесь написал функцию. Кстати, проверил функция примерно в 2 раза медленнее чем LAG + SUM
Например, есть навигационные данные с транспортного средства. Необходимо выбрать все интервалы превышения скорости и проанализировать их: время превышения, средняя скорость, пробег и т.д.
Запрос будет выглядеть примерно так:
WITH ALL_DATA AS (
SELECT TO_DATE('01.01.2018 00:00:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 5 SPEED, 0.01 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:01:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 25 SPEED, 0.1 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:02:31', 'DD.MM.YYYY HH24:MI:SS') SDATE, 61 SPEED, 0.3 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:02:58', 'DD.MM.YYYY HH24:MI:SS') SDATE, 68 SPEED, 0.3 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:05:01', 'DD.MM.YYYY HH24:MI:SS') SDATE, 45 SPEED, 0.1 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:06:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 25 SPEED, 0.1 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:07:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 25 SPEED, 0.1 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:08:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 70 SPEED, 0.9 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:09:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 75 SPEED, 0.9 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:10:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 78 SPEED, 0.9 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:11:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 50 SPEED, 0.1 DISTANCE FROM DUAL
)
, T AS (
SELECT SDATE, SPEED, DISTANCE
, GROUP_NUMBER(CASE WHEN D.SPEED > 60 THEN 1 ELSE 0 END) OVER (ORDER BY D.SDATE) ГР
FROM ALL_DATA D
)
SELECT ГР, SUM(DISTANCE) DISTANCE
, (MAX(SDATE) - MIN(SDATE)) * 24 * 60 TIME_MIN
, SUM(DISTANCE) / NULLIF((MAX(SDATE) - MIN(SDATE)) * 24, 0) AVG_SPEED
FROM T
WHERE SPEED > 60
GROUP BY ГР
Стандартно присвоение группы делается 2мя аналитическими надзапросами LAG + SUM. А здесь написал функцию. Кстати, проверил функция примерно в 2 раза медленнее чем LAG + SUM
0
А, понятно, старый добрый start-of-group :) кстати, конкретно этот пример и легче, и быстрее, и понятнее решается с помощью pattern matching:
pattern matching
WITH ALL_DATA AS (
SELECT TO_DATE('01.01.2018 00:00:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 5 SPEED, 0.01 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:01:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 25 SPEED, 0.1 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:02:31', 'DD.MM.YYYY HH24:MI:SS') SDATE, 61 SPEED, 0.3 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:02:58', 'DD.MM.YYYY HH24:MI:SS') SDATE, 68 SPEED, 0.3 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:05:01', 'DD.MM.YYYY HH24:MI:SS') SDATE, 45 SPEED, 0.1 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:06:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 25 SPEED, 0.1 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:07:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 25 SPEED, 0.1 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:08:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 70 SPEED, 0.9 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:09:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 75 SPEED, 0.9 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:10:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 78 SPEED, 0.9 DISTANCE FROM DUAL
UNION ALL SELECT TO_DATE('01.01.2018 00:11:28', 'DD.MM.YYYY HH24:MI:SS') SDATE, 50 SPEED, 0.1 DISTANCE FROM DUAL
)
, T AS (
SELECT SDATE, SPEED, DISTANCE
, GROUP_NUMBER(CASE WHEN D.SPEED > 60 THEN 1 ELSE 0 END) OVER (ORDER BY D.SDATE) ГР
,dense_rank()over(ORDER BY D.SDATE, CASE WHEN D.SPEED > 60 THEN 1 ELSE 0 END) ГР2
,CASE WHEN SPEED > 60 THEN 1 ELSE 0 END as FLAG
FROM ALL_DATA D
)
select
*
from t
MATCH_RECOGNIZE (
ORDER by SDATE
MEASURES
speeding.SDATE as speeding_start,
last(sdate) as speeding_end,
count(*) as points,
sum(distance) as distance,
(last(sdate)-first(sdate))*24*60 as TIME_MIN,
sum(distance)/nullif((last(sdate)-first(sdate))*24,0) as AVG_SPEED
PATTERN (speeding+)
DEFINE speeding as (speeding.SPEED>60)
)
+1
Если помните, функцию ODCIAggregateMerge мы уже написали в самом начале, поскольку в Oracle она является обязательной. Документация настаивает, что эта функция необходима не только для параллельной работы, но и для последовательной — хотя мне трудно понять, зачем (и на практике не приходилось сталкиваться с ее выполнением при последовательной обработке).
ODCIAggregateMerge используется для GROUP BY ROLLUP
The ODCIAggregateMerge() interface is invoked to compute super aggregate values in such roll-up operations.
+2
А медиану можно так написать?
И какие-нибудь примеры, которые нельзя переписать через встроенные агрегатные функции, можете привести?
И какие-нибудь примеры, которые нельзя переписать через встроенные агрегатные функции, можете привести?
0
Вот почему этому не обучают в наших замечательных ВУЗах?!. У меня ни один кандидат еще не ответил, что такое оконные функции, в глаза не видел оператора OVER... И даже не пытаются подумать - я не знаю, ни разу не слышал...
0
Sign up to leave a comment.
Пользовательские агрегатные и оконные функции в PostgreSQL и Oracle