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 がそのまま実行できる保証はありません。