dplyrからSQLへの翻訳はどのように行われているのか

## はじめに

`dplyr`、そしてデータベースを操作する際にバックエンドとして働く`dbplyr`がどのようにRのコードをSQLへ翻訳しているのかを見ていきます。

まずは必要なパッケージをインストールし、ロードしておきます。

``` r
install.packages("dbplyr")
install.packages("dplyr")
```

``` r
library(dbplyr)
library(dplyr)
```

## ベクトル演算

はじめにRでベクトル演算として表現できる単純な計算や集計について見ていきます。この種の単純な操作はRからSQLへ翻訳するのも比較的容易ですが、若干の違いもあります。

### 変数と文字列

標準SQLではダブルクォートで括った文字列はオブジェクト名として、シングルクォートで括られた文字列は文字列として扱われます（データベースによってはダブルクォートで括られた文字列を文字列リテラルとして許容する場合もあります）。そのため、`dplyr`はオブジェクト名や文字列を以下のように変換します。

- オブジェクト名（引用符なし、またはバッククォートで括られた文字列）→ダブルクォートで括られる
- 文字列（ダブルクォートまたはシングルクォートで括られた文字列）→シングルクォートで括られる

実際に見てみましょう。式をSQLに翻訳した結果を得るには、`translate_sql()`関数を以下のように使います。

``` r
translate_sql(x)
```

```sql:output
<SQL> "x"
```

``` r
translate_sql("x")
```

```sql:output
<SQL> 'x'
```

### 数字

数字、特に整数の扱いについては、若干注意する必要があります。SQLでは単に`1`と書いた場合は整数型として扱われます。一方、Rでは`1`と書いた場合は実数として扱われます。そのため、Rの`1`をSQLに翻訳すると`1.0`と翻訳されてしまいます。

``` r
translate_sql(1)
```

```sql:output
<SQL> 1.0
```

Rで整数型を使いたい場合は、明示的に`1L`と記述します。これはSQLでは`1`に翻訳されます。

``` r
translate_sql(1L)
```

```sql:output
<SQL> 1
```

### 関数

多くの関数が少し名前の違うSQLの関数に翻訳されます。

``` r
translate_sql(x == 1 && (y < 2 || z > 3))
```

```sql:output
<SQL> "x" = 1.0 AND ("y" < 2.0 OR "z" > 3.0)
```

SQLとして実行できるように、引数の順序が変更される場合もあります。

``` r
translate_sql(log(x, 10))
```

```sql:output
<SQL> LOG(10.0, "x")
```

#### 翻訳される関数

次に示す関数は、どのようにSQLに翻訳すべきかを`dplyr`が知っています。

  - 基本算術演算子: `+`, `-`, `*`, `/`, `%%`, `^`
  - 数学関数: `abs`, `acos`, `acosh`, `asin`, `asinh`, `atan`, `atan2`,
    `atanh`, `ceiling`, `cos`, `cosh`, `cot`, `coth`, `exp`, `floor`,
    `log`, `log10`, `round`, `sign`, `sin`, `sinh`, `sqrt`, `tan`,
    `tanh`
  - 比較演算子: `<`, `<=`, `!=`, `>=`, `>`, `==`, `%in%`
  - 論理演算子: `&`, `&&`, `|`, `||`, `!`, `xor`
  - 単純集計関数: `mean`, `sum`, `min`, `max`, `sd`, `var`
  - 文字列操作関数: `tolower`, `toupper`, `trimws`, `nchar`, `substr`
  - 型変換: `as.numeric`, `as.integer`,
`as.character`

しかし、これらの関数であっても翻訳は完璧に行われるとは限りません。これは、`dplyr`が「何をしているか」よりも「何を意味しているか」を重視しているからです。`dplyr`が意味の妥当性を重視するために、多少手の込んだSQLが生成される場合もあります。これはミスを防ぐありがたい気遣いになる場合もありますが、予期せぬバグの原因となることもあります。この点が気になる方は、一足先にこの記事の最後にある集合演算の説明を見てみると良いかもしれません。

また、RとSQLの関数は名前が似ていても完全に同じというわけではないので、上記の関数でも翻訳が失敗してしまうことがあります。たとえば、SQLがRの関数の引数に対応していない場合には翻訳は上手くいきません。例えば`mean()`にはトリム平均を計算するための`trim`という引数がありますが、これはSQLのAVG関数にはありませんから、翻訳ができません。

``` r
translate_sql(mean(x, trim = 0.1))
```

```r:output
#> mean(x, trim = 0.1) でエラー: 使われていない引数 (trim = 0.1)
```

#### 翻訳されない関数

翻訳が失敗するわけではなく、単に翻訳されない関数もあります。これには`dplyr`が翻訳方法を知らない**すべての関数**が該当します。このような関数は**そのまま**SQLになります。これはつまりデータベース関数がそのまま使えるということを意味しています。

例えば次の例はRの式としては動作しませんが、翻訳されたSQLは動作します。

``` r
translate_sql(glob(x, y))
```

```sql:output
<SQL> GLOB("x", "y")
```

``` r
translate_sql(x %like% "ab%")
```

```sql:output
<SQL> "x" LIKE 'ab%'
```

#### データベース間の差

データベースの種類によって使える関数や書き方は若干異なります。`dplyr`は接続しているデータベースの種類から自動的にどのデータベースに対応した翻訳をすべきか判断します。

`translate_sql()`には`con`引数があり、これにコネクションオブジェクトを渡すと接続先のデータベースに応じた翻訳をしてくれます。なお、デフォルトでは、（概ね）SQL-92に準拠するSQLが生成されます。

#### ウィンドウ関数

一部の関数はウィンドウ関数として翻訳されます。ウィンドウ関数はRや`dplyr`と比べてもかなり柔軟な表現が可能なので、翻訳も複雑になります。

まず、ウィンドウ関数について簡単に説明します。

ウィンドウ関数は以下のような構造を持っています。

```
[式] OVER ([パーティション句] [オーダー句] [フレーム句])
```

  - **式**は`avg("x")`のように関数名と変数名からなります。どのようなウィンドウ関数が使えるかはデータベースの種類によりますが、一般的にはランキング関数と`lead`,
    `lag`, `nth`, `first`, `last`, `count`, `min`, `max`, `sum`, `avg`,
    `stddev`といった関数がサポートされています。
  - **パーティション句**はウィンドウ関数を適用するグループの分割方法を指定します。これは`GROUP
    BY`と似たようなものです。実際、`dplyr`では`group_by()`によるグループ化は文脈に応じて`GROUP
    BY`句にも`PARTITION BY`句にも翻訳される可能性があります。`GROUP
    BY`との主な違いは、結果を1行に集約しないという点です。
  - **オーダー句**は順序を制御します。これは順序が結果に影響するような計算で重要となります。例えばランキング関数を使う場合や、累積和を求めるような場合です。あるべきオーダー句が無い場合は、エラーになったり結果が不定になったりします。データベースは指定しない限り行の順序が定まらないためです。
  - **フレーム句**は現在の行を基準として、前後どれだけの範囲を関数に渡すかを指定します。例えば最初から現在の行までの値を`sum`に渡せば累積和を計算できますし、前後何行かを`avg`に渡せば移動平均が計算できます。フレーム句の指定方法には無数のバリエーションがありますが、よく使われるのは以下の3つです。
      - 繰り返し: `BETWEEN UNBOUND PRECEEDING AND UNBOUND
            FOLLOWING`
          - （パーティションで区切られた）範囲のすべてを対象とします。例えば関数が`avg`であれば、すべての行に平均値が表示されます。
      - 累積: `BETWEEN UNBOUND PRECEEDING AND UNBOUND CURRENTROW`
          - 累積和を計算するような場合に使います。
      - ローリング: `BETWEEN 2 PRECEEDING AND 2
FOLLOWING`
          - 移動平均を計算するような場合に使います。

ウィンドウ関数がどのように機能するか、まずSQLを書いて確認してみましょう。

ローカルでコネクションを作成してあれこれ試すにはSQLiteを使うのが楽ですが、今のところ`RSQLite`に同梱されているSQLiteはウィンドウ関数に対応していません（新しいバージョンのSQLiteはウィンドウ関数に対応したようです）。

そこで今回はSparkを使用します。`sparklyr`パッケージを使えばSparkのインストールからコネクションの作成までR上で完結するので簡単です。

``` r
# インストールしていなければsparklyrとsparkをインストールする
#install.packages("sparklyr")
library(sparklyr)
#spark_install(version = "2.1.0")
sc <- spark_connect(master = "local", version = "2.1.0")
spark_mtcars <- copy_to(sc, mtcars, "mtcars")
```

ではウィンドウ関数を使ってみましょう。

``` sql
SELECT
    mpg, 
    cyl, 
    sum(cyl) OVER (
      PARTITION BY cyl
      ORDER BY mpg
      ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) as sum_cyl
  FROM mtcars
  LIMIT 10
```

|  mpg | cyl | sum\_cyl |
| ---: | --: | -------: |
| 17.8 |   6 |       18 |
| 18.1 |   6 |       24 |
| 19.2 |   6 |       30 |
| 19.7 |   6 |       30 |
| 21.0 |   6 |       30 |
| 21.0 |   6 |       24 |
| 21.4 |   6 |       18 |
| 21.4 |   4 |       12 |
| 21.5 |   4 |       16 |
| 22.8 |   4 |       20 |

cylでグループ化し、mpgでソートし、cylについて前後2行の範囲の合計を求めています。グループの境界の処理にも注目してみて下さい。

#### ウィンドウ関数への翻訳

同じ関数でも集計関数として計算される場合と、ウィンドウ関数として翻訳される場合があります。

集計関数は値を一つにまとめる働きをします。つまり、`summarise()`を使って値を集約するような式は集計関数として翻訳されます。

``` r
spark_mtcars %>% 
  summarise(mean = mean(mpg, na.rm = TRUE)) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT AVG(`mpg`) AS `mean`
FROM `mtcars`
```

上記で使用した`show_query()`は生成されるSQLを確認するもう一つの方法です。`summarise()`などを利用した場合は実際に生成されるクエリを確認するためにこちらを使う必要があります。

ウィンドウ関数の特徴は行ごとに値が計算されるという点にあります。つまり、`mutate()`や`transmute()`を使って**列を追加する**ような式がウィンドウ関数として翻訳されます。

``` r
spark_mtcars %>% 
  mutate(mean = mean(mpg, na.rm = TRUE)) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, avg(`mpg`) OVER () AS `mean`
FROM `mtcars`
```

パーティション句の指定は、`group_by()`を通じて行います。

``` r
spark_mtcars %>% 
  group_by(cyl) %>% 
  mutate(mean = mean(mpg, na.rm = TRUE)) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, avg(`mpg`) OVER (PARTITION BY `cyl`) AS `mean`
FROM `mtcars`
```

オーダー句の指定方法はいくつかあり、関数の種類によって多少異なります。例えばランキング関数の場合は明示的な指定が不要です。

``` r
spark_mtcars %>% 
  mutate(rank = rank(mpg)) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, rank() OVER (ORDER BY `mpg`) AS `rank`
FROM `mtcars`
```

累積関数の場合は、順序によって結果が変わりますから、明示的に指定する必要があります。指定しないと警告が出ます。まず、以下のように`order_by()`で関数を包むやり方があります。

``` r
spark_mtcars %>%
  mutate(cumsum = order_by(mpg, cumsum(mpg))) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, sum(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM `mtcars`
```

`window_order()`を使えば、パイプ処理の途中で順序を指定できます。

``` r
spark_mtcars %>% 
  window_order(mpg) %>% 
  mutate(cumsum = cumsum(mpg)) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, sum(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM `mtcars`
```

`arrange()`を使って全体の順序を指定している場合には、その順序がオーダー句でも使用されます。

``` r
spark_mtcars %>% 
  arrange(mpg) %>% 
  mutate(cumsum = cumsum(mpg)) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, sum(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM (SELECT *
FROM `mtcars`
ORDER BY `mpg`) `jhxezjnagn`
```

`window_order()`と`arrange()`は併用することもできます。`window_order()`はウィンドウ関数のオーダ句にのみ作用するためです。

``` r
spark_mtcars %>% 
  arrange(cyl) %>% 
  window_order(mpg) %>% 
  mutate(cumsum = cumsum(mpg)) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, sum(`mpg`) OVER (ORDER BY `cyl`, `mpg` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM (SELECT *
FROM `mtcars`
ORDER BY `cyl`) `xogpmlsput`
```

フレーム句は累積関数などでは自動的に設定されますが、`window_frame()`を使って明示的に指定することもできます。最初に示したSQLを`dplyr`で書くなら次のようになります

``` r
spark_mtcars %>% 
  select(mpg, cyl) %>% 
  group_by(cyl) %>% 
  window_frame(-2, 2) %>% 
  window_order(mpg) %>% 
  mutate(sum_cyl = sum(cyl, na.rm = TRUE))
```

```r:output
# Source:     spark<?> [?? x 3]
# Groups:     cyl
# Ordered by: mpg
     mpg   cyl sum_cyl
 * <dbl> <dbl>   <dbl>
 1  17.8     6      18
 2  18.1     6      24
 3  19.2     6      30
 4  19.7     6      30
 5  21       6      30
 6  21       6      24
 7  21.4     6      18
 8  21.4     4      12
 9  21.5     4      16
10  22.8     4      20
# ... with more rows
```

なお、`UNBOUND PRECEEDING`に対応するのは`-Inf`、 `UNBOUND FOLLOWING`に対応するのは`Inf`となっています。

## 単一テーブルの操作

既に例を見ていますが、`select()`や`mutate()`は`SELECT`句を変更します。

``` r
spark_mtcars %>% 
  select(mpg) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`
FROM `mtcars`
```

``` r
spark_mtcars %>% 
  mutate(mpg2 = mpg * 2) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, `mpg` * 2.0 AS `mpg2`
FROM `mtcars`
```

`filter()`は`WHERE`句に対応します。

``` r
spark_mtcars %>% 
  filter(cyl == 4) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT *
FROM `mtcars`
WHERE (`cyl` = 4.0)
```

`arrange()`は`ORDER BY`句に対応します。

``` r
spark_mtcars %>% 
  arrange(mpg) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT *
FROM `mtcars`
ORDER BY `mpg`
```

`group_by()`は`summarise()`と共に使うことで`GROUP BY`句を生成します。

``` r
spark_mtcars %>% 
  group_by(cyl) %>% 
  summarise(mpg_mean = mean(mpg, na.rm = TRUE)) %>% 
  show_query()
```

```sql:output
<SQL>
SELECT `cyl`, AVG(`mpg`) AS `mpg_mean`
FROM `mtcars`
GROUP BY `cyl`
```

## 複数テーブルの操作

### JOIN

各種の結合は概ね次のように翻訳されます。

| R              | SQL                                                                  |
| -------------- | -------------------------------------------------------------------- |
| `inner_join(x, y)` | `SELECT * FROM x JOIN y ON x.a = y.a`                                |
| `left_join(x, y)`  | `SELECT * FROM x LEFT JOIN y ON x.a = y.a`                           |
| `right_join(x, y)` | `SELECT * FROM x RIGHT JOIN y ON x.a = y.a`                          |
| `full_join(x, y)`  | `SELECT * FROM x FULL JOIN y ON x.a = y.a`                           |
| `semi_join(x, y)`  | `SELECT * FROM x WHERE EXISTS (SELECT 1 FROM y WEHRE x.a = y.a)`     |
| `anti_join(x, y)`  | `SELECT * FROM x WHERE NOT EXISTS (SELECT 1 FROM y WHERE x.a = y.a)` |

動作を確認しておきましょう。2つのテーブルを読み込んでおきます。

``` r
members <- copy_to(sc, band_members)
instruments <- copy_to(sc, band_instruments)
instruments2 <- copy_to(sc, band_instruments2)
```

`members`と`instruments`の2つはいずれも3行2列の小さなテーブルで、`name`という同じ名前の列を1つもっています。そして、`name`の一部は値が重複していますが、一部は異なります。

``` r
members
```

```r:output
# Source: spark<band_members> [?? x 2]
  name  band   
* <chr> <chr>  
1 Mick  Stones 
2 John  Beatles
3 Paul  Beatles
```

``` r
instruments
```

```r:output
# Source: spark<band_instruments> [?? x 2]
  name  plays 
* <chr> <chr> 
1 John  guitar
2 Paul  bass  
3 Keith guitar
```

`instruments2`は`instruments`と中身は同じですが列名が異なります。

``` r
instruments2
```

```r:output
# Source: spark<band_instruments2> [?? x 2]
  artist plays 
* <chr>  <chr> 
1 John   guitar
2 Paul   bass  
3 Keith  guitar
```

#### `inner_join()`

`inner_join()`は共通部分を抽出します。

``` r
inner_join(
  members, instruments,
  by = "name"
  )
```

```r:output
# Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass
```

他のJOINでも同じですが、SQLで`ON`以下に相当する部分は`by
=`で指定します。結合する2つの列名が同じなら上記例のような指定で大丈夫ですが、異なる場合は`by
= c("name" = "artist")`のように`c()`を使って指定します。

``` r
inner_join(
  members, instruments2,
  by = c("name" = "artist")
)
```

```r:output
# Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass
```

同じ列名を結合のキーとする場合は列名指定自体を省略することもできます。

``` r
inner_join(members, instruments)
```

```r:output
Joining, by = "name"
 # Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass
```

#### `left_join()`

`left_join()`は左側のテーブルに存在する項目をすべて残しつつ、右側のテーブルを結合します。右側のテーブルのうち、キー列の項目が存在しない部分はNAで埋められます。

``` r
left_join(
  members, instruments,
  by = "name"
)
```

```r:output
# Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 Mick  Stones  <NA>  
2 John  Beatles guitar
3 Paul  Beatles bass
```

#### `right_join()`

`right_join()`は`left_join()`の逆です。右側のテーブルが温存されます。

``` r
right_join(
  members, instruments,
  by = "name"
)
```

```r:output
# Source: spark<?> [?? x 3]
   name  band    plays 
* <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass  
3 Keith <NA>    guitar
```

#### `full_join()`

`full_join()`は`left_join()`と`right_join()`を組み合わせたようなものです。両方のテーブルのすべての項目が温存されます。

``` r
full_join(
  members, instruments,
  by = "name"
)
```

```r:output
# Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 Keith <NA>    guitar
2 Mick  Stones  <NA>  
3 Paul  Beatles bass  
4 John  Beatles guitar
```

#### `semi_join()`

`semi_join()`は少し分かりにくい結合ですが、キー列の項目が右側のテーブルに存在する左側のテーブルの行を返します。

まずクエリを確認してみます。

``` r
semi_join(
  members, instruments,
  by = "name"
) %>% show_query()
```

```sql:output
<SQL>
SELECT * FROM `band_members` AS `TBL_LEFT`

WHERE EXISTS (
  SELECT 1 FROM `band_instruments` AS `TBL_RIGHT`
  WHERE (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`)
)
```

`WHERE EXISTS`以下にサブクエリが書いてありますが、このサブクエリを実行して行の存在する部分が真、存在しない部分が偽として左側のテーブルのフィルタリングが行われます。`WHERE EXISTS`内部で入れ子になっている`WHERE`句に注目すると理解しやすいでしょう。「キー列の項目が右側のテーブルに存在する」という部分がここで指定されています。このサブクエリは行が返るかどうかだけが重要で値はどうでも良いので`SELECT 1`となっています。

これをどのようなタイミングで使うかですが、例えば手元に何らかの集計、フィルタリングが行われたテーブルがあるとしましょう。

``` r
members_filtered <- members %>% 
  filter(name == "Mick") %>% 
  mutate(some_value = 1) %>% 
  collect()
members_filtered
```

```r:output
# A tibble: 1 x 3
  name  band   some_value
  <chr> <chr>       <dbl>
1 Mick  Stones          1
```

この集計後の行に対して対応する元のテーブルの行を抽出したい場合に、`semi_join`が利用できます。

``` r
semi_join(members, members_filtered, by = c("name"), copy = TRUE)
```

```r:output
# Source: spark<?> [?? x 2]
  name  band  
* <chr> <chr> 
1 Mick  Stones
```

この挙動は2つのテーブルの共通部分を抽出する`intersect()`とよく似ていますが、抽出に用いるのはキーとして指定した列だけという点で異なります。また、抽出後のテーブルに右辺のテーブルは含まれません。これは右辺のテーブルのキーで左辺のテーブルをフィルタリングしているとも言えるので、semi joinは後述のanti joinと合わせてfiltering joinと呼ばれています。

なお、`copy = TRUE`を指定すると、第一引数と同じ場所に第二引数のテーブルをコピーした上でjoinが実行されます。第一引数がデータベース上のテーブルであればテンポラリテーブルが作成されます。コピーをするので実行コストは上がりますが、この仕組みを利用すれば異なるデータベース間、ローカルとリモート間のテーブルであってもjoinが可能です。

#### `anti_join()`

`anti_join()`は`semi_join()`の逆です。左辺のテーブルのうち`semi_join()`で選択されないものが返ります。クエリとしては`EXISTS`が`NOT EXISTS`に変わっているだけです。

``` r
anti_join(
  members, instruments,
  by = "name"
) %>% show_query()
```

```sql:output
<SQL>
ELECT * FROM `band_members` AS `TBL_LEFT`

WHERE NOT EXISTS (
  SELECT 1 FROM `band_instruments` AS `TBL_RIGHT`
  WHERE (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`)
)
```

`semi_join()`が`intersect()`と似ているように、`anti_join()`は`setdiff()`と似ています。

### 集合演算

集合演算は次のように翻訳されます。

| R                 | SQL                                         |
| ----------------- | ------------------------------------------- |
| `intersect(x, y)` | `SELECT * FROM x INTERSECT SELECT * FROM y` |
| `union(x, y)`     | `SELECT * FROM x UNION SELECT * FROM y`     |
| `union_all(x, y)` | `SELECT * FROM x UNION ALL SELECT * FROM y` |
| `setdiff(x, y)`   | `SELECT * FROM x EXCEPT SELECT * FROM y`    |

`intersect()`は共通部分、`union()`は和集合、`setdiff()`は差集合を計算します。`union_all()`は`union()`に似ていますが重複を除外しません。`rbind()`と同じようなものと考えると良いでしょう。

集合演算の翻訳は単純なように見えますが、大きな違いがあります。`dplyr`は列名を考慮するのです。

まずSQLの例を確認しておきましょう。SQLで集合演算を行う場合、列名はそれほど重要ではありません。例えば列名が違う2つのテーブルでもそのまま結合できます。

``` sql
SELECT * FROM band_instruments
UNION
SELECT * FROM band_members
```

| name  | plays   |
| :---- | :------ |
| Keith | guitar  |
| Mick  | Stones  |
| John  | guitar  |
| John  | Beatles |
| Paul  | Beatles |
| Paul  | bass    |

というよりそもそも列名なんか見ていませんから、同じ列名があっても無視して列の順序どおりに結合します。

``` sql
SELECT name, plays FROM band_instruments
UNION
SELECT plays, name FROM band_instruments
```

| name   | plays  |
| :----- | :----- |
| Keith  | guitar |
| guitar | Keith  |
| John   | guitar |
| guitar | John   |
| Paul   | bass   |
| bass   | Paul   |

一方、`dplyr`の場合は列名を重視し、丁寧に扱います。例えば異なる列名を含むテーブルを`union()`で結合すると、`full_join()`のような結果が得られます。

``` r
union(instruments, members)
```

```r:output
# Source: spark<?> [?? x 3]
  name  plays  band   
* <chr> <chr>  <chr>  
1 Keith guitar <NA>   
2 Mick  <NA>   Stones 
3 John  guitar <NA>   
4 John  <NA>   Beatles
5 Paul  <NA>   Beatles
6 Paul  bass   <NA>
```

クエリを確認するとわかりますが、結合相手のテーブルにしか存在しない列は生成され、列の順序は揃えられてから結合が行われています。

``` r
union(instruments, members) %>% show_query()
```

```sql:output
<SQL>
(SELECT `name`, `plays`, NULL AS `band`
FROM (SELECT *
FROM `band_instruments`) `ifrioiyfiu`)
UNION
(SELECT `name`, NULL AS `plays`, `band`
FROM (SELECT *
FROM `band_members`) `tpievfioxx`)
```

つまり、列の順序が違うテーブルでも、列名を考慮して結合が行われます。

``` r
union(
  instruments %>% select(name, plays),
  instruments %>% select(plays, name)
)
```

```r:output
# Source: spark<?> [?? x 2]
  name  plays 
* <chr> <chr> 
1 Keith guitar
2 John  guitar
3 Paul  bass
```

集合演算は書いた通りに動くことを重視するSQLと、書いてある文の意味を重視する`dplyr`の思想の違いが現れている例とも言えるでしょう。

## 参考

  - [SQL
    translation](https://cran.r-project.org/web/packages/dbplyr/vignettes/sql-translation.html)