dplyr で DB にクエリを投げる時に CASE 式を使いたい #rstatsj

dplyr でデータベースにクエリを投げるときに CASE 式を使いたくなりましたが、はて、どうやるんだろうと悩みました。

まずは検証用の DB を用意します。
`nycflights13` パッケージの `flights` データを使いましょう。

```r:R
library(dplyr)
library(nycflights13) # flights データを使用
  
db <- src_sqlite("my_db.sqlite3", create = TRUE)
copy_nycflights13(db)
show(db)
```

```:結果
src:  sqlite 3.8.6 [my_db.sqlite3]
tbls: airlines, airports, flights, planes, sqlite_stat1, weather
```

```r:R
flights_tbl <- tbl(db, "flights")
glimpse(flights_tbl)
```

```:結果
Observations: 336776
Variables:
$ year      (int) 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 20...
$ month     (int) 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,...
$ day       (int) 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,...
...
```

`flights` データは飛行機の発着データですが、今回は `month` カラムのみを使います。
`month` カラムには 1～12 の数字が入りますが、これを上半期(first_half)と下半期(second_half)に変換したいとします。

R では通常 `ifelse()` を使って行います。

```r:R
month <- 1:12
half_year <- ifelse(month <= 6, "first_half", "second_half")
print(half_year)
```

```:結果
 [1] "first_half"  "first_half"  "first_half"  "first_half"  "first_half"  "first_half" 
 [7] "second_half" "second_half" "second_half" "second_half" "second_half" "second_half"
```

そこで、dplyr でも `ifelse()` 関数を使って書いてみましょう。

```r:R
q <- flights_tbl %>% 
  select(month) %>%
  mutate(half_year = ifelse(month <= 6, "first_half", "second_half"))
data <- q %>% collect
```

```:結果
Error in sqliteSendQuery(conn, statement) : 
  error in statement: no such function: IFELSE
```

エラーが出ました。`IFELSE` という関数は無いという内容のようです。
クエリを見てみましょう。

```r:R
q$query
```

```sql:結果
<Query> SELECT "month" AS "month", IFELSE("month" <= 6.0, 'first_half', 'second_half') AS "half_year"
FROM "flights"
<SQLiteConnection>
```

`ifelse()` の部分がそのまま SQL に変換されています。これでは通らないはずですよね。

### 解答

解答は下記の Stack Overflow に載っていました

- [r - Summarize with conditions in dplyr - Stack Overflow](http://stackoverflow.com/questions/23528862/summarize-with-conditions-in-dplyr)

> Try using if(A=="foo") B else 0 - dplyr will try to convert that to a SQL case statement, which might work for you. –  hadley

なんと、神 Hadley から回答がついていました。
dplyr で CASE 式を使うには if 文を使えとのことです。

やってみましょう。

```r:R
q <- flights_tbl %>% 
  select(month) %>%
  mutate(half_year = if(month <= 6) "first_half" else "second_half")
data <- q %>% collect 
data %>% sample_n(6) %>% arrange(month)
```

```:結果
  month   half_year
1     1  first_half
2     2  first_half
3     6  first_half
4     7 second_half
5     9 second_half
6    12 second_half
```

おー、できました！
クエリを見てみましょう。

```r:R
q$query
```

```sql:結果
<Query> SELECT "month" AS "month", CASE WHEN "month" <= 6.0 THEN 'first_half' ELSE 'second_half' END AS "half_year"
FROM "flights"
<SQLiteConnection>
```

ちゃんと CASE 式に変換されています！

## 注意点

ところで、R では `if()` は長さ1のベクトルしか受け取ることができないので、次のように書くと警告が出ます。

```r:R
month <- 1:12
half_year <- if(month <= 6) "first_half" else "second_half"
```

```:結果
Warning message:
In if (month <= 6) "first_half" else "second_half" :
  the condition has length > 1 and only the first element will be used
```

この警告は、`if()` は長さ1のベクトルしか評価できないので、`month` ベクトルの最初の値しか評価しないよ、ということです。

したがって、次のように書くと、おかしなことが起こります。

```r:R
q <- flights_tbl %>% select(month)
data <- q %>% collect %>% 
  mutate(half_year = if(month <= 6) "first_half" else "second_half")
data %>% sample_n(6) %>% arrange(month)
```

```:結果
  month  half_year
1     1 first_half
2     4 first_half
3     5 first_half
4     8 first_half
5     9 first_half
6    10 first_half
```

これは、`half_year` に最初の一個の評価値が入るため、すべて同じ値になってしまっているのです。

dplyr でこれを書きたいときは、今度は `ifelse()` を使います。

```r:R
q <- flights_tbl %>% select(month)
data <- q %>% collect %>% 
  mutate(half_year = ifelse(month <= 6, "first_half", "second_half"))
data %>% sample_n(6) %>% arrange(month)
```

```:結果
  month   half_year
1     4  first_half
2     5  first_half
3     6  first_half
4     9 second_half
5    10 second_half
6    10 second_half
```

おー、できました！

どうやら、クエリの中では `if()`、外では `ifelse()` を使い分けなければいけないみたいです。

はまりどころかと思います。

以上です。

### 追記 2016/05/13

> dplyr 次バージョンで `if_else()` という関数が追加されるようで、これは data.frame と DB を統一的に扱えるようです。
> http://notchained.hatenablog.com/entry/2016/04/09/123059

### 関連

- [dplyr でデータベースを扱うときの情報をそろそろまとめておく](http://qiita.com/hoxo_m/items/341f499eb4117eec41df)