SQLShack (日本語)
この記事では、ここで説明します。 SQLROW_NUMBER関数。これは、SQLエッセンシャルシリーズの続きです。このガイドでは、ウィンドウ関数とは何かを説明し、SQLROW_NUMBER関数の背後にある概念を理解するためのサンプル例を示します。
はじめに
最もSQL Serverで一般的に使用される関数は、SQLROW_NUMBER関数です。 SQL ROW_NUMBER関数は、SQL Server2005以降のバージョンで使用できます。
ROW_NUMBERは、結果グリッドに一意の増分番号を追加します。行番号が適用される順序は、ORDERBY式によって決定されます。ほとんどの場合、1つ以上の列がORDER BY式で指定されますが、より複雑な式やサブクエリを使用することもできます。したがって、それは絶えず増加する整数値を作成し、常に1から始まり、後続の行は次に高い値を取得します。
PARTITIONBY句と一緒に使用することもできます。ただし、パーティションの制限または境界を超えると、カウンターがリセットされ、1から開始されます。したがって、パーティションの値は1、2、3などになり、2番目のパーティションは再び1、2、3…からカウンターを開始します。などなど。
基本：
- SQL ROW_NUMBER関数は、一連の一時値の非永続的な生成であり、クエリが実行されると動的に計算されます。
- SQLROW_NUMBER関数を使用したSQLクエリによって返される行が各実行でまったく同じ順序になるという保証はありません。
- ROW_NUMBER関数とRANK関数は類似しています。 ROW_NUMBERの出力は、1から始まり、1ずつ増加する一連の値ですが、RANK関数では、値も1ずつ増加しますが、値は同点で繰り返されます。
- Oracleの使用経験がある場合は、ROWNUMの方が使い慣れています。疑似列です。それは1から始まり、テーブルの終わりまで1ずつ増加していきます。
- SQL ROW_NUMBER関数は本質的に動的であり、PARTITIONBY句を使用して値をリセットできます。
- クエリのORDERBY句とOVERのORDERBY句を使用します。句は互いに関係がありません。
構文
|
1
2
|
ROW_NUMBER（）
OVER（] order_by_clause col1、col2 ..）
|
ROW_NUMBER
ROW_NUMBERの後にOVER関数次に、括弧内にORDERBY句を使用します。結果セットにある種の順序を課すには、ORDERBY句を使用する必要があります。
OVER
OVER句は、ウィンドウまたはウィンドウが設定する行のセットを定義します。関数は動作するので、理解することが非常に重要です。 OVER句の可能なコンポーネントは、ORDERBYとPARTITIONBYです。
OVER句のORDERBY式は、関数が機能するために行を特定の方法で並べる必要がある場合にサポートされます。
|
1
|
PARTITION BY value_expression1
|
PARTITION BY
PartitionBy句はオプションです。値を指定すると、FROM句によって生成された結果セットが、SQLROW_NUMBER関数が適用されるパーティションに分割されます。 PARTITION句で指定された値は、結果セットの境界を定義します。 PARTITION BY句が指定されていない場合、OVER句は結果セットのすべての行を単一のデータセットとして操作します。この句は、1つ以上の列、より複雑な式、またはサブクエリで構成されている場合があります。
order_by_clause
Orderby句は必須の句です。これは、指定されたパーティションの行への一時値の順序と関連付けを決定します。 ORDER BY句はOVER句の式であり、関数に対して特定の方法で行を並べる必要がある方法を決定します。
デモ
このセクションでは、 SQLROW_NUMBER関数を見てみましょう。デモ全体で、AdventureWorks2016データベースを使用しました。
SQLクエリでROW_NUMBERを使用する方法
次の例では、OVER句の使用を示します。
SalesOrderID、OrderDate、SalesOrderNumber、SubTotal、TotalDue、RowNumなどの列を投影して、すべての顧客のリストを取得しましょう。 Row_Number関数は、CustomerID列の順序で適用されます。一時値は、CustomerIDの順序に基づいて割り当てられた1から始まり、テーブルの最後の行まで継続されます。クエリでORDERBY句を指定していないため、CustomerIDの順序は保証されません。
|
1
2
3
4
5
6
7
8
9
10
11
|
AdventureWorks2016を使用します。
GO
SELECT ROW_NUMBER（）OVER（
ORDER BY CustomerID）AS RowNum、
CustomerID、
SalesOrderID、
OrderDate、
SalesOrderNumber、
SubTotal、
TotalDue
FROM Sales.SalesOrderHeader;
|
Orderby句の使用方法
次の例では、クエリでORDERBY句を使用しています。 SalesOrderID列に適用されるクエリのORDERBY句。出力の行はまだ順序付けられて返されていることがわかります。 Row_Numberは引き続きCustomerIDに適用されます。出力は、クエリのORDERBYとOVER句のORDERBYが出力から独立していることを示しています。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
USE AdventureWorks2016;
GO
SELECT ROW_NUMBER（）OVER（
ORDER BY CustomerID）AS RowNum、
CustomerID、
SalesOrderID、
OrderDate、
SalesOrderNumber、
SubTotal、
TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;
|
OVER句で複数の列を使用する方法
次の例では、ORDERBY句にcustomerIDとOrderDateがリストされていることがわかります。これにより、結果セット全体に割り当てられた番号のシーケンスとともに、最新の注文の詳細を含む顧客の詳細が提供されます。
|
1
2
3
4
5
6
7
8
9
10
|
USE AdventureWorks2016;
GO
SELECT ROW_NUMBER（）OVER（ORDER BY CustomerID、OrderDate DESC）AS RowNum、
CustomerID、
SalesOrderID、
OrderDate、
SalesOrderNumber、
SubTotal、
TotalDue
FROM Sales.SalesOrderHeader
|
PARTITIONでSQLROW_NUMBER関数を使用する方法
次の例では、CustomerIDフィールドとOrderDateフィールドでPARTITIONBY句を使用しています。出力では、顧客11019が2014年6月の月に3つの注文を持っていることがわかります。この場合、パーティションは複数の列で実行されます。
パーティションはOrderDateとCustomerIDの組み合わせです。 Row_Numberは、OrderDateとCustomerIDの一意の組み合わせごとに最初からやり直します。このようにして、同じ日に複数の注文をした顧客を簡単に見つけることができます。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
USE AdventureWorks2016;
GO
SELECT ROW_NUMBER（）OVER（PARTITION BY CustomerID、
DATEADD（MONTH、DATEDIFF（MONTH、0、OrderDate）、0）
ORDER BY SubTotal DESC）AS MonthlyOrders、
CustomerID、
SalesOrderID、
OrderDate、
SalesOrderNumber、
SubTotal、
TotalDue
FROM Sales.SalesOrderHeader;
|
CTEとROW_NUMBERを使用して行のサブセットを返す方法
次の例では、SalesOrderHeaderを分析して、各顧客からの上位5つの最大注文を表示します。月。 Month関数を使用して、orderDate列を操作して月の部分をフェッチします。このようにして、特定の月（OrderDate）に対応する売上と顧客（CustomerID）が分割されます。
各顧客の毎月最大の5つの注文を一覧表示するには、CTEを使用します。パーティションデータにウィンドウが作成され、値が割り当てられてから、CTEが呼び出されて最大の注文がフェッチされます。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
WITH cte
AS（SELECT ROW_NUMBER OVER（PARTITION BY customerID、MONTH（OrderDate）ORDER BY SubTotal DESC、TotalDue DESC）AS ROW_NUM、
CustomerID、
MONTH（OrderDate）Month、
SubTotal、
TotalDue、
OrderDate
FROM Sales.SalesOrderHeader
）
SELECT *
FROM cte
WHERE ROW_NUM < = 5
|
概要
これまで、SQLROW_NUMBER関数について詳しく説明してきました。単純なものから複雑なものまで、いくつかの例について説明しました。また、SQL ROW_NUMBER関数をCTE（共通テーブル式）で使用する方法についても説明しました。ほとんどの場合、すべてのウィンドウ関数で常にover句が表示されます。
over句は、各行に表示されるウィンドウを定義します。 over句内には、byパーティションがあり、これもすべてのウィンドウ関数でサポートされており、その後にorderby句が続きます。今のところこれですべてです…記事を楽しんでいただければ幸いです。
- 作成者
- 最近の投稿
私の専門は、高可用性ソリューションとクロスプラットフォームDB移行を実装する&の設計にあります。現在取り組んでいるテクノロジは、SQL Server、PowerShell、Oracle、MongoDBです。
PrashanthJayaramによる投稿をすべて表示
- SQLでのデータベース監査の概要-2021年1月28日
- AzureSQLデータベースとオンプレミスSQLServer間でAzureDataSyncをセットアップする方法-2021年1月20日
- PowerShellを使用してAzureSQLデータベースのインポート/エクスポート操作を実行する方法-1月14日、2021