SQL: типы функций

 Классификация функций:

• Детерминированные и недетерминированные
• Скалярные и агрегатные

  Функции - это особый тип команды в наборе команд SQL, а каждый диалект имеет свою реализацию набора команд. В результате можно сказать, что функции - это команды, состоящие из одного слова и возвращающие одиночное значение. Значение функции может зависеть от входных параметров, как, например, в случае функции AVG(), вычисляющей среднее значение в списке значений в базе данных. Однако многие функции не используют никаких входных параметров например, функция, возвращающая текущее системное время - CURRENT TIME.

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

ТИПЫ ФУНКЦИЙ

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

Детерминированные и недетермированные функции:

Функции могут быть детерминированные и недетерминированные. Детерминированная функция всегда возвращает один и тот же результат при одном и том же наборе входных значений. Недетерминированные функции могут возвращать разные результаты при разных вызовах, даже если им передаются одинаковые входные значения.
  Почему так важно, чтобы при одинаковых входных параметрах получались одинаковые результаты? Это важно потому, что это определяет способ использования функций в представлениях, пользовательских функциях и хранимых процедурах. Ограничения на разных платформах могут быть разными, но иногда в этих объектах можно использовать только детерминированные функции. Например, SQL Server может создавать индекс по выражению в столбце, если только это выражение не содержит недетерминированных функций. Правила и ограничения на разных платформах разные, потому обращайтесь при использовании функций к документации производителей.

Скалярные и агрегатные функции:
Еще один способ классификации функций - по их возможности работы только с одной строкой, с коллекцией значений или с наборами строк. Агрегатные функции работают с коллекцией значений и возвращают одно суммарное значение. Скалярные функции возвращают одно значение, зависящее от скалярных входных аргументов. Некоторые скалярные функции, например CURRENTTIME, не требуют никаких аргументов.

Скалярные:
Числовые функции
Строковые функции
Встроенные функции
Функции даты и времени
Агрегатные:
Оконные(аналитические) функции
Групповые функции
Унарные функции
Бинарные функции
Гипотетические функции 
Функции обратного распределения

Числовые функции
...

Строковые функции

• ascii(string) - возвращает код первого символа, эта функция обратна функции CHR;
• chr(x) - возвращает символ с номером х, в MySQL это функция char;
• length(string) - возвращает длину строки;
• lower(string) - понижает регистр букв;
• upper(string) - повышает регистр букв;
• ltrim(string1[, string2]) - удаляет слева из первой строки все символы встречающиеся во второй строке. Если вторая строка отсутствует, то удаляются пробелы. В MySQL второй аргумент не поддерживается;
• rtrim(string1[, string2]) - аналогична функции ltrim, только удаление происходит справа;
• trim(string) - удаляет пробелы с обоих концов строки;
• lpad(string1, n[, string2]) - дополняет первую строку слева n символами из второй строки, при необходимости вторая строка дублируется. Если string2 не указана, то используется пробел;
• rpad(string1, n[, string2]) - аналогична функции lpad, только присоединение происходит справа;
• replace(string1, c1, c2) - заменяет все вхождения символа/подстроки c1 на c2. Для простого удаления всех вхождений c1, в качестве третьего аргумента надо указать пустую строку (''). В Oracle третий аргумент не обязателен, и по умолчанию равен пустой строке;
• instr(string1, string2[, a][, b]) - возвращает b вхождение строки string2 в строке string1 начиная с позиции a. Если a отрицательно, то поиск происходит справа. По умолчанию a и b присваиваются значение 1. В MySQL последние два аргумента не поддерживаются. В PostgreSQL данной функции нет, однако ее реализация дана в документации, как раз для совместимости с Oracle;
• substr(string, pos, len) - возвращает подстрку с позиции pos и длины len.

Встроенные функции

...

Функции даты и времени

• current_date - глобальная переменная содержащая текущую дату. Можно использовать и в многих СУБД;
• trunc(d,s) - приводит дату к началу указанной временной отметки, например к началу месяца. В PostgreSQL есть аналогичная функция date_trunc(s,d). В MySQL для этих целей может использоваться функция date_format(d,s), но она возвращает результат в виде строки;
• add_months(d,n) - добавляет к дате указанное число месяцев;
• last_day(d) - последний день месяца, содержащегося в аргументе;
• months_between(d1,d2) - возвращает число месяцев между датами.

Ниже приведены допустимые форматы в строковом параметре s для функций trunc и date_trunc соответственно:

• квартал - q, quarter;
• год - yyyy, year;
• месяц - mm, month;
• неделя - ww, week;
• день - dd, day;
• час - hh, hour;
• минута - mi, minute.

Оконные(аналитические) функции

...

Групповые функции

...

Унарные функции

...

Бинарные функции

...

Гипотетические функции 

...

Функции обратного распределения

Существует всего две обратные функции percentile_cont и percentile_disk. Обе функции принимают аргументы в диапазоне от 0 до 1.