順位付け関数と行セット関数を使用する
順位付けおよび行セット関数は、1 つの値を返すものではないので、スカラー関数ではありません。 これらの関数は、行のセットを入力として受け取り、行のセットを出力として返します。
順位付け関数
順位付け関数を使用すると、ユーザー定義の行セットに対して計算を実行できます。 このような関数として、順位付け、オフセット、集計、分布の各関数があります。
この例では、RANK 関数を使用して、ListPrice に基づいた順位付けを計算しています。最も高い価格の順位が 1 です。
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
クエリの結果は次のようになります。
ProductID
名前
ListPrice
RankByPrice
749
Road-150 Red, 62
3578.27
1
750
Road-150 Red, 44
3578.27
1
751
Road-150 Red, 48
3578.27
1
771
Mountain-100 Silver, 38
3399.99
4
772
Mountain-100 Silver, 42
3399.99
4
775
Mountain-100 Black, 38
3374.99
6
...
...
...
...
OVER
OVER 句を使用して、データ内のパーティションまたはグループを定義できます。 たとえば、次のクエリでは、前の例を拡張して、各カテゴリの製品について価格ベースの順位を計算しています。
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
このクエリの結果は次のようになります。
カテゴリ
製品
ListPrice
RankByPrice
ビブショーツ
Men's Bib-Shorts, S
89.99
1
ビブショーツ
Men's Bib-Shorts, M
89.99
1
バイク ラック
Hitch Rack - 4-Bike
120
1
バイク スタンド
All-Purpose Bike Stand
159
1
ボトルとケージ
Mountain Bottle Cage
9.99
1
ボトルとケージ
Road Bottle Cage
8.99
2
ボトルとケージ
Water Bottle - 30 oz.
4.99
3
ボトム ブラケット
HL Bottom Bracket
121.49
1
ボトム ブラケット
ML Bottom Bracket
101.24
2
ボトム ブラケット
LL Bottom Bracket
53.99
3
...
...
...
...
Note
複数の行が同じ順位値を持ち、一部の値がスキップされていることに注目します。 これは、RANK のみを使用しているためです。 要件によっては、同じ順位値での結びつきを避けたい場合があります。 必要に応じて、他の関数 (DENSE_RANK、NTILE、ROW_NUMBER) を使用して順位値を制御することができます。 これらの関数の詳細については、Transact-SQL のリファレンス ドキュメントを参照してください。
行セット関数
行セット関数は、FROM 句でデータ ソースとして使用できる仮想テーブルを返す関数です。 これらの関数は、行セット関数自体に固有のパラメーターを受け取ります。 OPENDATASOURCE、OPENQUERY、OPENROWSET、OPENXML、OPENJSON があります。
OPENDATASOURCE、OPENQUERY、OPENROWSET の各関数を使用すると、リモート データベース サーバーにクエリを渡すことができます。 その後、リモート サーバーから一連の結果行が返されます。 たとえば、次のクエリでは、OPENROWSET を使用して、SalesDB という名前の SQL Server インスタンスからクエリの結果を取得します。
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
リモート サーバーを使用するには、クエリを実行している SQL Server インスタンスでいくつかの高度なオプションを有効にする必要があります。
OPENXML および OPENJSON 関数を使用すると、XML または JSON 形式で構造化データに対してクエリを実行し、表形式の行セットに値を抽出できます。
行セット関数の詳細な説明は、このモジュールの範囲外です。 詳細については、Transact-SQL のリファレンス ドキュメントを参照してください。