dplyrで書きたい！

Rユーザーの中にはdplyrパッケージが好きな方も多いのではないでしょうか。

dplyrといえばこんな感じですよね。

R

library(dplyr, warn.conflicts = FALSE)

mtcars |>
  filter(cyl > 6) |>
  select(cyl, mpg) |>
  mutate(mpg_int = mpg |> round(0)) |>
  slice(2:4) |>
  as_tibble() # 表示を整えるためにtibbleに変換
#> # A tibble: 3 × 3
#>     cyl   mpg mpg_int
#>   <dbl> <dbl>   <dbl>
#> 1     8  14.3      14
#> 2     8  16.4      16
#> 3     8  17.3      17

このようにdplyrはパイプ演算子を使い、データに対する処理を上から順番に書いていくことができます。分かりやすいですよね。

同じ処理をSQLで書くと以下のようになります。

SQL

SELECT
  cyl,
  mpg,
  ROUND(mpg, 0) AS mpg_int
FROM
  mtcars
WHERE
  cyl > 6
LIMIT
  3 OFFSET 1

SELECT、FROM、WHERE、LIMITという順番を守る必要があるため、dplyrよりもとっつきづらいかも知れません。（いくつかのDBでは構文を拡張子FROMを最初に持って来ることを許容していたりもします）

DBからデータを引っ張ってくるとき、私のように「SQL難しい……dplyrで書きたい……」と思われるような方もいらっしゃると思います。

そのような場合はまずdbplyrをお試しいただくのが良いと思いますが、もう一つ知っておいて損はないと思えるプロジェクトがPRQLです。

PRQLで書く！

PRQLのPはPipelinedのPとされています。PRQLのパイプ演算子は|もしくは改行です。

例えば先ほどのクエリはPRQLでは以下のように書けます。もちろんクエリはfromで始まります。

PRQL

from mtcars
filter cyl > 6
select [cyl, mpg]
derive [mpg_int = (mpg | round 0)]
take 2..4

いかがでしょう？
mutateとderive、sliceとtakeや括弧の使い方など細かな違いはありますが、dplyrにそっくりではないでしょうか？

PRQLは今年2022年に始まった非常に若いプロジェクトで、現在メインで開発されているのはPRQLをSQLにコンパイルする機能です。
ブラウザ上やVSCode拡張機能で実行できます。

R上でPRQLを使う

prqlrというRパッケージを作ってみましたので、R上でPRQLを使ってdplyrとの比較も簡単に行えます！

先ほどの例は本当にdplyrと同じ結果になるのか、sqliteにmtcarsを読み込んでクエリを実行してみましょう。

R

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)

"
from mtcars
filter cyl > 6
select [cyl, mpg]
derive [mpg_int = (mpg | round 0)]
take 2..4
" |>
  prqlr::prql_to_sql() |>
  dbGetQuery(con, statement = _) |>
  as_tibble() # 表示を整えるためにtibbleに変換
#> # A tibble: 3 × 3
#>     cyl   mpg mpg_int
#>   <dbl> <dbl>   <dbl>
#> 1     8  14.3      14
#> 2     8  16.4      16
#> 3     8  17.3      17

確かに同じ結果になりました！

結論

SQLを書く必要があるとき、dplyr感覚でPRQLで書いたものをSQLに変換すると楽できるかもしれません！