SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Применение коррелированных подзапросов в T-SQL

ПУБЛИКАЦИИ  

По материалам статьи Gregory A. Larsen: Using a Correlated Subquery in a T-SQL Statement
Перевод Алексея Сафонова

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

[В начало]

Что такое - Коррелированный Подзапрос?

Коррелированный подзапрос - это оператор SELECT, вложенный в другой оператор T-SQL, и ссылающийся на один или несколько столбцов внешнего запроса. Поэтому можно сказать, что коррелированный подзапрос зависит от внешнего запроса. Это - главное различие между коррелированным и простым подзапросом. Простой подзапрос не ссылается на внешний запрос, он может быть выполнен независимо от него. После того как коррелированный подзапрос будет связан с внешним запросом, он будет возвращать сообщение о синтаксической ошибке, если попытается вызвать самого себя.
Коррелированный подзапрос может быть исполнен несколько раз в процессе обработки оператора T-SQL, содержащего такой подзапрос. Он будет исполняться для каждой строки, отобранной во внешнем запросе. На каждом из этих шагов поля внешнего запроса, на которые ссылается коррелированный подзапрос, будут сравниваться с результатами выборки коррелированного подзапроса. Результат выполнения коррелированного подзапроса определит, попадет ли строка внешнего запроса в результирующую выборку.

[В начало]

Применение коррелированного подзапроса в условии WHERE

Предположим, что Вы хотите получить список всех OrderID, для которых покупатели приобрели не больше 10% от среднего объёма продаж каждого из товаров. Подобный анализ покажет тех покупателей, с которыми нужно связаться, чтобы выяснить причину столь низкого интереса к приобретённому товару. Для этих целей можно использовать коррелированный подзапрос, который будет помещён в предложении WHERE. Вот запрос, который вернет интересующий нас список товаров:

select distinct OrderId from Northwind.dbo.[Order Details] OD where Quantity <= (select avg(Quantity) * .1 from Northwind.dbo.[Order Details] where OD.ProductID = ProductID)

В приведённом выше запросе коррелированный подзапрос располагается в круглых скобках. Как Вы могли заметить, этот коррелированный подзапрос содержит ссылку на "OD.ProductID". Эта ссылка участвует в сравнении "ProductID" внешнего запроса с "ProductID" внутреннего запроса. Движок SQL Server будет исполнять внутренний запрос (коррелированный подзапрос) для каждой записи "[Order Details]". Этот внутренний запрос подсчитает среднее количество (Quantity) для записей каждого товара (ProductID), отобранных во внешнем запросе. Средствами коррелированного подзапроса будет определено, возвращает ли внутренний запрос значение, удовлетворяющее условию WHERE. Если да, то строка, возвращенная внешним запросом, будет включена в итоговую выборку всего запроса T-SQL.

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

select CompanyName, ContactName, Address, City, Country, PostalCode from Northwind.dbo.Customers OuterC where CustomerID in ( select top 2 InnerC.CustomerId from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O on OD.OrderId = O.OrderID join Northwind.dbo.Customers InnerC on O.CustomerID = InnerC.CustomerId Where Region = OuterC.Region group by Region, InnerC.CustomerId order by sum(UnitPrice * Quantity * (1-Discount)) desc ) order by Region

Как видно из примера, внутренний запрос - коррелированный, потому что он ссылается на "OuterC", псевдоним использующейся во внешнем запросе таблицы "Northwind.DBO.Customer". Внутренний запрос использует значения поля "Region" для определения двух лучших покупателей в каждом регионе, ассоциированных со строкой внешнего запроса. В результирующую выборку попадут записи о тех двух "CustomerID" из внешнего запроса, которые попадут в число лучших покупателей.

[В начало]

Коррелированный подзапрос в разделе HAVING

Допустим, в целях увеличения своего дохода ваша организация решила в течение года проводить акцию по стимуляции потребительского спроса. Для этого покупатели извещаются о том, что если каждый сделанный ими в течение года заказ будет превышать сумму 750$, то в конце года на каждый их заказ вы сделаете скидку в 75$. Ниже приведен пример вычисления размера скидки. В этом примере для определения подпадающих под условие получения скидки покупателей, используется коррелированный подзапрос, помещённый в раздел HAVING.

select C.CustomerID, Count(*)*75 Rebate from Northwind.DBO.Customers C join Northwind.DBO.Orders O on c.CustomerID = O.CustomerID where Datepart(yy,OrderDate) = '1998' group by C.CustomerId having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount)) from Northwind.DBO.Orders O join Northwind.DBO.[Order Details] OD on O.OrderID = OD.OrderID where CustomerID = C.CustomerId and Datepart(yy,OrderDate) = '1998' group by O.OrderId )

Как Вы можете заметить, имеющийся в разделе HAVING коррелированный запрос используется для того, чтобы вычислить сумму заказа для каждого заказа клиента. Из внешнего запроса выбираются "CustomerID" и "Datepart (yy, OrderDate)" - год, когда был сделан заказ - которые нужны для того, что бы отобрать те заказы клиента, которые были сделаны в 1998 году. Для отобранных таким образом записей вычисляется сумма покупки по каждому заказу, для чего суммируются все записи "[Order Details]" по такой формуле: sum(UnitPrice * Quantity * (1-Discount)). Если каждая сделанная в 1998 покупка клиента превышает в сумме 750$, тогда во внешнем запросе вычисляется размер скидки по формуле: "Count (*) * 75".
Процессор запросов SQL Server выполнит помещённый в разделе HAVING внутренний коррелированный подзапрос для всех отобранных внешним запросом покупателей, которые делали заказы в 1998 году.

[В начало]

Применение коррелированного подзапроса в оператора Update

Коррелированный подзапрос также может использоваться и в операторе Update:

create table A(A int, S int) create table B(A int, B int) set nocount on insert into A(A) values(1) insert into A(A) values(2) insert into A(A) values(3) insert into B values(1,1) insert into B values(2,1) insert into B values(2,1) insert into B values(3,1) insert into B values(3,1) insert into B values(3,1) update A set S = (select sum(B) from B where A.A = A group by A) select * from A drop table A,B

В результате мы получим:

A S ----------- ----------- 1 1 2 2 3 3

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

[В начало]

Заключение

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

[В начало]

Перевод: Алексея Сафонова  2005г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013