Функции в SQL

ЛЕКЦИЯ № 6 3.2.3. Функции в SQL................................................... 1 3.2.3.1. Классификация функций ......................... 1 3.2.3.2. Скалярные функции ................................. 3 3.2.3.3. Агрегатные функции ............................. 39 3.2.3.4. Функции для списка выражений .......... 42 3.2.3. Функции в SQL 3.2.3.1. Классификация функций Функции в SQL подобны любым другим запросам языка в том смысле, что они производят действия с данными и возвращают результат в качестве своего значения. В СУБД обычно имеется 3 основных класса функций: встроенные, определяемые пользователем и хранимые (процедурные). Встроенными являются функции, предопределенные в языке SQL конкретной СУБД. В SQL определено множество встроенных функций различных категорий [39, 40, 42, 44, 48]. Эти функции делятся на основные группы: 1) скалярные функции; 2) агрегатные функции; 3) функции для списка значений; 4) функции выбора вариантов; 5) оконные (аналитические) функции. Скалярные функции (их еще называют однострочными) обрабатывают одиночное значение и возвращают также одно значение. Скалярные функции разрешается использовать везде, где допускается применение выражений. Скалярные функции бывают следующих категорий:  строковые функции, которые выполняют определенные действия над строками и возвращают строковые или числовые значения (для Oracle здесь среди прочих можно отдельно выделить группу функций регулярных выражений);  числовые функции, которые возвращают числовые значения на основании заданных в аргументе значений того же типа;  функции времени и даты, которые выполняют различные действия над входными значениями времени и даты и возвращают строковое, числовое значения или значение в формате даты и времени;  функция преобразования типа. Агрегатные функции используются для получения обобщающих значений. Они, в отличие от скалярных функций, оперируют значениями столбцов множества строк. К агрегатным функциям относятся такие функции, как SUM, вычисляющая итог, MAX и MIN, возвращающие 1 соответственно наибольшее и наименьшее значения, AVG, вычисляющая среднее значение, и COUNT, вычисляющая количество значений в столбце. Функции для списка значений используются для обработки значений, заданных в списке аргументов. В Firebird такие функции представлены тремя функциями – MAXVALUE, MINVALUE и LIST. Функции MAXVALUE и MINVALUE в чем-то похожи на агрегатные функции MAX и MIN, однако выбирают максимальное и минимальное значения не из множества значений строк в одном столбце, а из значений, заданных в списке аргументов. Функция LIST объединяет обрабатываемые значения в единую строку. В Oracle функции для списка значений представлены двумя функциями – GREATEST и LEAST. Функция GREATEST возвращает наибольшее значение в списке выражений, функция LEAST возвращает наименьшее значение в списке выражений. Применительно к Oracle в отдельную группу могут быть выделены появившиеся в Oracle Database 10g функции регулярных выражений, которые значительно увеличивают возможности поиска символьных данных и манипулирования ими. Среди таких функций следующие:  REGEXP_LIKE – предикат, используемый в секции WHERE или же при определении ограничения на таблицу;  REGEXP_INSTR – возвращает позицию символа, находящегося в начале или конце соответствия для шаблона;  REGEXP_SUBSTR – возвращает подстроку, которая соответствует шаблону;  REGEXP_REPLACE – возвращает измененную входную строку, в которой все вхождения шаблона заменены заданным значением. Особое место среди встроенных скалярных функций языка SQL занимают функции выбора вариантов, которые являются разновидностью CASEвыражений. В качестве функций выбора вариантов используются функции COALESCE, NULLIF, DECODE, в Firebird и MS SQL Server также IIF. Возвращаемый этими функциями результат меняется в зависимости от значения, которое обрабатывается функцией. Оконные (аналитические) функции предназначены для статистического анализа данных в готовой выборке. Это достаточно мощный и удобный инструмент, который присутствует во многих СУБД. Например, в Oracle оконные функции поддерживаются, начиная с версии 8i, в MS SQL Server – начиная с версии 2005, в Firebird оконные функции появились только в версии 3.0. Существует несколько типов аналитических функций: 1) агрегатные функции; 2) функции ранжирования; 3) навигационные функции. Оконные функции будут подробно рассмотрены в п. 3.2.7 после изучения секций GROUP BY и ORDER BY, так как для понимания алгоритмов работы 2 аналитических функций необходимо понимание работы запросов с группировкой и сортировкой. Функции, определяемые пользователем (User Defined Function, UDF), являются вспомогательными программами, написанными на языке программирования, таком как C, C++ или Pascal, и компилированными как совместно используемые двоичные библиотеки – DLL [29]. Внешние функции можно использовать в выражениях, так же как и встроенные функции SQL. Как и встроенные функции, они могут возвращать значения для переменных или выражений SQL в хранимых процедурах и триггерах. СУБД Firebird, например, поставляет две готовые к использованию библиотеки UDF: Ib_Udf и FbUdf. Firebird загружает UDF из библиотек, находящихся в каталоге Udf каталога инсталляции или в других каталогах, указанных в параметре UdfAccess в файле конфигурации Firebird. Когда пользовательская функция написана, компилирована и инсталлирована в соответствующий каталог на сервере, она должна быть объявлена для БД, чтобы ее можно было использовать как функцию SQL. Для объявления внешней функции используется оператор DECLARE EXTERNAL FUNCTION. Можно объявить функцию также с использованием любого интерактивного инструмента SQL или скрипта. После того как функция будет объявлена для любой БД на сервере, содержащая ее библиотека будет загружаться при первом же обращении приложения к любой функции, включенной в эту библиотеку. Необходимо объявить каждую функцию, которую нужно использовать, для каждой БД, которая будет использоваться. Хранимые функции являются подпрограммами, написанными на процедурном языке СУБД. Такие функции обычно создаются для реализации бизнес-логики и являются самостоятельным объектом БД. Хранимые функции будут описаны в п. 6.3 при изучении процедурных языков. Рассмотрим подробнее различные виды встроенных функций СУБД Firebird 3.0, Oracle 11.2 XE и MS SQL Server 2014, познакомимся с правилами работы с функциями в этих СУБД на примерах. 3.2.3.2. Скалярные функции Строковые функции. Эти функции используют в качестве аргумента строку символов, а в качестве результата возвращают также символьную строку или числовое значение. Для выделения подстроки указанной длины из строкового выражения, начиная с заданной позиции, в Firebird и MS SQL Server используется функция SUBSTRING. В Firebird функция имеет формат SUBSTRING (строковое_выражение FROM позиция [FOR длина]), а в MS SQL Server SUBSTRING(строковое_выражение, позиция [, длина]). 3 Здесь позиция – позиция, начиная с которой выполняется выделение, например 1 для первого символа в строке; длина – количество выделяемых символов. Если длина не задана, то выделение производится до конца исходной строки. Задание отрицательного значения длины приводит к тому, что символы отсчитываются от конца строки, а не от начала. Задание длины, превышающей по абсолютному значению длину строки, приводит к тому, что функция возвращает NULL. Например, для вывода номеров лицевых счетов абонентов и первых трех символов их ФИО (рис. 3.21) в Firebird можно использовать запрос SELECT A.AccountCD, SUBSTRING (A.Fio FROM 1 FOR 3) AS "Fio3" FROM Abonent A;, а в MS SQL Server запрос SELECT AccountCD, SUBSTRING(Fio, 1, 3) AS "Fio3" FROM ABONENT; Следует учесть, что тип значений ACCOUNTCD Fio3 столбца Fio3 будет такой же, как и тип 005488 Акс первого аргумента (строковое 115705 Мищ выражение, из которого выделяются 015527 Кон символы). Так как ширина столбца Fio 443690 Тул таблицы Abonent равна 30 136159 Сви [VARCHAR(30)], то столбец Fio3 443069 Ста будет иметь также ширину 30, а не 3. Надо отметить, что в качестве 136160 Шма аргументов SUBSTRING могут 126112 Мар использоваться подзапросы, 136169 Ден возвращающие единственное 080613 Лук значение (<скалярный_подзапрос>). 080047 Шуб 080270 Тим Рис. 3.21. Результат работы функции SUBSTRING В качестве аргументов позиция и длина в SUBSTRING можно использовать и выражения (в том числе со скалярными функциями, возвращающими числовой результат). В Oracle вместо функции SUBSTRING используется аналогичная функция SUBSTR, имеющая формат SUBSTR(строковое_выражение, позиция [, длина]). Например, предыдущий запрос в Oracle должен быть записан так: SELECT A.AccountCD, SUBSTR(A.Fio, 1, 3) AS "Fio3" 4 FROM Abonent A; Следует отдельно отметить, что в Firebird 3.0 существует возможность использования функции SUBSTRING с регулярными выражениями [42, 43]. При этом функция имеет следующий формат: SUBSTRING (строковое_выражение SIMILAR '<регулярное_выражение>' ESCAPE 'символ_пропуска'), где <регулярное_выражение> ::= <шаблон1>символ_пропуска"<шаблон2>символ_пропуска"<шаблон3>. В таком формате функция SUBSTRING возвращает часть строки из строкового выражения, соответствующую регулярному выражению в предложении SIMILAR. Возвращаемое значение соответствует части <шаблон2> регулярного выражения, и для этого значения истинно выражение <значение> SIMILAR TO <шаблон1> || <шаблон2> || <шаблон3> ESCAPE символ_пропуска. Регулярные шаблоны составляются в соответствии с правилами, описанными в разд. 3.2.2.12. Если соответствие не установлено, возвращается значение NULL. Например, запрос вида SELECT SUBSTRING('abcabc' SIMILAR '_#"%#"_' ESCAPE '#') FROM RDB$DATABASE; вернет строку 'bcab', а запрос вида SELECT SUBSTRING('abcabc' SIMILAR '#"(abc)*#"' ESCAPE '#') FROM RDB$DATABASE; вернет строку вида 'abcabc'. Для вывода строки в обратной последовательности (начиная с конца) используется функция REVERSE. Функция имеет формат REVERSE (строковое_ выражение). Например, для вывода информации об абонентах, имеющих инициалы E.B. (рис. 3.22), можно использовать запрос SELECT * FROM Abonent WHERE REVERSE (Fio) STARTING WITH REVERSE ('Е.В.'); ACCOUNTC D 115705 STREETC D 3 HOUSEN O 1 FLATN O 82 443069 4 51 55 FIO Мищенко Е.В. Стародубце в Е.В. PHON E 769975 683014 Рис. 3.22. Результат работы функции REVERSE Функция REVERSE используется в Firebird и MS SQL Server, а в Oracle отсутствует. 5 Функции LEFT и RIGHT используются для выделения нужного количества символов соответственно из начала или конца определенной строки и имеют формат LEFT (строковое_выражение, длина), RIGHT (строковое_выражение, длина), где строковое_выражение – выражение, из которого будут выделяться символы; длина – количество выделяемых символов в начале (для LEFT) или в конце (для RIGHT) строки. Например, предыдущий запрос, реализованный с помощью функции REVERSE, можно реализовать с помощью функции RIGHT таким образом: SELECT * FROM Abonent WHERE RIGHT (Fio, 4) = 'Е.В.'; Результат выполнения запроса будет совпадать с результатом, представленным на рис. 3.22. Например, для вывода Fio абонентов, проживающих на улице с кодом 3, и первых 4-х цифр из номеров их лицевых счетов можно использовать запрос SELECT Fio, LEFT (AccountCD, 4) FROM Abonent WHERE StreetCD = 3; Результат выполнения запроса представлен на рис. 3.23. FIO LEFT Аксенов С.А. 0054 Мищенко Е.В. 1157 Конюхов В.С. 0155 Рис. 3.23. Результат работы функции LEFT Функции LEFT и RIGHT используются в Firebird и MS SQL Server, а в Oracle отсутствуют. Существует ряд функций для замены части исходной строки на другую последовательность символов. Функция OVERLAY заменяет в исходной строке подстроку, начинающуюся с номера позиция и имеющую размер длина, на значение строки для замены. Функция имеет формат OVERLAY ( исходная_строка PLACING строка_для_замены FROM позиция [ FOR длина ] ), где исходная_строка и строка_для_замены могут представлять собой строковое выражение. Если длина не указана, то по умолчанию принимается длина в символах строки для замены. Например, если требуется вывести номера телефонов абонентов с заменой первых 2-х цифр на '66', в Firebird можно использовать запрос SELECT OVERLAY (Phone PLACING '66' FROM 1) FROM Abonent; Функция OVERLAY эквивалентна следующему выражению с использованием функции SUBSTRING: SUBSTRING (исходная_строка FROM 1 FOR (позиция – 1)) 6 || строка_для_замены || SUBSTRING (исходная_строка FROM (позиция + длина)). Функция OVERLAY используется только в Firebird, в MS SQL Server и Oracle отсутствует. В MS SQL Server поддерживается функция STUFF, аналогичная функции OVERLAY. Функция STUFF вставляет одну строку в другую, удаляя указанное количество символов первой строки в начальной позиции и вставляя на их место вторую строку [37]. Функция имеет следующий синтаксис: STUFF (исходная_строка, позиция, длина, строка_для_замены) Если начальная позиция или число удаляемых символов отрицательны или если начальная позиция превышает длину первой строки, возвращается пустая строка. Если начальная позиция равна 0, то возвращается значение NULL. Если число удаляемых символов превышает длину первой строки, удаление выполняется до первого символа первой строки. Например, предыдущий запрос по выводу измененных тебефонов номеров абонентов реализовать в MS SQL Server можно так: SELECT STUFF (Phone, 1, 2, '66') FROM Abonent; Функция REPLACE заменяет все вхождения подстроки в строковое выражение на указанную строку для замены. Имеет формат, одинаковый для Firebird, MS SQL Server и Oracle: REPLACE (строковое_выражение, подстрока, строка_для_замены). Например, для замены в названии неисправностей слова «плиты» на словосочетание «газовой плиты» (рис. 3.24) можно выполнить запрос SELECT REPLACE (FailureNM, 'плиты', 'газовой плиты') FROM Disrepair; REPLACE Засорилась водогрейная колонка Не горит АГВ Течет из водогрейной колонки Неисправна печная горелка Неисправен газовый счетчик Плохое поступление газа на горелку газовой плиты Туго поворачивается пробка крана газовой плиты При закрытии краника горелка газовой плиты не гаснет Неизвестна Рис. 3.24. Результат работы функции REPLACE 7 Следует отметить, что в Oracle возвращаемый предыдущим запросом столбец будет иметь имя, включающее в себя не только название функции, но и ее аргументы: REPLACE(FAILURENM,'ПЛИТЫ','ГАЗОВОЙПЛИТЫ'). Функция TRIM возвращает строку аргумента, удаляя символы (по умолчанию – пробелы) из начала и/или конца строки. Для вызова функции в стандарте SQL используется синтаксис TRIM ( [ [LEADING | TRAILING | BOTH ] [ удаляемая_подстрока ] FROM ] строковое_выражение). LEADING указывает на то, что надо удалить указанную подстроку из начала строки, TRAILING – из конца строки, BOTH – из начала и конца строки. При использовании функции следует учитывать:  если LEADING, TRAILING или BOTH не указаны, то принимается BOTH по умолчанию;  если удаляемая подстрока не определена, то принимается по умолчанию пробел;  если LEADING, или TRAILING, или BOTH и/или удаляемая подстрока указаны, то после слова FROM должно быть обязательно указано строковое_выражение, из которого удаляются символы;  указание строки, из которой удаляются символы, после слова FROM не может быть использовано самостоятельно (если LEADING, TRAILING, BOTH и удаляемая_подстрока не указаны). Например, для вывода данных из таблицы Street, указывая название улицы без слова «УЛИЦА» (рис. 3.25), можно использовать запрос: SELECT StreetCD, TRIM (BOTH 'УЛИЦА' FROM StreetNM) AS "Str_Name” FROM Street; STREETCD 3 7 6 8 4 5 1 2 Str_Name ВОЙКОВ ПЕРЕУЛОК КУТУЗОВА МОСКОВСКАЯ МОСКОВСКОЕ ШОССЕ ТАТАРСКАЯ ГАГАРИНА ЦИОЛКОВСКОГО НОВАЯ Рис. 3.25. Результат работы функции TRIM Например, для вывода лицевых счетов абонентов без нулей слева (рис. 3.26) можно использовать запрос: SELECT TRIM(LEADING '0' FROM AccountCD) AS AccountCD, Fio 8 FROM Abonent; ACCOUNTCD 5488 115705 15527 443690 136159 443069 136160 126112 136169 80613 80047 80270 FIO Аксенов С.А. Мищенко Е.В. Конюхов В.С. Тулупова М.И. Свирина З.А. Стародубцев Е.В. Шмаков С.В. Маркова В.П. Денисова Е.К. Лукашина Р.М. Шубина Т.П. Тимошкина Н.Г. Рис. 3.26. Результат работы функции TRIM (удаление символов слева) Функция TRIM поддерживается в формате, определяемым стандартом, СУБД Firebird и Oracle. В Oracle также определены функции LTRIM и RTRIM. Функции LTRIM и RTRIM удаляют заданную подстроку из начала или конца заданной строки соответственно и имеют следующий формат: LTRIM(строковое_выражение [, удаляемая_подстрока]); RTRIM(строковое_выражение [, удаляемая_подстрока]). Например, приведенный выше запрос на вывод названий улиц без слова «УЛИЦА» в Oracle можно реализовать с помощью функции RTRIM: SELECT StreetCD, RTRIM(StreetNM, 'УЛИЦА') AS "Str_Name" FROM Street; Результат выполнения запроса будет совпадать с результатом, приведенным на рис. 3.25. MS SQL Server поддерживает функции LTRIM и RTRIM в упрощенном варианте: функции имеют синтаксис LTRIM(строковое_выражение); RTRIM(строковое_выражение); и используются только для удаления начальных или конечных пробелов. Чтобы удалить начальные и конечные пробелы, нужно к строке применить обе функции LTRIM() и RTRIM(). При указании удаляемой подстроки в функциях TRIM, RTRIM и LTRIM следует учитывать регистр символов (строчные или прописные буквы). Если в предыдущем примере вместо 'УЛИЦА' ввести 'улица', то запрос выдаст неверный результат, так как все названия улиц в таблице Street записаны в верхнем регистре символов. 9 Существуют функции LPAD и RPAD, которые дополняют строку аргумента слева (LPAD) или справа (RPAD) указанной последовательностью символов: строка_заполнитель до заданного размера длина. В случае если строка_заполнитель не указана, для дополнения используется пробел. Строка_заполнитель обрезается, когда результирующая строка достигает заданной длины. Функции имеют следующий формат: LPAD( строковое_выражение, длина [, строка_заполнитель ] ), RPAD( строковое_выражение, длина [, строка_заполнитель ] ). Например, требуется вывести номера лицевых счетов абонентов и Fio, дополненные справа знаком звездочки (*) до длины 20 символов (рис. 3.27). Запрос будет выглядеть таким образом: SELECT AccountCD, RPAD (Fio, 20,'*') FROM Abonent; ACCOUNTCD 005488 136169 015527 080613 126112 115705 136159 443069 080270 443690 136160 080047 RPAD Аксенов С.А.******** Денисова Е.К.******* Конюхов В.С.******** Лукашина Р.М.******* Маркова В.П.******** Мищенко Е.В.******** Свирина З.А.******** Стародубцев Е.В.**** Тимошкина Н.Г.****** Тулупова М.И.******* Шмаков С.В.********* Шубина Т. П.********* Рис. 3.27. Результат работы функции RPAD Функции LPAD и RPAD используются в Firebird и Oracle, а в MS SQL Server не поддерживаются. В Oracle столбец вышеприведенного запроса с результатом работы функции будет иметь имя не RPAD, а RPAD (FIO, 20,'*'). В Oracle и MS SQL Server для получения строки, являющейся результатом объединения двух или более строковых значений, может использоваться функция CONCAT [37, 49]. Функция фактически является аналогом операции конкатенации и имеет следующий синтаксис: CONCAT (строковое_выражение1, строковое_выражение2 [,строковое_выражениеN ]). CONCAT принимает переменное количество строковых аргументов и объединяет их в одну строку. Для этого требуется не менее двух входных значений, в противном случае возникает ошибка. Все аргументы неявно преобразуются в строковые типы и затем объединяются. Значения NULL неявно преобразуются в пустую строку. Если все аргументы имеют значение 10 NULL, то возвращается пустая строка типа VARCHAR(1). Неявное преобразование в строки выполняется по существующим в СУБД правилам преобразования типов данных. Например, запрос с конкатенацией ФИО и номеров телефонов абонентов (рис. 3.4), приведенный ранее в п. 3.2.1, в MS SQL Server может быть реализован с помощью функции CONCAT так: SELECT AccountCD AS "AccountCDRyazan", CONCAT (Fio, ' имеет телефон ', '8-4912-', Phone) "ФИО+телефон" FROM Abonent; Функция REPLICATE в MS SQL Server повторяет значение строки указанное число раз. Функция имеет формат REPLICATE ( строковое_выражение, числовое_выражение ), где числовое_выражение – выражение любого целого типа, задающее число повторов. Если числовое_выражение имеет отрицательное значение, то возвращается NULL. Существуют функции, которые выполняют преобразования между регистрами символов. Функция UPPER преобразует все символы строки в верхний регистр и имеет синтаксис UPPER (<значение>), где <значение> – преобразуемый столбец, переменная или выражение строкового типа. Если набор символов и последовательность сортировки поддерживают преобразование в верхний регистр (например, WIN1251), то функция возвращает строку, в которой все символы преобразованы в верхний регистр. Строка имеет ту же длину, что и входное <значение>. Для наборов символов, не поддерживающих преобразование в верхний регистр (в частности, кодировка по умолчанию NONE), функция возвращает неизменное входное значение. Следующий запрос выводит названия услуг заглавными буквами (рис. 3.28): SELECT UPPER(ServiceNM) FROM Services; UPPER ГАЗОСНАБЖЕНИЕ ЭЛЕКТРОСНАБЖЕНИЕ ТЕПЛОСНАБЖЕНИЕ ВОДОСНАБЖЕНИЕ Рис. 3.28. Результат работы функции UPPER Противоположной является функция LOWER, которая преобразует все символы строки в нижний регистр: LOWER (<значение>), где <значение> – преобразуемый столбец, переменная или выражение строкового типа. Функции UPPER, LOWER используются в Firebird, Oracle, MS SQL Server. 11 В Oracle также поддерживается функция INITCAP, которая преобразует первую букву каждого слова в заглавную, а остальные буквы – в строчные. Функция имеет следующий синтаксис: INITCAP(<значение>), где <значение> – преобразуемый столбец, переменная или выражение строкового типа. Существуют функции для определения символа по известному коду. Для Firebird – это функция ASCII_CHAR, для Oracle – функция CHR, для MS SQL Server – функция CHAR. Функции имеют синтаксис ASCII_CHAR (код_символа), CHR (код_символа), CHAR (код_символа). Например, запрос для определения символа с кодом 82 в Firebird SELECT ASCII_CHAR(82) FROM RDB$DATABASE; или в Oracle SELECT CHR(82) FROM DUAL; или в MS SQL Server SELECT CHAR(82); Код должен лежать в диапазоне от 0 до 255. При работе с Oracle Database 11g Express Edition дополнительно следует учесть, что БД создается по умолчанию с набором символов AL32UTF8, а функция CHR возвращает символ, имеющий заданный код в наборе символов БД. При попытке получить функцией CHR символ по коду более 193 в БД с кодировкой AL32UTF8 Oracle вернет ошибку. Функции ASCII_CHAR, CHR и CHAR могут использоваться для вставки управляющих символов в символьные строки. Рассмотренные выше строковые функции возвращают результат в виде строки символов. Существует также ряд строковых функций, которые в качестве результата возвращают числовое значение. Среди них функции, обратные предыдущим описанным, для определения кода символа (крайнего левого) в указанной строке. В Firebird используется функция ASCII_VAL, в Oracle и MS SQL Server – это функция ASCII. Функции имеют следующий синтаксис: ASCII_VAL (строка) , ASCII (строка). Например, запрос в Firebird, определяющий код символа «R»: SELECT ASCII_VAL('Ready') FROM RDB$DATABASE; Аналогичный ему запрос в Oracle: SELECT ASCII ('R') FROM DUAL; и в MS SQL Server SELECT ASCII ('Ready'); Функции вернут NULL, если указана пустая строка. Для определения позиции первого вхождения заданной подстроки в строку в Firebird можно использовать функцию POSITION: 12 POSITION (подстрока IN строковое_выражение) . Функция возвращает ноль, если подстрока отсутствует внутри строки. Например, для вывода номеров лицевых счетов и ФИО тех абонентов, у которых в фамилиях вторая буква «у» (рис. 3.29), можно использовать запрос SELECT AccountCD, Fio FROM Abonent WHERE POSITION ('у' IN Fio) = 2; ACCOUNTCD FIO 443690 Тулупова М.И. 080613 Лукашина Р.М. 080047 Шубина Т. П. Рис. 3.29. Результат работы функции POSITION В Oracle существует аналогичная функция INSTR, которая возвращает позицию вхождения подстроки в строку, начиная с позиции n и количества вхождений этой подстроки m. Функция имеет синтаксис INSTR(строковое_выражение, подстрока [,n [,m]]), где n – положение символа в строке, с которого начнется поиск, а m – n-е вхождение подстроки [49]. Если параметр n опущен, то он по умолчанию равен 1. Если параметр n отрицательный, то функция INSTR рассчитывает позицию n в обратном направлении от конца строки, а затем выполняет поиск по направлению к началу строки. Если параметр m опущен, то он по умолчанию равен 1. В MS SQL Server для определения позиции вхождения подстроки в строку используются функции CHARINDEX и PATINDEX. Функция CHARINDEX ищет в выражении другое выражение и возвращает его начальную позицию, если оно найдено. Функция имеет синтаксис CHARINDEX (подстрока, строковое_выражение [, n ]), где n – позиция, с которой начинается поиск. Если n не указано, имеет отрицательное значение или равно нулю, то поиск начинается с начала строкового выражения. Функция PATINDEX для любого допустимого символьного или текстового типа данных возвращает начальную позицию первого вхождения шаблона в указанном выражении или ноль, если шаблон не найден. Функция имеет синтаксис PATINDEX (шаблон_поиска, строковое_выражение), где шаблон_поиска задается по общим правилам задания шаблонов. В любой СУБД существуют функции для определения размера строки. В Firebird для этого применяются функции BIT_LENGTH, CHAR[ACTER]_LENGTH, OCTET_LENGTH. Функция BIT_LENGTHB возвращает длину строки в битах, функция CHAR[ACTER]_LENGTH – в символах, а функция OCTET_LENGTH – в байтах. Эти три функции имеют одинаковый синтаксис { BIT_LENGTH | CHAR[ACTER]_LENGTH | OCTET_LENGTH } ( строковое_выражение ). Например, вывести названия услуг и длины названий в символах и в байтах (рис. 3.30) можно с помощью запроса 13 SELECT ServiceNM, CHAR_LENGTH (ServiceNM), BIT_LENGTH (ServiceNM) FROM Services; SERVICENM Газоснабжение Электроснабжение Теплоснабжение Водоснабжение CHAR_LENGTH 13 16 14 13 BIT_LENGTH 104 128 112 104 Рис. 3.30. Результаты работы функций CHAR_LENGTH и BIT_LENGTH Проверить, что тип VARCHAR предусматривает автоматическое отбрасывание символов пробела (рис. 3.31), можно в Firebird с помощью следующего запроса, использующего функции CHAR_LENGTH и TRIM: SELECT FIRST 3 FailureNM AS "FailureNM", CHAR_LENGTH (FailureNM), CHAR_LENGTH (TRIM(FailureNM)) FROM Disrepair; FailureNM Засорилась водогрейная колонка Не горит АГВ Течет из водогрейной колонки CHAR_LENGTH CHAR_LENGTH1 30 30 12 12 28 28 Рис. 3.31. Результат работы функции CHAR_LENGTH Во втором столбце выводятся длины значений столбца FailureNM в символах, а в третьем – длины значений этого же столбца, но с удаленными символами пробелов. Можно заметить, что значения во втором и третьем столбце совпадают, так как тип столбца FailureNM – VARCHAR(50) и символы пробелов автоматически отбрасываются. Рассмотрим пример запроса, позволяющего подсчитать, сколько раз символ или подстрока встречается в заданной строке. Для подсчета количества букв 'и' в ФИО абонентов можно использовать такой запрос: SELECT Fio, 'и', (CHAR_LENGTH(Fio) – CHAR_LENGTH(REPLACE(Fio,'и','')))/CHAR_LENGTH('и') FROM Abonent; В этом запросе при вычитании из длины строки длины строки без подстроки получается разница, которая соответствует количеству букв "и" в ФИО. Последней операцией является деление на длину строки поиска. Эта операция необходима, если длина искомой строки более 1 символа. В Oracle существуют аналогичные функции LENGTH и LENGTHB. Функция LENGTH возвращает длину строки в символах, а функция LENGTHB – в байтах. Функции имеют синтаксис { LENGTH | LENGTHB } ( строковое_выражение ). Например, вышеприведенный запрос на вывод длины названий услуг в символах и в байтах в Oracle может быть записан так: SELECT ServiceNM, LENGTH(ServiceNM), LENGTHB(ServiceNM) 14 FROM Services; В MS SQL Server для определения длины строки используются функции LEN и DATALENGTH. Функции имеют формат { LEN | DATALENGTH } ( строковое_выражение ). Функция LEN возвращает количество символов указанного строкового выражения, исключая конечные пробелы, а функция DATALENGTH возвращает число байтов, использованных для представления выражения. Для преобразования значения уникального идентификатора (UUID) между двоичным и строковым видами в Firebird используются функции CHAR_TO_UUID и UUID_TO_CHAR. Функция CHAR_TO_UUID преобразует строковое (CHAR(32) кодировки ASCII) представление UUID (вида XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX) в двоичное (CHAR(16) кодировки OCTETS), более оптимизированное для хранения. Функция UUID_TO_CHAR выполняет обратное преобразование, т. е. преобразует двоичный UUID, полученный обычно с помощью функции GEN_UUID (см. табл. 3.4), в строковый. Функции имеют формат CHAR_TO_UUID (строковое_выражение), UUID_TO_CHAR (строковое_выражение). Например, можно использовать следующие запросы: SELECT CHAR_TO_UUID ('93519227-8D50-4E47-81AA-8F6678C096A1') FROM RDB$DATABASE; или SELECT UUID_TO_CHAR (GEN_UUID()) FROM RDB$DATABASE; В заключение описания строковых функций рассмотрим 2 системные функции Firebird, позволяющие получать информацию о параметрах БД, о текущем подключении и транзакции, а также параметры действия триггеров DDL: RDB$GET_CONTEXT и RDB$SET_CONTEXT [42, 44]. Синтаксис функции RDB$GET_CONTEXT RDB$GET_CONTEXT ('<пространство_имен>', '<переменная>'), где <пространство_имен> ::= { SYSTEM | DDL_TRIGGER | USER_SESSION | USER_TRANSACTION }, <переменная> – регистрозависимая строка длиной до 80 символов. Функция RDB$GET_CONTEXT возвращает значение контекстной переменной из пространств имен SYSTEM, DDL_TRIGGER, USER_SESSION, USER_TRANSACTION. Пространство имен и имя переменной – это строковые выражения, которые чувствительны к регистру и должны быть отличны от NULL. Если в указанном пространстве есть запрашиваемая переменная, будет возвращено ее значение типа VARCHAR(255). Если указано несуществующее пространство имен или указано пространство имен SYSTEM или DDL_TRIGGER, но запрашиваемая переменная в нем не существует, будет сгенерирована ошибка. Если запрашивается несуществующая переменная в другом пространстве имен, функция вернет NULL. Пространство имен SYSTEM доступно только для чтения и содержит предустановленные переменные, которые приведены в табл. 3.2. 15 Таблица 3.2. Контекстные переменные в пространстве имен SYSTEM Переменная Описание ENGINE_VERSION Возвращает текущую версию SQL-сервера. Например: '2.5.0' NETWORK Сетевой протокол, используемый клиентом в _PROTOCOL текущем соединении с БД. Может иметь значения 'TCPv4', 'WNET', 'XNET' или NULL CLIENT_ADDRESS Сетевой адрес клиента. Для протокола TCPv4 содержит IP-адрес. Для протокола XNET содержит ID локального процесса. Для всех остальных протоколов содержит NULL DB_NAME Имя БД, в зависимости от подключения возвращает алиас или путь к файлу, указанные в строке подключения к БД ISOLATION_LEVEL Уровень изоляции текущей транзакции. Может иметь значения 'READ, COMMITTED', 'SNAPSHOT' или 'CONSISTENCY' TRANSACTION_ID Номер текущей транзакции (значение идентично переменной CURRENT_TRANSACTION) SESSION_ID Номер текущего подключения (значение идентично переменной CURRENT_CONNECTION) CURRENT_USER Текущий пользователь (значение идентично переменной CURRENT_USER) CURRENT_ROLE Текущая роль (значение идентично переменной CURRENT_ROLE) Например, получить текущую версию SQL-сервера Firebird можно с помощью запроса SELECT RDB$GET_CONTEXT ('SYSTEM', 'ENGINE_VERSION') FROM RDB$DATABASE; Пространство имен DDL_TRIGGER доступно только для чтения и содержит предустановленные переменные, которые приведены в табл. 3.3. Значения переменных доступны только в процессе работы триггера DDL (триггеры DDL и примеры использования функций с пространством имен DDL_TRIGGER будут подробно рассмотрены в разд. 6.6.2). Таблица 3.3. Контекстные переменные в пространстве имен DDL_TRIGGER Переменная Описание EVENT_TYPE Тип события: CREATE, ALTER, или DROP OBJECT_TYPE Тип объекта БД (TABLE, VIEW и т. д.) DDL_EVENT Событие DDL в виде EVENT_TYPE || ' ' || OBJECT_TYPE 16 OBJECT_NAME Наименование объекта метаданных SQL_TEXT Текст SQL-запроса Пространства имен USER_SESSION и USER_TRANSACTION изначально пустые и не содержат переменных. Пользователь может создавать переменные и задавать им значения с помощью функции RDB$SET_CONTEXT(), а потом получить их значение с помощью функции RDB$GET_CONTEXT(). Функция RDB$SET_CONTEXT имеет синтаксис: RDB$SET_CONTEXT ('<пространство_имен>', '<переменная>', {<значение> | NULL}), где <пространство_имен> ::= { USER_SESSION | USER_TRANSACTION }; <переменная> – чувствительная к регистру строка длиной до 80 символов; <значение> – любое выражение, приводимое к типу VARCHAR(255). Функция RDB$SET_CONTEXT создает, задает или удаляет значение контекстной переменной в пространствах имен USER_SESSION и USER_TRANSACTION. При этом следует помнить, что максимальное количество переменных в любом контексте (транзакции или соединения) равно 1000. Переменные пространства имен USER_SESSION привязаны к текущему соединению, а переменные пространства имен USER_TRANSACTION существуют в контексте транзакции, в которой были созданы. Когда транзакция завершается, все созданные в ней переменные уничтожаются. Функция RDB$SET_CONTEXT возвращает результат типа INTEGER: 1, если переменная уже существует, и 0, если переменная еще не была создана. Чтобы удалить переменную, необходимо установить ее значение в NULL. Установим, например, в пределах текущего подключения значение пользовательской переменной MyVar в значение 86, а затем получим значение данной переменной. Запросы для выполнения указанных действий будут выглядеть следующим образом: SELECT RDB$SET_CONTEXT('USER_SESSION','MyVar',86) FROM RDB$DATABASE; SELECT RDB$GET_CONTEXT('USER_SESSION',' MyVar') FROM RDB$DATABASE; Функции RDB$SET_CONTEXT() и RDB$GET_CONTEXT() являются встроенными, но представляют собой подобие «предекларированных UDFфункций», хотя и находятся не во внешней библиотеке. Это сделано для возможности использовать их как UDF-функции – например, вызывать как процедуры в PSQL: New.UserAddr := RDB$GET_CONTEXT ('SYSTEM', 'CLIENT_ADDRESS'); С обычными встроенными функциями это невозможно. В MS SQL Server существуют так называемые функции конфигурации, которые позволяют получить сведения о текущих значениях параметров конфигурации [37]. Имена таких функций начинаются с символов @@, и все они являются недетерминированными, то есть данные функции не всегда возвращают одинаковый результат даже при одних и тех же входных 17 значениях. Например, следующий запрос возвращает сведения о версии текущего экземпляра MS SQL Server: SELECT @@VERSION AS 'SQL Server Version'; Также в MS SQL Server доступен ряд других системных функций для получения информации об объектах и параметрах БД. Числовые функции. Эти функции возвращают числовые значения на основании значений того же типа, заданных в аргументе. Числовые функции используются для обработки данных, а также в условиях поиска. Стандартные числовые функции СУБД перечислены в табл. 3.4 [28, 29, 37, 42, 49]. Таблица 3.4. Числовые функции Функци Описание Функция Oracle Функция MS я SQL Server Firebird RAND() Случайное число от 0 до 1 Нет RAND() ABS Абсолютное значение ABS (число) ABS (число) (число) SIGN Знаковая функция (возвращает SIGN (число) SIGN (число) (число) 1 для положительного числа, 0 – для нуля, -1 – для отрицательного числа) MOD Остаток от деления MOD (делимое, (делимое % (делимо делитель); делитель) е, REMAINDER делител (делимое, делитель) ь) Функция REMAINDER использует в своей формуле функцию ROUND, тогда как функция MOD использует функцию FLOOR в своей формуле LOG Логарифм числа по LOG (основание, LOG (число [, (основан указанному основанию число) основание]) ие, число) LN Натуральный логарифм числа LN (число) LOG (число) (число) LOG10 Десятичный логарифм числа Нет LOG10 (число) (число) 18 EXP (число) PI() POWER (число, степень) SQRT (число) FLOOR (число) CEIL | CEILIN G (число) Экспоненциальная функция (e EXP (число) в степени аргумента) Константа π = 3.1459… Нет POWER (число, Возведение числа в степень степень) Квадратный корень SQRT (число) Округление до целого числа вниз Округление до целого числа вверх FLOOR (число) CEIL (число) EXP (число) PI() POWER (число, степень) SQUARE (число) FLOOR (число) CEILING (число) Продолжение табл. 3.4 19 ROUN D (выраж ение, точност ь) Округление ROUND числа (выражение, <выражение> до точность) <точность> знаков после запятой в ближайшую сторону. Если число знаков отрицательное, то округление идет до <точность> знаков перед запятой ROUND (выражение, точность [, признак]) Если "признак" равен нулю или отсутствует, то выполняется обычное округление. Если "признак" имеет положительн ое значение, то результат округляется до соответствую щего количества знаков после десятичной точки. Если "признак" отрицательны й, то указанное количество знаков целого справа обнуляется, точнее, происходит округление числа до знака = "признак" + 1 20 Округление до указанного количества знаков <точность> после запятой в меньшую по модулю сторону. Если число знаков отрицательное, то округление идет до <точность> знаков перед запятой GEN_I Генерация D () уникального значения GEN_U Генерация UID () уникального значения в кодировке OCTETS HASH Хэш-функция (значен (рандомизация ие) значения) TRUNC (число, точност ь) SIN (число) SINH (число) TRUNC (число, точность) Нет Нет Нет Нет Нет ORA_HASH Нет (значение, [макс_значени е], [начальное_зн ачение]) Продолжение табл. 3.4 Тригонометрические функции Синус (аргумент SIN (число) SIN (число) задается в радианах) Гиперболический SINH Нет синус числа, (число) выраженный в радианах 21 COS (число) COSH (число) TAN (число) TANH (число) COT (число) ASIN (число) Косинус (угол определяется в радианах, результат в диапазоне от -1 до 1) Гиперболический косинус числа, выраженный в радианах Тангенс (аргумент задается в радианах) Гиперболический тангенс числа, выраженный в радианах Котангенс Арксинус (число должно быть в диапазоне от -1 до 1, результат от –π/2 до π/2) ASINH Гиперболический (число) арксинус числа, выраженный в радианах ACOS Арккосинус (число) (число должно быть в диапазоне от -1 до 1, результат от 0 до π) ACOSH Гиперболический (число) арккосинус числа, выраженный в радианах ATAN Арктангенс (число) (возвращает результат в диапазоне от –π/2 до π/2) COS (число) COS (число) COSH (число) Нет TAN (число) TAN (число) TANH (число) Нет Нет COT (число) ASIN (число) ASIN (число) Нет Нет ACOS (число) ACOS (число) Нет Нет ATAN (число) ATAN (число) 22 ATAN2 (число1 , число2) ATAN H (число) Нет Нет Арктангенс в градусах, вычисляемый как арктангенс результата деления одного тангенса на другой – ATAN(число1/числ о2). Возвращает результат в диапазоне (–π; π] Гиперболический арктангенс числа, выраженный в радианах ATAN2 (число1, число2) ATN2(число 1, число2) Нет Нет Окончание табл. 3.4 DEGREES Нет (число) Возвращает для значения угла в радианах соответствующее значение в градусах Для введенного RADIANS Нет числового (число) выражения в градусах возвращает значение в радианах Логические функции Логическое 'И' на Нет (число AND всех аргументах [,число...]) BIN_A ND (число [,число. ..]) BIN_N Логическое 'НЕ' OT аргумента (число) Нет NOT (число) 23 BIN_O Логическое 'ИЛИ' Нет (число OR R на всех [,число...]) (число аргументах [,число >…]) BIN_X Исключающее Нет (число XOR OR 'ИЛИ' на всех [,число...]) (число аргументах [,число. ..]) BIN_S Двоичный сдвиг Нет число << HL влево число (число, число) BIN_S Двоичный сдвиг Нет число >> HR вправо число (число, число) Пример запроса в Firebird по использованию числовых функций: SELECT POWER(COS(PI()),2) + POWER(SIN(PI()),2) FROM RDB$DATABASE; Функции даты и времени. Как уже отмечалось, эти функции выполняют различные действия над входными значениями времени и даты и возвращают строковое, числовое значения или значение в формате даты и времени. Функция EXTRACT используется для извлечения различных частей даты и времени. В Firebird функция EXTRACT используется для выделения года(1–9999), месяца(1–12), дня(1–31), дня года(0[1 января]–365), дня недели(0[воскресенье]–6), часа(0–23), минуты(0–59), секунды(0–59.9999), а также недели в году(1–52). Синтаксис этой функции EXTRACT( { YEAR | MONTH | DAY | YEARDAY | WEEKDAY | HOUR | MINUTE | SECOND | WEEK } FROM [DATE] <значение> ), где <значение> – любое выражение, возвращающее результат типа DATE, TIME или TIMESTAMP. Если выражение представляет собой константу, то после FROM указывается слово DATE, в остальных случаях <значение> указывается непосредственно после FROM. Выделяемая часть должна присутствовать в <значение>. Пусть, например, требуется для каждой ремонтной заявки, год поступления которой отличается от 2013, указать ее номер, день, месяц и год поступления (рис. 3.32). Для этого можно использовать запрос SELECT RequestCD, EXTRACT(DAY FROM IncomingDate) AS "IncomingDay", EXTRACT (MONTH FROM IncomingDate) AS "IncomingMonth", EXTRACT (YEAR FROM IncomingDate) AS "IncomingYear" FROM Request 24 WHERE EXTRACT (YEAR FROM IncomingDate) IS DISTINCT FROM 2013; REQUESTCD 1 2 3 5 6 7 9 10 12 13 15 16 17 18 19 20 21 22 23 IncomingDay 17 7 28 31 16 20 6 1 8 4 20 28 15 28 17 11 13 18 7 IncomingMonth 12 8 2 12 6 10 11 4 8 9 9 12 8 12 12 10 9 5 5 IncomingYear 2011 2011 2012 2011 2011 2012 2011 2011 2011 2010 2010 2011 2011 2012 2011 2011 2011 2011 2011 Рис. 3.32. Результат работы функции EXTRACT В Oracle функция EXTRACT используется для выделения года(1–9999), месяца(1–12), дня(1–31), часа(0–23), минуты(0–59), секунды(0–59.99…), часа, минуты, названия или сокращенного названия часового пояса и имеет формат EXTRACT( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_ MINUTE | TIMEZONE_REGION | TIMEZONE_ABBR } FROM <значение> ). В MS SQL Server вместо функции EXTRACT используется ряд других функций для получения компонентов даты и времени: DATEPART, DATENAME, DAY, MONTH, YEAR. Функция DATEPART является синонимом стандартной функции EXTRACT и имеет формат DATEPART (часть_даты, значение). Платформа MS SQL Server поддерживает части даты, перечисленные в табл. 3.5 [37]. В качестве результата функция возвращает целое число. Помимо DATEPART, могут использоваться функции DAY(значение), MONTH(значение) и YEAR(значение), которые возвращают целое число, представляющее день, месяц или год указанной даты. Для получения строкового представления указанной части даты используется функция DATENAME, имеющая формат DATENAME (часть_даты, значение). 25 Также MS SQL Server поддерживает функции для получения значения даты и времени из их компонентов. Среди них функция DATEFROMPARTS, которая возвращает значение типа date, соответствующее указанному числу, месяцу и году, и функция DATETIMEFROMPARTS, которая возвращает значение типа datetime, соответствующее указанной дате и времени. Функции имеют формат DATEFROMPARTS (год, месяц, день); DATETIMEFROMPARTS (год, месяц, день, час, минута, секунда, миллисекунда). Могут использоваться и другие функции MS SQL Server, возвращающие значения других типов дата/время из компонентов [37]. Таблица 3.5. Компоненты даты/времени MS SQL Server Часть даты Сокра Описание щения YY, Год YEAR YYYY QUARTER QQ, Q Квартал MONTH MM, M Месяц DAYOFYEAR DY, Y День года DAY DD, D День WK, Неделя WEEK WW WEEKDAY DW День недели HOUR HH Час MINUTE MI, N Минута SECOND SS, S Секунда MILLISECOND MS Миллисекунда MICROSECOND MCS Микросекунда NANOSECOND NS Наносекунда Количество TZOFFSET TZ минут Функция ISDATE в MS SQL Server определяет, является ли входное выражение допустимым значением даты или времени и возвращает значение 0 или 1 в зависимости от результатов проверки. Функция имеет формат ISDATE (выражение). Для получения значений текущей даты и системного времени используются константы времени и контекстные переменные (см. разд. 2.8). Например, вывести данные таблицы Services, текущие серверные дату и время (29.10.2015, 18:21) в Firebird можно с помощью запроса SELECT S.*, CURRENT_TIMESTAMP FROM Services S; Результат выполнения запроса представлен на рис. 3.33. 26 SERVICECD 1 2 3 4 SERVICENM Газоснабжение Электроснабжение Теплоснабжение Водоснабжение CURRENT_TIMESTAMP 29.10.2015 18:21 29.10.2015 18:21 29.10.2015 18:21 29.10.2015 18:21 Рис. 3.33. Результат работы функции CURRENT_TIMESTAMP Функция DATEADD возвращает значение типа дата, время или дата/время, увеличенное или уменьшенное (если количество отрицательное) по сравнению с исходным значением на заданное количество лет, месяцев, дней, часов, минут, секунд или миллисекунд. В Firebird функция имеет следующий формат: DATEADD ( количество <временной_отрезок> TO <значение> ) или DATEADD (<временной_отрезок>, количество, <значение> ), где количество – количество прибавляемых или вычитаемых единиц; <временной_отрезок> ::= { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND }; <значение> – значение типа дата, время или дата/время, которое увеличивается или уменьшается. Следует отметить:  что YEAR, MONTH и DAY в качестве временного отрезка не могут использоваться со значениями типа время (например, тип TIME);  HOUR, MINUTE, SECOND и MILLISECOND в качестве временного отрезка не могут использоваться со значениями типа дата (например, тип DATE);  все значения временного отрезка могут быть использованы для типов дата/время (TIMESTAMP). Например, требуется вывести даты регистрации ремонтных заявок с кодом неисправности, равным 1, и даты через 14 дней после их регистрации (рис. 3.34). Запрос будет выглядеть следующим образом: SELECT IncomingDate, DATEADD (14 DAY TO IncomingDate) AS "Exec_Limit" FROM Request WHERE FailureCD = 1; INCOMINGDATE 17.12.2011 07.08.2011 31.12.2011 06.11.2011 Exec_Limit 31.12.2011 21.08.2011 14.01.2012 20.11.2011 Рис. 3.34. Результат работы функции DATEADD в Firebird MS SQL Server поддерживает функцию DATEADD в формате DATEADD (<временной_отрезок>, количество, <значение> ). 27 Таким образом, предыдущий запрос должен быть записан в MS SQL Server как SELECT IncomingDate,DATEADD (DAY, 14,IncomingDate) AS "Exec_Limit" FROM Request WHERE FailureCD = 1; Результат выполнения запроса (рис. 3.35) будет отличаться от результата на рис. 3.34 форматом вывода даты (дата выводится в формате UTC). INCOMINGDATE Exec_Limit 2011-12-17 2011-12-31 2011-08-07 2011-08-21 2011-12-31 2012-01-14 2011-11-06 2011-11-20 Рис. 3.35. Результат работы функции DATEADD в MS SQL Также в MS SQL Server может использоваться функция EOMONTH для получения последнего дня месяца, содержащего заданную дату, с необязательным смещением [37]. Функция имеет формат EOMONTH ((<дата> [,<количество_месяцев> ] ), где <дата> – выражение даты, для которой необходимо возвратить последний день месяца; <количество_месяцев> – необязательное целочисленное выражение, задающее количество месяцев, добавляемых к дате. Если <количество_месяцев> указано, то EOMONTH добавляет указанное число месяцев к значению даты и возвращает последний день месяца, соответствующего полученной дате. СУБД Oracle не поддерживает функцию DATEADD, вместо нее для получения даты со сдвигом от заданной используется ряд других функций. Функция ADD_MONTHS возвращает дату, полученную в результате прибавления к исходной дате заданного числа месяцев. Формат функции: ADD_MONTHS(<дата>, <количество_месяцев>). Количество месяцев может быть отрицательным – в этом случае указанное <количество_месяцев> вычитается из заданной даты. Функция LAST_DAY возвращает последний день месяца на основе значения даты, имеет формат LAST_DAY((<дата>). Функция NEXT_DAY возвращает дату первого указанного дня недели, следующего за заданной датой. Имеет формат NEXT_DAY(<дата>, <день_недели>), где <дата> используется для поиска следующего дня недели; <день_недели> – день недели, дата которого ищется (понедельник, вторник, среда, четверг, пятница, суббота, воскресенье). Например, если необходимо получить дату первой среды после 1 декабря 2014 года (рис. 3.36), то в Oracle можно использовать запрос SELECT NEXT_DAY('01.12.2014', 'Среда') AS "Result" FROM DUAL; 28 Result 03.12.2014 Рис. 3.36. Результат работы функции NEXT_DAY Функции TRUNC и ROUND, описанные выше в качестве числовых функций, в Oracle могут использоваться для работы со значениями дата/время. Функция TRUNС производит усечение указанной даты в соответствии с маской. Функция имеет формат TRUNС(<дата>,[<маска>]). Если маска не указана, то усечение производится до даты (время отбрасывается). Например, вывести текущую дату в исходном и усеченном виде (рис. 3.37) можно с помощью запроса SELECT SYSDATE d1, TRUNC(SYSDATE) d2 FROM DUAL; D1 D2 04.12.2014 19:45 04.12.2014 0:00 Рис. 3.37. Результат работы функции TRUNC без маски Функция ROUND аналогична TRUNC, но вместо усечения она производит округление. В табл. 3.6 приведён перечень форматных масок, которые можно использовать в функциях TRUNC и ROUND. Таблица 3.6. Форматные маски для функций ROUND и TRUNC Маска Назначение СС Первый день столетия YEAR, или YYYY, или YY, или Первый день года Y Q Первый день квартала MONTH, или MON, или MM Первый день месяца WW Тот же день недели, что и первый день текущего года W Тот же день недели, что и первый день текущего месяца DDD или DD День (маска по умолчанию) DAY, или DY, или D Первый день недели HH, или HH12, или HH24 Час MI Минута Рассмотрим типовые примеры – усечение и округление даты до часов, дней, месяца и года. Запрос на вывод текущей даты, усеченной до различных форматов (рис. 3.38): 29 SELECT SYSDATE d1, TRUNC(SYSDATE, 'HH24') d2, TRUNC(SYSDATE, 'DD') d3, TRUNC(SYSDATE, 'MM') d4, TRUNC(SYSDATE, 'YYYY') d5 FROM DUAL; D1 26.09.2006 16:49:21 D2 D3 D4 D5 26.09.2006 26.09.2006 01.09.2006 01.01.2006 16:00:00 Рис. 3.38. Результат работы функции TRUNC Вывести текущую дату округленной до различных форматов (рис. 3.39) можно с помощью запроса SELECT SYSDATE d1, ROUND(SYSDATE, 'HH24') d2, ROUND(SYSDATE) d3, ROUND(SYSDATE, 'DD') d4, ROUND(SYSDATE, 'MM') d5 FROM DUAL; D1 26.09.2006 16:50:50 D2 D3 D4 D5 26.09.2006 27.09.2006 27.09.2006 01.10.2006 17:00:00 Рис. 3.39. Результат работы функции ROUND Для определения величины временного промежутка от первого заданного значения до второго в выбранных единицах измерения времени может использоваться функция DATEDIFF. Функция DATEDIFF поддерживается в Firebird и в MS SQL Server. В Firebird функция возвращает значение типа BIGINT и имеет формат DATEDIFF ( <временной_отрезок> FROM <значение1> TO <значение2> ) или DATEDIFF ( <временной_отрезок>, <значение1>, <значение2> ). где <временной_отрезок> имеет тот же синтаксис, что и в функции DATEADD. Нужно отметить, что:  функция возвращает положительное число, если <значение2> превышает <значение1>, отрицательное – если <значение1> превышает <значение2>, и ноль – если значения равны;  если результат вычисления дробный, то выводится округленное значение;  сравнение значения типа DATE со значением типа TIME недопустимо;  как и для функции DATEADD, определенные временные отрезки могут использоваться только с соответствующим им типом. REQUESTCD Interval Например, требуется для 5 заявок, поданных абонентом 2 3 с лицевым счетом '115705', 15 NULL вывести количество дней, 16 прошедших от даты 17 22 регистрации заявки до 18 7 момента ее выполнения (рис. Рис. 3.40. Результат 3.40). Для этого можно работы использовать запрос 30 функции DATEDIFF SELECT RequestCD, DATEDIFF (DAY FROM IncomingDate TO ExecutionDate) AS "Interval" FROM Request WHERE AccountCD = '115705'; В MS SQL Server функция поддерживается в формате DATEDIFF ( <временной_отрезок>, <значение1>, <значение2> ). Таким образом, предыдущий запрос в MS SQL Server должен быть записан так: SELECT RequestCD, DATEDIFF(DAY,IncomingDate,ExecutionDate) AS Interval FROM Request WHERE AccountCD = '115705'; В Oracle функция DATEDIFF не используется. Для определения величины временного промежутка от первого заданного значения до второго в месяцах в Oracle может использоваться функция MONTHS_BETWEEN. Данная функция имеет формат MONTHS_BETWEEN(<дата_окончания>, <дата_начала>). Например, предыдущий запрос, преобразованный для вычисления разницы в месяцах (рис. 3.41), в Oracle можно записать в виде SELECT RequestCD, MONTHS_BETWEEN(EXECUTIONDATE, IncomingDate) AS Months FROM Request WHERE AccountCD = '115705'; REQUESTCD MONTHS 2 0,1612903225806451612903225806451612903226 15 0,0967741935483870967741935483870967741935 16 (null) 17 0,7096774193548387096774193548387096774194 18 0,2258064516129032258064516129032258064516 Рис. 3.41. Результат работы функции MONTHS_BETWEEN Функции преобразования типа. В тех случаях, когда СУБД не может выполнить неявное преобразование типов, требуется выполнять явное преобразование. В Firebird явное преобразование выполняется с помощью функции CAST. Эта функция производит преобразование значения выражения, заданного первым аргументом, в тип, заданный вторым аргументом. Синтаксис функции: CAST (<выражение> AS <тип данных>). В качестве типа данных нельзя указывать домены. В большинстве случаев использование функции CAST не требуется, так как Firebird производит неявное преобразование типов данных. Например, сравнение столбца типа DATE с датой '12/31/2013' приведет к неявному преобразованию строкового литерала '12/31/2013' в тип данных DATE, и следующий запрос будет корректным: SELECT * FROM Request WHERE IncomingDate < '12/31/2013'; 31 Можно использовать функцию CAST для сравнения столбцов с различными типами данных из одной и той же таблицы или из различных таблиц. С помощью CAST можно выполнять преобразование из одного типа дата/время в другой. В табл. 3.7 представлены правила преобразования [29]. Таблица 3.7. Преобразования между типами дата/время Исходн В тип В тип В тип ый тип TIMESTAMP DATE TIME TIMEST Недоступно Да, Да, AMP преобразуе преобразует т дату, время, игнорируя игнорируя время дату DATE Да, время Недоступн Нет устанавливается о в значение полуночи TIME Да, дате Нет Недоступно присваивается значение CURRENT_DATE DATE+T Да Нет Нет IME При использовании констант даты 'NOW', 'TODAY', 'TOMORROW' и 'YESTERDAY' в SQL-запросах необходимо явно выполнить их преобразование к нужному типу данных, иначе они будут рассматриваться как строковые константы. Примерами использования константы 'YESTERDAY' могут служить запросы: SELECT * FROM PaySumma WHERE PayDate < CAST('YESTERDAY' AS DATE);, SELECT * FROM PaySumma WHERE PayDate < DATE 'YESTERDAY';, возвращающие всю информацию об оплатах, произведенных ранее вчерашней даты. Можно также преобразовывать правильно сформированную строку в тип «дата-время». Например, значение с типом даты из трех значений дня, месяца и года можно получить следующим образом: CAST(DAY || '. ' || MONTH || '. ' || YEAR AS DATE), где DAY, MONTH и YEAR могут представлять собой константы или столбцы таблицы, в которых содержится значение соответственно дня, месяца и года. Например, если требуется вывести различные значения месяца и года начислений за услугу с кодом 2, отнесенные на первое число соответствующего месяца (рис. 3.42), то запрос может выглядеть так: SELECT DISTINCT NachislMonth, NachislYear, 32 CAST('1.' || NachislMonth || '.' || NachislYear AS DATE) AS "FirstDay" FROM NachislSumma WHERE ServiceCD = 2; NACHISLMONTH 1 1 4 5 6 8 9 9 10 10 11 12 12 NACHISLYEAR 2010 2013 2013 2011 2011 2011 2010 2011 2011 2012 2011 2010 2011 FirstDay 01.01.2010 01.01.2013 01.04.2013 01.05.2011 01.06.2011 01.08.2011 01.09.2010 01.09.2011 01.10.2011 01.10.2012 01.11.2011 01.12.2010 01.12.2011 Рис. 3.42. Результат работы функции CAST Кроме этого, можно преобразовывать числовые типы в строку и наоборот. Например, чтобы при выводе увеличить номер лицевого счета всех абонентов на 2, нужно использовать запрос SELECT (CAST (AccountCD AS INTEGER)+2) AS New_Acc, Fio FROM Abonent; Если нужно вывести значения начислений абоненту с номером лицевого счета '115705' округленными до целого значения (рис. 3.43), то для этого можно использовать запрос: SELECT NachislFactCD, NachislSum, CAST(NachislSum AS INTEGER) AS "RoundSum" FROM NachislSumma WHERE AccountCD='115705'; NACHISLFACTCD 4 5 11 12 25 31 37 49 71 NACHISLSUM 40,00 250,00 250,00 58,70 37,15 37,80 37,15 37,15 553,85 RoundSum 40 250 250 59 37 38 37 37 554 Рис. 3.43. Результат округления числа до целого значения с помощью функции CAST 33 Для получения значения, округленного до целого, можно также использовать описанную выше функцию ROUND с указанием точности, равной нулю. В таком случае значения будут выведены с нулями после запятой. СУБД MS SQL Server и Oracle также поддерживают функцию CAST для выполнения преобразования типов. Помимо функции CAST, MS SQL Server для преобразования типов данных поддерживает функцию CONVERT в формате CONVERT (<тип данных> [ (<длина> ) ] , <выражение> [ , <стиль> ] ), где <длина> – длина целевого типа данных; <стиль> – целочисленное выражение, определяющее, как функция CONVERT преобразует <выражение>. Параметр <стиль> для каждого типа данных MS SQL Server может иметь определенный набор значений [37]. Если <стиль> имеет значение NULL, возвращается NULL. СУБД Oracle предлагает больше тридцати однострочных функций, позволяющих переводить значение из одного типа данных в другой. Одной из самых популярных функций преобразований является TO_CHAR, которая превращает дату или время в строку символов. Не менее популярными функциями являются TO_DATE и TO_NUMBER. Кроме того, существуют функции преобразования из двоичного формата в формат с плавающей запятой, из многобайтовых в однобайтовые символы и пр. Рассмотрим три популярные функции преобразования: TO_CHAR, TO_DATE и TO_NUMBER. Функция TO_CHAR применяется для преобразования даты или времени в строку символов и имеет следующий синтаксис: TO_CHAR(<выражение>[,<маска>]). Параметр <выражение> - это дата/время или числовое значение, столбец или выражение, за которым после необязательного параметра следует <маска>. Параметр <маска>, формально называемый форматной маской, задаёт точный формат выходной строки символов. Например, чтобы преобразовать столбец PayDate таблицы PaySumma в выходную строку, имеющую другой формат, необходимо следующим образом использовать функцию TO_CHAR в запросе: SELECT TO_CHAR(PayDate,'Month DD, YYYY') "PayDate" FROM PaySumma; В результате получим даты оплаты услуг вида «January 08, 2012» или «May 06, 2013». Можно пропустить форматную маску, тогда Oracle будет использовать формат, принятый по умолчанию, DD-MON-YYYY или DDMON-YY (например, 03-SEP-2011). Функция TO_CHAR часто используется для форматирования числовых результатов в форму, более удобную для восприятия. Например, с помощью приведённой ниже функции TO_CHAR можно преобразовать и отобразить на экране значения столбца, расположив перед ним символ доллара, использовав для отделения миллионов и тысяч запятую и округляя значения с точностью до двух знаков после десятичной точки. 34 SELECT TO_CHAR(1593480.829,'$9,999,999.99') "PayDate" FROM DUAL; Запрос выдаст строку «$1,593,480.83». Указанная модель формата, '$9,999,999.99', означает, что перед значением ставится знак $, а каждый символ 9 означает один разряд. Точка указывает положение десятичного разделителя, а следующие за ней две девятки обозначают, что в результате необходимо указывать два знака после запятой (даже если это 00). Существует целый набор символов модели формата, которые можно использовать для форматирования дат или чисел. Некоторые из важнейших символов, которые можно использовать в различных комбинациях при задании формата, перечислены в табл. 3.8. Рассмотрим пример еще одного запроса, результат которого представлен на рис. 3.44: SELECT TO_CHAR(345.678,'99,999.99') AS "99,999.99", TO_CHAR(0.00678,'9.999EEEE') AS "9.999EEEE", TO_CHAR(5280,'99.99') AS "09.99", TO_CHAR(1256,'RN') AS "RN: 1,256", TO_CHAR(1234.5678,'009,999.00') AS "009,999.00" FROM DUAL; 99,999.99 9.999EEEE 99.99 RN: 009,999.00 1,256 345.68 6.780E-03 ###### MCCLVI 001,234.57 Рис. 3.44. Действие форматных масок функции TO_CHAR Поскольку в форматной маске первого столбца количества разрядов слева от точки больше количества разрядов значения, то в результате число выводится с отступом слева. Второй столбец демонстрирует вывод результата в экспоненциальной форме. В третьем столбце количество позиций форматной маски недостаточно для отображения числа, поэтому результат выводится в виде символов #. Чтобы число отображалось правильно, нужно расширить форматную маску до необходимого количества разрядов. Четвёртый столбец показывает вывод числа римскими цифрами. И в последнем столбце нули добавляются в начале числа. Таблица 3.8. Элементы, применяющиеся для задания формата чисел Элемен Пример Описание т 9 999 Возвращает значение с заданным количеством цифр. Если число положительное, оно начинается с пробела, если отрицательное – со знака «минус». Число девяток соответствует максимальному количеству цифр, отображаемых на экране 35 Возвращает запятую в заданной позиции. Запятая не может быть первым элементом или появляться после десятичного разделителя . (точка) 99.99 Возвращает точку и помечает начало дробной части числа. Преобразуемое выражение округляется с точностью до разряда, заданного форматной маской 0999 Число начинается с нулей 9990 Число завершается нулями $ $999,999 Число начинается со знака доллара B B999 Если целая часть числа равна нулю, вместо неё подставляются пробелы MI 9999MI Если значение отрицательное, знак «минус» ставится в конце. Если число положительное, в конце ставится пробел RN RN Возвращает число прописными римскими цифрами rn Возвращает число строчными римскими цифрами S S9999 Для отрицательных значений в начале числа ставится знак «минус», для положительных – знак «плюс» или Для отрицательных значений в 9999S конце числа ставится знак «минус», для положительных – знак «плюс» EEEE 9,99EEE Возвращает значение в E экспоненциальной форме записи (например, 1.78Е+03) , 9,999 (запятая ) Функция TO_DATE преобразует строку в дату/время, имеет синтаксис: TO_DATE(<выражение>[,<маска>] [, <кодировка>])), где <выражение> – это строка, которую необходимо преобразовать в дату, а <маска> – необязательная модель формата, указывающая Oracle, как интерпретировать строку даты, <кодировка> – необязательный параметр, отвечающий за настройки языка для преобразования. Если формат не указывается, строка даты должна иметь один из принятых по умолчанию 36 форматов – DD-MON-YY или DD-MON-YYYY (в одинарных кавычках). Символьные названия месяцев должны быть записаны в соответствии с языковыми настройками для БД Oracle либо должна быть указана <кодировка>, используемая для преобразования. Формат дат по умолчанию определяется параметрами NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.) и может быть задан на уровне БД, уровне экземпляра или уровне сессии. Ниже приведено несколько примеров функции TO_DATE (языковые настройки БД по умолчанию – русский язык): TO_DATE('Январь 10, 2013', 'Month DD,YYYY'); TO_DATE('January 10, 2013', 'Month DD,YYYY' ,'NLS_DATE_LANGUAGE = American'); TO_DATE('10/17/2014 10:27:48', 'MM/DD/YYYY HH24:MI:SS'); TO_DATE('7.14.12', 'MM.DD.YY'); TO_DATE('2006/9/14', 'YYYY/MM/DD'); TO_DATE('10-ФЕВ-2010'). В первом примере модель формата указывает роль каждого элемента строки даты: дата начинается с полного названия месяца, затем идёт двухзначное представление дня месяца (DD), запятая и четырёхзначное представление года (YYYY). Другие примеры формата точно так же соответствуют форме строк даты, находящихся перед ними. Исключением является только пятый пример – он имеет форму, принятую по умолчанию, поэтому Oracle интерпретирует её правильно без помощи строки формата. Наиболее важные элементы модели формата даты, их значения и примеры использования приведены в табл. 3.9. Для преобразования строки в тип данных даты/времени в Oracle можно, например, использовать запрос SELECT TO_DATE('Январь 10, 2011','Month DD, YYYY') AS "Date-1", TO_DATE('10/07/2009 14:23:39','MM/DD/YYYY HH24:MI:SS') AS "Date-2" FROM DUAL;, результат которого представлен на рис. 3.45. Date-1 Date-2 10.01.2011 0:00:00 07.10.2009 14:23:39 Рис. 3.45. Пример работы функции TO_DATE Стоит обратить внимание на то, что результат отображается в формате Oracle, установленном по умолчанию. Функция TO_NUMBER используется для преобразования в число и имеет следующий формат: TO_NUMBER(<выражение>[,<маска>])). Строка формата модели (<маска>) может содержать те же элементы, что перечислены в табл. 3.8. Далее приведен запрос, который преобразует значения столбца AccountCD в число, добавляя к нему 10 (рис. 3.46): SELECT AccountCD, TO_NUMBER(AccountCD)+10 AS "AccountCD+10" 37 FROM NachislSumma WHERE ServiceCD = 4; ACCOUNTCD 015527 015527 080270 080270 080270 115705 126112 136159 136159 136169 443069 443069 443690 443690 AccountCD+10 15537 15537 80280 80280 80280 115715 126122 136169 136169 136179 443079 443079 443700 443700 Рис. 3.46. Результат работы функции TO_NUMBER Таблица 3.9. Элементы моделей формата, относящихся к дате и времени Понятие Параметр Описание Пример Столетие CC Двухзначное представление 20, 21 века Квартал Q Однозначное представление 1, 2, 3 или 4 квартала года Год YYYY, YYY, Год, представленный 2015, 006, 06, 6 YY, Y, RR четырьмя, тремя, двумя или одной цифрой. RR – округление до ближайшего года Месяц MONTH, Месяц, представленный JANUARY, Month, MON, полным или сокращённым January, JAN или Mon именем. Использование Jan прописных/строчных букв – как в шаблоне MM Двухзначное представление 01, 02, …, 12 месяца Неделя WW, W Двухзначное представление 01-52, 1-5 недели года, однозначное представление недели месяца День DDD, DD, D Трехзначное представления 38 SP дня года, двухзначное представление дня месяца, однозначное представление дня недели День недели: полное название, сокращенное название, прописными буквами, прописная и строчные буквы соответственно Двухзначное представление часа в 24- или 12-часовом формате Двухзначное представление минуты Двухзначное представление секунды. Диапазон: 0-59 Символы, используемые для разделения значения даты и времени (дефис, косая черта, точка с запятой, запятая, двоеточие, точка) Время до (AM, A.M.) или после полудня (PM, P.M.) Год до нашей эры (BC, B.C.) или нашей эры (AD, A.D.) Задает суффикс для порядковых чисел Число записывается словами TZR Часовой пояс DAY, Day, DY, Dy Час HH24, HH Минута MI Секунда SS Разделители - / ; , : . Суффиксы времени AM, A.M., PM, P.M. AD, BC, A.D., B.C. TH SUNDAY, Sunday, SUN, Sun 23, 11 Диапазон: 0-59 DD-MONYYYY; HH:MM:SS; YYYY/MM/DD 12:45 P.M. 1452 B.C. 1ST, 2ND, 3RD или 15TH DDSP дает FIFTEEN (если день равен 15) PST, EST 3.2.3.3. Агрегатные функции Для подведения итогов по данным, содержащимся в БД, в языке SQL предусмотрены агрегатные (статистические) функции. Агрегатная функция берет в качестве аргумента какой-либо столбец (для множества строк), а возвращает одно значение, определяемое типом функции. Основные агрегатные функции, поддерживаемые всеми рассматриваемыми СУБД:  AVG – среднее значение в столбце;  SUM – сумма значений в столбце;  MAX – наибольшее значение в столбце;  MIN – наименьшее значение в столбце;  COUNT – количество значений в столбце. 39 Аргументами агрегатных функций могут быть как столбцы таблицы, так и результаты выражений над ними. При этом выражение может быть сколь угодно сложным. Агрегатные функции могут использоваться в секции SELECT, а также в секции HAVING. Вложенность агрегатных функций не допускается, однако из этих функций можно составлять любые выражения. Для функций SUM и AVG столбец должен содержать числовые значения. Специальная функция COUNT (*) служит для подсчета всех без исключения строк в таблице (включая дубликаты). Результатом функции COUNT не может быть NULL. Она может вернуть только натуральное число (положительное целое) или ноль. Все другие агрегатные функции могут дать на выходе NULL, если аргумент не содержит ни одной строки или содержит строки, включающие только NULL. Аргументу всех функций, кроме COUNT (*), может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Если не используется секция GROUP BY, но в секции SELECT используется какая-либо агрегатная функция, то в качестве возвращаемых элементов нельзя указывать по отдельности столбцы таблиц (можно лишь в качестве аргументов агрегатных функций). Вычисление среднего значения. Для вычисления среднего всех значений, содержащихся в столбце, используется агрегатная функция AVG. Данные, содержащиеся в столбце, должны иметь числовой тип. Функция AVG подсчитывает сумму известных значений и делит ее на количество этих значений, а не на общее количество значений, среди которых могут быть NULL. В общем случае возвращаемый функцией результат может иметь тип, не совпадающий с типом столбца. Синтаксис использования функции AVG({[[ALL] | DISTINCT] столбец | [DISTINCT] <выражение>}). При указании аргумента ALL происходит вычисление по всем известным значениям множества элементов. Если указан аргумент DISTINCT, то перед вычислением среднего значения из рассмотрения исключаются дублирующиеся значения. Если число строк, обрабатываемых агрегатной функцией AVG, равно нулю или значения всех элементов не определены, то функция возвращает NULL. Выражение представляет собой скалярное численное выражение языка SQL. Например, чтобы вычислить среднее значение оплат всех абонентов, необходимо выполнить запрос: AVG SELECT AVG(Paysum) FROM 161,15 Paysumma; Результат выполнения Рис. 3.47. запроса представлен на рис. Результат работы 3.47. функции AVG Аргументом агрегатной функции может быть как простое имя столбца, как в предыдущем примере, так и выражение, как, например, в следующем запросе: 40 SELECT AVG(NachislSum+2) FROM NachislSumma; При вычислении среднего к каждому суммируемому значению NachislSum добавляется число 2, а затем полученная сумма делится на количество значений. В результате выполнения данного запроса будет возвращено число 168,02. Вычисление суммы значений в столбце. Для вычисления суммы значений, содержащихся в столбце, используется агрегатная функция SUM. При этом столбец должен иметь числовой тип данных. Результат, возвращаемый этой функцией, имеет тот же тип данных, что и столбец, но количество значащих цифр может быть больше, чем количество значащих цифр отдельных значений в столбце. Использование этой функции аналогично использованию функции AVG. Синтаксис использования функции SUM: SUM({[[ALL] | DISTINCT] столбец | [DISTINCT] <выражение>}). SUM Например, для нахождения 12949,73 суммы всех значений начислений (рис. 3.48) можно использовать Рис. 3.48. запрос: Результат SELECT SUM(NachislSum) FROM работы NachislSumma; функции SUM Вычислить среднее значение оплат и сумму всех оплат за услугу с кодом 1 можно так: SELECT AVG(PaySum), SUM(PaySum) FROM PaySumma WHERE ServiceCD=1; Вычисление экстремумов. Для нахождения наименьшего или наибольшего значения в столбце используются агрегатные функции – соответственно MIN или MAX. При этом столбец может содержать числовые и строковые значения либо значения даты/времени. Синтаксис использования агрегатных функций по нахождению максимального и минимального значений: MAX({[[ALL] DISTINCT] столбец | [DISTINCT] <выражение>}), MIN({[[ALL] | DISTINCT] столбец | [DISTINCT] <выражение>}). Результат, возвращаемый этими функциями, имеет такой же тип данных, что и сам столбец. В случае применения функций MIN и MAX к числовым данным числа сравниваются по арифметическим правилам. Например, чтобы найти в таблице PaySumma максимальное и минимальное значения оплат (рис. 3.49), можно выполнить запрос: SELECT MAX(PaySum), MIN(PaySum) FROM PaySumma; Сравнение дат MAX MIN происходит 611,30 8,30 последовательно: более Рис. 3.49. Результат ранние значения дат считаются меньшими, чем работы 41 более поздние. функций MAX и MIN Сравнение интервалов времени выполняется на основании их продолжительности: более короткие интервалы времени меньше, чем более длинные. Вычисление количества значений в столбце. Количество значений в столбце подсчитывает функция COUNT. При этом тип данных столбца может быть любым. Синтаксис использования этой агрегатной функции такой: COUNT({ * | [[ALL] | DISTINCT] столбец | [DISTINCT] <выражение>}). Символ звездочки в качестве аргумента функции используется для подсчета количества строк в заданной таблице, включая признак NULL. Если в качестве аргумента выступает имя столбца, то состояния NULL в нем не рассматриваются. При использовании выражения (в том числе константы) в качестве аргумента функция оценивает <выражение> для каждой строки в группе и возвращает количество значений (в случае указания DISTINCT – уникальных), не равных NULL. Например, чтобы подсчитать общее количество абонентов, можно использовать запрос: SELECT COUNT(*) FROM Abonent;, а для подсчета числа абонентов с указанным номером телефона такой: SELECT COUNT(Phone) FROM Abonent; COUNT Следующий запрос позволяет подсчитать число 10 абонентов, которые подавали Рис. 3.50. заявки на ремонт газового Результат оборудования (рис. 3.50): работы функции SELECT COUNT(DISTINCT COUNT AccountCD) FROM Request; Таким образом, только 10 разных абонентов из двенадцати подавали заявки на ремонт оборудования, хотя всего в таблице ремонтных заявок содержится 21 строка (некоторые абоненты подавали несколько заявок). 3.2.3.4. Функции для списка выражений В СУБД могут поддерживаться функции для списка выражений. Такие функции, хотя и обрабатывают набор значений, но не являются агрегатными. В Firebird функции для списка выражений представлены функциями MAXVALUE, MINVALUE и LIST. Функции MAXVALUE и MINVALUE возвращают соответственно максимальное и минимальное значения из списка выражений своих аргументов. Имеют формат: MAXVALUE (<выражение1> [,<выражение2> ...] ), MINVALUE (<выражение1> [,<выражение2> ...] ) . Например, поставить в соответствие ремонтным заявкам, принятым исполнителем с кодом 1, дату их выполнения или 1 января 2012 г., если 42 соответствующая заявка была выполнена раньше этой даты, можно запросом: SELECT RequestCD, MAXVALUE (ExecutionDate, CAST ('01.01.2012' AS DATE)) FROM Request WHERE ExecutorCD = 1; Результат выполнения REQUESTCD MAXVALUE запроса представлен на 1 01.01.2012 рис. 3.51. 3 08.03.2012 6 01.01.2012 11 12.01.2013 17 01.01.2012 21 01.01.2012 22 01.01.2012 Рис. 3.51. Результат работы функции MAXVALUE Из результата предыдущего запроса следует, что заявкам с кодами 1, 6, 17, 21 и 22, имеющим дату выполнения ранее 01.01.2012 г., поставлена в соответствие дата 1 января 2012 г., а заявкам с кодами 3 и 11 поставлена в соответствие дата выполнения заявки. Следует учесть, что если одно и более значений выражений в списке содержат NULL, функции MAXVALUE и MINVALUE возвращают NULL. Если выполнить предыдущий запрос по исполнителю с кодом 4: SELECT RequestCD, MAXVALUE (ExecutionDate, CAST ('01.01.2012' AS DATE)) FROM Request WHERE ExecutorCD = 4; , то в возвращенной строке с RequestCD = 5 получим NULL в столбце MAXVALUE, так как у заявки с кодом 5 дата выполнения неизвестна. В СУБД Oracle вместо функций MAXVALUE и MINVALUE используются аналогичные функции GREATEST и LEAST [49]. Синтаксис функций: GREATEST (<выражение1> [,<выражение2> ...] ), LEAST (<выражение1> [,<выражение2> ...] ) . Примечания. 1. Если типы данных выражений разные, все выражения будут преобразованы в тип данных выражения <выражение1>. 2. Если сравнение основано на сравнении символов, один символ считается меньше, чем другой, если он имеет более низкое значение кода символов. 3. Если одно из выражений имеет значение NULL, в качестве значения функции будет возвращено NULL. Функция LIST может использоваться в Firebird для возврата строки, полученной в результате соединения известных значений (не NULL) из 43 списка, представленного набором значений аргумента функции (<выражение>). Функция LIST имеет формат: LIST ( [ {ALL | DISTINCT} ] <выражение> [ , <разделитель> ] ), где <разделитель> ::={строковая_константа | параметр | переменная | строковая функция}. Функция возвращает NULL, если все значения из списка имеют NULL. Примечание. Входные параметры и переменные могут выступать в качестве разделителей при использовании функции LIST в хранимых процедурах (хранимые процедуры будут подробно описаны далее). В качестве аргумента функции LIST могут быть заданы числовые значения и значения типа дата/время, которые в процессе работы функции преобразуются в строку (результирующее значение имеет тип BLOB). Нужно учитывать следующие синтаксические правила:  если ни ALL, ни DISTINCT не указаны, то по умолчанию применяется ALL;  если <разделитель> опущен, то для разделения соединяемых величин используется запятая. Например, для вывода в одну строку через запятую названий всех услуг (рис. 3.52) можно использовать следующий запрос: SELECT LIST (ServiceNM) FROM Services; LIST Газоснабжение,Электроснабжение,Теплоснабжение,Водоснабжение Рис. 3.52. Результат работы функции LIST 44