erogov Mar 13 2018 at 12:46

Пользовательские агрегатные и оконные функции в PostgreSQL и Oracle

17 min

43K

Postgres Professional corporate blog Oracle *PostgreSQL *SQL *

+39

Comments 29

little-brother Mar 13 2018 at 14:33

Спасибо за статью.
Еще бы пример как с производительностью у пользовательских функций, хотя бы в сравнении с какой либо стандартной.

erogov Mar 13 2018 at 15:43

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

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

А вот если написать свою в Oracle на чем-нибудь компилируемом, то не знаю, не пробовал. Предполагаю, что будет чуть проигрывать.

RPG18 Mar 13 2018 at 17:08

Вроде же можно PL/V8 на случай математики с их JIT? Ваш коллега Иван Панченко на PGDay 15 и PGConf 18 да же бенчмарки давал.

erogov Mar 13 2018 at 17:30

Можно и так, там все равно, на чем функции написаны. Но надо пробовать, конечно, потому что от подводных граблей никто не застрахован.

zoroda Mar 14 2018 at 06:15

Буквально на прошлой неделе решал задачу корректного отображения, например, доли от итоговой суммы. Решил делать через агрегатные функции.

Код

-- Объявление агрегатной функции
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;

zoroda Mar 14 2018 at 06:40

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

erogov Mar 14 2018 at 12:37

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

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

zoroda Mar 15 2018 at 05:54

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

даём пользователю возможность ввести человекопонятную формулу без необходимости проверки на 0/null
можем проверить её корректность на этапе при сохранении
разрешаем использовать только те агрегатные функции, которые доступны в схеме calc

erogov Mar 15 2018 at 11:54

Идея понятная, цель благая.
В плане «поворчать» могу обратить внимание на пару моментов.
Во-первых, все абстракции текут. Например, стоит ошибиться с названием параметра и получим странное поведение (молчаливый null вместо сообщения об ошибке) — если, конечно, это не было предусмотрено в дополнение к регулярке.
Ну и во-вторых, с отдельными типами эта конструкция работала бы ничуть не сложнее и не хуже (:

ruslan_shv Mar 14 2018 at 11:25

как потом дебажить эти функции?

erogov Mar 14 2018 at 12:40

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

asmm Mar 14 2018 at 13:07

В Oracle прекрасно дебажится обычными методами в PL/SQL Developer

asmm Mar 14 2018 at 13:19

Заметил особенность реализации аналитических функций в 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

asmm Mar 14 2018 at 13:27

Копипаст подвёл. Первый запрос должен выглядеть так. Т.е. без 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
;

erogov Mar 14 2018 at 14:35

Есть такое дело, тоже натыкался. Спасибо, что напомнили, это важный момент.
И PostgreSQL себя точно так же ведет, кстати.

xtender Mar 17 2018 at 16:16

И это правильно, иначе попробуйте представить себе что должен был бы тогда возвращать 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

erogov Mar 17 2018 at 22:13

Правильно, спору нет.
Но интуитивно (а точнее — по аналогии с обычным ORDER BY) кажется, что если точный порядок не указан, то подразумевается какой-нибудь, не важно какой конкретно. Собственно, добавление ROWNUM ровно к этому и приводит.

xtender Mar 17 2018 at 16:07

Это ожидаемый результат, из доки:

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

asmm Mar 19 2018 at 08:27

dense_rank делает совсем не то

xtender Mar 19 2018 at 08:41

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

asmm Mar 19 2018 at 11:18

Сейчас постараюсь придумать живой пример…
Например, есть навигационные данные с транспортного средства. Необходимо выбрать все интервалы превышения скорости и проанализировать их: время превышения, средняя скорость, пробег и т.д.
Запрос будет выглядеть примерно так:

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

xtender Mar 19 2018 at 13:25

А, понятно, старый добрый 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)
)

asmm Mar 14 2018 at 14:05

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

ODCIAggregateMerge используется для GROUP BY ROLLUP

The ODCIAggregateMerge() interface is invoked to compute super aggregate values in such roll-up operations.

erogov Mar 14 2018 at 14:49

Вот оно что, оказывается! Действительно, это логично. Спасибо, теперь буду знать.
PostgreSQL так пока не умеет, он по нескольку раз считает.

petropavel Apr 10 2018 at 00:19

А медиану можно так написать?

И какие-нибудь примеры, которые нельзя переписать через встроенные агрегатные функции, можете привести?

erogov Apr 10 2018 at 13:17

Медиана уже есть готовая в обоих системах. А так-то да, можно и медиану изобразить (в режиме ORDER BY).

А пример, собственно, приведен в конце статьи.

dnazarov007 May 3 2022 at 08:35

Вот почему этому не обучают в наших замечательных ВУЗах?!. У меня ни один кандидат еще не ответил, что такое оконные функции, в глаза не видел оператора OVER... И даже не пытаются подумать - я не знаю, ни разу не слышал...

erogov May 3 2022 at 11:04

В каких-то учат, но без практики эти знания забываются примерно сразу после зачёта.

RPG18 May 3 2022 at 17:59

Я уже не могу вспомнить, когда в последний раз проходилось пользоваться оконными функциями.