Как объявить переменную в sql

Присвоение значений переменных для выделенных пулов SQL в Azure Synapse Analytics

В этой статье вы найдете важные советы по присвоению значений переменных T-SQL в выделенном пуле SQL.

Задание переменных с помощью DECLARE

Переменные в выделенном пуле SQL задаются с помощью операторов DECLARE или SET . Инициализация переменных с помощью DECLARE — один из наиболее гибких способов задать значение переменной в пуле SQL.

DECLARE @v int = 0 ; 

С помощью DECLARE можно задать одновременно несколько переменных. SELECT или UPDATE нельзя использовать для выполнения следующих задач:

DECLARE @v INT = (SELECT TOP 1 c_customer_sk FROM Customer where c_last_name = 'Smith') , @v1 INT = (SELECT TOP 1 c_customer_sk FROM Customer where c_last_name = 'Jones') ; 

Нельзя инициализировать и использовать переменную в одном и том же операторе DECLARE. Чтобы проиллюстрировать это, ниже приведен недопустимый пример, так как @p1 инициализируется и используется в одной и той же инструкции DECLARE. Следующий пример показывает возникающую ошибку:

DECLARE @p1 int = 0 , @p2 int = (SELECT COUNT (*) FROM sys.types where is_user_defined = @p1 ) ; 

Задание значений с помощью SET

SET — это очень распространенный метод задания одной переменной.

Ниже перечислены допустимые способы задания значения с помощью инструкции SET:

SET @v = (Select max(database_id) from sys.databases); SET @v = 1; SET @v = @v+1; SET @v +=1; 

С помощью SET можно одновременно задать только одну переменную. Тем не менее допускаются составные операторы.

Ограничения

Нельзя использовать UPDATE, чтобы присвоить значение переменной.

Дальнейшие действия

Дополнительные советы по разработке приведены в обзоре разработки.

Переменные (Transact-SQL)

Локальная переменная Transact-SQL представляет собой объект, содержащий одно значение определенного типа. Переменные обычно используются в пакетах и скриптах:

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

• Имена некоторых системных функций Transact-SQL начинаются с двух символов @ (@@). Хотя в предыдущих версиях сервера SQL Server @@функции называются глобальными переменными, @@функции не являются переменными и используются иначе. @@functions являются системными функциями, а их синтаксис использует правила для функций.
• В представлении нельзя использовать переменные.
• Откат транзакции не влияет на изменения переменных.

Следующий скрипт создает небольшую тестовую таблицу из 26 строк. Переменная используется в скрипте в качестве:

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

-- Create the table. CREATE TABLE TestTable (cola INT, colb CHAR(3)); GO SET NOCOUNT ON; GO -- Declare the variable to be used. DECLARE @MyCounter INT; -- Initialize the variable. SET @MyCounter = 0; -- Test the variable to see if the loop is finished. WHILE (@MyCounter < 26) BEGIN; -- Insert a row into the table. INSERT INTO TestTable VALUES -- Use the variable to provide the integer value -- for cola. Also use it to generate a unique letter -- for each row. Use the ASCII function to get the -- integer value of 'a'. Add @MyCounter. Use CHAR to -- convert the sum back to the character @MyCounter -- characters after 'a'. (@MyCounter, CHAR( ( @MyCounter + ASCII('a') ) ) ); -- Increment the variable to count this iteration -- of the loop. SET @MyCounter = @MyCounter + 1; END; GO SET NOCOUNT OFF; GO -- View the data. SELECT cola, colb FROM TestTable; GO DROP TABLE TestTable; GO 

Объявление переменных в языке Transact-SQL

Инструкция DECLARE инициализирует переменную Transact-SQL следующим образом:

• Назначение имени. Имя должно иметь один @ в качестве первого символа.
• Назначение длины и типа данных, определяемого системой или пользователем. Для числовых переменных задаются также точность и масштаб. Для переменных типа XML может быть дополнительно задана коллекция схем.
• Присваивает созданной переменной значение NULL.

Например, следующая инструкция DECLARE создает локальную переменную @mycounter типа int.

DECLARE @MyCounter INT; 

Инструкция DECLARE позволяет объявить несколько переменных одинакового или разного типов через запятую.

Например, следующая инструкция DECLARE создает три локальные переменные с именем @LastName, @FirstName и @StateProvince, присваивая каждой из них значение NULL:

DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2); 

Областью видимости переменной называют диапазон инструкций Transact-SQL, которые могут к ней обращаться. Областью видимости переменной являются все инструкции между ее объявлением и концом пакета или хранимой процедуры, где она объявлена. Например, следующий скрипт содержит синтаксическую ошибку, поскольку переменная объявлена в одном пакете, а используется в другом:

USE AdventureWorks2022; GO DECLARE @MyVariable INT; SET @MyVariable = 1; -- Terminate the batch by using the GO keyword. GO -- @MyVariable has gone out of scope and no longer exists. -- This SELECT statement generates a syntax error because it is -- no longer legal to reference @MyVariable. SELECT BusinessEntityID, NationalIDNumber, JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = @MyVariable; 

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

DECLARE @MyVariable INT; SET @MyVariable = 1; EXECUTE sp_executesql N'SELECT @MyVariable'; -- this produces an error 

Присвоение значения переменной в языке Transact-SQL

При объявлении переменной присваивается значение NULL. Чтобы изменить значение переменной, применяется инструкция SET. Этот способ присвоения значений переменным является предпочтительным. Кроме того, переменной можно присвоить значение, указав ее в списке выбора инструкции SELECT.

Чтобы присвоить значение переменной при помощи инструкции SET, необходимо указать ее имя и присваиваемое значение. Этот способ присвоения значений переменным является предпочтительным. Например, следующий пакет объявляет две переменные, присваивает им значения и использует их в предложении WHERE инструкции SELECT :

USE AdventureWorks2022; GO -- Declare two variables. DECLARE @FirstNameVariable NVARCHAR(50), @PostalCodeVariable NVARCHAR(15); -- Set their values. SET @FirstNameVariable = N'Amy'; SET @PostalCodeVariable = N'BA5 3HX'; -- Use them in the WHERE clause of a SELECT statement. SELECT LastName, FirstName, JobTitle, City, StateProvinceName, CountryRegionName FROM HumanResources.vEmployee WHERE FirstName = @FirstNameVariable OR PostalCode = @PostalCodeVariable; GO 

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

USE AdventureWorks2022; GO DECLARE @EmpIDVariable INT; SELECT @EmpIDVariable = MAX(EmployeeID) FROM HumanResources.Employee; GO 

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

Если инструкция SELECT возвращает более одной строки и переменная ссылается на нескалярное выражение, ей присваивается значение, которое возвращается для выражения в последней строке результирующего набора. Например, в следующем пакете переменной @EmpIDVariable присваивается значение идентификатора BusinessEntityID последней возвращенной строки, равное 1:

USE AdventureWorks2022; GO DECLARE @EmpIDVariable INT; SELECT @EmpIDVariable = BusinessEntityID FROM HumanResources.Employee ORDER BY BusinessEntityID DESC; SELECT @EmpIDVariable; GO 

Переменные и управляющие конструкции

Переменная представляет именованный объект, который хранит некоторое значение. Для определения переменных применяется выражение DECLARE , после которого указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @ :

DECLARE @название_переменной тип_данных

Например, определим переменную name, которая будет иметь тип NVARCHAR:

DECLARE @name NVARCHAR(20)

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

DECLARE @name NVARCHAR(20), @age INT

С помощью выражения SET можно присвоить переменной некоторое значение:

DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18;

Так как @name предоставляет тип NVARCHAR, то есть строку, то этой переменной соответственно и присваивается строка. А переменной @age присваивается число, так как она представляет тип INT.

Выражение PRINT возвращает сообщение клиенту. Например:

PRINT 'Hello World'

И с его помощью мы можем вывести значение переменной:

DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18; PRINT 'Name: ' + @name; PRINT 'Age: ' + CONVERT(CHAR, @age);

При выполнении скрипта внизу SQL Server Management Studio отобразится значение переменных:

Также можно использовать для получения значения команду SELECT :

DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18; SELECT @name, @age;

MySQL — Использование переменных в запросе

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

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

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

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

Предупреждение. В статье подразумевается, что обработка выражений в предложении SELECT осуществляется слева направо, однако официального подтверждения такого порядка обработки в документации MySQL нет. Это необходимо иметь в виду при смене версии сервера. Для гарантии последовательности вычисления можно использовать фиктивный оператор CASE или IF.

Аналог рекурсии

Рассмотрим простой пример, который генерирует последовательность Фибоначчи (в последовательности Фибоначчи каждый член равен сумме двух предыдущих, а первые 2 равны единице):

SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, /*Фиктивная таблица, для вывода последовательности в 1 столбец*/ (SELECT 1 X UNION ALL SELECT 2)X;

Данный запрос генерирует 18 чисел Фибоначчи, не считая первых двух:

2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765

Разберём теперь как оно работает.

В строчках 5) 6) генерируется 9 записей. Тут ничего необычного.

В строчке 7) мы объявляем две переменные @I, @J и присваиваем им 1.

В строке 3) происходит следующее: сначала переменной @I присваивается сумма двух переменных. Затем то же самое присваиваем переменной @J, причем с учетом того, что значение @I уже поменялось.

Другими словами, вычисления в SELECT выполняются слева направо – см. также замечание в начале статьи.

Причем изменение переменных осуществляется в каждой из наших 9 записей, т.е. при обработке каждой новой строки в переменных @I и @J будут содержаться значения, вычисленные при обработке предыдущей строки.

Чтобы решить эту же задачу средствами других СУБД, нам пришлось бы писать рекурсивный запрос!

Примечание:
Переменные нужно объявлять в отдельном подзапросе (строка 7), если бы мы объявили переменную в предложении SELECT, она, скорее всего, вычислилась бы только 1 раз (хотя конкретное поведение будет зависеть от версии сервера). Тип переменной определяется значением, которым она инициализирована. Этот тип может динамически меняться. Если переменной присвоить NULL, её типом будет BLOB.

Порядок обработки строк в SELECT, как было сказано выше, зависит от пользовательской сортировки. Простой пример нумерации строк в заданном порядке:

 SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val; 

Val Num 10 1 20 2 30 3 50 4

Аналоги аналитических функций

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

Для примеров создадим таблицу TestTable:

CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL ); 

где
group_id – идентификатор группы (аналог окна аналитической функции);
order_id – уникальное поле, по которому будет производиться сортировка;
value – некоторое числовое значение.

Заполним нашу таблицу тестовыми данными:

INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T;

Примеры замены некоторых аналитических функций.

1) ROW_NUMBER() OVER(ORDER BY order_id)

SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id;

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10

2) ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY order_id)

SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1

3) SUM(value) OVER(PARTITION BY group_id ORDER BY order_id)

SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;

group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1

4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)

SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T;

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

Для LEAD всё то же самое, только нужно сменить сортировку на ORDER BY group_id, order_id DESC

Для функций COUNT, MIN, MAX всё несколько сложнее, поскольку, пока мы не проанализируем все строчки в группе(окне), мы не сможем узнать значение функции. MS SQL, например, для этих целей «спулит» окно (временно помещает строки окна в скрытую буферную таблицу для повторного к ним обращения), в MySQL такой возможности нет. Но мы можем для каждого окна вычислить значение функции в последней строке при заданной сортировке (т.е. после анализа всего окна), а затем, отсортировав строки в окне в обратном порядке, проставить вычисленное значение по всему окну.

Таким образом, нам понадобится две сортировки. Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по полям group_id ASC, order_id DESC, затем по полям group_id ASC, order_id ASC.

5) COUNT(*) OVER(PARTITION BY group_id)

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

SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC /*первая сортировка*/ )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id /*вторая сортировка*/ )T;

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

Функции MAX и MIN вычисляются по аналогии. Приведу только пример для MAX:

6) MAX(value) OVER(PARTITION BY group_id)

SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T;

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)

Интересная вещь, которая отсутствует в MS SQL Server, но её можно вычислить с подзапросом, взяв MAX от RANK. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:

SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T;

group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

Производительность

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

1) Классический способ с самомоединением

SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;

Что на 10000 записей в таблице TestTable выдаёт:

Duration / Fetch
16.084 sec / 0.016 sec

2) С использованием переменных:

SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;

Duration / Fetch
0.016 sec / 0.015 sec

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

Рассмотрим более подробно на примере такой задачи:

Вывести по 2 первые строки из таблицы TestTable для каждого значения group_id, отсортированных по order_id.

Вот как эта задача решалась бы в СУБД с поддержкой аналитических функций:

SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum 

SELECT DISTINCT group_id FROM TestTable;

SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;