Вычисляемые поля sql: Указание вычисляемых столбцов в таблице — SQL Server

Содержание

Создание вычисляемых полей

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

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

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

Пример

SQL:
SELECT service, price, price*1.5

FROM tbl_service

Представленный запрос возвращает список услуг, с указанием их
текущей стоимости и стоимости, увеличенной на 50%.

Результат:

service	price	*price1.5**
Equipment rental	200,00	300.00
Calling card service	100,00	150. 00
Wireless service	150,00	225.00
Multiple lines	320,00	480.00
Voice mail	50,00	75.00
Paging service	50,00	75.00
Internet	250,00	375.00
Caller ID	20,00	30. 00
Call waiting	20,00	30.00
Call forwarding	20,00	30.00
3-way calling	100,00	150.00
Electronic billing	50,00	75.00

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

SQL:

SELECT lastname, name, TRUNCATE((TO_DAYS(CURRENT_DAY())
– TO_DAYS(d_birth)+364)/365,0)

FROM tbl_clients

При построении выражения для вычисления возраста были использованы встроенные
функции SQL.

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

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

Для
упрощения чтения и понимания результатов запроса можно переопределить
заголовки
столбцов. Чтобы получить необходимые имена заголовков, просто введите
<column> <alias> или <column>
as <alias> в списке выбора вместо обычных
имен столбцов.

Пример

Запрос из предыдущего примера можно переписать следующим
образом:

SQL:

SELECT lastname, name,

TRUNCATE((TO_DAYS(CURRENT_DAY())
– TO_DAYS(d_birth)+364)/365,0) AS age

FROM tbl_clients

« Previous | Next »

Создание вычисляемых полей — Язык реляционных баз данных SQL (Информатика и программирование)

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

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

1) Необходимо отразить поле, содержащее имя компании с ее адресом, но эта информация расположена в разных столбцах таблицы.

2) Город, штат и ZIP-код хранятся в отдельных столюцах, но для программы печати почтовых наклеек необходима эта информиация в одном, корректно сформированном поле.

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

4) Необходимы общая сумма, среднее значение или результаты других расчетов, основанных на данных, имеющихся в таблице.

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

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

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

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

Конкатенация значений – это присоединение их друг к другу для получения одного длинного значения.

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

SELECT vend_name + ‘(‘ + vend_country + ‘)’ формирование столбца

FROM Vendors из таблицы

ORDER BY vend_name; упорядочить по столбцу

—————————

Expr1000

Bear Emporium (USA )

Beras R Us (USA )

Doll House Inc. (USA )

Fun and Games (England )

Furball Inc. (USA )

Jouets et ours (France )

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

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

SELECT CONCAT(vend_name, ‘(‘, vend_country, ‘)’ формирование столбца

FROM Vendors из таблицы

ORDER BY vend_name; упорядочить по столбцу

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

SELECT RTRIM(vend_name) + ‘(‘ + RTRIM(vend_country) + ‘)’

FROM Vendors

ORDER BY vend_name;

—————————

Expr1000

Bear Emporium (USA)

Beras R Us(USA)

Doll House Inc.(USA)

Fun and Games(England)

Furball Inc.(USA)

Jouets et ours(France)

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

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

Псевдоним – это альтернативное имя для поля или значения. Псевдоним применяется для именования вычисляемых полей. Псевдонимы присваиваются при помощи ключевого слова AS.

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)

К созданному имени столбца vend_title может обращаться любое клиентское приложение.

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

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

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

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

В таблице Orders хранятся все полученные заказы, а в таблице OrderItems содержатся наименования продуктов для каждого заказа. Требуется осуществить выборку всех продуктов в заказе номер 20008.

SELECT prod_id, quantity, item_price выбор столбцов

FROM OrderItems из таблицы

WHERE order_num = 20008; условие выборки

—————————

prod_id quantity item_price

RGAN01 5 4,99р.

BR03 5 11,99р.

BNBG01 10 3,49р.

BNBG02 10 3,49р.

BNBG03 10 3,49р.

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

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price

FROM OrderItems

WHERE order_num = 20008;

—————————

prod_id quantity item_price expanded_price

RGAN01 5 4,99р. 24.95

BR03 5 11,99р. 59.95

BNBG01 10 3,49р. 34.90

BNBG02 10 3,49р. 34.90

Рекомендуем посмотреть лекцию «13 Коммуникационная сеть».

BNBG03 10 3,49р. 34.90

Столбец expanded_price является вычисляемым полем. В ЫЙД поддерживаются основные математические операции:

+ сложение

— вычитание

* умножение

/ деление

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

Введение

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

Например, таблица Employee_Salary содержит столбцы Emp_Id, Basic, HR, Da, Medical, Pf,+ Esi и Total_Salary, а столбец Total_Salary вычисляется таким образом, формула для Total_Salary :

Total_Salary=Basic+HR+Da+Medical+Pf+Esi

Что такое вычисляемые столбцы в SQL Server?

Вычисляемые столбцы — это виртуальные столбцы, которые физически не хранятся в таблице, если столбец не помечен как СОХРАНЕННЫЙ. Значения для вычисляемых столбцов пересчитываются каждый раз, когда на них ссылаются в запросе. Значения вычисляемых столбцов обновляются при изменении любых столбцов, которые являются частью их вычисления. Пометив вычисляемый столбец как PERSISTED, мы можем создать индекс для вычисляемого столбца. Вычисляемые столбцы, используемые в качестве ограничений CHECK, FOREIGN KEY или NOT NULL, должны быть помечены как PERSISTED.

Как создать вычисляемый столбец в SQL Server?

Мы можем создать вычисляемый столбец, создав запрос или используя обозреватель объектов; здесь мы читаем об обоих методах.

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

На изображении выше мы создаем таблицу Employee_Salary; последний столбец этой a=table является типом вычислений. Теперь проверяем оформление таблицы для столбца Total_Salary,

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

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

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

Теперь мы вставляем некоторые значения в таблицу Employee_Salary и позже изучаем данные таблицы.

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

Обновить содержимое таблицы

Теперь мы обновим значения основного столбца и столбца HR в таблице Employee_Salary и проверим изменения значений столбца Total_Salary.

Запрос

 /*Выбрать значения из таблицы*/

ВЫБЕРИТЕ * FROM dbo.Employee_Salaryes

/*Обновить запись*/

ОБНОВЛЕНИЕ dbo.Employee_Salary

SET Employee_Salary.Medical=1000,Employee_Salary.HR=1500

ГДЕ

Сотрудник_Зарплата.Базовый=17000

/*Выбрать значения из таблицы*/

SELECT * FROM dbo. Employee_Salaryes

Output

На изображении выше показано, что значение столбцов Total_Salary для Emp_Id 2 и 5 было изменено. Таким образом, ясно, что если изменить значения любого столбца, а этот столбец является частью вычисляемого столбца, значения вычисляемого столбца также изменятся.

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

Синтаксис

ALTER TABLE Table_Name ADD Column_Name AS (Write_Formula)

Пример 90 005

 ALTER TABLE dbo.Employee_Salary
ADD Total_Salary AS ([Basic]+([HR]*2)+([Da]*1.5)+([Medical]*1.4)+[Pf]+[Esi])

Чтобы изменить любой существующий столбец

Синтаксис

 Изменить таблицу Имя_таблицы Удалить столбец Имя_столбца
ALTER TABLE Table_Name ADD Column_Name AS (Write_Formula)

Пример

 ALTER TABLE dbo.Employee_Salary DROP COLUMN dbo. Employee_Salary.Total_Salary
ALTER TABLE dbo.Employee_Salary
ADD Total_Salary AS ([Basic]+([HR]*2)+([Da]*1.5)+([Medical]*1.4)+[Pf]+[Esi])

Использовать функцию User_Define в вычисляемом столбце

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

Функция

 СОЗДАТЬ ФУНКЦИЮ Calculate_Salary(@Employee_Typeint,@basic int,@Hrint,@Da int, @Medical [int],@Pf int, @Esi [int])

ВОЗВРАТ [с плавающей запятой]
С ПРИВЯЗКОЙ СХЕМ

КАК
НАЧИНАТЬ
DECLARE @Total_Salary [float];
ЕСЛИ @Employee_Type=1

НАЧИНАТЬ
SET @Total_Salary= @basic+@Da*1.2+@Esi*1.4+@Hr*2+@Medical*1.8+@Pf*2.5
КОНЕЦ
ИНАЧЕ ЕСЛИ @Employee_Type=2

НАЧИНАТЬ
SET @Total_Salary= @basic+@Da*1.3+@Esi*1.5+@Hr*2+@Medical*1.8+@Pf*2.5
КОНЕЦ
ЕСЛИ @Employee_Type=3
НАЧИНАТЬ

SET @Total_Salary= @basic+@Da*1.8+@Esi*1.6+@Hr*3+@Medical*1.8+@Pf*2.5
КОНЕЦ
ВОЗВРАТ @Total_Salary;
КОНЕЦ

Теперь мы используем эту функцию в вычисляемом столбце.

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

Вычисляемый столбец не может быть целью оператора INSERT или UPDATE.
Мы не можем напрямую ссылаться на столбцы из других таблиц для выражения вычисляемого столбца.
Механизм базы данных сам определяет допустимость нулевых значений вычисляемого значения столбца. Результат большинства выражений считается допускающим значение NULL, даже если присутствуют только столбцы, не допускающие значения NULL, поскольку возможные потери или переполнения также приведут к нулевым результатам. Чтобы преодолеть эту проблему, функция COLUMN PROPERTY с Свойство AllowsNull .
а. Подзапрос нельзя использовать в качестве выражения для создания вычисляемого столбца.
б. Если мы используем разные типы данных в нашем выражении, то оператор с более низким приоритетом попытается преобразовать его в тип данных с более высоким приоритетом. Если неявное преобразование невозможно, будет сгенерирована ошибка.

Заключение

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

Дополнительные статьи о SQL Server.

Перенос таблиц SQL Server 2016 в облако Azure
Простой генератор POCO N SQL в C#

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

В этом разделе описываются назначение и использование типа столбца Calculation , который можно добавлять в таблицы с помощью диспетчера хранилища данных. Ниже объясняется, что делают вычисления SQL, почему они используются, процесс создания вычисления SQL и приводятся два примера.

Объяснение

В прошлом столбцы, которые считались расширенными , мог выполнять только аналитик из отдела по работе с клиентами здесь, в Adobe Commerce Intelligence. Теперь вся власть находится в руках конечного пользователя, а расширенные столбцы могут быть созданы в виде столбцов SQL Calculation в новой архитектуре Commerce Intelligence.

Тип столбца Calculation , который теперь доступен в качестве опции в Data Warehouse Manager, представляет собой ту же табличную операцию, которая позволяет преобразовывать столбцы в таблице с помощью логики PostgreSQL. Документация по функциям и операторам, которые можно использовать в 9Тип столбца 0155 Calculation можно найти на веб-сайте PostgreSQL здесь.

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

Пример 1. Является ли последний заказ клиента?

В большинстве учетных записей есть столбец с названием Является ли последний заказ клиента? в своей таблице заказов для анализа частоты повторных покупок и оттока клиентов. Если ваша учетная запись находится на новой архитектуре, этот столбец построен с использованием Расчет столбца и можно увидеть на скриншоте ниже:

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

Строка за строкой значение PostgreSQL:

, когда A равно нулю или B равно нулю, а затем нулю: если какой-либо вход пуст, то вывод также должен быть пуст. Это сделано для предотвращения ошибок SQL

, когда A=B , затем Да : Если Общее количество заказов клиента равно Номеру заказа клиента для этой строки, то вернуть Да . Таким образом, если клиент разместил четыре заказа, строка для его четвертого заказа вернет Да для Является ли последний заказ клиента?

еще № : Если ни один из других операторов when не выполняется, вернуть Нет

end: Это завершает операторы If – Then

Возможные значения, которые может возвращать этот столбец ( NULL , Да , Нет ), содержат нечисловые символы, поэтому тип данных здесь — String.

Пример 2: Общая стоимость товара в заказе (количество * цена)

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

Чтобы обеспечить анализ доходов от продукта, в большинстве учетных записей есть столбец с названием Общая стоимость элемента заказа (количество * цена) в таблице Элементы заказа . Если ваша учетная запись использует новую архитектуру, этот столбец также создается с использованием столбца Calculation , что можно увидеть на снимке экрана ниже:

В схеме Commerce столбец Общая стоимость элемента заказа (количество * цена) использует входные данные заказанное количество и базовая цена , псевдонимы которых A и B соответственно.

Значения, возвращаемые этим новым столбцом, представлены в долларах и центах, поэтому правильный тип данных — Decimal(10,2) .

Механика

Новый столбец Расчет можно добавить в таблицу, перейдя к Управление данными > Хранилище данных , как показано ниже:

Отсюда вы можете создать столбец Расчет , выполнив следующие шаги:

Выберите таблицу, в которую вы хотите добавить столбец Calculation .
Находясь в нужной таблице, нажмите Создать новый столбец в правом верхнем углу экрана.
В раскрывающемся списке Выберите определение выберите Та же таблица .
Выберите Расчет в качестве уравнения определения столбца .
Введите имя столбца.
Выберите входных столбцов из таблицы, которые используются в логике для вашего нового столбца. Каждый добавляемый столбец получает буквенный псевдоним, поэтому первый столбец — A , второй — B и так далее.
В окне введите логику PostgreSQL для нового столбца, используя буквенные псевдонимы ваших входных данных. Расчет SQL должен быть ограничен определением одного столбца, включая всю логику между операторами SELECT и FROM запроса SQL. Ключевые слова SQL, использующие любую из входных букв, должны быть в нижнем регистре. Например, при использовании 9Оператор 0155 CASE должен быть написан строчными буквами — case . Система предполагает, что заглавная буква A относится к одному из входных данных.
Выберите подходящий тип данных.
- Целое число – Целое число
- Decimal(10,2) — десятичное число, состоящее из 10 цифр, из которых 2 находятся справа от запятой
- Строка — Любой тип текста или последовательность символов, в которых используются нецифры
- Datetime – формат гггг-мм-дд чч:мм:сс
Нажмите тестовый столбец .