Rで非等価結合

R

非等価結合 is 何

通常の結合(等価結合)においては、テーブル同士を結合する場合にそれぞれのテーブルから列を指定し、列の値が等しいという条件をもって行同士を結合する。

非等価結合は結合条件に不等式や範囲などの等値ではない条件を指定して結合する。非等結合、非等値結合、Non-equi Join、θ-Joinなどと呼ばれることもある。

Wikipediaのθ-Joinの説明(Relational algebra - Wikipedia)を例にする。

次のようなテーブルがあるとする。

car_model car_price
CarA 20000
CarB 30000
CarC 50000
boat_model boat_price
Boat1 10000
Boat2 40000
Boat3 60000

これらのテーブルから、car_price > boat_priceとなるような組合せを得るということを考える。それには、1つ目のテーブルに対して、car_price > boat_priceを条件とした結合を行えば良い。結果は次のようになる。

car_model car_price boat_model boat_price
CarA 20000 Boat1 10000
CarB 30000 Boat1 10000
CarC 50000 Boat1 10000
CarC 50000 Boat2 40000

このような結合は、SQLであれば結合条件にon car_price > boat_priceのような指定をすることで実現できる。ではRではどうすれば良いか？

Rでやる

Rで非等価結合を行う方法はいくつかあるが、以下では4つ方法を紹介する。

  1. dplyrを使う方法
  2. sqldfを使う方法
  3. data.tableを使う方法
  4. dbplyrでsql_onを使う方法

もしdbplyrを使用しRDBMSに接続しているなら4番一択なので1から3は読み飛ばして良い。

データ準備

上記の例と同じデータを用意しておく。

#install.packages("dplyr")
library(dplyr)
car <- tibble(
  car_model = c("CarA", "CarB", "CarC"),
  car_price = c(20000, 30000, 50000)
)
boat <- tibble(
  boat_model = c("Boat1", "Boat2", "Boat3"),
  boat_price = c(10000, 40000, 60000)
)

1. dplyrを使う

Rでデータフレームを操作するならdplyrパッケージ(A Grammar of Data Manipulation • dplyr)であり、Join系の関数も充実している。

しかし現在のところdplyrは非等価結合をサポートしていないので、同じ結果が得られる別の方法を採る必要がある。

(追記)

記事を公開したところ

との情報をいただきました。備えよう。

(追記終わり)

記述をシンプルにすることを優先するなら、full_join()を用いて交差結合を行ってからfilter()で条件を満たす行に絞り込むという方法で目的を達成できる。

car %>% 
  full_join(boat, by = character()) %>% 
  filter(car_price > boat_price)

full_join()で交差結合をするには上記のようにby = character()を指定する。

この方法はデータフレームがそれほど大きくなければ大丈夫だと思うが、パフォーマンスが問題になるケースも出てくるだろうと思う（交差結合をしてからフィルタしているように見えるからパフォーマンスが悪そうと思っているだけで、確認したわけではない）。

2. sqldfを使う

sqldfパッケージ(GitHub - ggrothendieck/sqldf: Perform SQL Selects on R Data Frames)は、データフレームをSQLで操作することを可能にするパッケージで、結合操作にも対応しているので非等価結合ができる。

#install.packages("sqldf")
library(sqldf)
sqldf(
  "
  SELECT car_model, car_price, boat_model, boat_price
  FROM car JOIN boat 
    on car_price > boat_price
  "
)

当然ではあるがSQLを書く必要があるので、SQLに馴染みがないとハードルが高い。また、上記のようにクエリは文字列で記述するため、おそらくほとんどのエディタでシンタックスハイライトや入力補完の恩恵を受けられないと思う。あまり複雑な集計・結合を要さない場合に、結合部分のみピンポイントで使用するなら有用だろう（ただ、非等価結合が必要になっている時点で単純な集計・結合では済まない状況になっている場合が多いのではないかと思う）。

1の方法よりはパフォーマンスが良いと思うが、確認はしていない。

3. data.tableを使う

data.tableパッケージ(Extension of `data.frame` • data.table)は大規模データを高速に扱えるようになるパッケージで、テーブル同士の結合もできる。そして、非等価結合をサポートしている。

#install.packages("data.table")
library(data.table)
# データは複製してdatatableにしておく
car_dt <- car
boat_dt <- boat
setDT(car_dt)
setDT(boat_dt)

car_dt[
  boat_dt, 
  on = .(car_price > boat_price),
  .(car_model, boat_model, boat_price = car_price)
  ][
    !is.na(car_model)
  ][
    car_dt,
    on = .(car_model = car_model),
    .(car_model, car_price, boat_model, boat_price)
  ]

(私が普段data.tableを使っていないので適切な記述になっていない可能性があるが)整形された結果を得ようとすると記述量がやや多くなる。 また、記法に癖があり、慣れていないと読むのも難しいだろうと思う。変数指定の順序にセンシティブな部分もあり、習熟していないと扱うのが難しいという印象を受けた。 data.tableなのでパフォーマンスは良いと思うが、これも確認はしていない。

なお、今回記事を書くにあたって次の記事を参考にしたが、そこで取り上げられている例ではそれほど記述は複雑になっていないように思える。データの形式と目的によっては有効な場合もあるのではないかと思う（今回の例のような交差結合的な操作が向いていないのかもしれない）。

4. dbplyrでsql_onを使う

dbplyr(A dplyr backend for databases • dbplyr)はdplyrの記法でデータベースを操作できるパッケージで、ローカルのデータフレームを扱うのと同じ感じでRDBMS上のテーブルを操作できる。記法はほぼdplyrと同じだが、SQLを直接記述する方法が用意されていたり、多少の違いがある。そして、*_join系の関数には結合条件を指定するsql_onという引数が追加されているので、ここに記述することで非等価結合が実行できる。

# 例のためにローカルにSQLiteのDBを作成し、データフレームをテーブルとしてコピーしておく。
#install.packages("DBI")
#install.packages("dbplyr")
library(DBI)
mydb <- dbConnect(RSQLite::SQLite(), "")
dbWriteTable(mydb, "car", car)
dbWriteTable(mydb, "boat", boat)

# 非等価結合の実行
tbl(mydb, "car") %>% 
  left_join(
    tbl(mydb, "boat"),
    sql_on = "car_price > boat_price"
  )

# 終わったらコネクションを閉じる
# dbDisconnect(mydb)

結合条件の中でテーブルを明示した列指定が必要な場合(自己結合など)は、LHS、RHSといったエイリアスも利用できる。

tbl(mydb, "car") %>% 
  left_join(
    tbl(mydb, "boat"),
    sql_on = "LHS.car_price > RHS.boat_price"
  )

この方法は記述がシンプルであり可読性にも優れる。私が普段dplyrばかり使っていて慣れているというのもあるが、それを差し引いても推奨できる方法だと思う。