dplyr で書いて SQL を出力する | コネクトデータ（Connecto Data）

1 dplyr と RDB
2 dbplyr による SQL への翻訳
3 例
4 注意点

dplyr と RDB

dplyr はパイプ演算子を利用することでデータフレームの操作を容易に行うことができます。

dplyr の便利な点は、バックエンドがデータフレームのみではなく、 RDB に対して同じ記述で SQL を実行することができることです。すなわち、 dplyr で記述した処理は SQL に変換され、その SQL がデータベースサーバー上で実行され、結果をローカルに取得します。

dbplyr による SQL への翻訳

デバッグ目的やロギング目的で、 dplyr で記述した処理がどのような SQL に変換されているかを知りたい場合があります。このような場合、 dbplyr パッケージの sql_render 関数を利用します。

library(dplyr)
library(dbplyr)
library(DBI)

con <- dbConnect(...)
query <- tbl(con, "mytable") %>%
  ...
print(sql_render(query))

例

iris を SQLite で処理する例を示します。

library(dplyr)
library(dbplyr)
library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)

query <- tbl(con, "iris") %>%
  group_by(Species) %>%
    summarise_all(funs(avg = mean))
print(sql_render(query))

<SQL> SELECT `Species`, AVG(`Sepal.Length`) AS `Sepal.Length_avg`, AVG(`Sepal.Width`) AS `Sepal.Width_avg`, AVG(`Petal.Length`) AS `Petal.Length_avg`, AVG(`Petal.Width`) AS `Petal.Width_avg`
FROM `iris`
GROUP BY `Species`
 警告メッセージ: 
1: Missing values are always removed in SQL.
Use `AVG(x, na.rm = TRUE)` to silence this warning 
2: Missing values are always removed in SQL.
Use `AVG(x, na.rm = TRUE)` to silence this warning 
3: Missing values are always removed in SQL.
Use `AVG(x, na.rm = TRUE)` to silence this warning 
4: Missing values are always removed in SQL.
Use `AVG(x, na.rm = TRUE)` to silence this warning

SQLite での SQL 文が出力されました。 SQL の学習にもりようできるかもしれませんね。

注意点

RDBMS がサポートしていない関数などは、そのまま翻訳される場合があります。通常の dplyr のパイプ処理であれば概ね問題ありませんが、凝ったことをやると出力された SQL がそのまま実行できる保証はありません。