SQLで重複データを扱うサンプルコード集 カウント、集計、最新のみ抽出、重複禁止
  • facebookページ
  • twitterページ
  • 2019.09.05

    SQLで重複データを扱うサンプルコード集 カウント、集計、最新のみ抽出、重複禁止

    SQLで重複データを扱うサンプルコードをまとめました。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    重複データを集計するSQLサンプルコード

    重複レコードをまとめるにはdistinctを使う

    社員約30万人分の役職情報が入っているtitlesテーブルから、title(役職名)を抽出し、distinctで重複をまとめます。

    titlesテーブルは、以下のようなデータが入っています。emp_noは社員番号、titleは役職名、from_dateとto_dateはその役職が有効だった期間を表しています。

    SQLの実行結果

    SQLを実行するとこうなります。

    SQLの実行結果

    上記の役職名が抽出できました。

    distinctは、selectで指定したカラムの重複を除外します。

    上記のSQLの場合、first_name, last_name, genderが全て一致しているレコードのみ重複除外されます。

    重複のカウントには、group byとcountを組み合わせる

    上記のSQLに手を加えて、同じ役職を持つ社員数をカウントしてみましょう。

    ただし、社員によっては役職が変化した社員も居るため、条件として「在職している社員の最新の役職」(to_date=9999/01/01)を加えます。

    実行結果はこうなりました。

    SQLの実行結果

    また、役職の数をカウントするには、countとdistinctを組み合わせます。

    結果はこうなりました。

    SQLの実行結果

    7種類のtitle(役職)があることがわかりました。

    重複を集計するにはgroup byと集計関数を組み合わせる

    salaries(年収テーブル)と、titles(役職テーブル)をemp_no(社員番号)で内部結合しています。

    salaries、titlesともにto_date=’9999/01/01’の最新データのみを対象にして、平均年収(avg)、最低年収(min)、最高年収(max)を集計してみました。

    平均年収がもっとも高い役職はSenior Staffでした。役員も含まれているのでしょうか。

    なお、集計関数はこの他にも、SUM(合計)、STD(母標準偏差)、VAR_SAMP(標本分散)などがあります。

    重複データを除外するSQLサンプルコード

    重複を除外して、最新のデータのみ残すにはサブクエリとnot existsを組み合わせる

    not exists と サブクエリを使って、emp_no(社員番号)が重複し、to_date(有効期限 終了日)が最新のデータのみ抽出します。

    SQLの実行結果

    エイリアスを使い、where句のa.to_date < b.to_dateという条件とnot existsを組み合わせて、最新データのみ残しています。

    【関連記事】
    SQL not exists サンプルコード 2テーブルの片方にしかないデータを抽出 

    重複データの優先順位を指定してデータを残す

    上記SQLは「最新のデータのみ残す」ようにしましたが、where句を変えることで優先順位を指定して残すエータを決めることができます。

    例えば、salaries(年収テーブル)のemp_no(社員番号)が重複しているデータから、salary(年収)が最も高いデータのみ抽出するSQLは以下のようになります。

    where句の「and a.salary < b.salary」の部分のみ差し替えました。

    SQLの実行結果はこうなりました。

    SQLの実行結果

    必ずしも、最新の年収が最高とは限らないという結果になりました。

    delete文と組み合わせて、実際に重複データを削除する方法は、こちらをご参考に。

    【関連記事】
    SQLで重複を削除するサンプルコード 最新データを残してdeleteするには?

    日付の重複データを除外するには、date_formatとgroup byを組み合わせる

    日付の入ったカラムを「年」で重複除外するサンプルコードです。

    「同い年で、一番先輩は誰?」を求めるSQLです。

    date_formatはMySQL独自の日付変換関数です。

    具体的には、employees(社員テーブル)のbirth_date(誕生日)から、誕生年(birth_year)を抽出し、同じ誕生年の社員のうち、もっとも入社年月日(hire_date)が古い社員を抽出しています。

    実行結果は以下のようになります。

    SQLの実行結果

    条件を少し変えることで、「同い年で、一番後輩は誰?」「同期入社で一番年上は誰?」などを求めることが出来ます。

    重複を禁止するSQL

     

    ユニークキー制約で指定カラムの重複を禁止

    テーブルのカラムのうち、プライマリキー以外のカラムの重複を禁止したい場合は、ユニークキー制約を設定します。

    MySQLの場合は、以下のSQLでユニークキー制約が設定可能です。

    ユニークキー制約は、複数の列に設定可能です。

    ユニークキー制約を適用したカラムには、NULLを格納することができます。複数のカラムにNULLが格納されたときに重複とはみなされないので注意が必要。

    NULLの重複制限は、DBMSによって異なります。MySQL、Oracleはユニークキー制約があってもNULL格納を許可にする設定が可能。SQL Serverは、NULL値も1列に一つしか使用できません。

    関連)UNIQUE 制約と CHECK 制約 – SQL Server | Microsoft Docs

    SQLの重複データのまとめ

    ポテパンダの一言メモ
    • 重複データを単純にまとめるにはdistinctを使用
    • 重複をカウントするには、group byとcountを組み合わせる
    • 重複データを除外するには、サブクエリとnot existsを組み合わせる
    • 重複データを禁止するには、ユニークキー制約を設定する

    ポテパンが提供するサービスについて

    本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。

    ポテパンフリーランス

    ポテパンフリーランス

    フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面(税金や保険など)についてお答えいたします!フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。

    ポテパンキャリア

    ポテパンキャリア

    エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。

    ポテパンキャンプ

    ポテパンキャンプ

    ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。



    株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください!

    ポテクラバナー ポテプロバナー

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE

    ポテパンおすすめ案件