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

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

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

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

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

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

q$query

結果

<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

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

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

q$query

結果

<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

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

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

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 でデータベースを扱うときの情報をそろそろまとめておく