Функции обработки значений
ВЫЧИСЛЕНИЯ
Итоговые функции
В выражениях SQL-запросов нередко требуется выполнить предварительную обработку данных. С этой целью используются специальные функции и выражения.
Довольно часто требуется узнать, сколько записей соответствует тому или иному запросу, какова сумма значений некоторого числового столбца, его максимальное, минимальное и среднее значения. Для этого служат так называемые итоговые (статистические, агрегатные) функции. Итоговые функции обрабатывают наборы записей, заданные, например, выражением WHERE. Если их включить в список столбцов, следующий за оператором SELECT, то результатная таблица будет содержать не только столбцы таблицы базы данных, но и значения, вычисленные с помощью этих функций. Далее приведен список итоговых функций.
• COUNT (параметр) — возвращает количество записей, указанных в параметре. Если требуется получить количество всех записей, то в качестве параметра следует указать символ звездочки (*). Если в качестве параметра указать имя столбца, то функция вернет количество записей, в которых этот столбец имеет значения, отличные от NULL. Чтобы узнать, сколько различных значений содержит столбец, перед его именем следует указать ключевое слово DISTINCT. Например:
SELECT COUNT(*) FROM Клиенты;
SELECT COUNT(Сумма_заказа) FROM Клиенты;
SELECT COUNT(DISTINCT Сумма_заказа) FROM Клиенты;
Попытка выполнить следующий запрос приведет к сообщению об ошибке:
SELECT Регион, COUNT(*) FROM Клиенты;
• SUM (параметр) — возвращает сумму значений указанного в параметре столбца. Параметр может представлять собой и выражение, содержащее имя столбца. Например:
SELECT SUM(Сумма_заказа) FROM Клиенты;
Данное SQL-выражение возвращает таблицу, состоящую из одного столбца и одной записи и содержащую сумму всех определенных значений столбца Сумма_заказа из таблицы Клиенты.
Допустим, что в исходной таблице значения столбца Сумма_заказа выражены в рублях, а нам требуется вычислить общую сумму в долларах. Если текущий обменный курс равен, например, 27,8, то получить требуемый результат можно с помощью выражения:
SELECT SUM (Сумма_заказа*27.8) FROM Клиенты;
• AVG (параметр) — возвращает среднее арифметическое всех значений указанного в параметре столбца. Параметр может представлять собой выражение, содержащее имя столбца. Например:
SELECT AVG (Сумма_заказа) FROM Клиенты;
SELECT AVG (Сумма_заказа*27.8) FROM Клиенты
WHERE Регион <> 'Северо_3апад';
• МАХ (параметр) — возвращает максимальное значение в столбце, указанном в параметре. Параметр может также представлять собой выражение, содержащее имя столбца. Например:
SELECT МАХ(Сумма__заказа) FROM Клиенты;
SELECT МАХ(Сумма_заказа*27.8) FROM Клиенты
WHERE Регион <> 'Северо_3апад';
• MIN(параметр) — возвращает минимальное значение в столбце, указанном в параметре. Параметр может представлять собой выражение, содержащее имя столбца. Например:
SELECT MIN(Сумма_заказа) FROM Клиенты;
SELECT MIN (Сумма__заказа*27 . 8) FROM Клиенты
WHERE Регион <> 'Северо_3апад';
На практике нередко требуется получить итоговую таблицу, содержащую суммарные, усредненные, максимальные и минимальные значения числовых столбцов. Для этого следует использовать группировку (GROUP BY) и итоговые функции.
SELECT Регион, SUM(Сумма_заказа) FROM Клиенты
GROUP BY Регион;
Результатная таблица для данного запроса содержит имена регионов и итоговые (общие) суммы заказов всех клиентов из соответствующих регионов (рис. 5).
Теперь рассмотрим запрос на получение всех итоговых данных по регионам:
SELECT Регион, SUM (Сумма_заказа), AVG(Сумма_заказа), МАХ(Сумма_заказа), MIN(Сумма_заказа)
FROM Клиенты
GROUP BY Регион;
Исходная и результатная таблицы показаны на рис. 8. В примере только Северо-Западный регион представлен в исходной таблице более чем одной записью. Поэтому в результатной таблице для него различные итоговые функции дают различные значения.
Рис. 8. Итоговая таблица сумм заказов по регионам
При использовании итоговых функций в списке столбцов в операторе SELECT заголовки соответствующих им столбцов в результатной таблице имеют вид Expr1001, Expr1002 и т.д. (или что-нибудь аналогичное, в зависимости от реализации SQL). Однако заголовки для значений итоговых функций и других столбцов вы можете задавать по своему усмотрению. Для этого достаточно после столбца в операторе SELECT указать выражение вида:
AS заголовок_столбца
Ключевое слово AS (как) означает, что в результатной таблице соответствующий столбец должен иметь заголовок, указанный после AS. Назначаемый заголовок еще называют псевдонимом. В следующем примере (рис. 9) задаются псевдонимы для всех вычисляемых столбцов:
SELECT Регион,
SUM(Сумма_заказа) AS [Общая сумма заказа],
AVG(Сумма_заказа) AS [Средняя сумма заказа],
МАХ(Сумма_заказа) AS Максимум,
MIN (Сумма_заказа) AS Минимум,
FROM Клиенты
GROUP BY Регион;
Рис. 9. Итоговая таблица сумм заказов по регионам с применением псевдонимов столбца
Псевдонимы, состоящие из нескольких слов, разделенных пробелами, заключаются в квадратные скобки.
Итоговые функции можно использовать в выражениях SELECT и HAVING, но их нельзя применять в выражении WHERE. Oneратор HAVING аналогичен оператору WHERE, но в отличие от WHERE он отбирает записи в группах.
Допустим, требуется определить, в каких регионах более одного клиента. С этой целью можно воспользоваться таким запросом:
SELECT Регион, Count(*)
FROM Клиенты
GROUP BY Регион HAVING COUNT(*) > 1;
Функции обработки значений
При работе с данными часто приходится их обрабатывать (преобразовывать к нужному виду): выделить в строке некоторую подстроку, удалить ведущие и заключительные пробелы, округлить число, вычислить квадратный корень, определить текущее время и т. п. В SQL имеются следующие три типа функций:
• строковые функции;
• числовые функции;
• функции даты-времени.
Строковые функции
Строковые функции принимают в качестве параметра строку и возвращают после ее обработки строку или NULL.
• SUBSTRING (строка FROM начало [FOR длина]) — возвращает подстроку, получающуюся из строки, которая указана в качестве параметра строка. Подстрока начинается с символа, порядковый номер которого указан в параметре начало, и имеет длину, указанную в параметре длина. Нумерация символов строки ведется слева направо, начиная с 1. Квадратные скобки здесь указывают лишь на то, что заключенное в них выражение не является обязательным. Если выражение FOR длина не используется, то возвращается подстрока от начало и до конца исходной строки. Значения параметров начало и длина должны выбираться так, чтобы искомая подстрока действительно находилась внутри исходной строки. В противном случае функция SUBSTRING вернет NULL.
Например:
SUBSTRING ('Дорогая Маша!' FROM 9 FOR 4) — возвращает 'Маша';
SUBSTRING ('Дорогая Маша! ' FROM 9) —возвращает 'Маша! ';
SUBSTRING('Дорогая Маша! ' FROM 15) —возвращает NULL.
Использовать эту функцию в SQL-выражении можно, например, так:
SELECT * FROM Клиенты
WHERE SUBSTRING(Регион FROM 1 FOR 5) = 'Север';
• UPPER (строка) — переводит все символы указанной в параметре строки в верхний регистр.
• LOWER (строка) — переводит все символы указанной в параметре строки в нижний регистр.
• TRIM (LEADING | TRAILING | BOTH ['символ'] FROM строка) — удаляет ведущие (LEADING), заключительные (TRAILING) или те и другие (BOTH) символы из строки. По умолчанию удаляемым символом является пробел (' '), поэтому его можно не указывать. Чаще всего эта функция используется именно для удаления пробелов.
Например:
TRIM (LEADING ' ' FROM 'город Санкт-Петербург') вращает ' город Санкт-Петербург ';
TRIM(TRALING ' ' FROM 'город Санкт-Петербург') возвращает 'город Санкт-Петербург';
TRIM (BOTH ' ' FROM ' город Санкт-Петербург ') — возвращает 'город Санкт-Петербург';
TRIM(BOTH FROM ' город Санкт-Петербург ') — возвращает 'город Санкт-Петербург';
TRIM(BOTH 'г' FROM 'город Санкт-Петербург') — возвращает 'ород Санкт-Петербур'.
Среди этих функций наиболее часто используемые - SUBSTRING() И TRIM().
Числовые функции
Числовые функции в качестве параметра могут принимать данные не только числового типа, но возвращают всегда число или NULL (неопределенное значение).
• POSITION(целеваяСтрока IN строка) — ищет вхождение целевой строки в указанную строку. В случае успешного поиска возвращает номер положения ее первого символа, иначе – 0. Если целевая строка имеет нулевую длину (например, строка ' '), то функция возвращает 1. Если хотя бы один из параметров имеет значение NULL, то возвращается NULL. Нумерация символов строки ведется слева направо, начиная с 1.
Например:
POSITION ('e' IN 'Привет всем') — возвращает 5;
POSITION ('всeм' IN 'Привет всем') — возвращает 8;
POSITION (' ' Привет всем') — возвращает 1;
POSITION('Привет!' IN 'Привет всем') — возвращает 0.
В таблице Клиенты (см. рис. 1) столбец Адрес содержит, кроме названия города, почтовый индекс, название улицы и другие данные. Возможно, вам потребуется выбрать записи о клиентах, проживающих в определенном городе. Так, если требуется выбрать записи, относящиеся к клиентам, проживающим в Санкт-Петербурге, то можно воспользоваться следующим выражением SQL-запроса:
SELECT * FROM Клиенты
WHERE POSITION ('Санкт-Петербург' IN Адрес) > 0;
Заметим, что этот простой запрос на выборку данных можно сформулировать иначе:
SELECT * FROM Клиенты
WHERE Адрес LIKE ' %Петербург% ';
• EXTRACT (параметр) — извлекает элемент из значения типа дата-время или из интервала. Например:
EXTRACT (MONTH FROM DATE '2005-10-25') — возвращает 10.
• CHARACTER_LENGTH (строка) — возвращает количество символов в строке.
Например:
CHARACTER_LENGTH('Привет всем') — возвращает 11.
• OCTET_LENGTH(строка) — возвращает количество октетов (байтов) в строке. Каждый символ латиницы или кириллицы представляется одним байтом, а символ китайского алфавита — двумя байтами.
• CARDINALITY (параметр) — принимает в качестве параметра коллекцию элементов и возвращает количество элементов в коллекции (кардинальное число). Коллекция может быть, например, массивом или мультимножеством, содержащим элементы различных типов.
• ABS (число) — возвращает абсолютное значение числа. Например:
ABS (-123) —возвращает 123;
ABS (2 - 5) — возвращает 3.
• МОD (число1, число2) — возвращает остаток от целочисленного деления первого числа на второе. Например:
MOD (5, з) — возвращает 2;
MOD (2, з) — возвращает 0.
• LN (число) — возвращает натуральный логарифм числа.
• ЕХР(число) — возвращает ечисло (основание натурального логарифма в степени число).
• POWER (число1, число2) — возвращает число1число2 (число1 в степени число2).
• SQRT (число) — возвращает квадратный корень из числа.
• FLOOR (число) — возвращает наибольшее целое число, не превышающее заданное параметром (округление в меньшую сторону). Например:
FLOOR (5.123) — возвращает 5.0.
• CEIL (число) или CEILING (число) — возвращает наименьшее целое число, которое не меньше заданного параметром округление в большую сторону). Например:
CEIL (5.123) — возвращает 6. 0.
• WIDTH_BUCKET(число1, число2, числоЗ, число4) возвращает целое число в диапазоне между 0 и число4 + 1. Параметры число2 и числоЗ задают числовой отрезок, разделенный на равновеликие интервалы, количество которых задается параметром число 4. Функция определяет номер интервала, в который попадает значение число1. Если число1 находится за пределами заданного диапазона, то функция возвращает 0 или число 4 + 1. Например:
WIDTH_BUCKET(3.14, 0, 9, 5) — возвращает 2.
Функции даты-времени
В языке SQL имеются три функции, которые возвращают текущие дату и время.
• CURRENT_DATE — возвращает текущую дату (тип DATE).
Например: 2005-06-18.
• CURRENT_TIME(число) — возвращает текущее время (тип TIME). Целочисленный параметр указывает точность представления секунд. Например, при значении 2 секунды будут представлены с точностью до сотых (две цифры в дробной части):
12:39:45.27.
• CURRENT_TIMESTAMP(число) — возвращает дату и время (тип TIMESTAMP). Например, 2005-06-18 12:39:45.27. Целочисленный параметр указывает точность представления секунд.
Обратите внимание, что дата и время, возвращаемые этими функциями, имеют не символьный тип. Если требуется представить их в виде символьных строк, то для этого следует использовать функцию преобразования типа CAST ().
Функции даты-времени обычно применяются в запросах на вставку, обновление и удаление данных. Например, при записи сведений о продажах в специально предусмотренный для этого столбец вносятся текущие дата и время. После подведения итогов за месяц или квартал, данные о продажах за отчетный период можно удалить.
Вычисляемые выражения
Вычисляемые выражения строятся из констант (числовых, строковых, логических), функций, имен полей и данных других типов путем соединения их арифметическими, строковыми, логическими и другими операторами. В свою очередь, выражения могут быть объединены посредством операторов в более сложные (составные) выражения. Для управления порядком вычисления выражений используются круглые скобки.
Логические операторы AND, OR и NOT и функции были рассмотрены ранее.
Арифметические операторы:
• + — сложение;
• - — вычитание;
• * — умножение;
• / — деление.
Строковый оператор только один — оператор конкатенации или склейки строк (| |). В некоторых реализациях SQL (например, Microsoft Access) вместо (| |) используется символ (+). Оператор конкатенации приписывает вторую строку к концу первой пример, выражение:
'Саша' | | 'любит' | | ' Машу'
вернет в качестве результата строку ' Сашалюбит Машу'.
При составлении выражений необходимо следить, чтобы операнды операторов имели допустимые типы. Например, выражение: 123 + 'Саша' недопустимо, поскольку арифметический оператор сложения применяется к строковому операнду.
Вычисляемые выражения могут находиться после оператора SELECT, а также в выражениях условий операторов WHERE и HAVING.
Рассмотрим несколько примеров.
Пусть таблица Продажи содержит столбцы Тип_товара, Количество и Цена, а нам требуется знать выручку для каждого типа товара. Для этого достаточно в список столбцов после оператора SELECT включить выражение Количество*Цена:
SELECT Тип_товара, Количество, Цена, Количество*Цена AS
Итого FROM Продажи;
Здесь используется ключевое слово AS (как) для задания псевдонима столбца с вычисляемыми данными.
На рис. 10 показаны исходная таблица Продажи и результатная таблица запроса.
Рис. 10. Результат запроса с вычислением выручки по каждому типу товара
Если требуется узнать общую выручку от продажи всех товаров, то достаточно применить следующий запрос:
SELECT SUM (Количество*Цена) FROM Продажи;
Следующий запрос содержит вычисляемые выражения и в списке столбцов, и в условии оператора WHERE. Он выбирает из таблицы продажи те товары, выручка от продажи которых больше 1000:
SELECT Тип_товара, Количество*Цена AS Итого
FROM Продажи
WHERE Количество*Цена > 1000;
Предположим, что требуется получить таблицу, в которой два столбца:
- Товар, содержащий тип товара и цену;
- Итого, содержащий выручку.
Поскольку предполагается, что в исходной таблице продажи столбец Тип_товара является символьным (тип CHAR), а столбец Цена — числовой, то при объединении (склейке) данных из этих столбцов необходимо выполнить приведение числового типа к символьному с помощью функции CAST (). Запрос, выполняющий это задание, выглядит так (рис. 11):
SELECT Тип_товара | | ' (Цена: ' | | CAST(Цена AS CHAR(5)) | | ')' AS Товар, Количество*Цена AS Итого
FROM Продажи;
Рис. 11. Результат запроса с объединением разнотипных данных в одном столбце
Примечание. В Microsoft Access аналогичный запрос будет иметь следующий вид:
SELECT Тип_товара + ' (Цена: ' + CStr (Цена) + ')' AS Товар,
Количество*Цена AS Итого
FROM Продажи;
Не нашли, что искали? Воспользуйтесь поиском по сайту:
©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|