Спасибо за статью.
Еще бы пример как с производительностью у пользовательских функций, хотя бы в сравнении с какой либо стандартной.
Если сравнивать свою функцию, написанную на PL/SQL (тем более на PL/pgSQL) со стандартной, то все будет довольно печально. Так что если есть стандартная функция, то изобретать велосипед не надо (:

Если написать свою на C в PostgreSQL, то будет ровно одно и то же.

А вот если написать свою в Oracle на чем-нибудь компилируемом, то не знаю, не пробовал. Предполагаю, что будет чуть проигрывать.
Вроде же можно PL/V8 на случай математики с их JIT? Ваш коллега Иван Панченко на PGDay 15 и PGConf 18 да же бенчмарки давал.
Можно и так, там все равно, на чем функции написаны. Но надо пробовать, конечно, потому что от подводных граблей никто не застрахован.
Буквально на прошлой неделе решал задачу корректного отображения, например, доли от итоговой суммы. Решил делать через агрегатные функции.
Код
-- Объявление агрегатной функции
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;


Точнее, в этом примере считается итоговый процент отношения одного показателя к другому.

Ага, можно так. Мне, правда, не очень нравится этот трюк с массивом: экономим на создании типа (а зачем?), но ведь код получается непонятный.
Кстати, как вариант, можно было бы обойтись обычной суммой: sum(...) / nullif(sum(...), 0).


А для отношения доли к сумме в Oracle даже есть стандартная функция ratio_to_report.

Поясню. Трюк с массивом и использование самописной агрегатной функции нужны были для того, чтобы сделать интерфейс администратора для настройки этого приложения. Таким образом, в админке можно написать формулу расчёта показателя:
=percent(PCurrent, PTotal)
Эта формула легко регулярными выражениями приводится к виду
calc.percent(array[indicators->>'PCurrent', indicators->>'PTotal']), который подставляется в текст создаваемого представления.
Так как подобных функций набирается как минимум с десяток и в них может быть более 2 параметров (присутствуют более сложные методики расчёта), решил не создавать типы для каждой из функций (впрочем, теперь это моё решение не кажется таким однозначным).
В результате:
  • даём пользователю возможность ввести человекопонятную формулу без необходимости проверки на 0/null
  • можем проверить её корректность на этапе при сохранении
  • разрешаем использовать только те агрегатные функции, которые доступны в схеме calc
Идея понятная, цель благая.
В плане «поворчать» могу обратить внимание на пару моментов.
Во-первых, все абстракции текут. Например, стоит ошибиться с названием параметра и получим странное поведение (молчаливый null вместо сообщения об ошибке) — если, конечно, это не было предусмотрено в дополнение к регулярке.
Ну и во-вторых, с отдельными типами эта конструкция работала бы ничуть не сложнее и не хуже (:
как потом дебажить эти функции?

А какие сложности? Отдельные функции же можно вызывать самостоятельно, вне агрегата. Так что при необходимости для них легко написать любые тесты.

В Oracle прекрасно дебажится обычными методами в PL/SQL Developer
Заметил особенность реализации аналитических функций в Oracle. Если в OVER() используем ORDER BY, то в ODCIAggregateTerminate зайдёт только при смене значения в ORDER BY. Надо об этом помнить, а то можно получить совсем неожиданные результаты.
Пример:
Функция 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
Копипаст подвёл. Первый запрос должен выглядеть так. Т.е. без 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
;

Есть такое дело, тоже натыкался. Спасибо, что напомнили, это важный момент.
И PostgreSQL себя точно так же ведет, кстати.
И это правильно, иначе попробуйте представить себе что должен был бы тогда возвращать SUM()OVER() в следующем запросе:
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
Правильно, спору нет.
Но интуитивно (а точнее — по аналогии с обычным ORDER BY) кажется, что если точный порядок не указан, то подразумевается какой-нибудь, не важно какой конкретно. Собственно, добавление ROWNUM ровно к этому и приводит.
Это ожидаемый результат, из доки:
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
dense_rank делает совсем не то

А что именно вы хотите-то? Ваш пример недетерминирован. А из словесного описания функции как раз напрашивается dense_rank.

Сейчас постараюсь придумать живой пример…
Например, есть навигационные данные с транспортного средства. Необходимо выбрать все интервалы превышения скорости и проанализировать их: время превышения, средняя скорость, пробег и т.д.
Запрос будет выглядеть примерно так:
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
А, понятно, старый добрый 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)
)

Если помните, функцию ODCIAggregateMerge мы уже написали в самом начале, поскольку в Oracle она является обязательной. Документация настаивает, что эта функция необходима не только для параллельной работы, но и для последовательной — хотя мне трудно понять, зачем (и на практике не приходилось сталкиваться с ее выполнением при последовательной обработке).

ODCIAggregateMerge используется для GROUP BY ROLLUP
The ODCIAggregateMerge() interface is invoked to compute super aggregate values in such roll-up operations.
Вот оно что, оказывается! Действительно, это логично. Спасибо, теперь буду знать.
PostgreSQL так пока не умеет, он по нескольку раз считает.
А медиану можно так написать?

И какие-нибудь примеры, которые нельзя переписать через встроенные агрегатные функции, можете привести?
Медиана уже есть готовая в обоих системах. А так-то да, можно и медиану изобразить (в режиме ORDER BY).

А пример, собственно, приведен в конце статьи.
Только полноправные пользователи могут оставлять комментарии.
Войдите, пожалуйста.