T-SQLステートメントで相関サブクエリを使用する

6月 2, 2021admin

先月の記事では、T-SQLステートメントでサブクエリを使用する内容と方法についてお話しました。今月は、相関するサブクエリについて説明することで、このテーマを拡張していきます。

相関サブクエリとは

相関サブクエリは、別の T-SQL ステートメント内に入れ子になった SELECT ステートメントで、外部クエリの 1 つ以上の列への参照を含んでいます。したがって、相関サブクエリは外部クエリに依存していると言うことができます。これが、相関サブクエリと単なるサブクエリとの主な違いです。単純なサブクエリは外部クエリに依存せず、外部クエリとは独立して実行することができ、結果セットを返します。

相関サブクエリは、相関サブクエリを含む T-SQL 文を処理する際に何度も実行されます。相関するサブクエリは、外部クエリによって選択された各候補行に対して 1 回実行されます。相関サブクエリで参照される外部クエリの列は、各実行前に候補行の値に置き換えられます。

WHERE 節での相関サブクエリの使用

与えられた製品の平均販売量の 10% 以上を顧客が購入しなかったすべての “OrderID” のレポートが必要だとします。この方法では、これらの注文を確認し、場合によっては顧客に連絡し、少量の注文に理由があるかどうかを判断するのに役立ちます。 WHERE句の相関するサブクエリを使用すると、このレポートを作成することができます。以下は、必要な “OrderID’s” のリストを生成する SELECT 文です。

select distinct OrderId from Northwind.dbo. OD where Quantity <l; (select avg(Quantity) * .1 from Northwind.dbo. where OD.ProductID = ProductID)

上記のコマンドの相関サブクエリは、上記の WHERE 句の大なり記号の次の括弧内に含まれています。ここで、この相関サブクエリには、「OD.ProductID」への参照が含まれていることがわかります。この参照は、外側のクエリの “ProductID” と内側のクエリの “ProductID” を比較します。このクエリが実行されると、SQL エンジンは各「”」レコードに対して内部クエリ (相関サブクエリ) を実行します。この内部クエリは、外部クエリで処理されている候補行の特定の「ProductID」に対する平均「数量」を計算します。この相関サブクエリは、内側クエリが WHERE 句の条件を満たす値を返すかどうかを判断します。

以下のコードは、WHERE 句で相関サブクエリを使用して、地域ごとに、注文に関連する金額に基づいて上位 2 名の顧客を表示する別の例です。

select CompanyName, ContactName, Address, City, Country, PostalCode from Northwind.dbo.Customers OuterCwhere CustomerID in ( select top 2 InnerC.CustomerId from Northwind.dbo. 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

ここで、内部クエリは、外部クエリの “Northwind.DBO.Customer” テーブルのテーブルエイリアスである “OuterC” を参照しているので、関連サブクエリであることがわかります。この内部クエリは “Region” 値を使用して、外部クエリから処理されている行に関連する地域の上位 2 名の顧客を計算します。

HAVING 節の関連サブクエリ

あなたの組織が、収益を増加させるために 1 年間のインセンティブプログラムを実行したいとするとします。したがって、彼らは顧客に、1 年間の各注文が 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. 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’ に配置されたものです。これらの関連するレコードの私は、各注文の合計注文金額を計算し、すべての” “レコードは、次の式を使用して合計：sum（単価*数量*（1 -割引））です。もし、ある顧客の1998年の各注文の合計金額が750ドル以上であれば、外側のクエリで「Count(*)*75」式を使ってRebate金額を計算します。

SQL Server のクエリエンジンは、外部クエリで識別された顧客レコードの HAVING 節で内部の相関サブクエリを実行するだけです。

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 Adrop table A,B

このクエリを私のマシンで実行したときの結果セットです。

A S ----------- ----------- 1 12 23 3

上記のクエリでは、更新対象の行と同じ値を列 A に持つ行についてテーブル A の列 B を合計で更新するのに相関サブクエリを使用しました。

結論

まとめます。サブクエリおよび相関サブクエリは、外部クエリとして知られる別のクエリの内部にコード化された SELECT クエリです。相関サブクエリとサブクエリは、完全なクエリによって返される結果セットの結果を決定するのに役立ちます。サブクエリは、外部クエリから独立して実行された場合、結果セットを返すので、外部クエリに依存しません。一方、相関サブクエリは、相関サブクエリから返される結果セットを決定するために、アウタークエリ内の1つ以上のカラムへの参照を使用するため、アウタークエリから独立して実行することはできません。サブクエリと相関サブクエリの違い、そしてそれらがどのようにT-SQLコードで使用できるかを理解していただけたでしょうか。