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

В этом разделе описаны встроенные функции SQL. Функции можно разделить на три типа — скалярные, агрегатные и оконные (аналитические) функции.

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

Агрегатные функции выполняют вычисление на наборе значений и возвращают одиночное значение.

Оконные функции (также известные как аналитические функции) являются своего рода агрегатными функциями, не уменьшающими степень детализации. При этом агрегированные данные выводятся вместе с неагрегированными.

36.1. Скалярные функции

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

RDB$GET_CONTEXT

Функция для работы с контекстными переменными. Функция возвращает значение типа VARCHAR(N) контекстной переменной одного из пространств имен:

SYSTEM — предоставляет доступ к системным контекстным переменным. Эти переменные доступны только для чтения;
USER_SESSION — предоставляет доступ к пользовательским контекстным переменным, заданным через функцию RDB$SET_CONTEXT. Переменные существуют в течение подключения;
USER_TRANSACTION — предоставляет доступ к пользовательским контекстным переменным, заданным через функцию RDB$SET_CONTEXT. Переменные существуют в течение транзакции;
DDL_TRIGGER — предоставляет доступ к системным контекстным переменным, доступным только во время выполнения DDL триггера. Эти переменные доступны только для чтения;
AUTHDATA — предоставляет доступ к информации об аутентификации и ФИО пользователя.

Длина возвращаемого значения (N) определяется исходя из размера фактических данных. По умолчанию используется VARCHAR(8192).

Функция RDB$GET_CONTEXT является заранее объявленной UDF, поэтому для вызова не требуется писать в базе объявление.

Листинг 36.1 Синтаксис функции RDB$GET_CONTEXT

RDB$GET_CONTEXT ('пространство имен', '<имя переменной>')

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

Пространства имен USER_SESSION и USER_TRANSACTION изначально пусты. Пользователь может создать и установить значение переменных в них функцией RDB$SET_CONTEXT и получить их значения из функции RDB$GET_CONTEXT.

Пространство имен SYSTEM доступно только для чтения. Оно содержит много предопределенных переменных, показанных в таблице 36.1.

Таблица 36.1 Контекстные переменные в пространстве имён `SYSTEM`
Имя переменной	Описание
ENGINE_VERSION	Версия сервера (например, 5.0.0)
FULL_VERSION	Полная версия сборки СУБД (например, `WI-V3.0.11.0 RedDatabase 5.0 SNAPSHOT.16` (`9ec7320661241a96270a45741e9aae609d024ade`))
EDITION	Установленная редакция СУБД РЕД База Данных: `Open`, `Standard` или `Enterprise`
DB_NAME	Полный путь к базе данных или, если подключение через путь запрещено, алиас
GLOBAL_CN	Последнее значение текущего глобального счётчика Commit Number
EXT_CONN_POOL_SIZE	Размер пула внешних соединений
EXT_CONN_POOL_IDLE_COUNT	Текущее количество неактивных соединений в пуле внешних соединений.
EXT_CONN_POOL_ACTIVE_COUNT	Текущее количество активных соединений в пуле внешних соединений.
EXT_CONN_POOL_LIFETIME	Время жизни неактивных соединений в пуле внешних соединений.
REPLICATING	Настроена ли репликация базы данных. Если настроена, то возвращает значение `TRUE`, если не настроена — `FALSE`.
REPLICATION_SEQUENCE	Текущее значение последовательности репликации (номер последнего сегмента, записанного в журнал репликации).
DB_GUID	`GUID` текущей базы данных.
DB_FILE_ID	Идентификатор текущей базы данных на уровне файловой системы.
REPLICA_MODE	Режим реплики базы данных: '`READ-ONLY`', '`READ-WRITE`' и `NULL`.
EDITION	Установленная редакция СУБД РЕД База Данных: `Open`, `Standard` или `Enterprise`.
SESSION_ID	Глобальная переменная `CURRENT_CONNECTION`
NETWORK_PROTOCOL	Протокол, используемый в соединении с базой данных: '`TCPv4`', '`WNET`', '`XNET`' или `NULL`
WIRE_COMPRESSED	Используется ли сжатие сетевого трафика. Если используется сжатие сетевого трафика возвращает `TRUE`, если не используется — `FALSE`. Для встроенных соединений — возвращает `NULL`.
WIRE_ENCRYPTED	Используется ли шифрование сетевого трафика. Если используется шифрование сетевого трафика возвращает `TRUE`, если не используется — `FALSE`. Для встроенных соединений — возвращает `NULL`.
WIRE_CRYPT_PLUGIN	Если соединение зашифровано - возвращает имя текущего плагина, иначе `NULL`.
CLIENT_ADDRESS	Для TCPv4 — IP адрес, для XNET — локальный ID процесса. Для всех остальных протоколов переменная имеет значение `NULL`
CLIENT_HOST	Имя хоста удаленного клиента. Значение возвращается для всех поддерживаемых протоколов.
CLIENT_PID	PID процесса на клиентском компьютере.
CLIENT_PROCESS	Полный путь к клиентскому приложению, подключившемуся к базе данных. Позволяет не использовать системную таблицу `MON$ATTACHMENTS` (поле `MON$REMOTE_PROCESS`)
CURRENT_USER	Глобальная переменная `CURRENT_USER`
CURRENT_ROLE	Глобальная переменная `CURRENT_ROLE`
SESSION_IDLE_TIMEOUT	Содержит текущее значение тайм-аут простоя соединения в секундах, который был установлен на уровне соединения, или ноль, если тайм-аут не был установлен.
STATEMENT_TIMEOUT	Содержит текущее значение тайм-аута выполнения оператора в миллисекундах, который был установлен на уровне подключения, или ноль, если тайм-аут не был установлен.
CURRENT_ROLES	Действующие в данный момент роли пользователя
LDAP_ROLES	Роли пользователя, полученные из LDAP
LDAP_ROLES_DN	DN ролей пользователя, полученных из LDAP
EFFECTIVE_USER	Эффективный пользователь в текущий момент. Указывает пользователя с привилегиями которого в текущий момент времени выполняется процедура, функция или триггер.
SESSION_TIMEZONE	Часовой пояс текущего соединения.
TRANSACTION_ID	Глобальная переменная `CURRENT_TRANSACTION`
ISOLATION_LEVEL	Уровень изоляции текущей транзакции `CURRENT_TRANSACTION`: '`READ COMMITTED`', '`SNAPSHOT`' или '`CONSISTENCY`'
LOCK_TIMEOUT	Время ожидания транзакцией высвобождения ресурса при блокировке (в секундах)
READ_ONLY	Является ли транзакция только для чтения. Если является, то значений `TRUE`, если нет — `FALSE`
SNAPSHOT_NUMBER	Номер моментального снимка базы данных: уровня транзакции (для транзакции `SNAPSHOT` или `CONSISTENCY`) или уровня запроса (для транзакции `READ COMMITTED READ CONSISTENCY`). `NULL`, если моментальный снимок не существует.
PAGES_ALLOCATED	Количество страниц, выделенных для базы данных.
PAGES_USED	Количество страниц, используемых базой данных
PAGES_FREE	Количество свободных страниц в базе данных.

Если запрошенная переменная существует в данном пространстве имен, то будет возвращено её значение в виде строки с длиной по умолчанию 8192 символа. Обращение к несуществующему пространству имён или несуществующей переменной в пространстве имен SYSTEM приведёт к ошибке. Если Вы опрашиваете несуществующую переменную в одном из пространств имен USER_SESSION и USER_TRANSACTION, функция вернёт NULL.

Использование пространства имён DDL_TRIGGER допустимо, только во время работы DDL триггера. Его использование также допустимо в хранимых процедурах и функциях, вызванных триггерами DDL.

Контекст DDL_TRIGGER работает как стек. Перед возбуждением DDL триггера, значения, относящиеся к выполняемой команде, помещаются в этот стек. После завершения работы триггера значения выталкиваются. Таким образом. В случае каскадных DDL операторов, когда каждая пользовательская DDL команда возбуждает DDL триггер, и этот триггер запускает другие DDL команды, с помощью EXECUTE STATEMENT, значения переменных в пространстве имён DDL_TRIGGER будут соответствовать команде, которая вызвала последний DDL триггер в стеке вызовов.

Пространство имен DDL_TRIGGER доступно только для чтения. Оно содержит много предопределенных переменных, показанных в таблице 36.2.

Таблица 36.2 Контекстные переменные в пространстве имён `DDL_TRIGGER`
Имя переменной	Описание
EVENT_TYPE	Тип события (`CREATE, ALTER, DROP`)
OBJECT_TYPE	Тип объекта (`TABLE, VIEW` и др.)
DDL_EVENT	Имя события. `DDL_EVENT = EVENT_TYPE \|\| ' ' \|\| OBJECT_TYPE`
OBJECT_NAME	Имя объекта метаданных
OLD_OBJECT_NAME	Имя объекта метаданных до переименования
NEW_OBJECT_NAME	Имя объекта метаданных после переименования
SQL_TEXT	Текст SQL запроса

Пространство имен AUTHDATA доступно только для чтения. Оно содержит предопределенные переменные, показанные в таблице 36.3.

Таблица 36.3 Контекстные переменные в пространстве имён `AUTHDATA`
Имя переменной	Описание
AUTH_TYPE	Тип аутентификации: `SECURITY` или `LDAP`
AUTH_PLUGIN	Плагин аутентификации: Legacy_Auth; Srp; GostPassword; Win_Sspi; Gss; Certificate.
USER_FIRST_NAME	Дополнительная информация: имя пользователя. При аутентификации через LDAP они считываются из атрибута пользователя "CN".
USER_MIDDLE_NAME	Дополнительная информация: отчество пользователя.При аутентификации через LDAP они считываются из атрибута пользователя "CN".
USER_LAST_NAME	Дополнительная информация: фамилия пользователя. При аутентификации через LDAP они считываются из атрибута пользователя "CN".
LDAP_SERVER	Адрес сервера.

См. также функцию RDB$SET_CONTEXT.

RDB$SET_CONTEXT

Функция для работы с контекстными переменными. Функция создает переменную, устанавливает ее значение или обнуляет в одном из используемых пользователями для записи пространстве имён: USER_SESSION, USER_TRANSACTION.

В рамках одного соединения может быть максимум 1000 переменных. Является заранее объявленной UDF, поэтому для вызова не требуется писать в базе объявление.

Листинг 36.2 Синтаксис функции RDB$SET_CONTEXT

RDB$SET_CONTEXT ('USER_SESSION'|'USER_TRANSACTION', '<имя переменной>', '<значение переменной>' | NULL)

Параметр <имя переменной> — регистрочувтвительная строка с максимальной длиной 80 символов. Параметр <значение переменной> — значение любого типа, приводимое к типу VARCHAR(N). Длина строки (N) определяется на этапе подготовки запроса по фактически переданному аргументу. Если тип аргумента на этапе подготовки не определен (используется параметр), то по умолчанию используется тип VARCHAR(8192).

Пространства имен USER_SESSION и USER_TRANSACTION изначально пусты. Пользователь может создать и установить значение переменных в них функцией RDB$SET_CONTEXT и получить их значения из функции RDB$GET_CONTEXT. Контекст USER_SESSION связан с текущим соединением. Переменные в USER_TRANSACTION существуют только в рамках транзакции, в которой они были созданы. Все переменные в этом пространстве имён сохраняются при ROLLBACK RETAIN или ROLLBACK TO SAVEPOINT, независимо от того, в какой точке во время выполнения транзакции они были установлены. При завершении транзакции (при её подтверждении или отмене) контекст и все переменные, созданные в ней, уничтожаются.

Функция возвращает только два значения типа INTEGER: 1 — если переменная уже существовала и 0 — если не существовала.

Для удаления переменной надо установить её значение в NULL. Если данное пространство имен не существует, то функция вернёт ошибку.

См. также функцию RDB$GET_CONTEXT.

Функции для работы с файлами

CREATE_FILE

Создает файл в директории, прописанной в directories.conf, и заполняет его BLOB данными.

Листинг 36.3 Синтаксис функции CREATE_FILE

CREATE_FILE(<псевдоним директории>, <имя файла>, <BLOB-данные>)

Перед вызовом этой функции нужно в directories.conf в секции blobs прописать алиас каталога (<псевдоним директории>), в котором будут храниться созданные файлы с BLOB данными, с указанием реального пути к нему. Если данной директории не существует, функция создаст ее.

Функция создает файл такого формата:

<псевдоним директории>/<дата>/<имя файла>-<рандом>.<расширение>,

где

<дата> — текущая дата в формате YYYYMMDD
<имя файла> — исходное имя файла
<расширение> — исходное расширение файла
<рандом> — 22 рандомных символа в кодировке BASE64

Данная строка возвращается в качестве результата.

Пример

В директории с псевдонимом test_dir (путь к которой прописан в directories.conf в секции blobs) создадим текстовый файл text.txt с BLOB-данными. Для этого выполним команду:

select CREATE_FILE('test_dir', 'text.txt', cast('Hello World!' as blob))
from rdb$database;
------------------------------------------
test_dir/20150708/text-UnceByjB8Nba1Bbo6+h9lS.txt

См. также функции READ_FILE, DELETE_FILE.

DELETE_FILE

Удаляет файл из директории, прописанной в directories.conf в секции blobs. Ее синтаксис:

Листинг 36.4 Синтаксис функции DELETE_FILE

DELETE_FILE(<файл с BLOB>)

Параметр <файл с BLOB> — это строка в формате: <псевдоним директории>/<имя файла>. Удаление выполняется в момент подтверждения транзакции.

select DELETE_FILE('test_dir/20150708/text-UnceByjB8Nba1Bbo6+h9lS.txt')
from rdb$database

См. также функции CREATE_FILE, READ_FILE.

READ_FILE

Функция читает файл из директории, прописанной в directories.conf в секции blobs, и возвращает данные типа BLOB. Ее синтаксис:

Листинг 36.5 Синтаксис функции READ_FILE

READ_FILE(<файл с BLOB>)

Параметр <файл с BLOB> — это строка в формате: <псевдоним директории>/<имя файла>.

Перед вызовом этой функции нужно в directories.conf в секции blobs прописать алиас каталога (<псевдоним директории>), в котором хранятся файлы с BLOB данными, с указанием реального пути к нему. Директория должна существовать.

select READ_FILE('test_dir/20150708/text-UnceByjB8Nba1Bbo6+h9lS.txt')
from rdb$database

Предупреждение

Функция не позволяет читать файлы в родительской директории алиаса, указанного в directories.conf.

См. также функции CREATE_FILE, DELETE_FILE.

Функции подсистемы безопасности

CHECK_DDL_RIGHTS

Системная функция проверки DDL прав на объекты. Ее синтаксис:

Листинг 36.6 Синтаксис функции CHECK_DDL_RIGHTS

CHECK_DDL_RIGHTS(<DDL-операция> ON <объект> WITH OWNER <имя пользователя>)

где:

<DDL-операция>: ANY, CREATE, ALTER, DROP
<объект>: TABLE, VIEW, PROCEDURE, FUNCTION, GENERATOR, EXCEPTION, SEQUENCE, DOMAIN, EXCEPTION, ROLE, SHADOW

Функция возвращает TRUE, если пользователь с именем <имя пользователя> имеет какие-либо DDL права на объекты типа <объект> (в эти права не входит право CREATE, рассматриваются права только на существующие объекты). Если указано слово ANY, то пользователь может создавать, удалять и модифицировать любой объект указанного типа.

См. также функцию CHECK_DML_RIGHTS.

CHECK_DML_RIGHTS

Системная функция проверки DML прав на объекты. Ее синтаксис:

Листинг 36.7 Синтаксис функции CHECK_DML_RIGHTS

CHECK_DML_RIGHTS ( <DML-операция> ON <объект> <имя объекта> [, <имя поля>])

где:

<DML-операция>: ANY, INSERT, SELECT, UPDATE, DELETE, GRANT, REFERENCES, EXECUTE
<объект>: TABLE, PROCEDURE, GENERATOR, VIEW
<имя поля> — имя поля для типа объекта TABLE, VIEW

Функция возвращает TRUE, если пользователь имеет какие-либо DML права на объект (чтение, запись, выполнение и т. д.). Если указано слово ANY, то пользователь может выполнять все вышеперечисленные DML-операции над объектом указанного типа.

См. также функцию CHECK_DDL_RIGHTS.

RDB$ROLE_IN_USE

Функция RDB$ROLE_IN_USE возвращает используется ли роль текущим пользователем.

Листинг 36.8 Синтаксис функции RDB$ROLE_IN_USE

RDB$ROLE_IN_USE (<имя роли>)

Тип возвращаемого результата: BOOLEAN

Данная функция позволяет проверить использование любой роли: указанной явно (при входе в систему или изменённой с помощью оператора SET ROLE) и назначенной неявно (роли назначенные пользователю с использованием предложения DEFAULT).

RDB$SYSTEM_PRIVILEGE

Функция RDB$SYSTEM_PRIVILEGE возвращает используется ли системная привилегия текущим соединением.

Листинг 36.9 Синтаксис функции RDB$SYSTEM_PRIVILEGE

RDB$SYSTEM_PRIVILEGE (<системная привилегия>)

Тип возвращаемого результата: BOOLEAN

Список системных привилегий представлен в таблице.

MAKE_DBKEY

MAKE_DBKEY создает значение DBKEY, используя имя или идентификатор таблицы, номер записи и (не обязательно) номера страницы данных и страницы указателей.

Листинг 36.10 Синтаксис функции MAKE_DBKEY

MAKE_DBKEY (<таблица>, <номер записи> [, <номер страницы данных> [, <номер страницы указателей>]])

Параметры функции имеют следующие значения:

Таблица - имя (в одинарных кавычках) или идентификатор таблицы (значение поля RDB$RELATION_ID из таблицы RDB$RELATIONS);
Номер записи - либо абсолютный номер записи (если аргументы <номер страницы данных> и <номер страницы указателей> отсутствуют), либо относительный номер записи (если присутствует аргумент <номер страницы данных>);
Номер страницы данных - может быть либо абсолютным (если аргумент <номер страницы указателей> не задан), либо относительным (если указанный аргумент задан);
Номер страницы указателя - логический номер страницы указателя в таблице.

Примеры работы функции MAKE_DBKEY:

Выбор записи с использованием имени таблицы (обратите внимание, что имя таблицы указано в верхнем регистре)
```
select *
from rdb$relations
where rdb$db_key = make_dbkey('RDB$RELATIONS', 0);
```
Выбор записи с использованием идентификатора таблицы
```
select *
from rdb$relations
where rdb$db_key = make_dbkey(6, 0);
```
Выбор всех записей, физически находящихся на первой странице данных
```
select *
from rdb$relations
where rdb$db_key >= make_dbkey(6, 0, 0) and
      rdb$db_key < make_dbkey(6, 0, 1);
```
Выбор всех записей, физически находящихся на первой странице данных шестой страницы указателей
```
select *
from SOMETABLE
where rdb$db_key >= make_dbkey('SOMETABLE', 0, 0, 5) and
      rdb$db_key < make_dbkey('SOMETABLE', 0, 1, 5);
```

RDB$TRACE_MSG

Функция RDB$TRACE_MSG записывает указанное сообщение в лог-файл.

Примечание

Для работы функции необходимо включить параметр log_message в fbtrace.conf.

Листинг 36.11 Синтаксис функции RDB$TRACE_MSG

RDB$TRACE_MSG('<сообщение>' [, <eol>])

<eol> ::= TRUE | FALSE

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

Параметр eol означает сброс буфера сообщений в лог. По умолчанию он включен (значение true), то есть каждое сообщение из буфера будет записано отдельным событием (MESSAGE) в лог-файл. Если eol выключен, то сообщение будет добавлено в буфер подключения. Сообщения из буфера будут записаны в лог, когда функция RDB$TRACE_MSG будет вызвана с включенным eol или, когда завершится текущий блок PSQL (execute block, функция, процедура).

Максимальный размер буфера составляет 1Мб. Если при вызове RDB$TRACE_MSG содержимое буфера превышает 1Мб, то будет получено сообщение об ошибке, а в лог будут записаны сообщения, добавленные в буфер до превышения лимита.

Функции для обработки ошибок

RDB$ERROR

Возвращает значение контекста активного исключения.

Листинг 36.12 Синтаксис функции RDB$ERROR

RDB$ERROR (<контекст>)

<контекст> ::= { GDSCODE | SQLCODE | SQLSTATE | EXCEPTION | MESSAGE }

Тип возвращаемого значения зависит от контекста:

Если задан контекст EXCEPTION, то функция возвращает имя исключения, если активно исключение определённое пользователем, и NULL если активно одно из системных исключений. Для контекста EXCEPTION тип возвращаемого значения: VARCHAR(63) CHARACTER SET UTF8.
Если задан контекст MESSAGE то функция возвращает интерпретированный текст активного исключения. Для контекста MESSAGE тип возвращаемого значения: VARCHAR(1024) CHARACTER SET UTF8.
Если заданы контексты GDSCODE, SQLCODE или SQLSTATE, то функция возвращает значение соответствующей контекстной переменной.

Примечание

Функция RDB$ERROR всегда возвращает NULL вне блока обработки ошибок WHEN ... DO.

Функции работы с транзакциями

RDB$GET_TRANSACTION_CN

Возвращает номер подтверждения (Commit Number) для заданной транзакции.

Листинг 36.13 Синтаксис функции RDB$GET_TRANSACTION_CN

RDB$GET_TRANSACTION_CN (<номер транзакции>)

Тип возвращаемого результата: BIGINT

Внутренние механизмы РЕД Базы Данных используют беззнаковое 8-байтное целое для Commit Number и беззнаковое 6-байтное целое для номера транзакции. Поэтому, не смотря на то, что язык SQL не имеет беззнаковых целых, а RDB$GET_TRANSACTION_CN возвращает знаковый BIGINT, невозможно увидеть отрицательный номер подтверждения, за исключением нескольких специальных значений, используемых для неподтверждённых транзакций.

Таким образом, числа возвращаемые RDB$GET_TRANSACTION_CN могут иметь следующие значения:

Значение	Описание
-2	Мёртвые транзакции (отмененные)
-1	Зависшие транзакции (в состоянии `limbo 2PC` транзакций)
0	Активные транзакции
1	Для транзакций подтверждённых до старта базы данных или с номером меньше чем OIT (Oldest Interesting Transaction)
>1	Транзакции подтверждённые после старта базы данных
NULL	Если номер транзакции равен `NULL` или больше чем `Next Transaction`

Математические функции

ABS

Функция возвращает абсолютное значение числового параметра с плавающей точкой или параметра, который может быть преобразован в число с плавающей точкой.

Листинг 36.14 Синтаксис функции ABS

ABS(<значение>)

Выходной параметр будет иметь тот же тип, что и у входного аргумента. Если входной параметр имеет значение NULL, то возвращается 0.

ACOS

Функция возвращает арккосинус числового параметра с плавающей точкой или параметра, который может быть преобразован в число с плавающей точкой.

Листинг 36.15 Синтаксис функции ACOS

ACOS(<значение>)

Входной параметр преобразуется в тип данных DOUBLE PRECISION. Может принимать значения от -1 до +1. Выходной параметр — угол в радианах. Возвращаемые значения находятся в диапазоне от 0 до числа $\pi$. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

ACOSH

Функция возвращает обратный гиперболический косинус числового параметра с плавающей точкой или параметра, который может быть преобразован в число с плавающей точкой.

Листинг 36.16 Синтаксис функции ACOSH

ACOSH(<параметр>)

Входной параметр может принимать значения большие или равные 1. Возвращаемые значения находятся в диапазоне от 0 до $+ \infty$. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

ASIN

Функция возвращает арксинус числового параметра с плавающей точкой или параметра, который может быть преобразован в число с плавающей точкой.

Листинг 36.17 Синтаксис функции ASIN

ASIN(<значение>)

Входной параметр преобразуется в тип данных DOUBLE PRECISION. Может принимать значения от -1 до +1. Выходной параметр — угол в радианах. Возвращаемые значения находятся в диапазоне от 0 до числа $\pi$. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

ASINH

Функция возвращает обратный гиперболический синус числового параметра с плавающей точкой или параметра, который может быть преобразован в число с плавающей точкой.

Листинг 36.18 Синтаксис функции ASINH

ASINH(<параметр>)

Входной параметр может принимать любые числовые значения. Возвращаемые значения находятся в диапазоне от $- \infty$ до $+ \infty$. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

ATAN

Функция возвращает арктангенс числового параметра с плавающей точкой или параметра, который может быть преобразован в число с плавающей точкой.

Листинг 36.19 Синтаксис функции ATAN

ATAN(<значение>)

Входной параметр преобразуется в тип данных DOUBLE PRECISION. Выходной параметр — угол в радианах. Возвращаемые значения находятся в диапазоне от $-\pi/2$ (для числа $\pi$ можно использовать функцию PI) до числа $\pi/2$. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

ATAN2

Функция возвращает арктангенс частного от деления первого числового параметра с плавающей точкой на второй числовой параметр.

Листинг 36.20 Синтаксис функции ATAN2

ATAN2(<значение 1>, <значение 2>)

Входные параметры преобразуются в тип данных DOUBLE PRECISION. Функция возвращает угол как отношение синуса к косинусу, аргументы, у которых задаются двумя параметрами, а знаки синуса и косинуса соответствуют знакам параметров. Возвращаемые значения находятся в диапазоне от $-\pi/2$ до $\pi/2$. Если любой из входных параметров или оба имеют значение NULL, то возвращается также пустое значение.

ATANH

Функция возвращает обратный гиперболический тангенс числового параметра с плавающей точкой или параметра, который может быть преобразован в число с плавающей точкой.

Листинг 36.21 Синтаксис функции ATANH

ATANH(<параметр>)

Входной параметр может принимать значения от -1 до 1. Возвращаемые значения находятся в диапазоне от $- \infty$ до $+ \infty$. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

CEIL|CEILING

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

Листинг 36.22 Синтаксис функции CEILING

{CEILING | CEIL} (<значение>)

Любое число всегда можно записать в виде разницы целого N и положительного вещественного числа f таких, что <значение> = N-f и $0\le f < 1$. Результат CEIL - это число N.

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

select CEIL(2.1), CEILING(-2.1) from rdb$database;
------------------------------------------
3, -2

См. также функцию FLOOR.

COS

Функция возвращает косинус заданного параметра, указанного в радианах. Возвращаемые значения находятся в диапазоне от –1 до + 1. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

Листинг 36.23 Синтаксис функции COS

COS(<параметр>)

COSH

Функция возвращает гиперболический косинус заданного параметра, указанного в радианах. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

Листинг 36.24 Синтаксис функции COSH

COSH(<параметр>)

COT

Функция возвращает значение 1, деленное на тангенс передаваемого функции значения. Другими словами - котангенс значения в радианах.

Листинг 36.25 Синтаксис функции COT

COT(<числовой параметр>)

Если параметр имеет значение NULL, то возвращается также пустое значение.

EXP

Функция возвращает число с плавающей точкой (типа данных DOUBLE PRECISION), которое получается при возведении экспоненты $e$ (2,718281828459) в заданную параметром "целое число" степень. Синтаксис функции:

Листинг 36.26 Синтаксис функции EXP

EXP(<целое число>)

FLOOR

Функция FLOOR возвращает наибольшее целое число, меньшее или равное указанного числового выражения.

Листинг 36.27 Синтаксис функции FLOOR

FLOOR (<значение>)

Любое число всегда можно записать в виде суммы целого N и положительного вещественного числа f таких, что <значение> = N+f и $0\le f < 1$. Результат FLOOR — это число N.

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

select FLOOR(2.1), FLOOR(-2.1) from rdb$database;
------------------------------------------
2, -3

См. также функцию CEIL|CEILING.

LN

Функция возвращает натуральный логарифм числа. Синтаксис:

Листинг 36.28 Синтаксис функции LN

LN(<числовой параметр>)

Входной параметр — положительное число типа данных DOUBLE PRECISION. Выходное значение также имеет тип данных DOUBLE PRECISION.

LOG

Функция возвращает логарифм числа по заданному основанию. Синтаксис:

Листинг 36.29 Синтаксис функции LOG

LOG(<числовой параметр 1>, <числовой параметр 2>)

Первый числовой параметр задает основание логарифма~ — положительное число типа данных DOUBLE PRECISION. Второй параметр — число, для которого вычисляется логарифм, тип данных параметра DOUBLE PRECISION. Выходное значение также имеет тип данных DOUBLE PRECISION.

См. также математические функции LN, LOG10.

LOG10

Функция возвращает десятичный логарифм числового параметра. Синтаксис:

Листинг 36.30 Синтаксис функции LOG10

LOG10(<числовой параметр>)

Числовой параметр — число, для которого вычисляется логарифм. Тип данных DOUBLE PRECISION. Выходное значение также имеет тип данных DOUBLE PRECISION.

См. также математические функции LN, LOG.

MOD

Функция возвращает остаток от деления первого целочисленного параметра на второй целочисленный параметр. Синтаксис:

Листинг 36.31 Синтаксис функции MOD

MOD(<числовой параметр 1>, <числовой параметр 2>)

Вещественные числа округляются до выполнения деления. Например, результатом mod(7.5, 2.5) будет 2, а не 0.

PI

Функция возвращает число "пи" (3.1459...) типа данных DOUBLE PRECISION.

POWER

Функция возводит число в степень. Синтаксис:

Листинг 36.32 Синтаксис функции POWER

POWER(<числовое значение 1>, <числовое значение 2>)

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

RAND

Функция возвращает случайное число типа данных DOUBLE PRECISION в диапазоне от 0 до 1.

ROUND

Функция выполняет округление числа. Синтаксис:

Листинг 36.33 Синтаксис функции ROUND

ROUND(<числовой параметр 1> [, <числовой параметр 2>])

Первое число округляется в соответствии с точностью, заданной вторым числовым параметром. Числовой параметр 2 задает количество требуемых знаков после десятичной точки в полученном числе. Если он имеет отрицательное значение, то происходит округление, при котором округляются указанные позиции целой части числа.

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

Если дробная часть равна 0.5, то округление до ближайшего большего целого числа для положительных чисел и до ближайшего меньшего для отрицательных чисел.

SIGN

Возвращает знак аргумента: -1, 0 или 1. Синтаксис:

Листинг 36.34 Синтаксис функции SIGN

SIGN(<числовой параметр>)

Функция возвращает $+1$, если число положительное, $0$, если число равно нулю, и $–1$, если число отрицательное.

SIN

Функция возвращает синус заданного параметра в радианах. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

Листинг 36.35 Синтаксис функции SIN

SIN(<числовой параметр>)

SINH

Функция возвращает гиперболический синус заданного параметра в радианах. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

Листинг 36.36 Синтаксис функции SINH

SINH(<числовой параметр>)

SQRT

Функция возвращает квадратный корень заданного параметра. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

Листинг 36.37 Синтаксис функции SQRT

SQRT(<числовой параметр>)

Входной параметр и возвращаемое значение имею тип данных DOUBLE PRECISION.

TAN

Функция возвращает тангенс заданного параметра в радианах. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

Листинг 36.38 Синтаксис функции TAN

TAN(<числовой параметр>)

TANH

Функция возвращает гиперболический тангенс заданного параметра. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

Листинг 36.39 Синтаксис функции TANH

TANH(<числовой параметр>)

TRUNC

Функция возвращает число той же точности, обнуляя указанное количество знаков, заданное вторым параметром. Если числовой параметр имеет значение NULL, то возвращается также пустое значение.

Листинг 36.40 Синтаксис функции TRUNC

TRUNC(<числовой параметр> [, <масштаб>])

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

select TRUNC(789.2225, 2) from rdb$database;
-- Результат: 789.2200 (а не 789.22)
select TRUNC(345.4, -2) from rdb$database;
-- Результат: 300.0 (а не 300)
select TRUNC(-163.41, 0) from rdb$database;
-- Результат: -163.00 (а не -163)
select TRUNC(-163.41) from rdb$database;
-- Результат: -163

Функции для работы со строками

ASCII_CHAR

Возвращает символ ASCII заданного числового параметра или параметра, который может быть преобразован в целое число. Если входным параметром задается дробное число, то происходит его банковское округление до целого числа. Результат возвращается в наборе символов NONE.

Листинг 36.41 Синтаксис функции ASCII_CHAR

ASCII_CHAR(<числовое значение>)

Входной параметр может принимать значения в диапазоне от 0 до 255, иначе выдается сообщение об арифметическом переполнении. Выходным параметром является символ. Если входной параметр имеет значение NULL, то возвращается пустое значение NULL.

См. также функцию ASCII_VAL.

ASCII_VAL

Возвращает число, соответствующее коду ASCII первого (единственного) символа заданного строкового параметра или параметра, который может быть преобразован в строку.

Листинг 36.42 Синтаксис функции ASCII_VAL

ASCII_VAL(<строка>)

Возвращается NULL, если входной параметр имеет значение NULL. Возвращается 0, если входной параметр является строкой, не содержащей ни одного символа (не пустым значением NULL, а строкой, не содержащей символов).

Входной параметр может принимать значение произвольного количества символов строкового типа данных. Выходным параметром является число.

См. также функцию ASCII_CHAR.

BIT_LENGTH

Функция BIT_LENGTH возвращает длину входной строки в битах. Для многобайтных наборов символов результат может быть в 8 раз больше, чем количество символов в "формальном" числе байт на символ, записанном в RDB$CHARACTER_SETS.

Листинг 36.43 Синтаксис функции BIT_LENGTH

BIT_LENGTH (<строка>)

С параметрами типа CHAR эта функция берет во внимание всю формальную строковую длину (например, объявленная длина поля или переменной). Если вы хотите получить "логическую" длину в битах, не считая пробелов, то перед передачей аргумента в BIT_LENGTH надо выполнить над ним операцию RIGHT TRIM.

Использование функции BIT_LENGTH:

select BIT_LENGTH ('Hello!') from rdb$database;
-- возвращает 48
select BIT_LENGTH (_ISO8859_1 'Grüß Di!') from rdb$database;
-- возвращает 80: ü и ß занимают 2 байта в ISO8859_1, остальные - 1
select BIT_LENGTH (CAST (_ISO8859_1 'Grüß di!' AS VARCHAR (24)
                   CHARACTER SET UTF8)) from rdb$database;
-- возвращает 112: ü и ß занимают по 4 байта в UTF8, остальные - 1
select BIT_LENGTH (CAST (_ISO8859_1 'Grüß di!' AS CHAR (24)
                   CHARACTER SET UTF8)) from rdb$database;
-- возвращает 224: размер всех 24 позиций CHAR - 1 байт и четыре из них 16-битные

См. также функции CHARACTER_LENGTH, OCTET_LENGTH.

CHARACTER_LENGTH

Функция CHARACTER_LENGTH (сокращенное название CHAR_LENGTH) возвращает количество символов, занимаемых входным параметром функции (константа, контекстная переменная, столбец таблицы). Для строки функция возвращает именно количество символов, а не байтов, отводимых под исходную строку. Если функция применяется к столбцу, который имеет набор символов, в котором для каждого символа используется более одного байта, то количество байтов этого столбца (функция OCTET_LENGTH) будет больше, чем количество символов.

Синтаксис функции CHARACTER_LENGTH:

Листинг 36.44 Синтаксис функции подсчета количества символов во входном параметре CHARACTER_LENGTH

{CHARACTER_LENGTH | CHAR_LENGTH} (<строка>)

См. также функции BIT_LENGTH, OCTET_LENGTH.

DAMLEV

Функция рассчитывает расстояние Дамерау — Левенштейна между двумя строками.

Листинг 36.45 Синтаксис функции DAMLEV

DAMLEV( <строка>, <строка> )

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

select DAMLEV('abcd','adcb') from rdb$database;
------------------------------------------
2
select DAMLEV('abcd','adcbe') from rdb$database;
------------------------------------------
3

LEFT

Возвращает указанные первые символы строки. Синтаксис:

Листинг 36.46 Синтаксис функции LEFT

LEFT(<строка>, <числовой параметр>)

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

Если строка типа BLOB, результатом будет BLOB, в противном случае результатом будет VARCHAR(N), при этом N – будет равно длине строки. Если числовой параметр превысит длину текста, результатом будет исходный текст.

См. также функцию RIGHT.

LOWER

Переводит все буквы строки в нижний регистр. Синтаксис функции:

Листинг 36.47 Синтаксис функции LOWER

LOWER (<строка>)

Функция работает с латинскими буквами и с буквами кириллицы.

Выполнение функции

LOWER ('РОССИЯ')

вернет строку "россия".

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

Точный результат зависит от набора символов входной строки. Например, для наборов символов NONE и ASCII только ASCII символы переводятся в нижний регистр; для OCTETS – вся входная строка возвращается без изменений.

Функция поддерживают тип данных BLOB.

См. также строковые функции UPPER, TRIM.

LPAD

Возвращает строку определенного вида. Синтаксис:

Листинг 36.48 Синтаксис функции LPAD

LPAD(<строка 1>, <числовой параметр> [, <строка      2>])

К строке, заданной параметром <строка 1>, в самое начало добавляется строка, заданная параметром <строка 2>, в том случае, если числовой параметр больше размера исходной строки.

Числовой параметр — неотрицательное целое число, не превышающее 32765. Если параметр имеет значение 0, то возвращается пустая строка (не NULL). Если значение числового параметра не превышает размера исходной строки (<строка 1>), то возвращаются первые заданные символы исходной строки.

Если опущена вторая необязательная строка (<строка 2>), то исходная строка дополняется слева пробелами до того размера, когда результирующая строка будет иметь длину, равную числовому параметру. Если при этом значение числового параметра меньше длины исходной строки, то происходит усечение этой строки справа до размера, заданного числовым параметром.

Если входная строка имеет тип BLOB, то результат также будет BLOB, в противном случае результат будет VARCHAR(<числовой параметр>).

select LPAD ('Hello' , 12) from rdb$database;               -- '       Hello'
select LPAD ('World' , 12, ',') from rdb$database;          -- ',,,,,,,World'
select LPAD ('Hello' , 12, '') from rdb$database;           -- 'Hello'
select LPAD ('World' , 12, 'abc') from rdb$database;        -- 'abcabcaWorld'
select LPAD ('Hello' , 12, 'abcdefghij') from rdb$database; -- 'abcdefgHello'
select LPAD ('World' , 2) from rdb$database;                -- 'Wo'
select LPAD ('Hello' , 2, ',') from rdb$database;           -- 'He'
select LPAD ('World' , 2, '') from rdb$database;            -- 'Wo'

См. также строковую функцию RPAD.

OCTET_LENGTH

Возвращает количество байтов, занимаемых входным параметром функции. При работе с параметрами типа CHAR функция возвращает значение всей формальной строковой длины. Для того чтобы узнать "логическую" длину строки в байтах, то перед передачей аргумента функции следует применить RIGHT TRIM.

Листинг 36.49 Синтаксис функции OCTET_LENGTH

OCTET_LENGTH(<строка>)

Следует помнить, что не во всех наборах символов количество байт, занимаемых строкой, равно количеству символов.

select OCTET_LENGTH('Hello!') from rdb$database;
-- возвратит 6
select OCTET_LENGTH(_iso8859_1 'Grüß di!') from rdb$database;
-- возвратит 10: ü и ß занимают 2 байта в ISO8859_1
select OCTET_LENGTH(CAST(_iso8859_1 'Grüß di!' AS VARCHAR(24) CHARACTER SET utf8))
from rdb$database;
-- возвратит 14: ü и ß занимают 4 байта в UTF8
select OCTET_LENGTH(CAST(_iso8859_1 'Grüß di!' AS CHAR(24) CHARACTER SET utf8))
from rdb$database;
-- возвратит 28: всего 24 CHAR позиции, и четыре из них занимают 2 байта

См. также функции BIT_LENGTH, CHARACTER_LENGTH.

OVERLAY

Функция переписывает часть строки. Синтаксис:

Листинг 36.50 Синтаксис функции OVERLAY

OVERLAY(<строка 1> PLACING <строка 2>
FROM <начальная позиция> [FOR <количество заменяемых символов>])

Часть символов в первой строке заменяется на вторую строку. Символы заменяются, начиная с позиции, заданной после ключевого слова FROM. Количество заменяемых символов исходной строки задается после ключевого слова FOR. Если это ключевое слово отсутствует, то заменяется количество символов, равное количеству символов во второй строке.

Результат выполнения этой функции соответствует результату, полученному при использовании следующей операции конкатенации:

SUBSTRING(<строка 1> FROM 1 FOR <начальная позиция> - 1)
|| <строка 2>
|| SUBSTRING(<строка 1> FROM <начальная позиция>+<кол-во заменяемых символов>)

Особенности использования:

Функция полностью поддерживает тестовые BLOB с любым набором символов и любой длины;
Если входная строка имеет тип BLOB, то и результат будет иметь тип BLOB. В противном случае типом результата будет VARCHAR(n), где n является суммой длин входных строк; Как и во всех строковых функциях SQL параметр pos является определяющим;
Если начальная позиция больше длины строки, то заменяющая строка помещается сразу после окончания первой строки;
Если число символов от начальной позиции до конца строки меньше, чем длина второй строки (или, чем количество заменяемых символов, если задано), то первая строка усекается до значения начальная позиция и вторая строка помещается после неё;
При нулевом количестве заменяемых символов (FOR 0) вторая строка просто вставляется в первую, начиная с указанной позиции;
Если любой из параметров имеет значение NULL, то и результат будет NULL;
Если параметры начальная позиция и количество заменяемых символов не являются целым числом, то используется банковское округление (до чётного): 0.5 становится 0, 1.5 становится 2, 2.5 становится 2, 3.5 становится 4 и т.д.

Использование функции OVERLAY:

select OVERLAY ('Goodbye' PLACING 'Hello' FROM 2) from rdb$database; -- 'GHelloe'
select OVERLAY ('Goodbye' PLACING 'Hello' FROM 5) from rdb$database; -- 'GoodHello'
select OVERLAY ('Goodbye' PLACING 'Hello' FROM 8) from rdb$database; -- 'GoodbyeHello'
select OVERLAY ('Goodbye' PLACING 'Hello' FROM 20) from rdb$database;-- 'GoodbyeHello'
select OVERLAY ('Goodbye' PLACING 'Hello' FROM 2 FOR 0) from rdb$database;            -- 'GHellooodbye'
select OVERLAY ('Goodbye' PLACING 'Hello' FROM 2 FOR 3) from rdb$database;            -- 'GHellobye'
select OVERLAY ('Goodbye' PLACING 'Hello' FROM 2 FOR 6) from rdb$database; -- 'GHello'
select OVERLAY ('Goodbye' PLACING 'Hello' FROM 2 FOR 9) from rdb$database; -- 'GHello'
select OVERLAY ('Goodbye' PLACING '' FROM 4) from rdb$database;           -- 'Goodbye'
select OVERLAY ('Goodbye' PLACING '' FROM 4 FOR 3) from rdb$database;      -- 'Gooe'
select OVERLAY ('Goodbye' PLACING '' FROM 4 FOR 20) from rdb$database;     -- 'Goo'
select OVERLAY ('' PLACING 'Hello' FROM 4) from rdb$database;              -- 'Hello'
select OVERLAY ('' PLACING 'Hello' FROM 4 FOR 0) from rdb$database;        -- 'Hello'
select OVERLAY ('' PLACING 'Hello' FROM 4 FOR 20) from rdb$database;       -- 'Hello'

См. также строковые функции UPPER, LOWER, TRIM, LPAD, SUBSTRING, REPLACE.

POSITION

Отыскивает позицию подстроки в исходной строке. Существует два варианта синтаксиса:

Листинг 36.51 Синтаксис функции POSITION

POSITION(<строка 1> IN <строка 2>)
POSITION(<строка 1>,<строка 2> [,<начальная позиция>[,<номер вхождения подстроки>]])

Функция возвращает целое число — позицию подстроки (строка 1) в исходной строке (строка 2). Если подстрока отсутствует в исходной строке, то функция возвращает 0. Третий аргумент (во втором варианте синтаксиса) задаёт позицию в строке, с которой начинается поиск подстроки, тем самым игнорируются любые вхождения подстроки в исходную строку до этой позиции. Четвёртый аргумент определяет, какое по счету вхождение подстроки нужно искать. Если аргумент не задан, то по умолчанию равен 1, то есть результатом будет позиция первого вхождения подстроки в строку.

REGEXP_SUBSTR

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

Листинг 36.52 Синтаксис функции REGEXP_SUBSTR

REGEXP_SUBSTR(<исходная строка>, <шаблон>[,<номер группы>, <номер вхождения>, <параметр сравнения>, <позиция>])

Функция возвращает найденную подстроку или NULL, если поиск не дал результата. Тип возвращаемого значения зависит от типа входного параметра <исходная строка>. Если он имеет строковый тип (CHAR, VARCHAR), возвращается строка в той же кодировке. Если параметр — BLOB, возвращается также BLOB того же подтипа и в той же кодировке.

<исходная строка> — строка для поиска в ней нужной подстроки.
<шаблон> — регулярное выражение для поиска подстроки. О синтаксисе регулярного выражения будет рассказано далее.
<позиция> - это порядковый номер символа исходной строки, с которого начнется поиск. По умолчанию 1, т.е. поиск с начала строки.
<номер вхождения> — это положительно целое число показывает какое по счету вхождение подстроки (удовлетворяющей шаблону) в исходной строке нужно искать. Если этот параметр не задан или его значение меньше 1, он считается равным 1. При этом в качестве результата работы функции используется первая найденная подстрока.

Если данный параметр больше 1, то ищутся следующие вхождения подстроки в исходную строку. Для этого на N-м этапе запускается поиск подстроки начиная с позиции M+1 исходной строки, где M – конец подстроки, найденной на этапе N-1. Если на одном из этапов подстрока не найдена, возвращается NULL.

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

Параметр	Описание
I	Нечувствительность к регистру. По умолчанию поиск чувствителен к регистру. Для корректной работы этого режима входная строка должна иметь правильно указанную кодировку и `COLLATE`, иначе ядро СУБД не сможет соотносить символы верхнего/нижнего регистра.
G	Включение "ленивого" (non-greedy) режима сопоставления. По умолчанию используется "жадный" режим, даже если используются квантификаторы `+?, *?, ?? и {n,}?`.
M	Режим множества строк (multi-line). Символы шаблона ^ и $ становятся спецсимволами, соответствующими началу и концу строки. Без этого режима символы ^ и $ не являются специальными (кроме использования ^ внутри [ ]).
X	Режим игнорирования пробельных символов (`free-spacing`). Все пробельные символы игнорируются в шаблоне. Чтобы при этом указать в шаблоне пробел, нужно его экранировать "", либо указать в квадратных скобках [ ]. Кроме того, в этом режиме символ решётки # начинает однострочный комментарий - игнорируется он сам и все символы после него до конца строки или регулярного выражения.
S	Режим единственной строки (single-line). Символ '.' будет соответствовать всем символам, в том числе переносу строки.
T	Обрезание пробелов справа в исходной строке и регулярном выражении. Необходим в случае передачи в функцию входных значений через механизм параметров в `EXECUTE STATEMENT`, так как в таком случае типы данных не сохраняются и значения дополняются пробелами справа.

<номер группы> — это номер группы в шаблоне, которая будет использована в качестве результата. Если этот параметр не задан или его значение меньше 0, он считается равным 0, т.е. в качестве результата функции возвращается целиком найденная подстрока. Если параметр больше 0, то, если подстрока была найдена, из неё извлекается значение указанной в данном параметре группы. Если такой группы не существует или подстрока не найдена – возвращается NULL.

Примеры

select REGEXP_SUBSTR('abcd1234efgh5678', '([[:ALPHA:]]+[0-9]+)',1,1)
from rdb$database;                                                  -- abcd1234
select REGEXP_SUBSTR('abcd1234efgh5678', '([[:ALPHA:]]+[0-9]+)',1,5)
from rdb$database;                                                  -- efgh5678
select REGEXP_SUBSTR('abcd1234efgh5678', '([[:ALPHA:]]+[0-9]+)',1,5,'G')
from rdb$database;                                                  -- efgh5

REPLACE

Отыскивает подстроку в исходной строке и заменяет на другую. Синтаксис:

Листинг 36.53 Синтаксис функции REPLACE

REPLACE(<исходная строка>, <отыскиваемая подстрока>, <строка замены>)

Функция выполняет замену в исходной строке всех найденных подстрок (отыскиваемая подстрока) на заданную третьим параметром строку замены.

Функция поддерживает текстовые BLOB любой длины и с любыми наборами символов. Если один из аргументов имеет тип BLOB, то результат будет иметь тип BLOB. В противном случае результат будет иметь тип VARCHAR(N), где N рассчитывается из длин всех параметров таким образом, что даже максимальное количество замен не будет вызывать переполнения поля.

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

Если любой из аргументов равен NULL, то результатом всегда будет NULL, даже если не было произведено ни одной замены

REVERSE

Функция возвращает значение строкового параметра, где символы располагаются в обратном порядке. Синтаксис:

Листинг 36.54 Синтаксис функции REVERSE

REVERSE(<строка>)

RIGHT

Возвращает указанные последние символы строки. Синтаксис:

Листинг 36.55 Синтаксис функции RIGHT

RIGHT(<строка>, <числовой параметр>)

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

Функция поддерживает текстовые BLOB любой длины и с любыми наборами символов. Если строка типа BLOB, результатом будет BLOB, в противном случае результатом будет VARCHAR(N), при этом N – будет равно длине строки.

См. также функцию LEFT.

RPAD

Возвращает строку определенного вида. Синтаксис:

Листинг 36.56 Синтаксис функции RPAD

RPAD(<строка 1>, <числовой параметр> [, <строка 2>])

К строке, заданной параметром "строка 1", справа добавляется строка, заданная параметром "строка 2", в том случае, если числовой параметр больше размера исходной строки.

Числовой параметр — неотрицательное целое число, не превышающее 32765. Если параметр имеет значение 0, то возвращается пустая строка (не NULL). Если значение числового параметра не превышает размера исходной строки ("строка 1"), то возвращаются первые заданные символы исходной строки.

Если опущена вторая необязательная строка ("строка 2"), то исходная строка дополняется справа пробелами до того размера, когда результирующая строка будет иметь длину, равную числовому параметру. Если при этом значение числового параметра меньше длины исходной строки, то происходит усечение этой строки справа до размера, заданного числовым параметром.

Функция поддерживает текстовые BLOB любой длины и с любыми наборами символов. Если входная строка имеет тип BLOB, то результат также будет BLOB, в противном случае результат будет VARCHAR(<числовой параметр>).

select RPAD ('Hello' , 12) from rdb$database;                  -- 'Hello       '
select RPAD ('World' , 12, ',') from rdb$database;             -- 'World,,,,,,,'
select RPAD ('Hello' , 12, '') from rdb$database;              -- 'Hello'
select RPAD ('World' , 12, 'abc') from rdb$database;           -- 'Worldabcabca'
select RPAD ('Hello' , 12, 'abcdefghij') from rdb$database;    -- 'Helloabcdefg'
select RPAD ('World' , 2) from rdb$database;                   -- 'Wo'
select RPAD ('Hello' , 2, ',') from rdb$database;              -- 'He'
select RPAD ('World' , 2, '') from rdb$database;               -- 'Wo'

См. также строковую функцию LPAD.

SUBSTRING

Встроенная функция SUBSTRING возвращает подстроку исходной строки. Синтаксис функции:

Листинг 36.57 Синтаксис функции выделения подстроки SUBSTRING

SUBSTRING ( <строка> FROM <начальная позиция> [FOR <длина подстроки>]
          | <строка> SIMILAR <шаблон> ESCAPE <символ экранирования> )

<шаблон> ::= <шаблон: R1><символ экр-ия>"<шаблон: R2><символ экр-ия>"<шаблон: R3>

Здесь <строка> — исходное строка (строковый домен, столбец таблицы, входной или выходной параметр хранимой процедуры, строковый литерал, заключенный в апострофы, локальная переменная, используемая в хранимой процедуре или триггере).

Начальная позиция — номер позиции в строке, начиная с которой выделяется подстрока. Нумерация символов в строке начинается с единицы. Если начальная позиция подстроки превышает количество символов в строке, то будет выделена пустая подстрока — строка, содержащая ноль символов.

Длина подстроки — количество символов, которые выбираются в результирующую строку. Должно быть положительным числом. Если задать количество символов, которое выходит за границы исходной строки, то результат будет усечен до размера, соответствующего положению последнего символа исходной строки. При этом не будет выдано никаких диагностических сообщений. Если ключевое слово FOR не указано, то в подстроку помещаются все оставшиеся до конца исходной строки символы.

Функция полностью поддерживает двоичные и текстовые BLOB любой длины и с любым набором символов. Если исходная строка имеет тип BLOB, то и результат будет иметь тип BLOB. Для любых других типов результатом будет тип VARCHAR(N), где N всегда будет равен длине исходной строки.

Функция SUBSTRING с регулярным выражением возвращает часть строки, соответствующую шаблону в предложении SIMILAR. Если соответствия не найдено, то возвращается NULL.

Если любая из частей (R1, R2 или R3) регулярного выражения не является пустой строкой и не соответствует формату <шаблон>, будет возбуждено исключение.

Возвращаемое значение соответствует части R2 регулярного выражения. Для этого значения истинно выражение:

<строка> SIMILAR TO R1 || R2 || R3 ESCAPE <символ экранирования>

Если любой из входных параметров имеет значение NULL, то и результат тоже будет иметь значение NULL.

select SUBSTRING('Руководство ' FROM 5 FOR 3) from rdb$database;               -- вод
select SUBSTRING('abcdefg' SIMILAR 'a#"bcde#"fg' ESCAPE '#') from rdb$database;       -- bcde
select SUBSTRING('abcdefg' SIMILAR 'a#"%#"' ESCAPE '#') from rdb$database;  -- bcdefg
select SUBSTRING('abcdefg' SIMILAR '_#"%#"_' ESCAPE '#') from rdb$database;  -- bcdef
select SUBSTRING('abcdefg' SIMILAR '#"abc#"%' ESCAPE '#') from rdb$database;  -- abc
select SUBSTRING('abcdefg' SIMILAR '#"abc#"' ESCAPE '#') from rdb$database; -- <null>

Позиционный SUBSTRING

В простой позиционной форме (с FROM) эта функция возвращает подстроку, начинающуюся с указанной позиции (позиция первого сивола равна 1). Без аргумента FOR он возвращает все оставшиеся символы в строке. С использованием FOR возвращается длина подстроки или остаток строки, в зависимости от того, что короче.

Начальная позиция может быть меньше 1, тогда подстрока ведет себя так, как если бы строка имела дополнительные позиции 1 - начальная позиция перед фактическим первым символом в позиции 1. Значение длины подстроки считается от этого воображаемого начала строки, поэтому результирующая строка может быть короче указанной длины или даже пустой.

Функция полностью поддерживает двоичные и текстовые BLOB любой длины и с любым набором символов. Если входная строка имеет тип BLOB, то и результатом будет BLOB. Для любых других типов результатом будет тип VARCHAR.

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

Примеры

select SUBSTRING('abcdef' from 1 for 2) from rdb$database;
-- результат: 'ab'
select SUBSTRING('abcdef' from 2) from rdb$database;
-- результат: 'bcdef'
select SUBSTRING('abcdef' from 0 for 2) from rdb$database;
-- результат: 'a' не 'ab', потому что в позиции 0 нет "ничего"
select SUBSTRING('abcdef' from -5 for 2) from rdb$database;
-- результат: '', длина заканчивается до фактического начала строки

SUBSTRING по регулярному выражению

Функция SUBSTRING с регулярным выражением (с SIMILAR) возвращает часть строки, соответствующей шаблону регулярного выражения. Если соответствий не найдено, то возвращается NULL.

Шаблон SIMILAR формируется из трех шаблонов регулярных выражений: R1, R2 и R3. Полностью шаблон имеет форму R1 || '"<escape>"' || R2 || '"<escape>"' || R3, где <escape> - это escape-символ, определенный в предложении ESCAPE. R2 - это шаблон, который соответствует подстроке для извлечения и заключен в экранированные двойные кавычки ("<escape>", например, ""#"" с escape-символом '#'). R1 соответствует префиксу строки, а R3 - суффиксу строки. И R1, и R3 необязательны (они могут быть пустыми), но шаблон должен соответствовать всей строке. Другими словами, недостаточно указать шаблон, который находит только подстроку для извлечения.

Использование функции SUBSTRING с регулярными выражениями:

select SUBSTRING('abcabc' SIMILAR 'a#"bcab#"c' ESCAPE '#') from rdb$database; -- bcab
select SUBSTRING('abcabc' SIMILAR 'a#"%#"c' ESCAPE '#') from rdb$database;    -- bcab
select SUBSTRING('abcabc' SIMILAR '_#"%#"_' ESCAPE '#') from rdb$database;    -- bcab
select SUBSTRING('abcabc' SIMILAR '#"(abc)*#"' ESCAPE '#') from rdb$database;-- abcabc
select SUBSTRING('abcabc' SIMILAR '#"abc#"' ESCAPE '#') from rdb$database;  -- <null>

См. также строковые функции UPPER, LOWER, TRIM, LPAD, OVERLAY.

TRIM

Встроенная функция TRIM удаляет начальные и/или конечные указанные символы (по умолчанию пробелы) в исходной строке, передаваемой функции в виде входного параметра. Ее синтаксис:

Листинг 36.58 Синтаксис встроенной функции удаления символов в строке TRIM

<функция TRIM> ::= TRIM ( [[<спецификация удаления>][<удаляемые символы>] FROM] <строка>)

<спецификация удаления> ::= LEADING | TRAILING | BOTH

Первый параметр — спецификация удаления — определяет, из какой части строки (начальной и/или конечной) будут удаляться указанные символы. Параметр может иметь следующие значения:

LEADING — символы удаляются из начальной части строки.
TRAILING — удаляются конечные символы строки.
BOTH (значение по умолчанию) — символы одновременно удаляются как из начальной, так и из конечной части стоки.

Удаляемые символы — строка, содержащая произвольное количество символов. Эта строка заключается в апострофы. Если параметр опущен, предполагаются пробелы.

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

Функция поддерживает тип BLOB. Если строка имеет тип BLOB, то и результат будет иметь тип BLOB. В противном случае результат будет иметь тип VARCHAR(n), где n является длиной строки.

Примечание

Подстрока для удаления, если она, конечно, задана, не должна иметь длину больше, чем 32767 байта. Однако при повторениях подстроки в начале и/или конце строки общее число удаляемых байтов может быть гораздо больше.

Примеры. Результатом выполнения такой операции

select TRIM ('  Руководство ' || 'по SQL  ') from rdb$database;

будет строка: Руководство по SQL. Здесь по умолчанию убираются символы пробелов.

В результате выполнения следующей функции будут удалены только начальные символы "звездочка".

TRIM (LEADING '*' FROM '***********Руководство ' || 'по SQL*******')

Функция вернет строку Руководство по SQL*******.

В результате выполнения следующей функции будут удалены только конечные символы "звездочка".

TRIM (TRAILING '*' FROM '***********Руководство ' || 'по SQL*******')

Функция вернет строку ***********Руководство по SQL.

Чтобы удалить как начальные, так и конечные символы "звездочка" из строки, нужно выполнить функцию:

TRIM (BOTH '*' FROM '***********Руководство ' || 'по SQL*******')

Ключевое слово BOTH можно не задавать. В этом случае удаляются указанные символы как с начала, так и с конца строки.

См. также функции BIT_LENGTH, CHARACTER_LENGTH, OCTET_LENGTH.

UPPER

Переводит все буквы строки в верхний регистр. Синтаксис функции:

Листинг 36.59 Синтаксис функции UPPER

UPPER(<строка>)

Функция работает не только с латинскими буквами, но и с буквами кириллицы.

Выполнение функции

UPPER ('россия')

вернет строку "РОССИЯ".

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

Точный результат зависит от набора символов входной строки. Например, для наборов символов NONE и ASCII только ASCII символы переводятся в верхний регистр; для OCTETS — вся входная строка возвращается без изменений.

Функция поддерживают тип данных BLOB.

См. также строковые функции LOWER, TRIM.

UNICODE_CHAR

Возвращает UNICODE символ для заданной кодовой точки.

Листинг 36.60 Синтаксис функции UNICODE_CHAR

UNICODE_CHAR( <числовое значение> )

Аргументом функции должна быть кодовая точка UTF-32 вне диапазона суррогатов верхней/нижней границы (от 0xD800 до 0xDFFF). В противном случае будет ошибка.

select unicode_char(0x1F601) from rdb$database;

UNICODE_VAL

Возвращает число, соответствующее коду Unicode первого символа в указанной строке.

Листинг 36.61 Синтаксис функции UNICODE_VAL

UNICODE_VAL( <строка> )

select unicode_val(unicode_char(0x1F601)) from rdb$database;

Функции для работы с датой и временем

AT

Преобразует время или временную метку в указанный часовой пояс.

Листинг 36.62 Синтаксис функции UPPER

<выражение> AT {TIME ZONE '<часовой пояс>' | LOCAL}

<часовой пояс> ::=
         <регион часового пояса> | [+/-] <разница часов с GMT> [:<разница минут с GMT>]

Если используется ключевое слово LOCAL, то преобразование происходит в часовой пояс сессии.

select time '12:00 GMT' at time zone '-03:00' from rdb$database;

select current_timestamp at time zone 'America/Sao_Paulo' from rdb$database;

select timestamp '2018-01-01 12:00 GMT' at local from rdb$database;

DATEADD

Возвращает значение типа данных DATE, TIME или TIMESTAMP в зависимости от типа данных входного параметра. Возвращаемое значение параметра увеличивается (уменьшается, если задано отрицательное значение параметра "целое число") на соответствующее количество секунд (миллисекунд, минут, часов, дней, месяцев, лет), заданных параметром "целое число". У функции есть два формата.

Листинг 36.63 Синтаксис функции DATEADD

DATEADD(<целое число> <элемент даты/времени> TO <входной параметр>)
DATEADD(<элемент даты/времени>, <целое число>, <входной параметр>)

Элемент даты/времени — это YEAR, MONTH, WEEK, DAY, WEEKDAY, YEARDAY, HOUR, MINUTE, SECOND, MILLISECOND. С типом данных, содержащим только время, не могут использоваться элементы, относящиеся к дате, с типом данных DATE не могут использоваться элементы времени. Для типа данных TIMESTAMP допустимы любые варианты.

Целое число в функции должно находиться в диапазоне от –2,147,483,648 до +2,147,483,647. Дробные знаки в числе отбрасываются без округления.

Функция возвращает значение, имеющее тот же тип данных, что и входной параметр, т.е. SMALLINT, INTEGER, BIGINT или NUMERIC.

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

DATEADD(SECOND, -2147483648, CURRENT_TIMESTAMP)

Функция требует явного преобразования литералов к соответствующему типу данных.

См. также функции CAST, DATEDIFF, EXTRACT.

DATEDIFF

Возвращает целое число, задающее интервал в соответствии с указанным выделяемым элементом между двумя значениями типа данных DATE, TIME или TIMESTAMP. У функции есть два формата.

Листинг 36.64 Синтаксис функции DATEDIFF

DATEDIFF(<элемент даты/времени> FROM <входной пар-тр 1> TO <входной пар-тр 2>)
DATEDIFF(<элемент даты/времени>, <входной пар-тр 1>, <входной пар-тр 2>)

Элемент даты/времени — это YEAR, MONTH, WEEK, DAY, WEEKDAY, YEARDAY, HOUR, MINUTE, SECOND, MILLISECOND. С типом данных, содержащим только время, не могут использоваться элементы, относящиеся к дате, с типом данных DATE не могут использоваться элементы времени. Для типа данных TIMESTAMP допустимы любые варианты.

Функция возвращает количество интервалов, заданных элементом даты/времени (лет, месяцев, дней, часов, минут, секунд или миллисекунд) между двумя входными параметрами. Возвращается число со знаком: из второго параметра производится соответствующее вычитание элемента первого параметра.

Дата и время имеет естественную иерархическую структуру: год, месяц, день, час, минута, секунда, миллисекунда. При вычислении разности элементов одного уровня учитываются значения лишь этого или более высокого уровня. Элементы нижележащих уровней не учитываются.

Функция требует явного преобразования литералов к соответствующему типу данных.

Пример Чтобы определить, сколько лет осталось до 2050 года, нужно выполнить функцию:

DATEDIFF (YEAR, CURRENT_DATE, CAST('01.01.2050' AS DATE))

См. также функции DATEADD, EXTRACT, CAST.

EXTRACT

Функция для типов данных даты (DATE), времени (TIME) и даты/времени (TIMESTAMP) позволяет выделять различные элементы даты и времени. Синтаксис функции:

Листинг 36.65 Синтаксис функции EXTRACT

EXTRACT (<выделяемый элемент> FROM <дата>)

Исходным данным может быть столбец, домен (ключевое слово VALUE), параметр или внутренняя переменная хранимой процедуры или триггера.

Выделяемый элемент:

YEAR — год: функция вернет целое число от 1 до 9999, ведущие нули отбрасываются,
MONTH — месяц: вернет целое число от 1 до 12, ведущий ноль отбрасывается,
DAY — день месяца: целое число от 1 до 31, ведущий ноль отбрасывается,
HOUR — функция возвращает часы: целое число от 0 до 23,
MINUTE — возвращаются минуты: целое число от 0 до 59,
SECOND — секунды, включая десятитысячные доли секунды,
MILLISECOND — возвращаются миллисекунды,
WEEK — номер недели в году: целое число от 1 до 53,
WEEKDAY — номер дня в неделе; 0 — воскресенье, 6 — суббота,
YEARDAY — номер дня в году: число от 0 до 365. Первый день в году имеет номер 0,
TIMEZONE_HOUR — функция возвращает смещение часов часового пояса: целое число от -23 до 23,
TIMEZONE_MINUTE — функция возвращает смещение минут часового пояса: целое число от -59 до 59,
QUARTER — квартал года, возвращает число от 1 до 4.

Выделять часы, минуты и секунды можно лишь в типах данных, содержащих время: TIME и TIMESTAMP. Выделение элементов даты возможно только для тех типов данных, которые содержат дату: DATE и TIMESTAMP.

В следующем операторе из переменной DATE_C типа DATE выделяются день, месяц и год. Полученные данные при помощи операции конкатенации приводятся к виду, принятому в нашей стране:

EXTRACT (DAY FROM DATE_C) || '.' ||
EXTRACT (MONTH FROM DATE_C) || '.' ||
EXTRACT (YEAR FROM DATE_C);

См. также функции DATEADD, DATEDIFF, CAST.

FIRST_DAY

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

Листинг 36.66 Синтаксис функции FIRST_DAY

FIRST_DAY( OF {YEAR | MONTH | WEEK | QUARTER} FROM <дата> )

Тип возвращаемого результата: DATE или TIMESTAMP

Примечание

Первым днём недели считается воскресенье, как это возвращает функция EXTRACT с частью WEEKDAY.

Примечание

Когда в качестве аргумента функции передаётся выражение типа TIMESTAMP, то возвращаемое значение сохраняет временную часть.

LAST_DAY

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

Листинг 36.67 Синтаксис функции FIRST_DAY

LAST_DAY( OF {YEAR | MONTH | WEEK | QUARTER} FROM <дата> )

Тип возвращаемого результата: DATE или TIMESTAMP

Примечание

Последним днём недели считается суббота, как это возвращает функция EXTRACT с частью WEEKDAY.

Примечание

Когда в качестве аргумента функции передаётся выражение типа TIMESTAMP, то возвращаемое значение сохраняет временную часть.

UTC_TIMESTAMP

Возвращает текущую дату и время по стандарту UTC в качестве значения в формате YYYY-MM-DD HH:MM:SS. Синтаксис:

Листинг 36.68 Синтаксис функции UTC_TIMESTAMP

UTC_TIMESTAMP

Функции для работы с типом DECFLOAT

COMPARE_DECFLOAT

Функция COMPARE_DECFLOAT сравнивает два значения типа DECFLOAT, которые могут быть одинаковыми, разными или неупорядоченными.

Листинг 36.69 Синтаксис функции COMPARE_DECFLOAT

COMPARE_DECFLOAT (<значение1>, <значение2>)

Тип возвращаемого результата: SMALLINT

Таблица 36.4 Результирующие значения функции `COMPARE_DECFLOAT`
Результат	Описание
0	Значения равны
1	Первое значение меньше, чем второе
2	Первое значение больше, чем второе
3	Значения не упорядочены (одно или оба `NAN / SNAN`)

В отличие от операторов сравнения (<, >, = и др.) сравнение с помощью COMPARE_DECFLOAT является точным, т.е.

COMPARE_DECFLOAT(2.17, 2.170)

вернёт 2, а не 0

NORMALIZE_DECFLOAT

Функция NORMALIZE_DECFLOAT возвращает число в нормализованном виде. Это обозначает, что для любого ненулевого значения удаляются завершающие нули с соответствующей коррекцией экспоненты.

Листинг 36.70 Синтаксис функции NORMALIZE_DECFLOAT

NORMALIZE_DECFLOAT (<значение>)

Тип возвращаемого результата: DECFLOAT

select NORMALIZE_DECFLOAT(12.00) from rdb$database;-- 12
select NORMALIZE_DECFLOAT(120) from rdb$database;-- 1.2E+2

QUANTIZE

Функция QUANTIZE возвращает значение первого аргумента масштабированным с использованием второго значения в качестве шаблона.

Листинг 36.71 Синтаксис функции QUANTIZE

QUANTIZE (<значение>, <шаблон>)

Тип возвращаемого результата: DECFLOAT

Функция QUANTIZE возвращает значение DECFLOAT, равное по значению (за исключением любого округления) и знаку <значение>, а также экспоненте, равной по значению экспоненте <шаблон>. Функцию QUANTIZE можно использовать для реализации округления с точностью до нужного знака, например, округление до ближайшего цента с использованием установленного режима округления DECFLOAT.

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

select v, pic, quantize(v, pic) from examples;

V           PIC         QUANTIZE
==========  ==========  ==========
16        0.001       3.160
16        0.01        3.16
16        0.1         3.2
16        1           3
16        1E+1        0E+1
-0.1        1           -0
         1E+5        0E+5
       0.1         316.0
       1           316
       1E+1        3.2E+2
       1E+2        3E+2

TOTALORDER

Функция TOTALORDER сравнивает два значения типа DECFLOAT, включая специальные значения. Сравнение является точным.

Листинг 36.72 Синтаксис функции TOTALORDER

TOTALORDER (<значение1>, <значение2>)

Тип возвращаемого результата: SMALLINT

Таблица 36.5 Результирующие значения функции `TOTALORDER`
Результат	Описание
-1	Первое значение меньше второго
0	Значения равны
1	Первое значение больше второго

Значения DEFLOAT в следующем виде:

-nan < -snan < -inf < -0.1 < -0.10 < -0 < 0 < 0.10 < 0.1 < inf < snan < nan

Кодирование и декодирование бинарных данных

BASE64_ENCODE

Функция BASE64_ENCODE кодирует входные данные в представлении BASE64. Функция может работать как с символьной строкой, так и с BLOB.

Листинг 36.73 Синтаксис функции BASE64_ENCODE

BASE64_ENCODE (<двоичные данные>)

Тип возвращаемого результата: BLOB или VARCHAR

BASE64_DECODE

Функция BASE64_DECODE декодирует входные данные из представления BASE64. Функция может работать как с символьной строкой, так и с BLOB.

Листинг 36.74 Синтаксис функции BASE64_DECODE

BASE64_ENCODE (<данные в base64>)

Тип возвращаемого результата: BLOB или VARCHAR

HEX_ENCODE

Функция HEX_ENCODE кодирует двоичные данные в шестнадцатеричное представлении. Функция может работать как с символьной строкой, так и с BLOB.

Листинг 36.75 Синтаксис функции HEX_ENCODE

HEX_ENCODE (<двоичные данные>)

Тип возвращаемого результата: BLOB или VARCHAR

HEX_DECODE

Функция HEX_DECODE декодирует данные в шестнадцатеричном представлении в двоичные данные. Функция может работать как с символьной строкой, так и с BLOB.

Листинг 36.76 Синтаксис функции HEX_DECODE

HEX_DECODE (<16-ричные данные>)

Тип возвращаемого результата: BLOB или VARCHAR

BLOB_APPEND

Оператор || с BLOB-аргументами создает временный BLOB для каждой пары аргументов, содержащих BLOB. Это может привести к чрезмерному потреблению памяти и увеличению файла базы данных. Функция BLOB_APPEND предназначена для объединения BLOB без создания промежуточных объектов.

Чтобы достичь этого, результирующий BLOB остается открытым для записи, а не закрывается сразу после заполнения данными. Данные в такой BLOB можно добавлять столько раз, сколько потребуется. Сервер помечает такой BLOB внутренним флагом BLB_close_on_read и закрывает его при необходимости.

Листинг 36.77 Синтаксис функции BLOB_APPEND

BLOB_APPEND( <значение> [, <значение>, ... <значение> ] )

Входные параметры:

В зависимости от значения первого аргумента возможны следующие варианты поведения:
- NULL — создается новый BLOB (незакрытый, с флагом BLB_close_on_read).
- постоянный BLOB (из таблицы) или временный BLOB, который уже был закрыт — создается новый BLOB (незакрытый, с флагом BLB_close_on_read), его содержимое копируется из первого аргумента.
- временный незакрытый BLOB — он будет использоваться в дальнейшем.
- другие типы данных преобразуются в строку, создается новый BLOB (незакрытый, с флагом BLB_close_on_read), его содержимое копируется из этой строки.
Другие аргументы могут быть любого типа, для них определено следующее поведение:
- значения NULL игнорируются.
- не BLOB преобразуются в строки и добавляются к результату.
- BLOB при необходимости переводятся в кодировку первого аргумента, и их содержимое добавляется к результату.

Функция BLOB_APPEND возвращает временный незакрытый BLOB с флагом BLB_close_on_read. Это либо новый BLOB, либо тот, который указан в качестве первого аргумента. Таким образом, серия операций типа blob = BLOB_APPEND (blob, ...) приведет к созданию не более одного BLOB (если только вы не попытаетесь добавить BLOB к самому себе). Этот BLOB будет закрыт, когда клиент прочитает его, назначит его таблице или использует в других выражениях, требующих чтения содержимого.

Примечание

Проверка BLOB на наличие значения NULL с помощью оператора IS [NOT] NULL не считывает его, и поэтому BLOB не будет закрыт после такой проверки.

Используйте функции LIST или BLOB_APPEND для объединения BLOB. Это уменьшает потребление памяти и дисковый ввод-вывод, а также предотвращает рост базы данных из-за создания большого количества временных BLOB.

execute block
  returns (b blob sub_type text)
as
begin
  -- создает новый временный незакрытый BLOB
  -- записывает в него строку из второго аргумента
  b = blob_append(null, 'Hello ');

  -- добавляет две строки во временный BLOB, не закрывая его
  b = blob_append(b, 'World', '!');

  -- сравнение BLOB со строкой приведет к его закрытию, потому что BLOB должен быть прочитан
  if (b = 'Hello World!') then
  begin
      ...
  end

  --создает временный закрытый BLOB, добавляя к нему строку
  b = b || 'Close';
  suspend;
end!

Хэш функции

HASH

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

Листинг 36.78 Синтаксис функции HASH

HASH (<значение> [USING <алгоритм>])

<алгоритм> ::= CRC32

Значение - Выражение или значение любого типа. Нестроковые и небинарные типы будут преобразованы в строку. Функция поддерживает тип данных BLOB.
Алгоритм - Алгоритм хэширования, который нужно применить.

Необязательное предложение USING указывает, какой алгоритм хэширования нужно применить. Если алгоритм не указан, то по умолчанию используется PJW. При использовании алгоритма PJW функция вернёт значение типа BIGINT.

При указании CRC32 будет использован полином 0x04C11DB7. В этом случае функция вернёт значение INTEGER.

Примеры использования функции HASH:

Хэширование с использованием алгоритма CRC32:

SELECT HASH(X USING CRC32) FROM Y;

Хэширование с использованием алгоритма PJW:

SELECT HASH(X) FROM Y;

См. также функцию HASH_CP.

CRYPT_HASH

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

Листинг 36.79 Синтаксис функции CRYPT_HASH

CRYPT_HASH (<значение> USING <алгоритм>)

<алгоритм> ::= MD5 | SHA1 | SHA256 | SHA512 | SHA3_224 | SHA3_256 | SHA3_384 | SHA3_512

Значение - Выражение или значение любого типа. Нестроковые и небинарные типы будут преобразованы в строку. Функция поддерживает тип данных BLOB.
Алгоритм - Криптографический алгоритм хэширования, который нужно применить.

Функция возвращает VARBINARY, длина которого зависит от указанного алгоритма.

Алгоритмы MD5 и SHA1 не рекомендуется использовать, они предоставляются только для обратной совместимости.

При хэшировании строк или двоичных значений нужно учитывать влияние пробелов и NULL. Хэши значения 'ab' в CHAR(5) (3 пробела в конце строки), в VARCHAR(5) (без пробелов в конце строки) и CHAR(6) (4 пробела в конце строки) будут отличаться. Чтобы избежать этого, нужно использовать тип данных переменной длины, или тот же тип данных фиксированной длины. Также можно нормализовать значение перед хэшированием, например, используя TRIM(TRAILING FROM <значение>).

HASH_CP

Функция возвращает хэш-значение, соответствующее входной строке, используя криптографический плагин и алгоритм хэширования, указанный в параметре конфигурации HashMethod (по умолчанию ГОСТ Р 34.11-94)

Листинг 36.80 Синтаксис функции HASH_CP

HASH_CP(<входной параметр>)

Функция поддерживает тип данных BLOB.

См. также функцию HASH.

HASHAGG

Агрегатная функция. Можно использовать в качестве оконной. Функция возвращает хэш всех элементов выборки, которые не равны NULL. При пустой выборке, или при выборке из одних NULL функция возвратит NULL.

Листинг 36.81 Синтаксис функции HASHAGG

HASHAGG [ALL | DISTINCT] (<выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

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

SELECT
  dept_no,
  HASHAGG(salary)
FROM employee
GROUP BY dept_no;

См. также агрегатные функции MIN, MAX, AVG, SUM, LIST, Оконные функции.

Криптографические функции

В РЕД Базе Данных 5.0 поддерживается только подмножество симметричных алгоритмов шифрования (как блочных так и потоковых) и RSA.

CRC32

Функция CRC32 возвращает CRC-32 с полиномом 0x04C11DB7. Функция может работать с любым типом данных.

Листинг 36.82 Синтаксис функции CRC32

CRC32(<данные>)

Тип возвращаемого результата: VARCHAR

ENCRYPT

Функция ENCRYPT шифрует данные с использованием симметричного шифра.

Листинг 36.83 Синтаксис функции ENCRYPT

ENCRYPT (<строка> [USING <алгоритм шифрования>] [MODE <режим шифрования>]
    KEY <ключ шифрования> [IV <вектор инициализации>]
    [<порядок байтов счётчика>] [CTR_LENGTH <длина счётчика>]
    [COUNTER <начальное значение счётчика>])

<алгоритм шифрования> ::= { <блочные алгоритмы> | <потоковые алгоритмы> }

<блочные алгоритмы> ::=
       { AES | ANUBIS | BLOWFISH | KHAZAD | RC5 | RC6 | SAFER+ | TWOFISH | XTEA }

<потоковые алгоритмы> ::= { CHACHA20 | RC4 | SOBER128 }

<режим шифрования> ::= { CBC | CFB | CTR | ECB | OFB }

<порядок байтов счётчика> ::= { CTR_BIG_ENDIAN | CTR_LITTLE_ENDIAN }

Тип возвращаемого результата: BLOB или VARBINARY

Здесь:

<строка> — выражение строкового типа или BLOB, которое необходимо зашифровать. Размеры строк передаваемых в эту функцию должны соответствовать требованиям выбранного алгоритма и режима.
<режим шифрования> обязателен для блочных алгоритмов шифрования.
<вектор инициализации> должен быть указан для всех блочных алгоритмов шифрования за исключением ECB и всех потоковых алгоритмов шифрования за исключением RC4.
<порядок байтов счётчика> может быть указан только в режиме CTR. По умолчанию используется CTR_LITTLE_ENDIAN.
<длина счётчика> (в байтах) может быть указана только в режиме CTR. По умолчанию равна длине вектора инициализации IV.
<начальное значение счётчика> может быть указана только для алгоритма CHACHA20. По умолчанию равно 0.

Эта функция возвращает BLOB SUB_TYPE BINARY, если первым аргументом является BLOB, и VARBINARY для всех других текстовых и двоичных типов.

Размеры строк, передаваемых в эту функцию должны соответствовать требованиям выбранного алгоритма и режима. Как правило, размер вектора инициализации должен соответствовать размеру блока алгоритма. Для режимов ECB и CBC входная строка должна быть кратной размеру блока, её нужно будет вручную заполнить нулями или пробелами, если это необходимо.

Таблица 36.6 Требования алгоритмов шифрования
Алгоритм	Размер ключа (байт)	Размер блока (байт)	Примечание
AES	16, 24, 32	16
ANUBIS	16 - 40 с шагом 4	16
BLOWFISH	8 - 56	8
KHAZAD	16	8
RC5	8 - 128	8
RC6	8 - 128	16
SAFER+	16, 24, 32	16
TWOFISH	16, 24, 32	16
XTEA	16	8
CHACHA20	16, 32	1	Размер (IV) составляет 8 или 12 байт. Для размера 8 initial_counter - это 64-битное целое число, для размера 12 - 32-битное.
RC4	5 - 256	1
SOBER128	4x	1	Размер (IV) составляет 4y байт, длина не зависит от размера ключа.

Использование функции ENCRYPT:

select encrypt('897897' using sober128 key 'AbcdAbcdAbcdAbcd' iv '01234567')
from rdb$database;

DECRYPT

Функция DECRYPT дешифрует данные с использованием симметричного шифра.

Листинг 36.84 Синтаксис функции DECRYPT

DECRYPT (<строка> [USING <алгоритм шифрования>] [MODE <режим шифрования>]
    KEY <ключ шифрования> [IV <вектор инициализации>]
    [ <порядок байтов счётчика>] [CTR_LENGTH <длина счётчика>]
    [COUNTER <начальное значение счётчика>])

<алгоритм шифрования> ::= { <блочные алгоритмы> | <потоковые алгоритмы> }

<блочные алгоритмы> ::=
         { AES | ANUBIS | BLOWFISH | KHAZAD | RC5 | RC6 | SAFER+ | TWOFISH | XTEA }

<потоковые алгоритмы> ::= { CHACHA20 | RC4 | SOBER128 }

<режим шифрования> ::= { CBC | CFB | CTR | ECB | OFB }

<порядок байтов счётчика> ::= { CTR_BIG_ENDIAN | CTR_LITTLE_ENDIAN }

Тип возвращаемого результата: BLOB или VARBINARY

Здесь:

<строка> — выражение строкового типа или BLOB, которое необходимо зашифровать. Размеры строк передаваемых в эту функцию должны соответствовать требованиям выбранного алгоритма и режима.
<режим шифрования> обязателен для блочных алгоритмов шифрования.
<вектор инициализации> должен быть указан для всех блочных алгоритмов шифрования за исключением ECB и всех потоковых алгоритмов шифрования за исключением RC4.
<порядок байтов счётчика> может быть указан только в режиме CTR. По умолчанию используется CTR_LITTLE_ENDIAN.
<длина счётчика> (в байтах) может быть указана только в режиме CTR. По умолчанию равна длине вектора инициализации IV.
<начальное значение счётчика> может быть указана только для алгоритма CHACHA20. По умолчанию равно 0.

RSA_PRIVATE

Функция RSA_PRIVATE возвращает RSA закрытый ключ заданной длины (в байтах) в PKCS#1 формате как строку VARBINARY.

Листинг 36.85 Синтаксис функции RSA_PRIVATE

RSA_PRIVATE (<размер ключа>)

Тип возвращаемого результата: VARBINARY

RSA_PUBLIC

Функция RSA_PUBLIC возвращает RSA открытый ключ для заданного RSA закрытого ключа. Оба ключа должны быть в PKCS#1 формате.

Листинг 36.86 Синтаксис функции RSA_PUBLIC

RSA_PUBLIC (<RSA закрытый ключ>)

Тип возвращаемого результата: VARBINARY

RSA_ENCRYPT

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

Листинг 36.87 Синтаксис функции RSA_ENCRYPT

RSA_ENCRYPT (<данные> KEY <открытый RSA ключ> [LPARAM <тег>] [HASH <алгоритм хэширования>])

<алгоритм хэширования> ::= { MD5 | SHA1 | SHA256 | SHA512 }

Тип возвращаемого результата: VARBINARY

Здесь:

<данные> — строка или BLOB для шифрования.
<открытый RSA ключ> — открытый RSA ключ, который возвращает функция RSA_PUBLIC.
<тег> — дополнительный системный тег, который можно применять для определения того, какая система закодировала сообщение. Значением по умолчанию является NULL.
<алгоритм хэширования> по умолчанию SHA256.

RSA_DECRYPT

Расшифровывает с использованием закрытого ключа RSA, и удаляет OAEP дополненные данные.

Листинг 36.88 Синтаксис функции RSA_DECRYPT

RSA_DECRYPT (<данные> KEY <закрытый RSA ключ> [LPARAM <тег>] [HASH <алгоритм хэширования>])

<алгоритм хэширования> ::= { MD5 | SHA1 | SHA256 | SHA512 }

Тип возвращаемого результата: VARCHAR

Здесь:

<данные> — строка или BLOB для дешифрования.
<закрытый RSA ключ> — закрытый RSA ключ, который возвращает функция RSA_PRIVATE.
<тег> — дополнительный системный тег, который должен быть тем же самым значением, которое передавалось RSA_ENCRYPT. Если оно не совпадает с тем, который использовался во время кодирования, эта функция не расшифровывает пакет. Значением по умолчанию является NULL.
<алгоритм хэширования> по умолчанию SHA256.

RSA_SIGN

Выполняет PSS-кодирование дайджеста сообщения для подписи и подписывает его с использованием закрытого ключа RSA. Возвращает подпись сообщения.

Листинг 36.89 Синтаксис функции RSA_SIGN

RSA_SIGN (<данные> KEY <закрытый RSA ключ> [HASH <алгоритм хэширования>] [SALT_LENGTH <длина>])

<алгоритм хэширования> ::= { MD5 | SHA1 | SHA256 | SHA512 }

Тип возвращаемого результата: VARBINARY

Здесь:

<данные> — строка или BLOB для кодирования.
<закрытый RSA ключ> — закрытый RSA ключ, который возвращает функция RSA_PRIVATE.
<алгоритм хэширования> по умолчанию SHA256.
<длина> указывает на длину желаемой соли и, как правило, должен быть небольшим. Хорошее значение от 8 до 16.

RSA_VERIFY

Выполняет PSS-кодирование дайджеста сообщения для подписи и проверяет его цифровую подпись, используя открытый ключ RSA. Возвращает результат проверки подписи.

Листинг 36.90 Синтаксис функции RSA_SIGN

RSA_VERIFY (<данные>
            SIGNATURE <подпись>
            KEY <открытый RSA ключ>
            [HASH <алгоритм хэширования>]
            [SALT_LENGTH <длина>])

<алгоритм хэширования> ::= { MD5 | SHA1 | SHA256 | SHA512 }

Тип возвращаемого результата: BOOLEAN

Здесь:

<данные> — строка или BLOB для кодирования.
<подпись> должно быть значением возвращаемым функцией RSA_SIGN.
<открытый RSA ключ> — открытый RSA ключ, который возвращает функция RSA_PUBLIC.
<алгоритм хэширования> по умолчанию SHA256.
<длина> указывает на длину желаемой соли и, как правило, должен быть небольшим. Хорошее значение от 8 до 16.

Функции преобразования типов

CAST

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

Листинг 36.91 Синтаксис функции CAST

CAST ({<значение> | NULL} AS <тип данных>    [CHARACTER SET <набор символов>])

<тип данных> ::= {
             <тип данных SQL>
           | [TYPE OF] <имя домена>
           | TYPE OF COLUMN <имя таблицы/представления>.<имя столбца> }

Преобразование NULL в любой тип данных всегда дает тот же NULL.

Значением здесь может быть имя столбца таблицы, литерал или выражение.

Тип данных BLOB подтипа TEXT также допускает преобразования (но с максимальным размером $32765$ байт).

В целочисленные типы данных (SMALLINT, INTEGER, BIGINT) можно выполнять преобразование числовых данных и констант с фиксированной точкой (DECIMAL, NUMERIC), с плавающей точкой (FLOAT, DOUBLE PRECISION), данных текстового BLOB и строковых данных (CHAR, VARCHAR, NCHAR и NCHAR VARYING), содержащих только цифры и десятичную точку.

В дробные числа с фиксированной точкой (DECIMAL, NUMERIC) можно преобразовывать все целочисленные данные и данные с фиксированной или плавающей точкой, данные типа BLOB подтипа TEXT, а также строки, содержащие данные, по форме соответствующие числам.

В строковые типы данных (CHAR, VARCHAR, NCHAR и NCHAR VARYING) можно преобразовывать любой тип данных. Необходимо лишь указать размер строкового типа, достаточный для того, чтобы в него поместился результат преобразования.

В типы данных DATE, TIME и TIMESTAMP можно преобразовать любую строку, содержащую дату в одном из допустимых форматов.

Более подробно типы данных, предварительно определенные литералы и контекстные переменные, а также примеры их преобразования описаны в главе 4.

См. также функцию EXTRACT.

Функции побитовых операций

BIN_AND

Функция BIN_AND возвращает результат побитовой операции AND над аргументами.

Листинг 36.92 Синтаксис функции BIN_AND

BIN_AND (<целое число>, <целое число> [, <целое число> ...])

Тип возвращаемого результата: SMALLINT, INTEGER, BIGINT или INT128.

Результат SMALLINT возвращается только в том случае, если все аргументы являются явными значениями типа SMALLINT или NUMERIC(n, 0), где n <= 4. В противном случае для небольших целых чисел возвращается результат типа INTEGER.

BIN_NOT

Возвращает результат логического отрицания к каждому биту двоичного представления целочисленного параметра.

Листинг 36.93 Синтаксис функции BIN_NOT

BIN_NOT(<значение>)

Выходной параметр — результат выполнения логической функции отрицания над целочисленным параметром. Если входной параметр имеет значение NULL, то возвращается также пустое значение.

Пример:

Пусть есть целое число 6, которое в двоичном представлении имеет вид 0110. Результатом операции логического отрицания будет 1001, что является дополнительным кодом для отрицательного числа -7.

select BIN_NOT(6) from rdb$database;
------------------------------------------
-7

BIN_OR

Функция BIN_OR возвращает результат побитовой операции OR (ИЛИ) над аргументами.

Листинг 36.94 Синтаксис функции BIN_OR

BIN_OR (<целое число>, <целое число> [, <целое число> ...])

Тип возвращаемого результата: SMALLINT, INTEGER, BIGINT или INT128.

Результат SMALLINT возвращается только в том случае, если все аргументы являются явными значениями типа SMALLINT или NUMERIC(n, 0), где n <= 4. В противном случае для небольших целых чисел возвращается результат типа INTEGER.

BIN_SHL

Возвращает результат операции сдвига влево двоичных знаков целочисленного параметра.

Листинг 36.95 Синтаксис функции BIN_SHL

BIN_SHL(<значение 1>, <значение 2>)

Значение входного числа сдвигается влево на количество битов, заданных вторым входным параметром. Выходной параметр — целое число, результат сдвига влево значения первого параметра на заданное количество битов, заданных вторым параметром. Если любой из входных параметров или оба имеют значение NULL, то возвращается также пустое значение.

Пример:

Пусть есть целое число 2, которое в двоичной системе имеет вид 0010. Если сделать сдвиг влево на 2 бита, то получим число 1000 = 8.

select BIN_SHL(2,2) from rdb$database;
------------------------------------------
8

Доступна для DSQL, PSQL.

BIN_SHR

Возвращает результат операции сдвига вправо двоичных знаков целочисленного параметра.

Листинг 36.96 Синтаксис функции BIN_SHR

BIN_SHR(<значение 1>, <значение 2>)

Значение входного числа сдвигается вправо на количество битов, заданных вторым входным параметром. Выходной параметр — целое число, результат сдвига вправо значения первого параметра на заданное количество битов, заданных вторым параметром. Если любой из входных параметров или оба имеют значение NULL, то возвращается также пустое значение.

Пример:

Пусть есть целое число 7, которое в двоичной системе имеет вид 0111. Если сделать сдвиг вправо на 1 бит, то получим число 0011 = 3. Поэтому

select BIN_SHR(7,1) from rdb$database;
------------------------------------------
3

Доступна для DSQL, PSQL.

BIN_XOR

Функция BIN_XOR возвращает результат побитовой операции XOR для аргументов.

Листинг 36.97 Синтаксис функции BIN_XOR

BIN_XOR (<целое число>, <целое число> [, <целое число> ...])

Тип возвращаемого результата: SMALLINT, INTEGER, BIGINT или INT128.

Результат SMALLINT возвращается только в том случае, если все аргументы являются явными значениями типа SMALLINT или NUMERIC(n, 0), где n <= 4. В противном случае для небольших целых чисел возвращается результат типа INTEGER.

Функции для работы с UUID

CHAR_TO_UUID

Функция для работы с UUID. Данная функция преобразует переданное в качестве параметра 32-х символьное ASCII представление UUID (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX) в восьмеричное представление, оптимизированное для хранения.

Листинг 36.98 Синтаксис функции CHAR_TO_UUID

CHAR_TO_UUID(<string>)

Примечание

Было обнаружено, что в версиях Firebird до 2.5.2 функции CHAR_TO_UUID и UUID_TO_CHAR работали неправильно на серверах с архитектурой "big-endian". В этих машинах байты/символы менялись местами и переходили в чужие позиции при преобразовании. Эта ошибка была исправлена в версиях 2.5.2 и 3.0.

select CHAR_TO_UUID('93519227-8D50-4E47-81AA-8F6678C096A1') from rdb$database;
------------------------------------------
935192278D504E4781AA8F6678C096A1

См. также функции GEN_UUID, UUID_TO_CHAR.

GEN_UUID

Функция возвращает универсальный уникальный идентификатор ID в виде 16-байтной строки символов, отвечающий требованиям стандарта RFC-4122. Функция возвращает строку UUID 4-ой версии, где несколько битов зарезервированы, а остальные являются случайными.

Синтаксис функции:

Листинг 36.99 Синтаксис функции GEN_UUID

GEN_UUID()

Тип возвращаемого результата: BINARY(16).

SELECT GEN_UUID() AS GUID FROM RDB$DATABASE

GUID
========
017347BFE212B2479C00FA4323B36320

См. также функции CHAR_TO_UUID, UUID_TO_CHAR.

UUID_TO_CHAR

Функция для работы с UUID. Данная функция преобразует переданное в качестве параметра восьмеричное представление UUID CHAR(16) в 32-х символьное ASCII представление (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX). Тип возвращаемого значения CHAR(36).

Листинг 36.100 Синтаксис функции UUID_TO_CHAR

UUID_TO_CHAR(<uuid>)

Примечание

Было обнаружено, что в версиях Firebird до 2.5.2 функции CHAR_TO_UUID и UUID_TO_CHAR работали неправильно на серверах с архитектурой "big-endian". В этих машинах байты/символы менялись местами и переходили в чужие позиции при преобразовании. Эта ошибка была исправлена в версиях 2.5.2 и 3.0.

select UUID_TO_CHAR(GEN_UUID()) from rdb$database;

См. также функции GEN_UUID, CHAR_TO_UUID.

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

GEN_ID

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

Листинг 36.101 Синтаксис функции GEN_ID

GEN_ID(<имя генератора>, <приращение>)

При выполнении функции выбирается текущее значение указанного генератора, изменяется на величину приращения (это может быть положительное, отрицательное число или ноль). Полученное значение функция возвращает вызвавшему ее программному компоненту.

Обычно функция используется в операторах INSERT для получения уникальных числовых значений для искусственного первичного ключа.

Вместо функции GEN_ID рекомендуется использовать конструкцию NEXT VALUE FOR. Выполнение функции

GEN_ID (<имя генератора>, 1)

эквивалентно выполнению следующей конструкции:

NEXT VALUE FOR <имя генератора>

Предупреждение

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

NEXT VALUE FOR

Конструкция (функция) NEXT VALUE FOR позволяет получить значение указанного генератора, увеличенное на единицу. Синтаксис:

Листинг 36.102 Синтаксис функции NEXT VALUE FOR

NEXT VALUE FOR <имя генератора>

Точно такой же результат можно получить, вызвав функцию:

GEN_ID(<имя генератора>, 1)

Подробное описание дано в главе Генераторы.

См. также операторы CREATE GENERATOR, CREATE SEQUENCE, DROP GENERATOR, DROP SEQUENCE, SET GENERATOR, ALTER SEQUENCE, функцию GEN_ID.

Условные функции

CASE-WHEN-ELSE

Условное выражение. Дает возможность выбрать результирующее значение из множества различных выражений.

Листинг 36.103 Синтаксис простого выражения CASE

CASE <исходное выражение>
 WHEN <выражение 1> THEN {<результат 1> | NULL}
 [WHEN <выражение 2> THEN {<результат 2> | NULL}] ...
 [ELSE {<значение по умолчанию> | NULL}]
END

Исходное выражение возвращает значение, с которым сравниваются выражения $N$ в последующих предложениях WHEN. Если исходное выражение равно выражению $N$ в соответствующем предложении WHEN, то функция возвращает результат $N$ или NULL, если пустое значение указано в этом предложении.

Если ни одно выражение $N$ в списке предложений WHEN не равно исходному выражению, то, в случае присутствия предложения ELSE, возвращается значение по умолчанию (или NULL, если именно оно указано в этом предложении). Если же в этом случае отсутствует предложение ELSE, то функция возвращает значение NULL.

Если исходное выражение имеет значение NULL, то оно не будет соответствовать ни одному из выражений $N$, даже тем, которые имеют значение NULL.

Есть еще один синтаксический вариант функции CASE — поисковый CASE:

Листинг 36.104 Синтаксис поискового выражения CASE

CASE
 WHEN <логическое выражение> THEN {<результат> | NULL}
 [WHEN <логическое выражение> THEN {<результат> | NULL}] ...
 [ELSE {<выражение по умолчанию> | NULL}]
END

Здесь <логическое выражение> даёт тройной логический результат: TRUE, FALSE или NULL. Первое выражение, возвращающее TRUE, определяет результат. Если нет выражений, возвращающих TRUE, то в качестве результата берётся <выражение по умолчанию> из ветви ELSE. Если нет выражений, возвращающих TRUE, и ветвь ELSE отсутствует, результатом будет NULL.

В этих операторах CASE, результаты не должны быть литеральным значением: они могут быть полями или именами переменных, сложными выражениями, или иметь значение NULL.

См. также функции IIF, DECODE, оператор IF-THEN-ELSE.

COALESCE

Возвращает первое по порядку непустое значение в списке.

Листинг 36.105 Синтаксис функции COALESCE

COALESCE (<выражение 1>, <выражение 2> [, <выражение 3>]...)

Выполняется просмотр выражений в списке слева направо. Функция возвращает первое встретившееся непустое значение (NOT NULL). Если все выражения в списке имеют пустое значение, то функция возвращает NULL.

См. также функции CASE-WHEN-ELSE, NULLIF, IIF, операторы IF-THEN-ELSE, WHILE-DO.

DECODE

Является сокращенным вариантом функции CASE-WHEN-ELSE. Дает возможность выбрать возвращаемое значение из множества различных выражений.

Листинг 36.106 Синтаксис функции DECODE

DECODE(<исходное выражение>,
 <выражение 1>, {<результат 1> | NULL}
 [, <выражение 2>, {<результат 2> | NULL}]...
 [{<значение по умолчанию> | NULL}])

Исходное выражение возвращает значение, с которым сравниваются выражения $N$ в последующих параметрах. Если исходное выражение равно выражению $N$ в соответствующем параметре, то функция возвращает результат $N$ или NULL, если пустое значение указано в этом предложении.

Если ни одно выражение $N$ в списке не равно исходному выражению, то, в случае присутствия последнего элемента в списке, возвращается значение по умолчанию (или NULL, если именно оно указано в этом значении).

См. также функции IIF, CASE-WHEN-ELSE, оператор IF-THEN-ELSE.

IIF

Проверяет условие, если оно истинно, то возвращает первое значение, иначе — второе.

Листинг 36.107 Синтаксис функции IIF

IIF (<условие>, <значение 1> <значение 2>)

См. также функции CASE-WHEN-ELSE, NULLIF, COALESCE, операторы IF-THEN-ELSE, WHILE-DO.

MAXVALUE

Отыскивает максимальное значение в заданном списке. Применима к любому типу данных. Синтаксис функции:

Листинг 36.108 Синтаксис функции MAXVALUE

MAXVALUE (<значение> [, <значение>]...)

См. также функции MIN, MAX, MINVALUE, GREATEST, LEAST.

MINVALUE

Отыскивает минимальное значение в заданном списке. Применима к любому типу данных. Синтаксис функции:

Листинг 36.109 Синтаксис функции MINVALUE

MINVALUE (<значение> [, <значение>]...)

См. также функции MIN, MAX, MAXVALUE, GREATEST, LEAST.

NULLIF

Проверяет два значения на равенство, если они равны, возвращает NULL, иначе первое значение.

Листинг 36.110 Синтаксис функции NULLIF

NULLIF(<значение 1>, <значение 2>)

См. также функции CASE-WHEN-ELSE, IIF, COALESCE, операторы IF-THEN-ELSE, WHILE-DO.

GREATEST

Возвращает максимальное значение в заданном списке. Применима к любому типу данных. Синтаксис функции:

Листинг 36.111 Синтаксис функции GREATEST

GREATEST(<значение> [, <значение>]...)

См. также функции MIN, MAX, MAXVALUE, MINVALUE, LEAST.

LEAST

Возвращает минимальное значение в заданном списке. Применима к любому типу данных. Синтаксис функции:

Листинг 36.112 Синтаксис функции LEAST

LEAST(<значение> [, <значение>]...)

См. также функции MIN, MAX, MINVALUE, MAXVALUE, GREATEST.

Функции мониторинга ЦП

CPU_LOAD

Возвращает среднюю загрузку процессора в течение заданного интервала. Синтаксис выглядит следующим образом:

Листинг 36.113 Синтаксис функции CPU_LOAD

CPU_LOAD(<интервал>)

Функция выполняет два измерения загрузки процессора, между которыми спит в течение указанного интервала (в миллисекундах). По умолчанию тайм-аут — 1 секунда.

select CPU_LOAD(500) from rdb$database;

Функции для работы с LDAP

LDAP_ATTR

Читает указанный аттрибут из LDAP записи текущего пользователя.

Листинг 36.114 Синтаксис функции LDAP_ATTR

LDAP_ATTR(<имя атрибута>)

Чтобы использовать эту функцию, параметры аутентификации LDAP должны быть указаны в firebird.conf.

select LDAP_ATTR('mail') from rdb$database;

36.2. Агрегатные функции

Агрегатные функции выполняют вычисление на наборе значений и возвращают одиночное значение. Агрегатные функции, за исключением COUNT, не учитывают значения NULL. Агрегатные функции часто используются совместно с предложением GROUP BY.

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

Список выбора инструкции SELECT (вложенный или внешний запрос);
Предложение HAVING.

Обобщённый синтаксис агрегатных функций выглядит следующим образом:

Листинг 36.115 Обобщённый синтаксис агрегатных функций

<агрегатная функция>([ALL | DISTINCT] <выражение>) [FILTER (WHERE <условие>)] [OVER ({<спецификация окна> | <имя окна>})]

Предложение FILTER

Предложение FILTER расширяет агрегатные функции дополнительным предложением WHERE. Если используется предложение FILTER, то результат агрегата строится только из строк, которые также удовлетворяют условию в дополнительном предложении WHERE.

Как правило, предложение фильтра может быть реализовано с использованием выражения CASE внутри агрегатной функции: условие фильтра должно быть помещено в предложение WHEN, значение, которое должно быть агрегировано в предложение THEN. Поскольку агрегатные функции обычно пропускают значения NULL, неявное предложение ELSE NULL достаточно, чтобы игнорировать не подходящие под условия фильтрации строки. Следующие два выражения эквивалентны:

SUM(<выражение>) FILTER(WHERE <условие>)

и

SUM(CASE WHEN <условие> THEN <выражение> END)

Для COUNT(*) этот пример выглядит иначе, потому что выражение * не может быть использовано в предложении THEN. Вместо этого обычно используется любое константное значение не равное NULL.

COUNT(*) FILTER(WHERE <условие>)

и

SUM(CASE WHEN <условие> THEN 1 END)

SELECT
  invoice_year,
  SUM(revenue) FILTER (WHERE invoice_month = 1) AS jan_revenue,
  SUM(revenue) FILTER (WHERE invoice_month= 2) AS feb_revenue,
  ...
  SUM(revenue) FILTER (WHERE invoice_month = 12) AS dec_revenue
FROM (
  SELECT
    EXTRACT(YEAR FROM invoices.invoice_date) AS invoice_year,
    EXTRACT(MONTH FROM invoices.invoice_date) AS invoice_month, invoices.revenue AS revenue
  FROM invoices
)
GROUP BY invoice_year;

AVG

Агрегатная функция. Можно использовать в качестве оконной. Вычисляет среднее значение среди множества значений числового столбца или выражения. Синтаксис:

Листинг 36.116 Синтаксис функции AVG

AVG([ALL | DISTINCT] <выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: тот же что и аргумент функции <выражение>

Ключевое слово ALL (принимается по умолчанию) означает, что в подсчете должны принимать участие все непустые значения, полученные оператором SELECT.

Ключевое слово DISTINCT указывает, что из исходных значений для подсчета среднего значения должны исключаться дублирующие значения.

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

Если при выполнении оператора SELECT было получено нулевое количество записей или содержит только значения NULL, то функция возвращает пустое значение NULL.

SELECT
  dept_no,
  AVG(salary)
FROM employee
GROUP BY dept_no;

См. также агрегатные функции MIN, COUNT, SUM, LIST, Оконные функции.

COUNT

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

Листинг 36.117 Синтаксис функции COUNT

COUNT ({* | [ALL | DISTINCT] <выражение>}) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: BIGINT

При указании DISTINCT из выборки устраняются дубликаты, ALL является значением по умолчанию для всех выборки значений не NULL.

Если вместо выражения <выражение> указана звёздочка (*), то будут подсчитаны все строки. Функция COUNT(*) не принимает параметры и не может использоваться с ключевым словом DISTINCT. Для функции COUNT(*) не нужен параметр <выражение>, так как по определению она не использует сведения о каких-либо конкретных столбцах. Функция COUNT(*) возвращает количество строк в указанной таблице, не отбрасывая дублированные строки. Она подсчитывает каждую строку отдельно. При этом учитываются и строки, содержащие значения NULL.

Для пустой выборки данных или если при выборке окажутся одни значения, содержащие NULL, функция возвратит значение равное 0.

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

SELECT
  dept_no,
  COUNT(*) AS cnt,
  COUNT(DISTINCT full_name) AS cnt_name
FROM employee
GROUP BY dept_no;

См. также агрегатные функции MIN, MAX, AVG, SUM, LIST, Оконные функции.

LIST

Агрегатная функция. Можно использовать в качестве оконной. Объединяет в один объект типа BLOB все значения элементов выборки, которые не равны NULL. При пустой выборке функция возвратит NULL. Синтаксис функции:

Листинг 36.118 Синтаксис функции LIST

LIST ([ALL | DISTINCT] <выражение> [, '<разделитель>']) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: BLOB

Тип возвращаемого значения текстовый BLOB за исключением тех случаев, когда выражением являются BLOB других подтипов.

Ключевое слово ALL (значение по умолчанию) указывает, что в список попадают все значения выборки, не содержащие NULL. Ключевое слово DISTINCT устраняет дубликаты.

В функции можно задать разделитель — произвольный символ или группу символов, заключенные в апострофы, которые в результирующем списке будут отделять одно полученное значение от другого. Если разделитель не указан, то будет использован символ запятой. Можно в качестве разделителя задать два подряд идущих апострофа. В этом случае никакой разделитель не будет использован, значения будут соединяться друг с другом.

Выражение может содержать столбец таблицы, константу, переменную, выражение, неагрегатную функцию или UDF, которая возвращает строковый тип данных или BLOB. Поля типа дата/время и числовые преобразуются к строке. Агрегатные функции в качестве выражения не допускаются.

Примечание

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

-- Получение списка, порядок не определён
SELECT LIST (display_name, '; ' )
FROM GR_WORK;
-- Получение списка в алфавитном порядке
SELECT LIST (display_name, '; ' )
FROM (SELECT display_name
      FROM GR_WORK
      ORDER BY display_name);

См. также агрегатные функции MIN, MAX, AVG, COUNT, SUM, Оконные функции.

MAX

Агрегатная функция. Можно использовать в качестве оконной. Отыскивает максимальный элемент выборки, которые не равны NULL. При пустой выборке, или при выборке из одних NULL функция возвратит NULL. Применима к любому типу данных. Синтаксис функции:

Листинг 36.119 Синтаксис функции MAX

MAX ([ALL | DISTINCT] <выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION или масштабируемый BIGINT в зависимости от типа аргумента функции <выражение>

Здесь <выражение> может быть имя столбца, константа, переменная, выражение, неагрегатная функция или UDF. Агрегатные функции в качестве выражения не допускаются.

Если аргумент функции строка, то функция вернёт значение, которое окажется последним в сортировке при применении COLLATE.

Параметр DISTINCT не имеет смысла при использовании функцией MAX и доступен только для совместимости со стандартом.

SELECT
  dept_no,
  MAX(salary)
FROM employee
GROUP BY dept_no;

См. также агрегатные функции MIN, MINVALUE, MAXVALUE, Оконные функции.

MIN

Агрегатная функция. Можно использовать в качестве оконной. Отыскивает минимальный элемент выборки, которые не равны NULL. При пустой выборке, или при выборке из одних NULL функция возвратит NULL. Применима к любому типу данных. Синтаксис функции:

Листинг 36.120 Синтаксис функции MIN

MIN ([ALL | DISTINCT] <выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: тот же что и аргумент функции <выражение>

Здесь <выражение> может быть имя столбца, константа, переменная, выражение, неагрегатная функция или UDF. Агрегатные функции в качестве выражения не допускаются.

Если аргумент функции строка, то функция вернёт значение, которое окажется первым в сортировке при применении COLLATE.

Параметр DISTINCT не имеет смысла при использовании функцией MIN и доступен только для совместимости со стандартом.

См. также агрегатные функции MAX, MINVALUE, MAXVALUE, Оконные функции.

SUM

Агрегатная функция. Можно использовать в качестве оконной. Функция возвращает сумму элементов выборки, которые не равны NULL. При пустой выборке, или при выборке из одних NULL функция возвратит NULL.

Листинг 36.121 Синтаксис функции SUM

SUM ([ALL | DISTINCT] <выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: тот же что и аргумент функции <выражение>

Здесь <выражение> может быть имя столбца, константа, переменная, выражение, неагрегатная функция или UDF, имеющие числовой тип данных. Агрегатные функции в качестве выражения не допускаются.

ALL является опцией по умолчанию. При ней обрабатываются все значения из выборки, не содержащие NULL. При указании DISTINCT из выборки устраняются дубликаты, после осуществляется подсчёт.

SELECT
  dept_no,
  SUM(salary)
FROM employee
GROUP BY dept_no;

См. также агрегатные функции MIN, MAX, AVG, COUNT, LIST, Оконные функции.

36.3. Статистические функции

Статистические функции являются агрегатными функциями. Эти функции не учитывают значения NULL. К аргументу статистической функции не применимы параметры ALL и DISTINCT.

Статистические функции часто используются совместно с предложением GROUP BY. Любую из статистических функций можно использовать в качестве оконной.

CORR

Функция CORR возвращает коэффициент корреляции для пары выражений, возвращающих числовые значения.

Листинг 36.122 Синтаксис функции CORR

CORR(<выражение1>, <выражение2>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

В статистическом смысле, корреляция - это степень связи между переменными. Связь между переменными означает, что значение одной переменной можно в определённой степени предсказать по значению другой. Коэффициент корреляции представляет степень корреляции в виде числа в диапазоне от -1 (высокая обратная корреляция) до 1 (высокая корреляция). Значение 0 соответствует отсутствию корреляции.

Эта функция эквивалентна:

COVAR_POP(<выражение1>, <выражение2>)/(STDDEV_POP(<выражение2>) * STDDEV_POP(<выражение1>))

В случае если выборка записей пустая или содержит только значения NULL, результат будет содержать NULL.

См. также функции Оконные функции, COVAR_POP, COVAR_SAMP, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP.

COVAR_POP

Функция COVAR_POP возвращает ковариацию совокупности пар выражений с числовыми значениями.

Листинг 36.123 Синтаксис функции COVAR_POP

COVAR_POP(<выражение1>, <выражение2>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

Эта функция эквивалентна такой формуле:

(SUM(<выражение1> * <выражение2>) - SUM(<выражение1>) * SUM(<выражение2>)/COUNT(*))/COUNT(*)

В случае если выборка записей пустая или содержит только значения NULL, результат будет содержать NULL.

См. также функции COUNT, SUM, Оконные функции, CORR, COVAR_SAMP, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP.

COVAR_SAMP

Функция COVAR_SAMP возвращает выборочную ковариацию пары выражений с числовыми значениями.

Листинг 36.124 Синтаксис функции COVAR_SAMP

COVAR_SAMP(<выражение1>, <выражение2>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

Эта функция эквивалентна такой формуле:

(SUM(<выражение1> * <выражение2>) - SUM(<выражение1>) * SUM(<выражение2>)/COUNT(*))/(COUNT(*)-1)

В случае если выборка записей пустая, содержит только 1 запись или содержит только значения NULL, результат будет содержать NULL.

См. также функции COUNT, SUM, Оконные функции, CORR, COVAR_POP, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP.

STDDEV_POP

Функция STDDEV_POP возвращает среднеквадратичное отклонение для группы. Значения NULL пропускаются.

Листинг 36.125 Синтаксис функции STDDEV_POP

STDDEV_POP(<выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION или NUMERIC в зависимости от типа выражения

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

Эта функция эквивалентна:

SQRT(VAR_POP(<выражение>))

В случае если выборка записей пустая или содержит только значения NULL, результат будет содержать NULL.

См. также функции Оконные функции, COVAR_POP, COVAR_SAMP, CORR, STDDEV_SAMP, VAR_POP, VAR_SAMP.

STDDEV_SAMP

Функция STDDEV_SAMP возвращает стандартное отклонение для группы. Значения NULL пропускаются.

Листинг 36.126 Синтаксис функции STDDEV_SAMP

STDDEV_SAMP(<выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION или NUMERIC в зависимости от типа выражения

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

Эта функция эквивалентна:

SQRT(VAR_SAMP(<выражение>))

В случае если выборка записей пустая, содержит только 1 запись или содержит только значения NULL, результат будет содержать NULL.

См. также функции Оконные функции, COVAR_POP, COVAR_SAMP, CORR, STDDEV_POP, VAR_POP, VAR_SAMP.

VAR_POP

Функция VAR_POP возвращает выборочную дисперсию для группы. Значения NULL пропускаются.

Листинг 36.127 Синтаксис функции VAR_POP

VAR_POP(<выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION или NUMERIC в зависимости от типа выражения

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

Эта функция эквивалентна формуле:

(SUM(<выражение> * <выражение>) - SUM(<выражение>) * SUM(<выражение>)/COUNT(<выражение>))/COUNT(<выражение>)

В случае если выборка записей пустая или содержит только значения NULL, результат будет содержать NULL.

См. также функции COUNT, SUM, Оконные функции, COVAR_POP, COVAR_SAMP, CORR, STDDEV_SAMP, STDDEV_POP, VAR_SAMP.

VAR_SAMP

Функция VAR_SAMP возвращает несмещённую выборочную дисперсию для группы. Значения NULL пропускаются.

Листинг 36.128 Синтаксис функции VAR_SAMP

VAR_SAMP(<выражение>) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION или NUMERIC в зависимости от типа выражения

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

Эта функция эквивалентна формуле:

(SUM(<выражение> * <выражение>) - SUM(<выражение>) * SUM(<выражение>) / COUNT(<выражение>))/(COUNT(<выражение>)-1)

В случае если выборка записей пустая, содержит только 1 запись или содержит только значения NULL, результат будет содержать NULL.

См. также функции COUNT, SUM, Оконные функции, COVAR_POP, COVAR_SAMP, CORR, STDDEV_SAMP, STDDEV_POP, VAR_POP.

36.4. Функции линейной регрессии

Агрегатные функции. Функции линейной регрессии полезны для продолжения линии тренда. Линия тренда - это, как правило, закономерность, которой придерживается набор значений. Линия тренда полезна для прогнозирования будущих значений. Этот означает, что тренд будет продолжаться и в будущем. Для продолжения линии тренда необходимо знать угол наклона и точку пересечения с осью Y. Набор линейных функций включает функции для вычисления этих значений.

В синтаксисе функций, у интерпретируется в качестве переменной, зависящей от х.

Любую функции линейной регрессии из статистических функций можно использовать в качестве оконной.

REGR_AVGX

Функция REGR_AVGX вычисляет среднее независимой переменной линии регрессии.

Листинг 36.129 Синтаксис функции REGR_AVGX

REGR_AVGX(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

Эта функция эквивалентна:

SUM(CASE WHEN x IS NOT NULL AND y IS NOT NULL THEN x END):REGR_COUNT(y, x)

См. также функции SUM, Оконные функции, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY.

REGR_AVGY

Функция REGR_AVGY вычисляет среднее зависимой переменной линии регрессии.

Листинг 36.130 Синтаксис функции REGR_AVGY

REGR_AVGY(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

Эта функция эквивалентна:

SUM(CASE WHEN x IS NOT NULL AND y IS NOT NULL THEN y END):REGR_COUNT(y, x)

См. также функции SUM, Оконные функции, REGR_AVGX, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY.

REGR_COUNT

Функция REGR_COUNT возвращает количество непустых пар, используемых для создания линии регрессии.

Листинг 36.131 Синтаксис функции REGR_COUNT

REGR_COUNT(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: BIGINT

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

Эта функция эквивалентна:

SUM(CASE WHEN x IS NOT NULL AND y IS NOT NULL THEN 1 END)

См. также функции SUM, Оконные функции, REGR_AVGX, REGR_AVGY, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY.

REGR_INTERCEPT

Функция REGR_INTERCEPT вычисляет точку пересечения линии регрессии с осью Y.

Листинг 36.132 Синтаксис функции REGR_INTERCEPT

REGR_INTERCEPT(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

Эта функция эквивалентна:

REGR_AVGY(y, x) - REGR_SLOPE(y, x) * REGR_AVGX(y, x)

См. также функции Оконные функции, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY.

REGR_R2

Функция REGR_R2 вычисляет коэффициент детерминации, или R-квадрат, линии регрессии.

Листинг 36.133 Синтаксис функции REGR_R2

REGR_R2(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

Эта функция эквивалентна:

POWER(CORR(y, x), 2)

См. также функции CORR, Оконные функции, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY.

REGR_SLOPE

Функция REGR_SLOPE вычисляет угол наклона линии регрессии.

Листинг 36.134 Синтаксис функции REGR_SLOPE

REGR_SLOPE(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

Эта функция эквивалентна:

COVAR_POP(y, x)/VAR_POP(CASE WHEN x IS NOT NULL AND y IS NOT NULL THEN x END)

См. также функции COVAR_POP, VAR_POP, Оконные функции, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SXX, REGR_SXY, REGR_SYY.

REGR_SXX

Функция REGR_SXX показывает диагностическую статистику, используемую для анализа регрессии.

Листинг 36.135 Синтаксис функции REGR_SXX

REGR_SXX(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

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

Эта функция эквивалентна:

REGR_COUNT(y, x) * VAR_POP(<X>)

<X> ::= CASE WHEN x IS NOT NULL AND y IS NOT NULL THEN x END

См. также функции VAR_POP, Оконные функции, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXY, REGR_SYY.

REGR_SXY

Функция REGR_SXY показывает диагностическую статистику, используемую для анализа регрессии.

Листинг 36.136 Синтаксис функции REGR_SXY

REGR_SXY(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

В синтаксисе функций, y интерпретируется в качестве переменной, зависящей от x.

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

Эта функция эквивалентна:

REGR_COUNT(y, x) * COVAR_POP(y, x)

См. также функции COVAR_POP, Оконные функции, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SYY.

REGR_SYY

Функция REGR_SYY показывает диагностическую статистику, используемую для анализа регрессии.

Листинг 36.137 Синтаксис функции REGR_SYY

REGR_SYY(y, x) [FILTER (WHERE <условие>)]
[OVER ({<спецификация окна> | <имя окна>})]

Тип возвращаемого результата: DOUBLE PRECISION

В синтаксисе функций, y интерпретируется в качестве переменной, зависящей от x.

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

Эта функция эквивалентна:

REGR_COUNT(y, x) * VAR_POP(<Y>)

<Y> ::= CASE WHEN x IS NOT NULL AND y IS NOT NULL THEN y END

См. также функции VAR_POP, Оконные функции, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY.

36.5. Оконные функции

Оконная функция выполняет вычисления над списком строк в таблице, которые как-то относятся к текущей строке. Это сравнимо с типом вычислений, которые могут быть выполнены с помощью какой-либо агрегатной функции. Но в отличие от обычных агрегатных функций, использование оконной функции не заставляет строки группироваться в одну; строки сохраняют свои отдельные значения. Другими словами, оконная функция позволяет получить доступ более чем только к текущей строке результата запроса.

Синтаксически вызов оконной функции есть указание её имени, за которым всегда следует ключевое слово OVER() с возможными аргументами внутри скобок. В этом и заключается её синтаксическое отличие от обычной функции или агрегатной функции. Оконные функции могут находиться только в списке SELECT и предложении ORDER BY.

Предложение OVER может содержать разбивку по группам ("секционирование"), сортировку и рамку окна.

Листинг 36.138 Синтаксис оконных функций

 <оконная функция> ::=
   <агрегатная функция> OVER <имя или определение окна>
 | <оконная функция> ([<выражение> [, <выражение> ...]])
   OVER <имя или определение окна>

 <имя или определение окна> ::=
 (<определение окна>) | <имя существующего окна>

 <тип оконной функции> ::=
   <ранжирующая функция>
 | <навигационная функция>

 <ранжирующая функция> ::=
   RANK
 | DENSE_RANK
 | PERCENT_RANK
 | ROW_NUMBER
 | CUME_DIST
 | NTILE

 <навигационная функция>
   LEAD
 | LAG
 | FIRST_VALUE
 | LAST_VALUE
 | NTH_VALUE

 <определение окна> ::=
   [<имя существующего окна>]
     [<выражение секционирования>]
     [<выражение сортировки>]
     [<рамка окна>]

 <выражение секционирования> ::=
   PARTITION BY <выражение> [, <выражение> ...]

 <выражение сортировки> ::=
   ORDER BY <определение сортировки> [, <определение сортировки> ...]

 <определение сортировки> ::=
   <выражение> [<определение порядка>] [<порядок null>]

 <определение порядка> ::=
   ASC  | ASCENDING
 | DESC | DESCENDING

 <порядок null> ::=
   NULLS FIRST
 | NULLS LAST

 <рамка окна> ::= { RANGE | ROWS } <границы рамки>

 <границы рамки> ::=
   <начало рамки>
 | <рамка между>

 <начало рамки> ::=
   UNBOUNDED PRECEDING
 | <выражение> PRECEDING
 | CURRENT ROW

 <рамка между> ::=
   BETWEEN { UNBOUNDED PRECEDING | <выражение> PRECEDING
           | CURRENT ROW | <выражение> FOLLOWING }
   AND { <выражение> PRECEDING | CURRENT ROW
       | <выражение> FOLLOWING | UNBOUNDED FOLLOWING }

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

Имя существующего окна - именованное окно, определенное с помощью пункта WINDOW в текущем запросе.

Агрегатные функции

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

Допустим, у нас есть таблица EMPLOYEE со столбцами FULL_NAME, DEPT_NO и SALARY. Нам необходимо показать для каждого сотрудника, соответствующую ему заработную плату и процент от фонда заработной платы. Простым запросом это решается следующим образом:

select
   FULL_NAME,
   DEPT_NO,
   SALARY,
   SALARY / (select sum(SALARY) from employee) percentage
from employee
order by FULL_NAME;

Запрос повторяется и может работать довольно долго, особенно если EMPLOYEE является сложным представлением.

Этот запрос может быть переписан в более быстрой и элегантной форме с использованием оконных функций:

select
   FULL_NAME,
   DEPT_NO,
   SALARY,
   SALARY / sum(SALARY) OVER () percentage
from employee
order by FULL_NAME;

Здесь sum(salary) OVER () вычисляет сумму всех зарплат из запроса (таблицы сотрудников).

Секционирование

Как и агрегатные функции, которые могут работать отдельно или по отношению к группе, оконные функции тоже могут работать для групп, которые называются "секциями" (partition) или разделами.

<оконная функция>(...) OVER (PARTITION BY <выражение> [, <выражение> ...])

Для каждой строки, оконная функция обрабатывает только строки, которые попадают в ту же самую секцию, что и текущая строка.

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

Продолжая пример с сотрудниками, вместо того чтобы считать процент зарплаты каждого сотрудника от суммарной зарплаты сотрудников, посчитаем процент от суммарной зарплаты сотрудников того же отдела:

select
   FULL_NAME,
   DEPT_NO,
   SALARY,
   SALARY / sum(SALARY) OVER (PARTITION BY DEPT_NO) percentage
from employee
order by FULL_NAME;

Сортировка

Предложение ORDER BY может использоваться как с секционированием, так и без него. Предложение ORDER BY внутри OVER определяет порядок, в котором оконная функция будет обрабатывать строки. Этот порядок не обязательно должен совпадать с порядком вывода строк.

С оконными функциями связана важная особенность: для каждой строки есть набор строк в ее разделе, называемый рамкой окна. По умолчанию, при указании ORDER BY, рамка состоит из всех строк от начала раздела до текущей строки и строк, равных текущему выражению ORDER BY. Без ORDER BY рамка по умолчанию состоит из всех строк в разделе.

В результате для стандартных агрегатных функций предложение ORDER BY дает результаты агрегации частично по мере обработки строк.

select
   EMP_NO,
   SALARY,
   SUM(SALARY) OVER (ORDER BY SALARY) AS cumul_salary
from employee
order by SALARY;

EMP_NO SALARY CUMUL_SALARY
====== ====== =============
3      8.00   8.00
4      9.00   17.00
1      10.00  37.00
5      10.00  37.00
2      12.00  49.00

В этом случае cumul_salary возвращает частичную/накопительную агрегацию (функции SUM). Может показаться странным, что значение 37,00 повторяется для идентификаторов 1 и 5, но так и должно быть. Ключи ORDER BY группируются вместе, и агрегация вычисляется один раз (но суммирует два 10.00). Чтобы избежать этого, можно добавить поле ID в конец предложения ORDER BY.

Можно использовать несколько окон с разной сортировкой, а также такие пункты ORDER BY, как ASC/DESC и NULLS {FIRST | LAST}.

С секциями предложение ORDER BY работает таким же образом, но на границе каждой секции агрегаты сбрасываются.

Все агрегатные функции могут использовать ORDER BY, кроме LIST().

Следующий пример показывает сумму кредита, накопленную сумму выплат и остаток по выплатам.

select
   payments.id AS id,
   payments.bydate AS bydate,
   credit.amount AS credit_amount,
   payments.amount AS pay,
   SUM(payments.amount) OVER(ORDER BY payments.bydate) AS s_amount,
   SUM(payments.amount) OVER(ORDER BY payments.bydate, payments.id) AS s_amount2,
   credit.amount - SUM(payments.amount) OVER(ORDER BY payments.bydate, payments.id) AS balance
from credit
JOIN payments ON payments.credit_id = credit.id
WHERE credit.id = 1
ORDER BY payments.bydate;

ID BYDATE      CREDIT_AMOUNT PAY    S_AMOUNT S_AMOUNT2 BALANCE
== =========== ============= ====== ======== ========= ===========
1 2023-01-01   1000.00       100.00 100.00   100.00    900.00
2 2023-01-05   1000.00       200.00 300.00   300.00    700.00
3 2023-01-10   1000.00       150.00 450.00   450.00    550.00
4 2023-01-15   1000.00       250.00 700.00   700.00    300.00
5 2023-01-20   1000.00       300.00 1000.00  1000.00   0.00
6 2023-01-25   1000.00        50.00 1050.00  1050.00   -50.00

Рамка окна

Набор строк внутри секции, которым оперирует оконная функция, называется рамкой окна (кадры окна). Рамка окна определяет, какие строки следует учитывать для текущей строки при оценке оконной функции

Рамка окна состоит из трёх частей: единица (unit), начальная граница и конечная граница. В качестве единицы могут быть использованы ключевые слова RANGE или ROWS, которые указывают, каким образом будут работать границы окна. Границы окна определяются следующими выражениями:

UNBOUNDED PRECEDING
<выражение> PRECEDING
UNBOUNDED FOLLOWING
<выражение> FOLLOWING
CURRENT ROW

При использовании RANGE в ORDER BY должно быть указано только одно выражение, и оно должно быть числового типа, DATE, TIME или TIMESTAMP. Для PRECEDING выражение вычитается из выражения в ORDER BY, а для FOLLOWING добавляется. Для CURRENT ROW выражение в ORDER BY используется как есть.

Все строки внутри секции, находящиеся между границами, считаются частью результирующей рамки окна.

При использовании ROWS на предложение ORDER BY не накладывается ограничений на количество и типы выражений. В этом случае <выражение> PRECEDING указывает количество строк предшествующее текущей строке, а <выражение> FOLLOWING количество строк после текущей строки.

UNBOUNDED PRECEDING и UNBOUNDED FOLLOWING работают одинаково для предложений ROWS и RANGE.

Фраза UNBOUNDED PRECEDING указывает, что окно начинается с первой строки секции. UNBOUNDED PRECEDING может быть указано только как начальная точка окна.

Фраза UNBOUNDED FOLLOWING указывает, что окно заканчивается последней строкой секции. UNBOUNDED FOLLOWING может быть указано только как конечная точка окна.

Фраза CURRENT ROW указывает, что окно начинается или заканчивается на текущей строке при использовании совместно с предложением ROWS или что окно заканчивается на текущем значении при использовании с предложением RANGE. CURRENT ROW может быть задана и как начальная, и как конечная точка.

Предложение BETWEEN используется совместно с ключевым словом ROWS или RANGE для указания нижней (начальной) или верхней (конечной) граничной точки окна. Верхняя граница не может быть меньше нижней границы.

Примечание

Если указана только начальная точка окна, то конечной точкой окна считается CURRENT ROW. Например, если указано ROWS 1 PRECEDING, то это аналогично указанию ROWS BETWEEN 1 PRECEDING AND CURRENT ROW.

Некоторые оконные функции игнорируют выражение рамки:

ROW_NUMBER, LAG и LEAD всегда работают как ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
DENSE_RANK, RANK, PERCENT_RANK и CUME_DIST работают как RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
FIRST_VALUE, LAST_VALUE и NTH_VALUE работают на рамке, но RANGE работает идентично ROWS.

Таким образом, предложения ROWS и RANGE позволяют довольно гибко настроить размер плавающего окна. Чаще всего встречаются следующие варианты:

Нижняя граница фиксирована (совпадает с первой строкой упорядоченной группы), а верхняя граница ползёт (совпадает с текущей строкой упорядоченной группы). В этом случае получаем нарастающий итог (кумулятивный агрегат). В этом случае размер окна меняется (расширяется в одну сторону) и само окно движется за счёт расширения. Возможна и обратная ситуация, когда нижняя граница ползёт, а верхняя зафиксирована. В этом случае окно будет сужаться.
Если верхняя и нижняя границы фиксированы относительно текущей строки, например 1 строка до текущей и 2 после текущей, то получаем скользящий агрегат. В этом случае размер окна фиксирован, а само окно скользит.

Окна диапазона

Окна диапазона объединяют строки в соответствии с заданным порядком. Например, если рамка окна задана выражением RANGE 5 PRECEDING, то будет сгенерировано перемещающееся окно, включающее предыдущие строки группы, значение которых меньше текущего не более чем на 5.

SELECT FIRST 5
   emp_no,
   salary,
   SUM(salary) OVER() AS s1,
   SUM(salary) OVER(ORDER BY salary) AS s2,
   SUM(salary) OVER(ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s3,
   SUM(salary) OVER(ORDER BY salary RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s4,
   SUM(salary) OVER(ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s5,
   SUM(salary) OVER(ORDER BY salary RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s6,
   SUM(salary) OVER(ORDER BY salary RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s7,
   SUM(salary) OVER(ORDER BY salary RANGE 1 PRECEDING) AS s8
FROM employee;

EMP_NO SALARY  S1      S2      S3      S4      S5      S6      S7      S8
====== ======= ======= ======= ======= ======= ======= ======= ======= =======
3      8.00    49.00   8.00    8.00    49.00   49.00   17.00   17.00   8.00
4      9.00    49.00   17.00   17.00   41.00   49.00   29.00   37.00   17.00
1      10.00   49.00   37.00   37.00   32.00   49.00   20.00   29.00   29.00
5      10.00   49.00   37.00   37.00   32.00   49.00   20.00   29.00   29.00
2      12.00   49.00   49.00   49.00   12.00   49.00   12.00   12.00   12.00

Для того чтобы понять, какие значения будут входить в диапазон, можно использовать функции FIRST_VALUE и LAST_VALUE. Это помогает увидеть диапазоны окна и проверить, корректно ли установлены параметры.

Окна строк

Окна срок задаются в физических единицах, строках. Например, если рамка окна задана выражением ROWS 5 PRECEDING, то окно будет включать в себя до 6 строк: текущую и пять предыдущих (порядок определяется конструкцией ORDER BY).

SELECT FIRST 5
   emp_no,
   salary,
   SUM(salary) OVER() AS s1,
   SUM(salary) OVER(ORDER BY salary) AS s2,
   SUM(salary) OVER(ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s3,
   SUM(salary) OVER(ORDER BY salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s4,
   SUM(salary) OVER(ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s5,
   SUM(salary) OVER(ORDER BY salary ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s6,
   SUM(salary) OVER(ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s7,
   SUM(salary) OVER(ORDER BY salary ROWS 1 PRECEDING) AS s8
FROM employee;

EMP_NO SALARY  S1      S2      S3      S4      S5      S6      S7      S8
====== ======= ======= ======= ======= ======= ======= ======= ======= =======
3      8.00    49.00   8.00    8.00    49.00   49.00   17.00   17.00   8.00
4      9.00    49.00   17.00   17.00   41.00   49.00   19.00   27.00   17.00
5      10.00   49.00   37.00   37.00   22.00   49.00   20.00   29.00   19.00
1      10.00   49.00   37.00   27.00   32.00   49.00   22.00   32.00   20.00
2      12.00   49.00   49.00   49.00   12.00   49.00   12.00   22.00   22.00

Именованные окна

Предложение WINDOW можно использовать для явного наименования окна, например, чтобы избежать повторяющихся или сложных выражений. Именованное окно можно использовать в предложении OVER для ссылки на определение окна, например OVER <имя окна>, и в качестве базы другого именованного или встроенного окна (в предложении OVER). Окна с рамкой (с предложениями RANGE и ROWS) не могут быть использованы в качестве базового окна (но могут быть использованы в предложении OVER <имя окна>).

Окно, которое ссылается на базовое окно, не может содержать предложения PARTITION BY или переопределять сортировку (ORDER BY) базового окна.

SELECT first 5
   emp_no,
   dept_no,
   salary,
   count(*) OVER w1,
   first_value(salary) OVER w2,
   last_value(salary) OVER w2,
   sum(salary) over (w2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s
FROM employee
WINDOW w1 AS (PARTITION BY dept_no),
       w2 AS (w1 ORDER BY salary)
ORDER BY dept_no, salary;

Ранжирующие функции

Ранжирующие функции вычисляют порядковый номер ранга внутри секции окна.

Эти функции могут применяться с использованием секционирования и сортировки и без них. Однако их использование без сортировки почти никогда не имеет смысла.

Функции ранжирования могут быть использованы для создания различных типов инкрементных счётчиков. Рассмотрим SUM(1) OVER (ORDER BY SALARY) в качестве примера того, что они могут делать, каждая из них различным образом. Ниже приведён пример запроса, который позволяет сравнить их поведение по сравнению с SUM.

SELECT first 5
   emp_no,
   salary,
   DENSE_RANK() OVER (ORDER BY salary),
   RANK() OVER (ORDER BY salary),
   PERCENT_RANK() OVER(ORDER BY salary),
   CUME_DIST() OVER(ORDER BY salary),
   NTILE(3) OVER(ORDER BY salary),
   ROW_NUMBER() OVER (ORDER BY salary) ROW_NUM,
   SUM(1) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;

EMP_NO SALARY DENSE_RANK RANK PERCENT_RANK       CUME_DIST          NTILE ROW_NUM SUM
====== ====== ========== ==== ================== ================== ===== ======= ===
3      8.00   1          1    0.000000000000000 0.2000000000000000  1     1       1
4      9.00   2          2    0.2500000000000000 0.4000000000000000 1     2       2
1      10.00  3          3    0.5000000000000000 0.8000000000000000 2     3       4
5      10.00  3          3    0.5000000000000000 0.8000000000000000 2     4       4
2      12.00  4          5    1.000000000000000 1.000000000000000   3     5       5

Разница между DENSE_RANK и RANK заключается в том, что только в RANK есть пропуски, связанные с дублированием строк (относительно упорядочивания окна). DENSE_RANK продолжает присваивать порядковые номера после дублирующейся зарплаты. С другой стороны, ROW_NUMBER всегда присваивает порядковые номера, даже при наличии дублирующихся значений.

DENSE_RANK

DENSE_RANK() OVER { <определение окна> | <имя окна>}

Тип возвращаемого результата: BIGINT

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

SELECT first 5
   emp_no,
   salary,
   DENSE_RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;

EMP_NO SALARY DENSE_RANK
====== ====== ==========
3      8.00   1
4      9.00   2
1      10.00  3
5      10.00  3
2      12.00  4

RANK

RANK() OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: BIGINT

Возвращает ранг каждой строки в секции результирующего набора. Строки с одинаковыми значениями <выражение сортировки> получают одинаковый ранг в пределах группы <выражение секционирования>, если она указана. Ранг строки вычисляется как единица плюс количество рангов, находящихся до этой строки.

SELECT FIRST 5
   emp_no,
   salary,
   RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;

EMP_NO SALARY RANK
====== ====== ====
3      8.00   1
4      9.00   2
1      10.00  3
5      10.00  3
2      12.00  5

PERCENT_RANK

PERCENT_RANK() OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: DOUBLE PRECISION

Возвращает относительный ранг текущей строки в группе строк. Функция PERCENT_RANK используется для вычисления относительного положения значения в секции или результирующем наборе запроса. Диапазон значений, возвращаемый функцией PERCENT_RANK, больше 0 и меньше или равен 1. В первой строке любого набора PERCENT_RANK равна 0. Значения NULL по умолчанию включаются и рассматриваются как наименьшие возможные значения.

Функция PERCENT_RANK вычисляется как

PERCENT_RANK = (RANK-1 текущей строки)/(<число строк в разделе> - 1)

Другими словами, P PERCENT_RANK() OVER <имя_окна> эквивалентно (RANK() OVER <имя_окна> - 1)/ CAST(COUNT(*) OVER() - 1 AS DOUBLE PRECISION) <имя_окна> - 1) / CAST(COUNT(*) OVER() - 1 AS DOUBLE PRECISION).

SELECT FIRST 5
   emp_no,
   salary,
   PERCENT_RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;

EMP_NO SALARY PERCENT_RANK
====== ====== ==================
3      8.00   0.000000000000000
4      9.00   0.2500000000000000
1      10.00  0.5000000000000000
5      10.00  0.5000000000000000
2      12.00  1.000000000000000

CUME_DIST

CUME_DIST() OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: DOUBLE PRECISION

Функция CUME_DIST рассчитывает кумулятивное распределение значения внутри раздела окна. Возвращаемое значение находится в диапазоне от 0 до 1. Функция CUME_DIST рассчитывается как (число строк, предшествующих или равных текущей) / (общее число строк в разделе). Другими словами, CUME_DIST() OVER <имя_окна> эквивалентно COUNT(*) OVER <имя_окна> / COUNT(*) OVER(). Для равных значений всегда вычисляется одно и то же значение накопительного распределения. Значения NULL по умолчанию включаются и рассматриваются как наименьшие возможные значения.

SELECT FIRST 5
   emp_no,
   salary,
   CUME_DIST() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;

EMP_NO SALARY CUME_DIST
====== ====== ==================
3      8.00   0.2000000000000000
4      9.00   0.4000000000000000
1      10.00  0.8000000000000000
5      10.00  0.8000000000000000
2      12.00  1.000000000000000

NTILE

Функция NTILE распределяет строки раздела в заданное количество групп так, чтобы размеры групп были максимально близки. Группы нумеруются, начиная с единицы. Для каждой строки функция NTILE возвращает номер группы, к которой принадлежит строка.

NTILE(<выражение>) OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: BIGINT

Выражение целочисленного типа. Указывает количество групп, на которые необходимо разделить каждую секцию. Ограничивается целым положительным литералом, именованным параметром (PSQL) или позиционным параметром (DSQL).

Если количество строк в секции не делится на <число>, то формируются группы двух размеров, отличающихся на единицу. Группы большего размера следуют перед группами меньшего размера в порядке, заданном в предложении OVER.

SELECT FIRST 5
   emp_no,
   salary,
   NTILE(3) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;

EMP_NO SALARY NTILE
====== ====== =====
3      8.00   1
4      9.00   1
1      10.00  2
5      10.00  2
2      12.00  3

ROW_NUMBER

ROW_NUMBER() OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: BIGINT

Возвращает порядковый номер строки в разделе, где 1 - первая строка в каждом из разделов.

SELECT FIRST 5
   emp_no,
   salary,
   ROW_NUMBER() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;

EMP_NO SALARY ROW_NUMBER
====== ====== ==========
3      8.00   1
4      9.00   2
1      10.00  3
5      10.00  4
2      12.00  5

Навигационные функции

Навигационные функции получают простые (не агрегированные) значения выражения из другой строки запроса в той же секции.

Предупреждение

Функции FIRST_VALUE, LAST_VALUE и NTH_VALUE оперируют на рамке окна (кадрах окна). По умолчанию, если задано предложение ORDER BY, то рамка состоит из всех строк, от начала разбиения до текущей строки, плюс любые следующие строки, которые равны текущей строке в соответствии с предложением ORDER BY, т.е.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Из-за этого результаты функций NTH_VALUE и в особенности LAST_VALUE могут показаться странными. Для устранения этого "недостатка" вы можете задать другую рамку окна, например:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

SELECT FIRST 5
   emp_no,
   salary,
   FIRST_VALUE(salary) OVER (ORDER BY salary),
   LAST_VALUE(salary) OVER (ORDER BY salary),
   NTH_VALUE(salary, 2) OVER (ORDER BY salary),
   LAG(salary) OVER (ORDER BY salary),
   LEAD(salary) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;

EMP_NO SALARY FIRST_VALUE LAST_VALUE NTH_VALUE  LAG    LEAD
====== ====== =========== ========== ========== ====== ======
3      8.00   8.00        8.00       <null>     <null> 9.00
4      9.00   8.00        9.00       9.00       8.00   10.00
1      10.00  8.00        10.00      9.00       9.00   10.00
5      10.00  8.00        10.00      9.00       10.00  12.00
2      12.00  8.00        12.00      9.00       10.00  <null>

Вариант с изменённой рамкой окна для функций LAST_VALUE и NTH_VALUE.

SELECT FIRST 5
   emp_no,
   salary,
   FIRST_VALUE(salary) OVER (ORDER BY salary),
   LAST_VALUE(salary) OVER w,
   NTH_VALUE(salary, 2) OVER w,
   LAG(salary) OVER (ORDER BY salary),
   LEAD(salary) OVER (ORDER BY salary)
FROM employee
WINDOW w AS (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)
ORDER BY salary;

EMP_NO SALARY FIRST_VALUE LAST_VALUE NTH_VALUE  LAG    LEAD
====== ====== =========== ========== ========== ====== ======
3      8.00   8.00        12.00      9.00       <null> 9.00
4      9.00   8.00        12.00      9.00       8.00   10.00
1      10.00  8.00        12.00      9.00       9.00   10.00
5      10.00  8.00        12.00      9.00       10.00  12.00
2      12.00  8.00        12.00      9.00       10.00  <null>

FIRST_VALUE

FIRST_VALUE(<выражение>) OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: тот же что и аргумент функции <выражение>

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

Функция FIRST_VALUE(<выражение>) возвращает первое значение из упорядоченного набора значений.

LAG

LAG(<выражение> [, <смещение> [, <default>]]) OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: тот же что и аргумент функции <выражение>

Функция LAG обеспечивает доступ к строке с заданным физическим смещением перед началом текущей строки. Если смещение указывает за пределы секции, то будет возвращено значение <default>, которое по умолчанию равно NULL.

Параметр <выражение> может содержать столбец таблицы, константу, переменную, выражение, неагрегатную функцию или UDF. Агрегатные функции в качестве выражения не допускаются.

Параметр <смещение> — количество строк до строки перед текущей строкой, из которой необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1. <смещение> может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целая положительная величина, или другим типом, который может быть неявно преобразован в BIGINT. <смещение> не может быть отрицательным значением или аналитической функцией.

Предположим у вас есть таблица rate, которая хранит курс валюты на каждый день. Необходимо проследить динамику изменения курса за последние пять дней.

SELECT
   bydate,
   cost,
   cost - LAG(cost) OVER(ORDER BY bydate) AS change,
   100 * (cost - LAG(cost) OVER(ORDER BY bydate)) / LAG(cost) OVER(ORDER BY bydate) AS percent_change
FROM rate
ORDER BY bydate;

BYDATE      COST   CHANGE PERCENT_CHANGE
=========== ====== ====== ==============
2014-10-27  31.00  <null> <null>
2014-10-28  31.53  0.53   1.7096
2014-10-29  31.40  -0.13  -0.4123
2014-10-30  31.67  0.27   0.8598
2014-10-31  32.00  0.33   1.0419

LAST_VALUE

LAST_VALUE(<выражение>) OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: тот же что и аргумент функции <выражение>

Функция LAST_VALUE возвращает последнее значение из упорядоченного набора значений рамки окна. Параметр <выражение> может содержать столбец таблицы, константу, переменную, выражение, неагрегатную функцию или UDF. Агрегатные функции в качестве выражения не допускаются.

LEAD

LEAD(<выражение> [, <смещение> [, <default>]]) OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: тот же что и аргумент функции <выражение>

Функция LEAD обеспечивает доступ к строке на заданном физическом смещении после текущей строки. Если смещение указывает за пределы секции, то будет возвращено значение <default>, которое по умолчанию равно NULL.

Параметр <выражение> может содержать столбец таблицы, константу, переменную, выражение, неагрегатную функцию или UDF. Агрегатные функции в качестве выражения не допускаются.

Параметр <смещение> — количество строк после текущей строки до строки, из которой необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1. <смещение> может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целая положительная величина, или другим типом, который может быть неявно преобразован в BIGINT. <смещение> не может быть отрицательным значением или аналитической функцией.

NTH_VALUE

NTH_VALUE(<выражение> [, <смещение> ]) [FROM FIRST | FROM LAST]
OVER {<определение окна> | <имя окна>}

Тип возвращаемого результата: тот же что и аргумент функции <выражение>

Функция NTH_VALUE возвращает $N$-ое значение, начиная с первой (опция FROM FIRST) или последней (опция FROM LAST) записи. По умолчанию используется опция FROM FIRST. Смещение 1 от первой записи будет эквивалентно функции FIRST_VALUE, смещение 1 от последней записи будет эквивалентно функции LAST_VALUE.

Параметр <выражение> может содержать столбец таблицы, константу, переменную, выражение, неагрегатную функцию или UDF. Агрегатные функции в качестве выражения не допускаются

Параметр <смещение> — номер записи, начиная с первой (опция FROM FIRST) или последней (опция FROM LAST) записи.

Агрегатные функции внутри оконных

В качестве аргументов оконных функций, а также в предложении OVER разрешено использование агрегатных функций (но не оконных). В этом случае сначала вычисляются агрегатные функции, а только затем на них накладываются окна оконных функций.

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

Пример использования агрегатной функции в качестве аргумента оконной:

SELECT first 5
   emp_no,
   AVG(salary) AS avg_salary,
   RANK() OVER(ORDER BY AVG(salary)) AS salary_rank
FROM employee
GROUP BY emp_no;

36.6. Табличные функции

Табличные функции возвращают набор строк, содержащий поля базовых типов. Они могут использоваться в предложении FROM в качестве источника, из которого осуществляется выборка данных. Столбцы, возвращаемые табличными функциями, можно указывать в выражениях SELECT, JOIN и WHERE.

UNLIST

Функция UNLIST преобразует входную строку в набор записей, состоящий из одного столбца.

Синтаксис функции:

Листинг 36.139 Синтаксис функции UNLIST

UNLIST(<входная строка> [, <разделитель>] [<возвращаемый тип данных>]) AS <псевдоним набора записей> [(<псевдоним результирующего столбца>)]

<разделитель> ::= <строковый литерал>

<возвращаемый тип данных> ::= RETURNING <тип данных>

Описание параметров функции:

Входная строка — Входное значение, представляющее собой любое выражение, возвращающее строку символов (строковый литерал, столбец таблицы, константа, переменная, выражение и т.д). Также может быть значением BLOB TEXT. Обязательный параметр.
Разделитель — Строковый литерал, заключенный в апострофы, который в результирующем списке будет отделять одно полученное значение от другого. Также может содержать выражение, возвращающее строку символов. Может быть значением BLOB TEXT. Если в качестве разделителя указана пустая строка, то значения будут выведены одной записью. Если разделитель не задан, то будет использован символ запятой.
Возвращаемый тип данных — Тип данных, к которому будут приведены значения в результирующем наборе записей. Если возвращаемый тип не задан, то по умолчанию используется тип VARCHAR(32). В качестве возвращаемого типа может быть указан домен.
Псевдоним набора записей — Псевдоним набора записей, возвращаемого функцией UNLIST. Обязательный параметр.
Псевдоним результирующего столбца — Псевдоним столбца, возвращаемого функцией UNLIST. Если псевдоним не указан, то по умолчанию используется UNLIST.

Пример работы функции:

SELECT * FROM UNLIST('1,2,3,4') AS A;

UNLIST
=======
1
2
3
4

Пример использования UNLIST в качестве источника данных:

CREATE TABLE TEST_TABLE (ID INT);
INSERT INTO TEST_TABLE (ID) SELECT * FROM UNLIST('1,2,3,4' RETURNING INT) AS A;
SELECT * FROM TEST_TABLE;

ID
=======
1
2
3
4