sql server вычисляемое поле

SQL-Урок 6. Вычисляемые поля

1. Выполнение математических операций

Одним из способов использования расчетных полей является выполнение математических операций над выбранными данными. Давайте на примере рассмотрим как это происходит, использовав снова нашу таблицу Sumproduct. Предположим, нам нужно вычислить среднюю цену приобретения каждого товара. Для этого нужно переделить колонку Amount (сумма) на Quantity (количество):

SELECT DISTINCT Product, Amount/Quantity FROM Sumproduct

Как видим, СУБД отобрала все наименования товаров и отобразила их среднюю стоимость в отдельном столбце, который был создан во время выполнения запроса. Также можно заметить, что мы использовали дополнительный оператор DISTINCT, который нам нужен для отображения уникальных названий товаров (без него мы бы получили дублирование записей).

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

SELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct

Видим, наше расчетное поле получило собственное название AvgPrice. Для этого мы использовали оператор AS, после которого указали необходимое нам название. Стоит отметить, что в SQL поддерживаются только основные математические операции: сложение (+), вычитание (-), умножение (*), деление (/). Также для изменения очередности выполнения операции можно использовать круглые скобки.

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

3. Соединение полей (конкатенация)

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

SELECT Month + ‘ ‘ + Product AS NewField, Quantity FROM Sumproduct

В этом примере мы соединили значение в двух столбцах и вывели результат в новое поле NewField.

Источник

Вычисляемые столбцы в Transact-SQL

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

Вычисляемые столбцы в таблицах

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

Вычисляемые столбцы нужны для того, чтобы было проще и надежней получить результат каких-то постоянных вычислений. Например, при обращении к таблице, Вы всегда в SQL запросе применяете какую-нибудь формулу (один столбец перемножаете с другим или что-то в этом роде, хотя формула может быть и сложней), так вот, если в таблице определить вычисляемый столбец, указав в его определении нужную формулу, Вам больше не нужно будет каждый раз писать эту формулу в SQL запросе в инструкции SELECT. Вам достаточно обратиться к определенному столбцу (вычисляемому столбцу), который автоматически при выводе значений применяет эту формулу. При этом этот столбец можно использовать в запросах также как обычный столбец, например, в секциях WHEHE (в условии) или в ORDER BY (в сортировке).

Также важно понимать, что вычисляемый столбец не может быть указан в инструкциях INSERT или UPDATE в качестве целевого столбца.

Создание вычисляемого столбца при создании новой таблицы в Microsoft SQL Server

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

Добавление вычисляемого столбца в существующую таблицу в Microsoft SQL Server

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

Добавление вычисляемых столбцов доступно также и в графической среде SQL Server Management Studio. Для этого Вам нужно просто в обозревателе объектов найти нужную таблицу и щелкнуть правой кнопкой мыши по контейнеру «Столбцы» и выбрать «Создать столбец», т.е. все как обычно.

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

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

Удаление вычисляемого столбца из таблицы

В случае, если Вам больше не нужен вычисляемый столбец, то его можно удалить, делается это также, как и удаление обычного столбца.

На этом мой рассказ про вычисляемые столбцы закончен, всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL, удачи!

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

Источник

Указание вычисляемых столбцов в таблице

Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлен признак PERSISTED. В выражении вычисляемого столбца для вычисления значения могут использоваться данные из других столбцов. Вы можете задать выражение для вычисляемого столбца в SQL Server с использованием SQL Server Management Studio или Transact-SQL.

В этом разделе

Перед началом работы

Задание вычисляемого столбца с использованием:

Перед началом

Ограничения

безопасность

Permissions

Требуется разрешение ALTER на таблицу.

Использование среды SQL Server Management Studio

Добавление нового вычисляемого столбца

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

В меню Файл выберите команду Сохранить имя_таблицы.

Добавление определения вычисляемого столбца к существующему столбцу

Использование Transact-SQL

Добавление вычисляемого столбца при создании таблицы

Добавление нового вычисляемого столбца в существующую таблицу

В следующем примере в таблицу, созданную в предыдущем примере, будет добавлен новый столбец.

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

Замена существующего столбца на вычисляемый столбец

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

Дополнительные сведения см. в разделе ALTER TABLE (Transact-SQL).

Источник

BestProg

Пример создания представления (View) в базе данных типа MS SQL Server средствами MS Visual Studio. Создание вычисляемого поля

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

Содержание

Поиск на других ресурсах:

Условие задачи

Выполнение

1. Загрузить Microsoft Visual Studio

2. Подключить базу данных Education.mdf к перечню баз данных утилиты Server Explorer

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

В результате, откроется окно мастера, в котором нужно настроить базу данных.

Рис. 1. Способы добавления/подключения базы данных

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

Подробное описание того, как осуществляется подключение базы данных типа Microsoft SQL Server в Microsoft Visual Studio, приведено в теме:

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

Рис. 2. Утилита Server Explorer с подключенной базой данных Education.mdf

3. Добавление нового представления ( View ). Команда «Add New View»

Система Microsoft Visual Studio позволяет создавать представление данных (views). Сами представления только отображают данные таблиц в удобном для чтения формате. Они не являются непосредственно данными таблиц ( Tables ). В нашем случае нужно создать представление в соответствии с условием задачи.

Рис. 3. Команда «Add New View…»

Рис. 4. Выбор таблиц, которые будут использоваться в запросе

Для нашего случая нужно выбрать обе таблицы.

В результате, окно Microsoft Visual Studio примет вид, как показано на рисунке 5.

Рис. 5. Окно Microsoft Visual Studio после создания представления

Для нашего случая выбор полей изображен на рисунке 6.

Рис. 6. Выбор полей для представления

4. Добавление вычисляемого поля Average

Рис. 7. Добавление вычисляемого поля Average

5. Запуск SQL-запроса для отображения представления

6. Сохранение представления

После вызова команды

откроется окно «Choose Name» (рисунок 9), в котором нужно задать имя новосозданного представления. В нашем случае можно оставить имя ( View1 ), которое предлагается системой по умолчанию.

Рис. 9. Окно задания имени для представления

7. Отображение представления в окне Server Explorer

Рис. 10. Окно Server Explorer с отображением представления View1

8. Просмотр результатов работы представления. Команда «Show Results»

С помощью команды «Show Results» из контекстного меню, можно просмотреть результат выполнения представления View1 (рисунок 11).

Рис. 11. Вызов команды «Show Results»

Рис. 12. Результат выполнения представления

Источник

Учебник SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функции TRIM

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Резюме

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

Источник