SQLを用いて重複レコードを全抽出(2回目以降出現のレコードを抽出するのではなく!)[R]

データには重複が含まれることがある

　重複データを抽出したり、削除したりを何回か話題に挙げました。
今回も、また重複データを取り上げるのは重複データに重要な意味があることが多いためです。
　例えば、1購入で1行というトランザクション形式の商品販売履歴データならば、リピーター様のデータは重複して出現します。

重複データの例

重複データの例

# 姓+名+購入品   
NameFam <-c("山田","山田","鈴本","山田","下山","下山","大林","浅井","下山","早川")
Name1st <-c("直子","太郎","次郎","直子","次郎","次郎","深見","沼男","次郎","分子")
Purchase<-c("花札","帽子","雨傘","日傘","眼鏡","下着","菓子","パン","牛乳","文庫")
Sample <-data.frame(NameFam,Name1st,Purchase)
Sample

   NameFam Name1st Purchase
1     山田    直子     花札
2     山田    太郎     帽子
3     鈴本    次郎     雨傘
4     山田    直子     日傘
5     下山    次郎     眼鏡
6     下山    次郎     下着
7     大林    深見     菓子
8     浅井    沼男     パン
9     下山    次郎     牛乳
10    早川    分子     文庫

duplicated だと困ったことが…

　Rにはduplicated関数というものがあります。この関数は、重複判定となる項目が同じものが2回目以降に現れたときにTRUEとなります。1回目はFalseなのです。重複しているデータを全て抽出しているわけではありません。

duplicatedの例

    # duplicated は一部の複数列を判定に用いることができないらしい   
 df<-subset(Sample,,c(NameFam,Name1st))
    # duplicatedでTRUE判定のものを表示
    Sample[duplicated(df),]


  NameFam Name1st Purchase
4    山田    直子     日傘
6    下山    次郎     下着
9    下山    次郎     牛乳

　duplicated は2回目以降に出現するデータを抽出します。
　そのため、山田直子さんが花札を買ったデータと、下山次郎さんがメガネを買ったデータが抽出されません。
　こういう抽出結果に意味がある場合もあるかもしれませんが、重複データ全体を把握するためにはこの方法は使えません。

重複とは出現回数が2以上ということ

出現回数を数える

　根本的なところで、重複データとは出現回数が2以上ということですから、出現回数を数えることからはじめます。

出現回数をカウントする

     require("sqldf") # sqldfパッケージを読み込む
 
# 姓+名　毎で　出現回数をカウントする
sqldf("select NameFam,Name1st,count(*) from Sample Group by Name1st,NameFam")

  NameFam Name1st count(*)
1    下山    次郎        3
2    鈴本    次郎        1
3    浅井    沼男        1
4    大林    深見        1
5    山田    太郎        1
6    山田    直子        2
7    早川    分子        1

　ここでは、sqldfを用いて SQL により、Name1st,NameFam をユニークキーとして出現回数を求めています。
　count(*) が出現回数であり、これが2以上であれば重複があるということです。
　下山次郎さんと山田直子さんに重複データがあります。

出現回数>1のデータのみ数える

　 　出現回数が2以上が重複データということですから、各レコードに出現回数を付与した後、出現回数>1に絞り込みます。

元データに出現回数を追加し、その出現回数により重複レコードを抽出する

     require("sqldf") # sqldfパッケージを読み込む
 
# 出現回数>1のみ抽出する
sqldf("select x.* from Sample as x,(select Name1st as name,NameFam as sei,count(*) as ct from Sample Group by Name1st,NameFam) 
 where Name1st=name and NameFam=sei and ct>1")

  NameFam Name1st Purchase
1    山田    直子     花札
2    山田    直子     日傘
3    下山    次郎     眼鏡
4    下山    次郎     下着
5    下山    次郎     牛乳

　ここでも sqldfを用いて SQL を活用します。
　元のデータに、副照会クエリを利用して出現回数を結合し、出現回数>1のみに絞り込んでいます。
　SQLを用いて重複レコードを抽出できました。

RではなくてもSQLなら同じこと

　ここまで R言語 の話としてきましたが、実際には SQL ならRでなくても同じことです。

SQLの書き方

select x.* from 
     Sample as x,
     (select Name1st as name,NameFam as sei,count(*) as ct from Sample Group by Name1st,NameFam) 
 where Name1st=name and NameFam=sei and ct>1