テーブル内の重複した値を見つけるための最高のSQLトリック2つ

2 Best SQL tricks to find duplicate values in a table.

データサイエンス

重複レコードを削除して時間とコストを節約しましょう

重複レコードはどこにでもあります！

それはすべてのデータベースユーザーが直面する最も一般的な問題の一つです。

このような重複レコードは、データの統合、システムの欠陥、データベースの更新時に人為的なエラー、データ検証チェックの不足など、さまざまな理由でデータベースに存在します。

重複レコードによりデータが不整合になります。このようなデータベースは、メンテナンスや作業に多くのお金、時間、またはリソースを必要とします。また、重複レコードは不必要に多くのストレージスペースを消費し、クエリ実行を遅くします。

• Cox回帰の隠されたダークシークレット：Coxを解きほぐす
• Pythonを使用して北極の氷の傾向を分析する
• DataFrameを効率的に操作するためのloc Pandasメソッドの使い方

したがって、分析を進める前に、クエリするテーブルから少なくともこのような重複レコードを削除する必要があります。

そのため、面接時にも企業はしばしば重複レコードの取り扱いに関する質問をします。

この記事では、SQLで重複レコードを特定するための2つの最高の時間節約方法を探求します。わかりやすくするために、興味深い例をいくつか紹介します。

また、この記事の最後には、素晴らしいSQLリソースをチェックすることを忘れないでください。

さあ、始めましょう！

私はMySQL Workbenchを使用して、自分で作成したSales Dataのサブセットを使用しています。あなたもMITライセンスの下で無料で取得できます！

これは、このデータセットの小さな18×6サブセットであり、以下に示すように、いくつかの重複レコードを持っています。

クエリに直接入る前に、テーブル内の重複レコードを検索するための基準を定義する必要があります。単一の列内の特定の値が重複している場合や、テーブル内の特定の行のすべての列の値が重複している場合など、シナリオがあるかもしれません。

このクイックリードで、重複レコードに対処する方法と可能性の両方を探求します。

重複レコードを特定する最も簡単な方法は、テーブル内の各レコードがいくつ出現するかを単純にカウントすることです。そして、1回以上出現するレコードは重複しています。

GROUP BYとHAVINGを使用して重複レコードを検索する

データ集計のためにSQLで広く使用されるGROUP BY関数を使用すると、1つまたは複数の列の値に基づいてレコードをグループ化し、カウント、または他の列の合計などの集計値を取得できます。

これを念頭に置いて、単一の列内で重複した値を特定する方法を探求しましょう。

1つの列内で重複する値を見つける

重複した値が1つの列にのみ存在する場合があります。そのような重複レコードの理由は、データ入力ミスまたはデータベースの更新時の人為的なエラーなどが考えられます。

注文テーブルから例を取り、どのOrderIDが重複しているかを見つけましょう。テーブル内で各OrderIDが何回出現したか数える必要があるため、以下のようにOrderIDでレコードをグループ化する必要があります。

SELECT OrderID , COUNT(*) as occurrences FROM ordersGROUP BY OrderID

ハイライトされたレコード（OrderID）はデータセット内で複数回出現したことがあり、つまり重複しています。

ただし、前述の画像のように別々の列を作成する必要はありません。以下のように、GROUP BYの後にHAVING句を使用して、重複したOrderIDを直接取得できます。

SELECT OrderIDFROM ordersGROUP BY OrderIDHAVING COUNT(*) > 1;

上記の表で強調表示されたものと同じである重複したOrderIDのみが得られます。

同様に、複数の列の値がテーブル内で重複している場合があります。

複数の列で重複する値を見つける

テーブル全体で行が重複している場合でも、ロジックは同じままで、GROUP BY句で指定する列だけが変わります。

単一の列でレコードをグループ化する代わりに、テーブル内でOrderID、Quantity、およびProduct_Categoryの組み合わせが複数回表示されたレコードを表示する必要があります。

SELECT OrderID    , Quantity    , Product_Category    , COUNT(*) as occurrencesFROM ordersGROUP BY OrderID    , Quantity    , Product_Category

この方法で、列OrderID、Quantity、およびProduct_Categoryの値の組み合わせがテーブル内で複数回表示されたことがわかります。

再度、単にHAVING COUNT(*) > 1をクエリの末尾に追加するだけで、これらの重複したレコードを取得できます。

重複を検出するプロセスは、レコードがテーブルに表示される回数の数に依存するため、ウィンドウ関数ROW_NUMBERを使用することもできます。

ROW_NUMBER()を使用して重複するレコードを検出する

ウィンドウ関数ROW_NUMBER()は、PARTITION_BY句を使用して定義されたウィンドウ内の各レコードに一意の連続番号を割り当てます。

したがって、重複するレコードが複数回表示される場合は、重複したレコードには1以上の行番号が割り当てられます。

同じ例を続けましょう。

テーブル内でOrderID、Quantity、およびProduct_Categoryの組み合わせが複数回表示されたレコードを取得するには、以下に示すように、これらの列をPARTITION_BY句で定義したウィンドウを定義する必要があります。

SELECT OrderID    , Quantity    , Product_Category    , ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_numFROM orders

これにより、指定された列でパーティション分割されたすべてのレコードと対応する行番号が取得されます。したがって、行番号が2であるハイライトされたレコードは重複したレコードです。

重複したレコードのみを取得するには、上記のクエリ全体を外部のSELECTステートメントにサブクエリとして渡すことができます。

SELECT OrderID     , Quantity     , Product_CategoryFROM (    SELECT OrderID         , Quantity         , Product_Category         , ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_num    FROM orders     ) AS subqueryWHERE row_num > 1;

もしもサブクエリを使用することが望ましくない場合は、CTEを作成し、以下に示すように別のクエリを使用してそのCTEからデータを取得することができます。

WITH temp_orders AS(SELECT OrderID    , Quantity    , Product_Category    , ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_numFROM orders)SELECT OrderID    , Quantity    , Product_CategoryFROM temp_ordersWHERE row_num > 1;

このクエリでもまったく同じ出力が返されます。ですから、選択はあなた次第です。

ROW_NUMBER()、CTE、GROUP BYについて詳しく学びたい場合は、この記事の最後にある興味深いリソースをチェックすることを忘れないでください。

このような重複レコードを含むデータをさらに分析に使用する場合、正しいレコードについての混乱を引き起こします。そのため、分析を進める前に、このような重複レコードを修正する必要があります。

プロジェクトの早い段階で重複レコードに対処することは、時間、労力、および計算費用を節約することが確実です。したがって、重複を見つけて対処することで、データの検証と高品質のデータの取得に役立ちます。

それにもかかわらず、これはデータアナリストまたはデータエンジニアの役割の最も一般的な面接質問の1つです。したがって、これらの技術をマスターし、この問題にアプローチする方法を理解することは、面接で成功するために役立ちます。

私はこれ以上の興味深いトピックやよくあるSQLの面接質問、およびそれらを解決するための異なるアプローチをカバーしていきます。お楽しみに！

VoAGIで無制限のストーリーを読むことに興味がありますか？

💡 VoAGIメンバーになって、VoAGIと毎日の興味深いVoAGIニュースレターの無制限のストーリーにアクセスしてください。あなたの料金の一部を私が受け取りますが、あなたには追加費用はかかりません。

💡 データサイエンスのガイド、トリック、ヒント、SQLおよびPythonのベストプラクティスの記事を見逃さないように、サインアップして200人以上のメンバーになってください。

以下の記事をチェックして、ROW_NUMBER()とCTEについて詳しく理解してください。

2022年に知っておくべき5つの高度なSQLコンセプト

今日からこれらの時間を節約できる高度なSQLクエリをマスターしてください。

towardsdatascience.com

SQL GROUP BYをマスターするには、以下の記事を強くお勧めします。

SQL GROUP BYをマスターするための5つの実用的なSQL例

10分以下で完了する簡単から複雑なSQL GROUP BY使用例！

towardsdatascience.com

読んでくださり、ありがとうございました！

We will continue to update VoAGI; if you have any questions or suggestions, please contact us!