logo

TechCodeview

SQL ServerのRANK関数

SQL Server の RANK 関数は、ランキング関数の一種です。この機能は、 出力のパーティション内の各行に番号を割り当てます。 。前の行のランクに 1 を加えたものとして各行にランクを割り当てます。 RANK 関数は、同じパーティション内で同一の 2 つの値を見つけると、それらに同じランク番号を割り当てます。また、ランキングの次の番号は、前のランクに重複した番号を加えたものとなります。したがって、この関数は必ずしも連続した順序で行のランキングを割り当てるわけではありません。

RANK 関数はウィンドウ関数のサブ部分でもあります。 この機能を使用する場合、次の点に注意してください。 :

  • これは常に OVER() 句と併用して機能します。
  • ORDER BY 句に基づいて各行にランクを割り当てます。
  • 各行に連続した順序でランクを割り当てます。
  • 新しいパーティションごとに 1 つずつ、常に行にランクが割り当てられます。

注: ランクは、クエリの実行時にパーティション内の行に一時的な値を割り当てます。

SQL Server は次のランク関数を提供します。 :

  • ランク()
  • ROW_NUMBER()
  • DENSE_RANK()
  • タイル()

それぞれのランク関数を詳しく学習してみましょう。まず、これらすべての機能をデモンストレーションするためのテーブルを作成します。次のステートメントは、という名前のテーブルを作成します。 rank_demo 3 つの列:

 CREATE TABLE rank_demo ( first_name VARCHAR(60), last_name VARCHAR(40), city VARCHAR(30) );

次に、以下のようにこのテーブルにいくつかの行を挿入します。

 INSERT INTO rank_demo (first_name, last_name, city) VALUES ('Luisa', 'Evans', 'Texas'), ('Paul', 'Ward', 'Alaska'), ('Peter', 'Bennett', 'California'), ('Carlos', 'Patterson', 'New York'), ('Rose', 'Huges', 'Florida'), ('Marielia', 'Simmons', 'Texas'), ('Antonio', 'Butler', 'New York'), ('Diego', 'Cox', 'California');

SELECT ステートメントを使用してテーブルを検証できます。以下の出力が表示されます。

SQL ServerのRANK関数

RANK()関数

この関数は、結果セット内の各行のランクを決定するために使用されます。次の構文は、SQL Server での RANK 関数の使用を示しています。

 SELECT column_name RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;

この構文では、

日付を文字列に変換する
  • の OVER句 関連するウィンドウ関数が適用される前に、結果の分割と順序付けを設定します。
  • の PARTITION BY 句 FROM 句によって生成された出力をパーティションに分割します。次に、関数が各パーティションに適用され、分割境界がパーティションをまたぐときに再初期化されます。この句を定義していない場合、関数はすべての行を単一のパーティションとして扱います。
  • の 注文方法 は、関数が適用される前に 1 つ以上の列名に基づいて行の順序を降順または昇順で決定する必須の句です。

例

SQL Server で RANK() 関数がどのように機能するかを見てみましょう。以下のステートメントは、rank 関数を使用して各行に番号を割り当てます。

 SELECT first_name, last_name, city, RANK () OVER (ORDER BY city) AS Rank_No FROM rank_demo;

私たちは使ったことがないので、 PARTITION BY 句 、関数は結果全体を単一のパーティションとして扱いました。ステートメントを実行すると、次の出力が表示されます。

SQL ServerのRANK関数

この出力では、一部の行が同じ値を持っているため、同じランクを取得していることがわかります。 都市コラム 。そして、ランキングの次の番号は、前のランクに重複する番号を加えたものになります。

次の発言は、 もう一つの例 ここで、partition by 句を使用して、 市 列を作成し、パーティション内の各行にランキングを割り当てます。出力の順序は、 ファーストネーム :

 SELECT first_name, last_name, city, RANK () OVER (PARTITION BY city ORDER BY first_name) AS Rank_No FROM rank_demo;

以下の出力が表示されます。

SQL ServerのRANK関数

ROW_NUMBER() 関数

この機能は次の目的で使用されます 各行の一意の連続番号を返します そのパーティション内で。行番号は 1 から始まり、パーティションの合計行数に達するまで 1 ずつ増加します。 RANK() 関数とは異なる、同様の値を持つ行の異なるランクを返します。

次の構文は、SQL Server での ROW_NUMBER() 関数の使用法を示しています。

 SELECT column_name ROW_NUMBER() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;

例

次のクエリを実行して、各行にシーケンス番号を割り当てます。

 SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city) AS my_rank FROM rank_demo;

に従ってテーブルのランキングが割り当てられます。 市 。ここでは、同じ都市値を持つ行に異なるランクが割り当てられていることがわかります。

SQL ServerのRANK関数

ソート順を変更すると、 昇順から降順まで ORDER BY 句を使用すると、この関数はそれに応じて RANK も変更します。以下のステートメントを参照してください。

 SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city DESC) AS my_rank FROM rank_demo;

結果は次のとおりです。

SQL ServerのRANK関数

DENSE_RANK() 関数

この関数は次のように割り当てます。 パーティション内の各行の一意のランク ギャップのない指定された列の値に従って。常に連続した順序でランキングを指定します。を取得したら 重複した値 、この関数は同じランクを割り当て、次のランクは次の連続番号になります。この特性は DENSE_RANK() 関数と RANK() 関数とは異なります。

以下の構文は、SQL Server での DENSE_RANK() 関数の使用を示しています。

Cの標準入力 
 SELECT column_name DENSE_RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;

例

次のクエリでは、DENSE_RANK() 関数を使用して各行にランク番号を割り当てます。

 SELECT first_name, last_name, city, DENSE_RANK() OVER(ORDER BY city) AS my_rank FROM rank_demo;

以下の出力が返され、重複する値が同じランクを持ち、次のランクが次の連続番号になることがわかります。

SQL ServerのRANK関数

これは、PARTITION BY 句を使用した DENSE_RANK() 関数の別の例です。この句は city 列に基づいて行を分割し、結果セットの順序は first_name に基づきます。

 SELECT first_name, last_name, city, DENSE_RANK() OVER(PARTITION BY city ORDER BY first_name) AS my_rank FROM rank_demo;

同じ名前は 2 つとしてないため、次の出力が得られます。この場合、出力は RANK() 関数と似ています。

SQL ServerのRANK関数

次のクエリを使用して名前を更新してみましょう。

 Update rank_demo set first_name = 'Diego' WHERE city = 'California'

ここで、同じクエリを再度実行します。このテーブルに同じ名前が付けられていることがわかります。 カリフォルニアシティ 。したがって、ランクも両方の名前で同じになります。

SQL ServerのRANK関数

NTILE(N) 関数

この機能は次の目的で使用されます 順序付けされたパーティションの行を事前定義された数に分散します (N) のほぼ等しいグループ。各行グループは、定義された条件に基づいてランクを取得し、1 つのグループから番号付けを開始します。を割り当てます。 バケット番号 属するグループを表すグループ内のすべての行に対して。

次の構文は、SQL Server での NTILE() 関数の使用を示しています。

 SELECT column_name NTILE(N) OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;

例

次のクエリでは、NTILE() 関数を使用して各行にランク番号を割り当てます。

 SELECT first_name, last_name, city, NTILE(3) OVER(ORDER BY city) AS my_rank FROM rank_demo;

指定されたテーブルには、 8つのレコード 。したがって、 ナタイル(3) 結果セットには 3 つのレコードのグループ 。ステートメントを実行すると、次の出力が表示されます。

SQL ServerのRANK関数

この記事では、SQL Server で使用されるすべての RANK 関数についてさまざまな例を示して説明します。また、これらの関数間の主な違いも示します。これらの関数は、開発者がデータを適切に探索および管理するために非常に役立ちます。