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

Учебник SQL

Урок 7. Создание вычисляемых полей

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

Что такое вычисляемые поля

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

  • Вам необходимо отобразить поле, содержащее имя компании с ее адресом, но эта информация расположена в разных столбцах таблицы.
  • Город, штат и ZIP-код хранятся в отдельных столбцах (как и должно быть), но для программы печати почтовых наклеек необходима эта информация в одном, корректно сформированном поле.
  • Данные в столбце введены с заглавными и строчными буквами, но в вашем отчете необходимо использовать только заглавные буквы.
  • В таблице с предметами заказа хранятся цены продуктов и их количество, но не полная цена (цена одного продукта, умноженная на его количество) каждого продукта. Чтобы распечатать счет, необходимы полные цены.
  • Вам необходимы общая сумма, среднее значение или результаты других расчетов, основанные на данных, имеющихся в таблице.

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

Именно здесь помогут вычисляемые поля. В отличие от всех выбранных нами ранее столбцов, вычисляемых полей на самом деле в таблице базы данных нет. Они создаются "на лету" SQL-оператором SELECT.

Поле

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

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

Клиентское или серверное форматирование?

Многие преобразования и изменения форматов, которые могут быть выполнены посредством SQL-операторов, могут быть также выполнены и клиентским приложением. Однако, как правило, эти операции гораздо быстрее выполняются на сервере базы данных, чем у клиента, так как СУБД предназначена, кроме всего, для быстрого и эффективного выполнения операций такого типа.

Конкатенация полей

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

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

В отчете должно быть одно значение, а данные в таблице хранятся в двух столбцах: vend_name и vend_country. Кроме того, значение vend_country необходимо заключить в скобки, которых нет в таблице базы данных. Выражение SELECT, которое возвращает имена поставщиков и адреса, довольно простое, но как создать комбинированное значение?

Конкатенация

Комбинирование значений (путем присоединения их друг к другу) для получения одного "длинного" значения.

Для этого необходимо соединить два значения. В SQL-выражении SELECT можно выполнить конкатенацию двух столбцов при помощи специального оператора. В зависимости от СУБД это может быть знак "плюс" (+) или две вертикальные черточки (||).

Оператор + или ||

В СУБД Access, SQL Server и Sybase для конкатенации используется знак +. В СУБД DB2, Oracle, PostgreSQL и Sybase используется знак ||. Более подробную информацию ищите в документации по вашей СУБД.
Вообще-то || – более предпочтительный оператор конкатенации, так что он поддерживается все большим и большим количеством СУБД.

Ниже приведен пример использования знака "плюс" (применяется синтаксис, принятый в большинстве СУБД).

SELECT vend_name + ' (' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;

Вывод:

-------------------------
Bear Emporium   (USA)
Beras R Us      (USA)
Doll House Inc. (USA)
Fun and Games   (England)
Furball Inc.    (USA)
Jouets et ours  (France)

Ниже приведена та же инструкция, но с использованием оператора ||

SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

Вывод:

-------------------------
Bear Emporium   (USA)
Beras R Us      (USA)
Doll House Inc. (USA)
Fun and Games   (England)
Furball Inc.    (USA)
Jouets et ours  (France)

В предыдущих операторах SELECT была выполнена конкатенация следующих элементов:

  • имя, хранящееся в столбце vend_name;
  • строка, содержащая пробел и открывающую круглую скобку;
  • название штата, хранящееся в столбце vend_country;
  • строка, содержащая закрывающую круглую скобку.

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

Конкатенация в MySQL

В MySQL не поддерживается конкатенация при помощи оператора + или ||. Здесь необходимо использовать функцию CONCAT(), в которой указывается список элементов, по отношению к которым необходимо выполнить конкатенацию. При использовании функции CONCAT() первая строка примера выглядела бы так:

SELECT CONCAT( vend_name, ' (', vend_country, ')' )

В MySQL поддерживается использование оператора ||, но не для конкатенации. В MySQL || является эквивалентом логического оператора OR, a && — эквивалентом логического оператора AND.

Взгляните еще раз на результат, полученный после применения оператора SELECT. Два столбца, объединенные в вычисляемое поле, заполнены пробелами. Во многих базах данных (но не во всех) сохраненный текст дополняется пробелами до ширины столбца. Чтобы выбрать правильно отформатированные данные, необходимо убрать добавленные пробелы. Это можно сделать при помощи SQL-функции RTRIM() следующим образом:

SELECT RTRIM( vend_name ) + ' (' + RTRIM( vend_country ) + ') '
FROM Vendors
ORDER BY vend_name;

Вывод:

-----------------------
Bear Emporium (USA) 
Beras R Us (USA) 
Doll House Inc. (USA) 
Fun and Games (England) 
Furball Inc. (USA) 
Jouets et ours (France) 

Ниже приведено это же выражение, но с использованием оператора ||:

SELECT RTRIM( vend_name ) || ' (' || RTRIM( vend_country ) "" ') '
FROM Vendors
ORDER BY vend_name;

Вывод:

-----------------------
Bear Emporium (USA) 
Beras R Us (USA) 
Doll House Inc. (USA) 
Fun and Games (England) 
Furball Inc. (USA) 
Jouets et ours (France) 

Функция RTRIM() отбрасывает все пробелы справа от указанного значения. При использовании функции RTRIM() каждый отдельный столбец обрабатывается корректно. Город, штат указываются через запятую и пробел, а штат и ZIP-код – через пробел.

Функции TRIM

В большинстве СУБД поддерживаются как функция RTRIM () (которая, как мы увидели, "обрезает" правую часть строки), так и LTRIM() (которая удаляет левую часть строки), а также TRIM() (которая "обрезает" строку слева и справа).

Использование псевдонимов

Оператор SELECT, который использовался для конкатенации полей имени и адреса, как видите, справился со своей задачей. Но как же называется новый вычисляемый столбец? По правде говоря – никак, это просто значение. Этого может быть достаточно, если вы просматриваете результаты в программе тестирования SQL-запросов, однако столбец без названия нельзя использовать в клиентском приложении, так как клиент не сможет к нему обратиться.

Для решения этой проблемы в SQL была включена поддержка псевдонимов. Псевдоним – это альтернативное имя для поля или значения. Псевдонимы присваиваются при помощи ключевого слова AS. Взгляните на следующий оператор SELECT:

SELECT RTRIM( vend_name ) + ' (' + RTRIM( vend_country ) + ') ' AS vend_title
FROM Vendors
ORDER BY vend_name;

Вывод:

vend_title
-----------------------------
Bear Emporium (USA) 
Beras R Us (USA) 
Doll House Inc. 
(USA) Fun and Games (England) 
Furball Inc. (USA) 
Jouets et ours (France) 

Ниже приведена эта же инструкция, но с использованием оператора ||:

SELECT RTRIM( vend_name ) || ' (' || RTRIM( vend_country ) || ') ' AS vend_title
FROM Vendors
ORDER BY vend_name;

Вывод:

vend_title
-----------------------------
Bear Emporium (USA) 
Beras R Us (USA) 
Doll House Inc. 
(USA) Fun and Games (England) 
Furball Inc. (USA) 
Jouets et ours (France) 

Сам по себе этот оператор SELECT ничем не отличается от предыдущего, за исключением того, что вычисляемое поле указывается после текста AS vend_title. Таким образом, SQL создает вычисляемое поле, содержащее результат вычислений, под названием vend_title. Как видите, результат остается тем же, но столбец теперь носит имя vend_title и любое клиентское приложение может обращаться к нему по имени, как если бы это был реальный столбец таблицы.

Другое использование псевдонимов

Псевдонимы можно использовать и по-другому. Часто псевдонимы используются для переименования столбца, если в реальном названии присутствуют недопустимые символы (например, пробелы) или если название сложное и трудночитаемое.

Имена псевдонимов

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

Производные столбцы

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

Выполнение математических вычислений

Еще одним способом использования вычисляемых полей является выполнение математических операций над выбранными данными. Рассмотрим пример. В таблице Orders хранятся все полученные заказы, а в таблице Order Items содержатся наименования продуктов для каждого заказа. Следующий SQL-оператор осуществляет выборку всех продуктов в заказе номер 20008:

SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_nam = 20008;

Вывод:

prod_id  quantity  item_price
-----------------------------
RGAN01   5         4.9900
BR03     5         11.9900
BNBG01   10        3.4900
BNBG02   10        3.4900
BNBG03   10        3.4900

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

SELECT prod_id,
       quantity,
       item_price
       quantity*item_price AS expanded_price 
FROM OrderItems 
WHERE order_nam = 20008;

Вывод:

prod_id  quantity  item_price  expanded_price
---------------------------------------------
RGAN01   5         4.9900      24.9500
BR03     5         11.9900     59.9500
BNBG01   10        3.4900      34.9000
BNBG02   10        3.4900      34.9000
BNBG03   10        3.4900      34.9000

Столбец expanded_price, показанный в предыдущем результате, является вычисляемым полем; вычисление было простым: quantity*item_price. Теперь клиентское приложение может использовать этот новый вычисляемый столбец, как и любой другой в таблице.

В SQL поддерживаются основные математические операции, перечисленные в табл. 7.1. Кроме того, для управления порядком обработки можно использовать круглые скобки. В уроке 5, "Расширенная фильтрация данных", рассказывается о порядке обработки.

Таблица 7.1. Математические операции в SQL

Операция Описание
+ Сложение
Вычитание
* Умножение
/ Деление

Резюме

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