Как добавить вычисляемый столбец в таблицу sql
Перейти к содержимому

Как добавить вычисляемый столбец в таблицу sql

  • автор:

Как добавить столбец в таблицу sql

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

-- в таблице "games" -- добавить столбец с именем "price" и типом "int" ALTER TABLE games ADD COLUMN price int; 

07 сентября 2023

Здравствуй те. Спасибо за информацию. У меня один вопрос. ALTER TABLE tb_site ADD COLUMN sites_wm varchar(12); Строка sites_wm varchar(12) добавится в самый конец, можно ли изменить её местонахождение и скажем расположить пятой с верху или в этом запросе этого сделать нельзя!? Тогда подскажите как перемещать строки, если их скажем штук 15 и мне нужно сделать сортировку что то поднять что то опустить. Да я могу качнуть Таблицу и отредактировать это отдельно. Но может это возможно сделать прямо в базе данных .

Как добавить вычисляемый столбец в таблицу sql

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

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

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

Выдать для каждого офиса список городов, регионов и сумм, на которые был перевыполнен/недовыполнен план по продажам:

SELECT CITY, REGION, (SALES — TARGET) FROM OFFICES

CITY REGION (SALES-TARGET)

Denver Western -$113,958.00

New York Eastern $117,637.00

Chicago Eastern -$64,958.00

Atlanta Eastern $17,911.00

Los Angeles Western $110,915.00

При выполнении этого запроса для каждой строки таблицы OFFICES создаётся одна строка результатов. Значения первых двух столбцов результатов запроса извлекаются непосредственно из таблицы OFFICES. Третий столбец для каждой строки результатов запроса вычисляется на основании значений столбцов текущей строки таблицы OFFICES.

1.3.12. Выборка всех столбцов (инструкция select *)

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

С учётом этого в SQL разрешается использовать вместо списка возвращаемых столбцов символ звёздочки (*), который означает, что требуется извлечь все столбцы:

Показать все данные, содержащиеся в таблице OFFICES.

SELECT * FROM OFFICES

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

В стандарте ANSI/ISO сказано, что в предложении SELECT можно использовать либо символ выборки всех столбцов, либо список возвращаемых столбцов, но не оба одновременно. Однако во многих СУБД символ звёздочки считается просто одним из возвращаемых столбцов. Таким образом, запрос

SELECT *, (SALES — TARGET) FROM OFFICES

допустим в большинстве коммерческих диалектов SQL (в частности, в DB2, Oracle и SQL Server), однако не разрешён стандартом ANSI/ISO.

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

Если программа ожидает, что запрос SELECT * FROM OFFICES возвратит таблицу, содержащую шесть столбцов определённых типов, она почти наверняка перестанет работать после изменения порядка столбцов и добавления нового столбца.

Этих сложностей можно избежать, если в программах запрашивать требуемые столбцы по именам.

Например, следующий запрос возвращает те же результаты, что и запрос SELECT * FROM OFFICES. Он не восприимчив к изменениям структуры базы данных, пока в таблице OFFICES существуют столбцы с указанными именами:

Generated Columns

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

2. Syntax

Синтаксически сгенерированные столбцы обозначаются с помощью ограничения столбца «GENERATED ALWAYS» . Например:

Приведенное выше утверждение имеет три обычных столбца «a» (PRIMARY KEY),»b» и «c» и два порожденных столбца «d» и «e».

Ключевые слова «GENERATED ALWAYS» в начале ограничения и «VIRTUAL» или «STORED» в конце являются необязательными.Только ключевое слово «AS» и выражение в круглых скобках являются обязательными.Если ключевое слово «VIRTUAL» или «STORED» в конце опущено,то по умолчанию используется VIRTUAL.Следовательно,приведенный выше пример можно упростить до простого:

2.1.ВИРТУАЛЬНЫЕ и ХРАНЯЩИЕСЯ колонки

Генерируемые столбцы могут быть ВИРТУАЛЬНЫМИ или ХРАНЯЩИМИСЯ.Значение ВИРТУАЛЬНОГО столбца вычисляется при чтении,в то время как значение ХРАНЯЩЕГО столбца вычисляется при записи строки.Колонки STORED занимают место в файле базы данных,в то время как колонки VIRTUAL используют больше циклов процессора при чтении.

С точки зрения SQL столбцы STORED и VIRTUAL почти одинаковы. Запросы к любому классу сгенерированного столбца дают одинаковые результаты. Единственное функциональное отличие состоит в том, что нельзя добавлять новые столбцы STORED с помощью команды ALTER TABLE ADD COLUMN . Только ВИРТУАЛЬНЫЕ столбцы могут быть добавлены с помощью ALTER TABLE.

2.2. Capabilities

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

Сгенерированные столбцы могут иметь ограничения NOT NULL,CHECK и UNIQUE,а также ограничения внешнего ключа,как и обычные столбцы.

Сгенерированные столбцы могут участвовать в индексах,как и обычные столбцы.

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

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

2.3. Limitations

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

Созданные столбцы нельзя использовать как часть ПЕРВИЧНОГО КЛЮЧА . (В будущих версиях SQLite это ограничение для столбцов STORED может быть ослаблено.)

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

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

Выражение сгенерированного столбца не может напрямую ссылаться на ROWID , хотя оно может ссылаться на столбец INTEGER PRIMARY KEY , что часто является одним и тем же.

Каждая таблица должна иметь хотя бы один негенерируемый столбец.

Невозможно ИЗМЕНИТЬ ТАБЛИЦУ ДОБАВИТЬ СТОЛБЦУ в СОХРАНЕННЫЙ столбец. Однако можно добавить виртуальный столбец.

Тип данных и последовательность сортировки сгенерированного столбца определяются только типом данных и предложением COLLATE в определении столбца. Тип данных и последовательность сортировки выражения GENERATED ALWAYS AS не влияют на тип данных и последовательность сортировки самого столбца.

3. Compatibility

Поддержка сгенерированных колонок была добавлена в SQLite версии 3.31.0 (2020-01-22).Если более ранняя версия SQLite попытается прочитать файл базы данных,содержащий в своей схеме сгенерированный столбец,то эта более ранняя версия воспримет синтаксис сгенерированного столбца как ошибку и сообщит,что схема базы данных повреждена.

Поясним:SQLite версии 3.31.0 может читать и записывать любую базу данных,созданную любой предыдущей версией SQLite,начиная с SQLite 3.0.0 (2004-06-18).Более ранние версии SQLite,до 3.31.0,могут читать и записывать базы данных,созданные SQLite версии 3.31.0 и более поздней,при условии,что схема базы данных не содержит функций,таких как генерируемые колонки,которые не понимаются более ранней версией.Проблемы возникают только в том случае,если вы создаете новую базу данных,содержащую генерируемые колонки,используя SQLite версии 3.31.0 или более поздней,а затем пытаетесь прочитать или записать файл этой базы данных,используя более раннюю версию SQLite,которая не понимает генерируемые колонки.

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

Для чего нужно использовать расчетные поля? Как правило, информация в БД представлена ??в разрезе отдельных фрагментов, поскольку так легче структурировать данные и оперировать ими. Однако нам часто будет нужно использовать не отдельные части данных, а уже соединенную и обработанную информацию. Например, часто необходимо сочетать имя и фамилию клиентов, сочетать элементы адресов, которые находятся в разных столбцах таблицы, обрабатывать текст и отдельные слова, буквы и символы, суммировать общую стоимость покупки, отображать статистику по информации, находящейся в БД. Данные обычно хранятся отдельными «кусками», что требует их дополнительной обработки на стороне клиентского приложения. Однако есть возможность получать уже обработанную информацию с помощью СУБД. Именно в этом случае помогают расчетные поля. Они автоматически создаются при выполнении запроса и имеют вид и свойства обычных столбцов, которые уже имеются в таблице. Единственное отличие заключается в том, что физически расчетных полей нет, поэтому они не занимают дополнительного места в БД, а временно существуют в «оперативной памяти» СУБД. Преимуществом выполнения операций на стороне СУБД является скорость обработки данных.

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

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

SELECT DISTINCT Product, Amount/Quantity FROM Sumproduct

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

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

В предыдущем примере мы рассчитывали среднюю стоимость покупки каждого товара и отобразили значение в расчетном столбце. Однако в дальнейшем, нам неудобно обращаться к этому полю, так как его название является неинформативным для нас (СУБД дала название полю — Expr1001 ). Однако мы можем назвать поле самостоятельно, заранее указав его название в запросе, то есть дать псевдоним. Давайте перепишем предыдущий пример и укажем псевдонима для расчетного поля:

SELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct

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

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

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

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

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

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

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

Мне действительно нужен вычисляемый столбец в таблице с простой суммой.

Пожалуйста, смотрите ниже:

Я попытался создать вычисляемый столбец, добавив следующее

Я получил сообщение об ошибке:

Сообщение 1046, уровень 15, состояние 1, строка 4
Подзапросы не допускаются в этом контексте. Разрешены только скалярные выражения.

Посоветуйте пожалуйста что можно с этим сделать.

Часть 2

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

Просто выберите оценку, которая возвращает значения, которые я хочу добавить в таблицу Phone_list

Табличные определения

6 ответов

Вы можете заключить ваш запрос в функцию следующим образом (она должна вернуть одно значение):

И используйте эту функцию вместо поля calc — что-то вроде этого:

НОТА

что это будет убийство производительности для таких запросов:

Вы должны изменить свою функцию таким образом, чтобы принять НЕ varchar ценность, но NVARCHAR(255) — тот же тип, что и в столбце диспетчера. Попытайся.

ИМХО Это неверный путь. Вы должны использовать триггер в таблицах CONTACT и обновлять WeeklyBudget в этих триггерах.

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

У вас может быть выражение, которое возвращает одно значение — или вы можете заключить код в хранимую функцию — или вы можете создать представление (с помощью JOIN или подзапроса), которое объединяет эту логику в то, что вы можете использовать

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

  • Я предположил, что это Key3 который должен быть передан в функцию и добавлен оператор where для возврата еженедельного бюджета для заданного Key3 значение.
  • Функция будет выполняться для каждой записи в наборе результатов, где вы добавляете вычисляемый столбец.

скрипт

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

Похожие публикации:

  1. Как в sql отсортировать по убыванию
  2. Как вместо null поставить 0 sql
  3. Как выбрать максимальное значение sql
  4. Как вывести максимальное значение в sql

Производительность вычисляемых столбцов в SQL Server

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

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

Часто несохраняемые (non-persistent) вычисляемые столбцы создают большую нагрузку на процессор, что приводит к замедлению запросов и зависанию приложений. К счастью, SQL Server предоставляет несколько способов улучшения производительности вычисляемых столбцов. Можно создавать сохраняемые (persisted) вычисляемые столбцы, индексировать их или делать и то и другое.

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

  • Таблица Orders1 — несохраняемый вычисляемый столбец.
  • Таблица Orders2 — сохраняемый вычисляемый столбец.
  • Таблица Orders3 — несохраняемый вычисляемый столбец с индексом.
  • Таблица Orders4 — сохраняемый вычисляемый столбец с индексом.

Несохраняемый вычисляемый столбец

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

Однако следует отметить, что пользовательские функции могут создать свои проблемы с производительностью. Если таблица содержит вычисляемый столбец с функцией, то Query Engine не будет использовать параллелизм (только если вы не используете SQL Server 2019). Даже в ситуации, если вычисляемый столбец не указан в запросе. Для большого набора данных это может сильно влиять на производительность. Функции также могут замедлять выполнение UPDATE и влиять на то, как оптимизатор вычисляет стоимость запроса к вычисляемому столбцу. Это не значит, что вы никогда не должны использовать функции в вычисляемом столбце, но определенно к этому следует относиться с осторожностью.

Независимо от того, используете вы функции или нет, создание несохраняемого вычисляемого столбца довольно просто. Следующая инструкция CREATE TABLE определяет таблицу Orders1 , которая включает в себя вычисляемый столбец Cost .

USE WideWorldImporters; GO DROP TABLE IF EXISTS Orders1; GO CREATE TABLE Orders1( LineID int IDENTITY PRIMARY KEY, ItemID int NOT NULL, Quantity int NOT NULL, Price decimal(18, 2) NOT NULL, Profit decimal(18, 2) NOT NULL, Cost AS (Quantity * Price - Profit)); INSERT INTO Orders1 (ItemID, Quantity, Price, Profit) SELECT StockItemID, Quantity, UnitPrice, LineProfit FROM Sales.InvoiceLines WHERE UnitPrice IS NOT NULL ORDER BY InvoiceLineID;

Чтобы определить вычисляемый столбец, укажите его имя с последующим ключевым словом AS и выражением. В нашем примере мы умножаем Quantity на Price и вычитаем Profit . После создания таблицы заполняем ее с помощью INSERT, используя данные из таблицы Sales.InvoiceLines базы данных WideWorldImporters. Далее выполняем SELECT.

SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;

Этот запрос должен вернуть 22 973 строки или все строки, которые есть у вас в базе данных WideWorldImporters. План выполнения этого запроса показан на рисунке 1.

Рисунок 1. План выполнения запроса к таблице Orders1

Первое, что следует отметить — это сканирование кластерного индекса (Clustered Index Scan), что не является эффективным способом получения данных. Но это не единственная проблема. Давайте посмотрим на количество логических чтений (Actual Logical Reads) в свойствах Clustered Index Scan (см. рисунок 2).

Рисунок 2. Логические чтения для запроса к таблице Orders1

Количество логических чтений (в данном случае 1108) — это количество страниц, которые прочитаны из кэша данных. Цель состоит в том, чтобы попытаться максимально уменьшить это число. Поэтому полезно его запомнить и сравнить с другими вариантами.

Количество логических чтений можно также получить, запустив инструкцию SET STATISTICS IO ON перед выполнением SELECT. Для просмотра процессорного и общего времени — SET STATISTICS TIME ON или посмотреть свойства оператора SELECT в плане выполнения запроса.

Еще один момент, на который стоит обратить внимание — в плане выполнения присутствуют два оператора Compute Scalar. Первый (тот, что справа) — это вычисление значения вычисляемого столбца для каждой возвращаемой строки. Поскольку значения столбцов вычисляются на лету, вы не можете избежать этого шага с несохраняемыми вычисляемыми столбцами, если только не создадите индекс на этом столбце.

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

Сохраняемый вычисляемый столбец

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

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

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

DROP TABLE IF EXISTS Orders2; GO CREATE TABLE Orders2( LineID int IDENTITY PRIMARY KEY, ItemID int NOT NULL, Quantity int NOT NULL, Price decimal(18, 2) NOT NULL, Profit decimal(18, 2) NOT NULL, Cost AS (Quantity * Price - Profit) PERSISTED); INSERT INTO Orders2 (ItemID, Quantity, Price, Profit) SELECT StockItemID, Quantity, UnitPrice, LineProfit FROM Sales.InvoiceLines WHERE UnitPrice IS NOT NULL ORDER BY InvoiceLineID;

Таблица Orders2 практически идентична таблице Orders1 , за исключением того, что столбец Cost содержит ключевое слово PERSISTED . SQL Server автоматически заполняет этот столбец при добавлении и изменении строк. Конечно, это означает, что таблица Orders2 будет занимать больше места, чем таблица Orders1 . Это можно проверить с помощью хранимой процедуры sp_spaceused .

sp_spaceused 'Orders1'; GO sp_spaceused 'Orders2'; GO

На рисунке 3 показан результат выполнения этой хранимой процедуры. Объем данных в таблице Orders1 составляет 8 824 КБ, а в таблице Orders2 — 12 936 КБ. На 4 112 КБ больше, что необходимо для хранения вычисленных значений.

Рисунок 3. Сравнение размера таблиц Orders1 и Orders2

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

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

SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;

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

Рисунок 4. План выполнения запроса к таблице Orders2

Здесь также все начинается с Clustered Index Scan. Но на этот раз, есть только один оператор Compute Scalar, потому что вычисляемые столбцы больше не нужно вычислять во время выполнения. В общем случае чем меньше шагов, тем лучше. Хотя это и далеко не всегда так.

Второй запрос генерирует 1593 логических чтения, что на 485 больше по сравнению с 1108 чтений для первой таблицы. Несмотря на это, он выполняется быстрее, чем первый. Хотя и только примерно на 100 мс, а иногда и намного меньше. Процессорное время также уменьшилось, но тоже не на много. Скорее всего, разница была бы гораздо больше на больших объемах и более сложных вычислениях.

Индекс на несохраняемом вычисляемом столбце

Другой метод, который обычно используется для улучшения производительности вычисляемого столбца, — это индексирование. Для возможности создания индекса столбец должен быть детерминированным и точным, что означает, что выражение не может использовать типы float и real (если столбец несохраняемый). Существуют также ограничения и для других типов данных, а также на параметры SET. Полный перечень ограничений см. в документации SQL Server Indexes on Computed Columns (Индексы на вычисляемых столбцов).

Проверить подходит ли несохраняемый вычисляемый столбец для индексирования можно через его свойства. Для просмотра свойств воспользуемся функцией COLUMNPROPERTY . Нам важны свойства IsDeterministic, IsIndexable и IsPrecise.

DECLARE @id int = OBJECT_ID('dbo.Orders1') SELECT COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic', COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable', COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';

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

Рисунок 5. Проверка возможности создания индекса

После проверки вы можете создать некластерный индекс. Вместо изменения таблицы Orders1 я создал третью таблицу ( Orders3 ) и включил индекс в определение таблицы.

DROP TABLE IF EXISTS Orders3; GO CREATE TABLE Orders3( LineID int IDENTITY PRIMARY KEY, ItemID int NOT NULL, Quantity int NOT NULL, Price decimal(18, 2) NOT NULL, Profit decimal(18, 2) NOT NULL, Cost AS (Quantity * Price - Profit), INDEX ix_cost3 NONCLUSTERED (Cost, ItemID)); INSERT INTO Orders3 (ItemID, Quantity, Price, Profit) SELECT StockItemID, Quantity, UnitPrice, LineProfit FROM Sales.InvoiceLines WHERE UnitPrice IS NOT NULL ORDER BY InvoiceLineID;

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

SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;

На рисунке 6 показан план выполнения этого запроса, который теперь использует некластерный индекс ix_cost3 (Index Seek), а не выполняет сканирование кластерного индекса.

Рисунок 6. План выполнения запроса к таблице Orders3

Если вы посмотрите свойства оператора Index Seek, то обнаружите, что запрос теперь выполняет только 92 логических чтения, а в свойствах оператора SELECT увидите, что процессорное и общее время стало меньше. Разница несущественная, но, опять же, здесь небольшой набор данных.

Следует также отметить, что в плане выполнения присутствует только один оператор Compute Scalar, а не два, как было в первом запросе. Поскольку вычисляемый столбец проиндексирован, то значения уже вычислены. Это устраняет необходимость вычисления значений во время выполнения, даже если столбец не был определен как сохраняемый.

Индекс на сохраняемом столбце

Вы также можете создать индекс для сохраняемого вычисляемого столбца. Хотя это приведет к хранению дополнительных данных и данных индекса, но в некоторых случаях может быть полезно. Например, вы можете создать индекс для сохраняемого вычисляемого столбца, даже если он использует типы данных float или real. Этот подход также может быть полезен при работе с функциями CLR, и когда нельзя проверить, являются ли функции детерминированными.

Следующая инструкция CREATE TABLE создает таблицу Orders4 . Определение таблицы включает в себя как сохраняемый столбец Cost , так и некластерный покрывающий индекс ix_cost4.

DROP TABLE IF EXISTS Orders4; GO CREATE TABLE Orders4( LineID int IDENTITY PRIMARY KEY, ItemID int NOT NULL, Quantity int NOT NULL, Price decimal(18, 2) NOT NULL, Profit decimal(18, 2) NOT NULL, Cost AS (Quantity * Price - Profit) PERSISTED, INDEX ix_cost4 NONCLUSTERED (Cost, ItemID)); INSERT INTO Orders4 (ItemID, Quantity, Price, Profit) SELECT StockItemID, Quantity, UnitPrice, LineProfit FROM Sales.InvoiceLines WHERE UnitPrice IS NOT NULL ORDER BY InvoiceLineID;

После того как таблица и индекс созданы и заполнены, выполним SELECT.

SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;

На рисунке 7 показан план выполнения. Как и в предыдущем примере, запрос начинается с поиска по некластерному индексу (Index Seek).

Рисунок 7. План выполнения запроса к таблице Orders4

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

sp_spaceused 'Orders1'; GO sp_spaceused 'Orders2'; GO sp_spaceused 'Orders3'; GO sp_spaceused 'Orders4'; GO

Результаты показаны на рисунке 8. Как и ожидалось, в сохраняемых вычисляемых столбцах больше объем данных, а в индексированных — больше объем индексов.

Рисунок 8. Сравнение использования пространства для всех четырех таблиц

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

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

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

Читать ещё

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

Часто после создания сводной таблицы возникает необходимость расширить анализ и включить в него больше данных/вычислений.

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

Что такое вычисляемое поле в сводной таблице?

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

Допустим, у нас есть набор данных о розничных продажах, и вы создаете сводную таблицу, как показано ниже:

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

А теперь, допустим, вы хотите узнать, какова была маржа прибыли этих розничных торговцев (где маржа прибыли — это «Прибыль», деленная на «Продажи»).

Можно сделать это по-разному:

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

  1. Вернуться к изначальной таблице и добавить эти данные. Таким образом, вы можете вставить новый столбец в исходные данные и рассчитать в нем маржу прибыли.
    • Можно сделать так, но вам придется сделать все это вручную. Вам может понадобиться добавить еще один столбец для расчета средней продажи на единицу (Sales/Quantity).
    • Существенный минус такого метода — расширение таблицы, когда данных очень много это может быть неудобно.
  2. Вы можете добавить вычисления вне сводной таблицы, а в ней уже отобразить результат и делать расчеты дальше. Но в этом способе существует риск того, что если вы измените исходные данные таблица не сможет обновится и выдаст ошибку.
  3. А еще есть вариант создать вычисляемое поле в сводной таблице. На мой взгляд, это самый эффективный способ делать расчеты. Вычисляемое поле — это как виртуальный столбец, вы видите только результат, а расчеты производятся “внутри”. Этот метод имеет много преимуществ:
    • Он не требует от вас работы с формулами или обновления исходных данных.
    • Он масштабируем, поскольку автоматически учитывает все новые данные, которые вы будете добавлять в сводную таблицу.
    • В нем легко менять данные. Например, вам нужно изменить расчет, вы можете легко сделать это из самой сводной таблицы.

Как добавить вычисляемое поле

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

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

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

Как это сделать:

  • Выберите любую ячейку в сводной таблице;
  • Перейдите в раздел «Анализ» -> «Поля, элементы и наборы»;

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

  • Из выпадающего списка выберите «Вычисляемое поле»;

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

  • В окне «Вставка вычисляемого поля»:
    • Введите название поля
    • И пропишите формулу для него. Вы можете выбирать из имен полей, перечисленных слева. В данном случае формула будет ’= Прибыль/Продажи’. Вы можете ввести имена полей вручную, либо выбрать поле и нажать «Добавить поле».

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

  • Нажмите кнопку «Добавить» и подтвердите.

Как только вы создадите вычисляемое поле, оно появится в списке полей сводной таблицы.

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

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

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

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

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

В приведенном выше примере я использовал простую формулу (Прибыль/Продажи) для вычисляемого поля. Однако вы также можете использовать некоторые более сложные формулы.

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

  • При создании вычисляемого поля нельзя использовать ссылки или диапазоны элементов. Это исключает многие формулы, такие как VLOOKUP, INDEX, OFFSET и так далее. Однако вы можете использовать формулы, которые могут работать без ссылок (например, SUM, IF, COUNT и так далее…).
  • В формуле можно использовать константу. Например, если вы хотите узнать прогнозируемый объем продаж, где прогнозируется его рост на 10%, вы можете использовать формулу =Sales*1.1 (где 1.1 — константа).
  • В формуле, составляющей вычисляемое поле, соблюдается порядок математики, при проведении расчетов(первое действие будет в скобках, потом умножение/деление и так далее).

Теперь давайте рассмотрим пример использования более сложных формул.

Предположим, нам нужно рассчитать прогнозируемое значение продаж.

Для прогнозируемого значения вам нужно использовать 5%-ный рост продаж для крупных розничных магазинов (у которых продажи свыше 3 миллионов) и 10%-ный рост продаж для мелких и средних розничных магазинов (продажи менее 3 миллионов).

Вот как это сделать:

  • Выберите любую ячейку в сводной таблице;
  • Перейдите в раздел «Анализ» -> «Поля, элементы и наборы»;

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

  • Из выпадающего списка выберите Вычисляемое поле;

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

  • В диалоговом окне «Вставка вычисляемого поля»:
    • Введите название.
    • И наберите такую формулу: =ЕСЛИ(Sales > 3000000,Sales *1.05,Sales *1.1);

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

  • Нажмите кнопку «Добавить» и подтвердите.

Это добавит новый столбик с прогнозируемым значением продаж.

Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.

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

Проблема c вычисляемым полем

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

Однако она не идеальна и с ней бывают проблемы.

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

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

Обратите внимание, что промежуточные и общие итоги неверны.

Хотя, по нашей задумке, они должны расчитывать индивидуальные прогнозные значения продаж для каждого магазина, на самом деле они следуют той же формуле вычисляемого поля, которое мы создали.

Так, для South Total значение должно быть 22 824 000, но в South Total стоит цифра 22 287 000. Это произошло потому, что для получения результата используется формула 21,225,800*1.05.

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

К сожалению, исправить это невозможно.

Лучшим решением этой проблемы будет удаление промежуточных итогов из таблицы.

Как изменить или удалить вычисляемое поле?

На стадии, когда мы уже создали вычисляемое поле, вы можете изменять формулу или вообще удалить ее, таким образом:

  • Выберите любую ячейку в таблице Pivot Table;
  • Перейдите в раздел «Анализ» -> «Поля, элементы и наборы»;
  • В раскрывающемся списке выберите Вычисляемое поле;
  • В поле Имя нажмите на выпадающую стрелку (маленькая стрелка вниз);
  • Из списка выберите вычисляемое поле, которое вы хотите удалить или изменить;

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

  • Если вы хотите изменить формулу, сначала поменяйте её, а потом нажмите кнопку «Изменить» или нажмите «Удалить», если вы хотите удалить ее.

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

Как просмотреть все формулы использованные в вычисляемом поле?

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

Excel позволяет быстро открыть список всех формул, используемых в таблице.

Как это сделать:

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

  • Выберите любую ячейку в сводной таблице;
  • Перейдите в раздел «Анализ» -> «Поля, элементы и наборы»;
  • Нажмите на кнопку «Вывести формулы».

Как только вы нажимаете на «Вывести формулы», Excel автоматически создает новый рабочий лист, в котором содержатся сведения обо всех вычисляемых полях/элементах, которые вы создали в этой сводной таблице.

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

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *