SQL における distinct と row_number() over() の違いと使い方

1 はじめに

データベース内のデータを操作するための SQL 文を記述するときに、いくつかの不快な問題に遭遇することがあります。たとえば、同じフィールド内の同じ名前のレコードの場合、1 つだけ表示する必要がありますが、実際にはデータベースに同じ名前のレコードが複数含まれている可能性があります。そのため、検索時に複数のレコードが表示され、本来の意図に反します。したがって、このような状況を回避するには、「重複排除」処理を行う必要があります。では、「重複排除」とは何でしょうか?簡単に言えば、同じフィールドに対して、同じ内容のレコードが 1 つだけ表示されることを意味します。

では、「重複排除」機能を実現するにはどうすればよいでしょうか?この点に関して、この機能を実現するには 2 つの方法があります。

最初の方法は、select ステートメントを記述するときに distinctive キーワードを追加することです。

2 番目の方法は、select ステートメントを記述するときに row_number() over() 関数を呼び出すことです。

上記の両方の方法で「重複排除」機能を実現できますが、それらの類似点と相違点は何でしょうか?次に著者が詳しく説明します。

2つの異なる

SQL では、一意に異なる値を返すために、キーワード distinctive が使用されます。構文形式は次のとおりです。

SELECT DISTINCT 列名 FROM テーブル名

次の形式の NAME と AGE という 2 つのフィールドを含むテーブル「CESHIDEMO」があるとします。

上記の表を見ると、同じ NAME のレコードが 2 つ、同じ AGE のレコードが 3 つあることがわかります。次のSQL文を実行すると、

/**
* ここで、PPPRDER はスキーマの名前です。つまり、テーブル CESHIDEMO は PPPRDER にあります。*/

PPPRDER.CESHIDEMOから名前を選択

次の結果が得られます。

結果を観察すると、上記の 4 つのレコードの中に、同じ NAME 値を持つ 2 つのレコードがあることがわかります。つまり、2 番目と 3 番目のレコードの値は両方とも「gavin」です。では、同じ名前のレコードを 1 つだけ表示したい場合は、どうすればよいでしょうか?このとき、distinct キーワードを使用する必要があります。次に、次のSQL文を実行します。

PPPRDER.CESHIDEMO から異なる名前を選択

次の結果が得られます。

結果を観察すると、私たちの要件が達成されたことは明らかです。しかし、distinct キーワードを 2 つのフィールドに同時に適用するとどのような効果があるのか​​疑問に思わざるを得ません。ここまで考えたので、試しに次の SQL ステートメントを実行してみましょう。

PPPRDER.CESHIDEMO から固有の名前と年齢を選択

結果は次のとおりです。

結果を観察してください。おっと、効果がないようです?彼女はすべての記録を見せてくれました！同じ NAME 値を持つレコードが 2 つあり、同じ AGE 値を持つレコードが 3 つあります。まったく変化はありません。しかし、実際には、結果は次のようになるはずです。複数のフィールドで distinctive を使用すると、同じフィールド値を持つレコードのみが削除されます。明らかに、4 つの「不良」レコードはこの条件を満たしていないため、distinct は上記の 4 つのレコードが同じではないと判断します。言葉だけでは足りないので、「CESHIDEMO」テーブルに同一のレコードを追加して検証してみましょう。レコードを追加した後のテーブルは次のようになります。

次に次のSQL文を実行します。

PPPRDER.CESHIDEMO から固有の名前と年齢を選択

結果は次のとおりです。

結果を観察すると、上記の結論を完全に検証できます。

さらに、特別な注意を払う必要がある点が 1 つあります。それは、distinct キーワードは、SQL ステートメントのすべてのフィールドの先頭にのみ配置できるということです。間違った場所に配置すると、SQL はエラーを報告しませんが、効果はありません。

3 row_number() オーバー()

SQL Server データベースには、データベース テーブル内のレコードに番号を付ける関数 row_number() が用意されています。これを使用する場合、関数 over() が続き、関数 over() を使用してテーブル内のレコードをグループ化および並べ替えます。両方に使用される構文は次のとおりです。

ROW_NUMBER() OVER(COLUMN1 によるパーティション、COLUMN2 による順序)

意味: テーブル内のレコードをフィールドCOLUMN1でグループ化し、フィールドCOLUMN2で並べ替えます。

PARTITION BY: グループ化を示します。ORDER BY: 並べ替えを示します。

次に、テーブル「CESHIDEMO」のデータでもテストします。まず、row_number() over() 関数を使用しないクエリ結果を以下に示します。

次に、次のSQL文を実行します。

PPPRDER.CESHIDEMO.*、row_number() を PPPRDER.CESHIDEMO から (partition by age order by name desc) で選択します。

結果は次のとおりです。

上記の結果から、元のテーブルに基づいて、数値ソートでマークされた追加の列があることがわかります。次に、逆に実行した SQL ステートメントを分析すると、実際にフィールド AGE の値によってグループ化され、フィールド NAME の値によってソートされていることがわかります。したがって、機能の機能性が検証されます。

次に、row_number() over() 関数を使用して「重複排除」機能を実現する方法を学習します。上記の結果を観察すると、NAME でグループ化し、AGE で並べ替え、各グループの最初のレコードを取得すると、「重複排除」機能を実現できる可能性があることがわかります。次に、次のSQL文を実行してみましょう。

/*
* rnは最後に追加された列を示します*/

*から選択 
(PPPRDER.CESHIDEMO.*、row_number() を PPPRDER.CESHIDEMO から (名前によるパーティション、年齢による順序、降順) で繰り返し実行)
ここでrn = 1

実行後の結果は次のようになります。

上記の結果を観察すると、誤ってデータの「重複排除」機能を実現してしまったことがわかります。残念ながら、注意してみると、不愉快なことが起こります。つまり、上記の「重複排除」SQL 文を実行すると、NAME 値が「gavin」で AGE 値が「18」のレコードが除外されます。しかし、現実の世界では、名前が同じでも年齢が異なることはよくあります。

4 結論

上記の内容を読んで実践することで、distinct キーワードを使用するか、関数 row_number() over() を使用するかに関係なく、データの「重複排除」機能を実現できることがすでにわかります。ただし、実装の過程では、両者の使用特性と違いに特に注意する必要があります。

キーワード distinctive を使用する場合、単一のフィールドで動作する場合と複数のフィールドで動作する場合とで違いがあることを知っておく必要があります。単一のフィールドで動作する場合、テーブル内のフィールドで重複する値を持つすべてのデータを「重複排除」します。複数のフィールドで動作する場合、テーブル内のすべてのフィールド (つまり、distinct が動作する複数のフィールド) で同じ値を持つデータを「重複排除」します。

row_number() over() 関数を使用する場合、最初にレコードをグループ化して並べ替え、次に各グループの最初のレコードを取り出して「重複排除」を実行します (このブログ投稿のように)。もちろん、ここで「重複排除」を実行するためにさまざまな制限を使用することもできます。具体的にどのように実装するかについては、全員が考える必要があります。

最後に、このブログ投稿では、著者は、distinct キーワードと row_number() over() 関数を使用してデータを「重複排除」することについての理解を詳しく説明しています。上記の内容が皆様のお役に立てば幸いです。

読んでいただきありがとうございます。お役に立てれば幸いです。このサイトをサポートしていただきありがとうございます。