MySQL での Row_Number()関数の使用

1. MySQL での ROW_NUMBER() の使用と ORDER BY 句の使用
2. MySQL で PARTITION BY 句を使用したの ROW_NUMBER() の使用
3. MySQL で PARTITION BY および ORDER BY 句を使用したの ROW_NUMBER() の使用
4. MySQL でセッション変数を使用したの ROW_NUMBER() のレプリケーション
5. まとめ

このチュートリアルでは、MySQL で ROW_NUMBER() 関数を使用する方法を紹介します。これは、1 から始まるパーティション内の連続した番号を割り当てるランク付け方法です。パーティション内の 2つの行が同じ番号を持っていないことに注意することが重要です。

また、PARTITION BY と ORDER BY が MySQL の結果にどのように影響するかについても説明します。ROW_NUMBER() を使用するには、必須であるため、ORDER BY 句を使用する必要があります。ただし、PARTITION BY 句はオプションです。

PARTITION BY と ORDER BY の両方の句を使用すると、結果は不確定になります。ここでは、セッション変数を使用して ROW_NUMBER() 関数をエミュレートして、目的の結果を取得する方法を説明します。

ROW_NUMBER() は MySQL バージョン 8.0 より前では使用できなかったことに注意してください。MySQL バージョン 8.0 の新機能をご覧くださいこちら。

MySQL での ROW_NUMBER() の使用と ORDER BY 句の使用

ROW_NUMBER() 関数を ORDER BY 句とともに使用し、結果を観察するだけです。最初にテーブルを作成し、それにいくつかのデータを入力しましょう。

サンプルコード：

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `test_db`.`tb_student` (
 STUDENT_ID	INTEGER NOT NULL,
 FIRST_NAME	VARCHAR(30) NOT NULL,
 LAST_NAME	VARCHAR(30) NOT NULL,
 GENDER	VARCHAR(30) NOT NULL,
 CITY_NAME	VARCHAR(64) NOT NULL,
 EMAIL_ADDRESS	VARCHAR(64) NOT NULL,
 REGISTRATION_YEAR INTEGER NOT NULL,
 PRIMARY KEY	(STUDENT_ID)
);

このクエリは、MySQL データベースで確認できる tb_student という名前のテーブルを作成します。

INSERT クエリの次の構文を使用して、tb_student という名前のテーブルに 6つのレコードを挿入します。

# SQL Programming Using MySQL Version 8.27
INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(1,'Ayush','Kumar', 'Male', 'Washington', 'akuman@yahoo.com', 2010);

次に、次のクエリを使用して、テーブルからすべてのデータを選択して表示します。

# SQL Programming Using MySQL Version 8.27
SELECT * FROM test_db.tb_student

テーブルには次のデータが含まれます。自分の側を確認して比較することもできます。

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(ORDER BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

上記のクエリを実行すると、次の結果が得られます。

上記の出力を観察すると、登録年順に並べられたすべてのレコードが表示されていることがわかります（緑色のボックス内の列を参照）。また、row_number も予想どおりであり、1 から始まり、tb_student からすべてのデータを読み取っている間、テーブルの最後まで順番に増加し続けます。

MySQL で PARTITION BY 句を使用したの ROW_NUMBER() の使用

ROW_NUMBER() 関数は PARTITION BY 句とともにのみ使用し、結果を観察します。また、この出力を、ORDER BY 句で ROW_NUMBER() を使用して得られた結果と比較します。

サンプルコード：

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

これで、次の結果が得られます。

REGISTRATION_YEAR 列を見てください。5つのパーティション（2010、2011、2012、2013、および 2014）があります。partition 2010 のテーブルには 2つの行があり、行番号は正しく割り当てられています（上のスクリーンショットを再度参照してください）。partition 2011、2012、2013、2014 の行は 1つだけです。そのため、row_numb 列に 1 が表示されます。

PARTITION BY 句を使用している場合、REGISTRATION_YEAR という名前の列が昇順であるのはなぜですか？PARTITION BY 句は、これらのパーティション内のデータを並べ替えるためです。REGISTRATION_YEAR の値が 2009 になる別のレコードを挿入して、結果を観察してみましょう。

# SQL Programming Using MySQL Version 8.27

INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(7,'Mashal','Naaz', 'Female', 'Florida', 'mashalnaaz@gmail.com', 2009);

SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

これで、最近のレコードが一番上に表示されます。

MySQL で PARTITION BY および ORDER BY 句を使用したの ROW_NUMBER() の使用

ここでは、ROW_NUMBER() 関数を PARTITION BY 句と ORDER BY 句でのみ使用し、それでも正しい行番号が提供されるかどうかを確認します。

サンプルコード：

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_YEAR ORDER BY REGISTRATION_YEAR) AS row_numb
FROM test_db.tb_student;

上記のクエリを実行すると、PARTITION BY 句で ROW_NUMBER() を使用した場合と同じ出力が表示されます。次のスクリーンショットを参照してください。

背景が黄色の列を参照してください。これは私たちが期待していたことです。ここでは、セッション変数を使用して行番号を正しく割り当てます。

MySQL でセッション変数を使用したの ROW_NUMBER() のレプリケーション

PARTITION BY 句と ORDER BY 句を同時に使用すると、MySQL は正しいランキング機能を提供しません。このシナリオでは、セッション変数を使用してこれをエミュレートします。セッション変数はユーザー定義です。詳細については、こちらでご覧いただけます。

サンプルコード：

# SQL Programming Using MySQL Version 8.27
SET @row_numb = 0;
SELECT *,
    (@row_numb:=@row_numb + 1) AS row_numb
FROM test_db.tb_student ORDER BY REGISTRATION_YEAR;

以下に示すように、row_numb は 1 から始まり、連続して増加します。

それはどのように機能していますか？最初に@プレフィックスを使用してセッション変数 row_numb を設定し、0 で初期化します。次に、テーブルからデータを選択して並べ替え、出力しました。(@row_numb:=@row_numb + 1) は、変数の値をインクリメントおよび更新するのと同じです。

まとめ

上記の議論に照らして、MySQL でも ROW_NUMBER() 関数を使用できますが、バージョン 8.0 以降があれば、ランク付けの目的でセッション変数を使用しなければならない状況があると結論付けました。