R で簡単データベース操作！ - dplyr は SQL にどう変換されるのか？実例で解説

dplyr でのデータベース操作が、どのように SQL クエリに変換されるかを実例でわかりやすく解説！ SQL と R を両方活用したい方におすすめの内容です。

前回の記事:

R で簡単データベース操作！ - dplyr で SQL クエリを自動生成する方法

はじめに

前回は、dplyr を用いたテーブル操作と SQL クエリの自動生成について解説しました。
今回は、dplyr の主要な操作が SQL にどのように変換されるのか、具体例を交えて説明します。
これにより、dplyr コードと SQL クエリの対応関係を深く理解し、R と SQL を効果的に組み合わせて活用できるようになります。

本記事で紹介するコードは、すべてコピー＆ペーストでそのまま実行できます。

データベースへの接続とデータの準備

データベースは前回に続き DuckDB を使用します。DuckDB のメリットについては、以下の記事を参考にしてください。

DuckDB を使用するメリット

まず、デモ用の売上データ (df_sales) と店舗マスタ (df_master) を作成し、DuckDB のインメモリデータベースに登録します。
(未インストールのパッケージは install.packages() でインストールしてください。)

インメモリデータベースのため、セッション終了時にデータは自動で削除されます。

library(DBI)
library(dplyr)
library(dbplyr)
library(duckdb)
library(tibble)

df_sales = tribble(
  ~store, ~month, ~sales, ~profit, 
  "S001",  4L,     150,   30, 
  "S001",  5L,     170,   34, 
  "S001",  6L,     140,   27, 
  "S001",  7L,     160,   32, 
  "S002",  4L,     NA,    28, 
  "S002",  5L,     160,   31, 
  "S002",  6L,     130,   27, 
  "S002",  7L,     150,   28
)

df_master = tribble(
  ~store, ~name,    ~pref, 
  "S001", "storeA", "Tokyo", 
  "S002", "storeB", "Osaka", 
  "S003", "storeC", "Kanagawa", 
  "S004", "storeD", "Fukuoka"
)

# DuckDB に接続 (一時データベース)
con = DBI::dbConnect(duckdb::duckdb())

# テーブルとしてデータベースに登録
DBI::dbWriteTable(
  con, "store_sales", df_sales, overwrite = TRUE
)
DBI::dbWriteTable(
  con, "store_master", df_master, overwrite = TRUE
)

# テーブルを dplyr で参照
db_sales = tbl(con, "store_sales")
db_master = tbl(con, "store_master")

SQL 変換に関する補足

最初に補足ですが、dbplyr による SQL 変換が常に最適な SQL を生成するわけではありません。冗長的な SQLクエリに変換されるケースもあります。

例えば、以下の R コードは、

db_sales %>% filter(!is.na(sales))

次の SQLクエリに変換されます。

SELECT store_sales.*
FROM store_sales
WHERE (NOT((sales IS NULL)))

この SQL は論理的には正しいですが、
WHERE (NOT((sales IS NULL))) の部分は
WHERE sales IS NOT NULL と書いた方が簡潔で可読性が高くなります。
このような点も考慮しながら読み進めていただければと思います。

dplyr 操作全体と操作内の式について

dbplyr による SQL 変換は、次の 2 つの側面 に分けられます。それぞれの観点から解説します。

dplyr 操作全体の SQL 変換
dplyr 操作内の個々の式の SQL 変換

例えば、以下の R コードを考えます。

db_sales %>% filter(!is.na(sales))

この場合、

filter(, !is.na(sales)) → 「dplyr 操作全体」 に該当
!is.na(sales) → 「dplyr 操作内の式」 に該当
! 演算子、is.na(sales) → 「dplyr 操作内の個々の式」 に該当

このように、dplyr の処理は 「操作全体」 と 「操作内の式」 に分けて考えることができます。

SQL 変換の基となる SELECT 文

dbplyr は純粋なテーブルに対して次のような SELECT 文を生成します。

db_sales %>% show_query()

SELECT *
FROM store_sales

すべての dplyr 操作は、上記のような「全ての列を選択する SELECT 文」を基にして SQL を生成します。

以降では、次の3点を添えて SQL 変換について解説します。

dplyr によるテーブル操作の R コード
自動生成される SQLクエリ
実行結果として得られるテーブルの内容

dplyr 操作全体の SQL 変換

このセクションでは、dplyr 操作全体がどのように SQL に変換されるかについて、代表的な操作を例に挙げて解説します。

単一テーブルの操作

列に影響を与える操作

`select()`、`rename()`、`relocate()`

select() は SQL の SELECT 句を修正します。

db_sales %>% 
  select(store, sales) %>% 
  show_query()

SELECT store, sales
FROM store_sales

  store sales
  <chr> <dbl>
1 S001    150
2 S001    170
3 S001    140
...

rename() についても同様に変換されます。

db_sales %>% 
  rename(store_code = store) %>% 
  show_query()

SELECT store AS store_code, "month", sales, profit
FROM store_sales

  store_code month sales profit
  <chr>      <int> <dbl>  <dbl>
1 S001           4   150     30
2 S001           5   170     34
3 S001           6   140     27
...

relocate() についても同様に変換されます。

db_sales %>% 
  relocate(profit, sales, .after = store) %>% 
  show_query()

SELECT store, profit, sales, "month"
FROM store_sales

  store profit sales month
  <chr>  <dbl> <dbl> <int>
1 S001      30   150     4
2 S001      34   170     5
3 S001      27   140     6
...

ここで、"month" がダブルクォートで括られているのは、これが DuckDB の予約語だからです。

`mutate()`

mutate() は SQL の SELECT 句を修正します。

db_sales %>% 
  mutate(
    margin = 100 * profit / sales, 
    .keep = "unused"
  ) %>% 
  show_query()

SELECT store, "month", (100.0 * profit) / sales AS margin
FROM store_sales

  store month margin
  <chr> <int>  <dbl>
1 S001      4   20  
2 S001      5   20  
3 S001      6   19.3
...

行に影響を与える操作

`filter()`

filter() は SQL の WHERE 句を生成します。

db_sales %>% 
  filter(month == 4L & profit >= 30) %>% 
  show_query()

SELECT store_sales.*
FROM store_sales
WHERE ("month" = 4) AND (profit >= 30.0)

  store month sales profit
  <chr> <int> <dbl>  <dbl>
1 S001      4   150     30

`arrange()`

arrange() は SQL の ORDER BY 句を生成します。

db_sales %>% 
  arrange(month, desc(profit)) %>% 
  show_query()

SELECT store_sales.*
FROM store_sales
ORDER BY "month", profit DESC

  store month sales profit
  <chr> <int> <dbl>  <dbl>
1 S001      4   150     30
2 S002      4    NA     28
3 S001      5   170     34
4 S002      5   160     31
...

`head()`

head() は SQL の LIMIT 句を生成します。
ただし、一部のデータベース (SQL Server など) では、TOP 句や FETCH FIRST 句が生成される場合があります。

db_sales %>% 
  head(3) %>% 
  show_query()

SELECT store_sales.*
FROM store_sales
LIMIT 3

  store month sales profit
  <chr> <int> <dbl>  <dbl>
1 S001      4   150     30
2 S001      5   170     34
3 S001      6   140     27

`distinct()`

distinct() は SQL の DISTINCT 修飾子を生成します。

db_sales %>% 
  distinct() %>% 
  show_query()

SELECT DISTINCT store_sales.*
FROM store_sales

  store month sales profit
  <chr> <int> <dbl>  <dbl>
1 S002      6   130     27
2 S001      4   150     30
3 S001      7   160     32
...

列を指定した場合の例:

db_sales %>% 
  distinct(store) %>% 
  show_query()

SELECT DISTINCT store
FROM store_sales

  store
  <chr>
1 S002 
2 S001 

グループ化と集約の操作

`summarise()`

summarise() は mean() などの集約関数と合わせて SELECT 句を修正します。

db_sales %>% 
  summarise(avg_profit = mean(profit)) %>% 
  show_query()

SELECT AVG(profit) AS avg_profit
FROM store_sales

  avg_profit
       <dbl>
1       29.6

`group_by()` + `summarise()`

また、summarise() は group_by() と合わせて GROUP BY 句を生成します。

db_sales %>% 
  group_by(store) %>% 
  summarise(avg_profit = mean(profit)) %>% 
  show_query()

SELECT store, AVG(profit) AS avg_profit
FROM store_sales
GROUP BY store

  store avg_profit
  <chr>      <dbl>
1 S002        28.5
2 S001        30.8

`group_by()` + `summarise()` + `filter()`

さらに、集約後の filter() と合わせて HAVING 句を生成します。

db_sales %>% 
  group_by(store) %>% 
  summarise(avg_profit = mean(profit)) %>% 
  filter(avg_profit > 30) %>% 
  show_query()

SELECT store, AVG(profit) AS avg_profit
FROM store_sales
GROUP BY store
HAVING (AVG(profit) > 30.0)

  store avg_profit
  <chr>      <dbl>
1 S001        30.8

2つのテーブルによる操作

テーブルの結合

`inner_join()`、`left_join()`、`right_join()`

inner_join() は INNER JOIN 句を生成します。

db_sales %>% 
  inner_join(db_master, by = "store") %>% 
  show_query()

SELECT store_sales.*, "name", pref
FROM store_sales
INNER JOIN store_master
  ON (store_sales.store = store_master.store)

  store month sales profit name   pref 
  <chr> <int> <dbl>  <dbl> <chr>  <chr>
1 S001      4   150     30 storeA Tokyo
2 S001      5   170     34 storeA Tokyo
3 S001      6   140     27 storeA Tokyo
4 S001      7   160     32 storeA Tokyo
5 S002      4    NA     28 storeB Osaka
...

left_join()、right_join() についても同様です。

`full_join()`

full_join() は FULL JOIN 句を生成します。

db_sales %>% 
  full_join(db_master, by = "store") %>% 
  show_query()

SELECT
  COALESCE(store_sales.store, store_master.store) AS store,
  "month",
  sales,
  profit,
  "name",
  pref
FROM store_sales
FULL JOIN store_master
  ON (store_sales.store = store_master.store)

   store month sales profit name   pref    
   <chr> <int> <dbl>  <dbl> <chr>  <chr>   
S001      4   150     30 storeA Tokyo   
S001      5   170     34 storeA Tokyo   
S001      6   140     27 storeA Tokyo   
S001      7   160     32 storeA Tokyo   
S002      4    NA     28 storeB Osaka   
S002      5   160     31 storeB Osaka   
S002      6   130     27 storeB Osaka   
S002      7   150     28 storeB Osaka   
S004     NA    NA     NA storeD Fukuoka 
S003     NA    NA     NA storeC Kanagawa

`cross_join()`

cross_join() は CROSS JOIN 句を生成します。

db_master %>% 
  select(store) %>% 
  cross_join(db_sales %>% select(month)) %>% 
  show_query()

SELECT store_master.store AS store, "month"
FROM store_master
CROSS JOIN store_sales

   store month
   <chr> <int>
 1 S001      4
 2 S001      5
 3 S001      6
 4 S001      7
 ...

`semi_join()` (準結合)

semi_join() は WHERE EXISTS を生成します。

db_master %>% 
  semi_join(db_sales, by = "store") %>% 
  show_query()

SELECT store_master.*
FROM store_master
WHERE EXISTS (
  SELECT 1 FROM store_sales
  WHERE (store_master.store = store_sales.store)
)

  store name   pref 
  <chr> <chr>  <chr>
1 S001  storeA Tokyo
2 S002  storeB Osaka

`anti_join()` (アンチ結合)

anti_join() は WHERE NOT EXISTS を生成します。

db_master %>% 
  anti_join(db_sales, by = "store") %>% 
  show_query()

SELECT store_master.*
FROM store_master
WHERE NOT EXISTS (
  SELECT 1 FROM store_sales
  WHERE (store_master.store = store_sales.store)
)

  store name   pref    
  <chr> <chr>  <chr>   
1 S003  storeC Kanagawa
2 S004  storeD Fukuoka 

テーブルの集合演算

`intersect()`

intersect() は INTERSECT 演算子を生成します。

db_sales %>% 
  select(store) %>% 
  intersect(db_master %>% select(store)) %>% 
  show_query()

(
  SELECT store
  FROM store_sales
)
INTERSECT
(
  SELECT store
  FROM store_master
)

  store
  <chr>
1 S002 
2 S001 

`union()`

union() は UNION 演算子を生成します。

db_sales %>% 
  select(store) %>% 
  union(db_master %>% select(store)) %>% 
  show_query()

SELECT store
FROM store_sales

UNION

SELECT store
FROM store_master

  store
  <chr>
1 S001 
2 S003 
3 S002 
4 S004 

`union_all()`

union_all() は UNION ALL 演算子を生成します。

db_sales %>% 
  select(store) %>% 
  union_all(db_master %>% select(store)) %>% 
  show_query()

SELECT store
FROM store_sales

UNION ALL

SELECT store
FROM store_master

   store
   <chr>
 1 S001 
 2 S001 
 3 S001 
 4 S001 
 5 S002 
 ...

`setdiff()`

setdiff() は EXCEPT 演算子を生成します。

db_master %>% 
  select(store) %>% 
  setdiff(db_sales %>% select(store)) %>% 
  show_query()

(
  SELECT store
  FROM store_master
)
EXCEPT
(
  SELECT store
  FROM store_sales
)

  store
  <chr>
1 S004 
2 S003 

その他のテーブル操作

これまでに紹介した代表的なテーブル操作の関数に加えて、count(), slice_min(), slice_max(), replace_na(), pivot_longer() などの関数もあります。
これらの関数は、前述の SQL の句や演算子、SQL 関数を組み合わせて変換されます。

以下に、count() と pivot_longer() を使用した場合の変換例を示します。

`count()`

count() は次のように、SQL の集約関数 COUNT() を用いて SELECT 句を修正し、GROUP BY 句を生成します。

db_sales %>% 
  count(store, name = "n_month") %>% 
  show_query()

SELECT store, COUNT(*) AS n_month
FROM store_sales
GROUP BY store

  store n_month
  <chr>   <dbl>
1 S001        4
2 S002        4

`pivot_longer()`

pivot_longer() は次のように UNION ALL 演算子を用いたクエリを生成します。

db_sales %>% 
  tidyr::pivot_longer(
    -c(store, month), names_to = "name", values_to = "amount"
  ) %>% 
  show_query()

SELECT store, "month", 'sales' AS "name", sales AS amount
FROM store_sales

UNION ALL

SELECT store, "month", 'profit' AS "name", profit AS amount
FROM store_sales

   store month name   amount
   <chr> <int> <chr>   <dbl>
 1 S001      4 sales     150
 2 S001      5 sales     170
 3 S001      6 sales     140
 4 S001      7 sales     160
 5 S002      4 sales      NA
 ...

dplyr 操作内の式の SQL 変換

このセクションでは、dplyr 操作内で使用する個々の式がどのように SQL に変換されるかに焦点を当てて解説します。
主に以下の2つの観点を元に、代表的な演算子や関数について例を挙げて説明します。

dplyr が認識できる式
dplyr が認識できない式

dplyr が認識できる式

基本的な演算子

算術演算子 (`+`、`-`、`*`、`/`、`^`)

dplyr の算術演算子は、SQL に変換される際にそれぞれ対応する式や関数にマッピングされます。

db_sales %>% 
  mutate(
    v1 = sales + profit, 
    v2 = 100 * (sales - profit) / sales, 
    v3 = profit ^ 2L, 
    .keep = "none"
  ) %>% 
  show_query()

SELECT
  sales + profit AS v1,
  (100.0 * (sales - profit)) / sales AS v2,
  POW(profit, 2) AS v3
FROM store_sales

     v1    v2    v3
  <dbl> <dbl> <dbl>
1   180  80     900
2   204  80    1156
3   167  80.7   729
...

比較演算子、論理演算子

dplyr の比較演算子 (==、<、%in% など)、論理演算子 (&、|、! など) は、SQL に変換される際にそれぞれ対応する式にマッピングされます。

db_sales %>% 
  mutate(
    v1 = (sales == 150), 
    v2 = (!(sales > 150)), 
    v3 = (sales != 150 & profit >= 30), 
    v4 = (sales < 150 | profit <= 30), 
    v5 = (store %in% c("S001", "S003")), 
    .keep = "used"
  ) %>% 
  show_query()

SELECT
  store,
  sales,
  profit,
  (sales = 150.0) AS v1,
  (NOT((sales > 150.0))) AS v2,
  (sales != 150.0 AND profit >= 30.0) AS v3,
  (sales < 150.0 OR profit <= 30.0) AS v4,
  (store IN ('S001', 'S003')) AS v5
FROM store_sales

  store sales profit v1    v2    v3    v4    v5   
  <chr> <dbl>  <dbl> <lgl> <lgl> <lgl> <lgl> <lgl>
1 S001    150     30 TRUE  TRUE  FALSE TRUE  TRUE 
2 S001    170     34 FALSE FALSE TRUE  FALSE TRUE 
3 S001    140     27 FALSE TRUE  FALSE TRUE  TRUE 
4 S001    160     32 FALSE FALSE TRUE  FALSE TRUE 
5 S002     NA     28 NA    NA    FALSE TRUE  FALSE
...

基本的な関数

数学関数、数値の丸め関数

一般的な数学関数、数値の丸め関数は、SQL に変換される際にそれぞれ対応する関数にマッピングされます。

db_sales %>% 
  mutate(
    v1 = log(profit), 
    v2 = sqrt(profit), 
    v3 = sin(profit), 
    v4 = floor(sales / profit), 
    .keep = "none"
  ) %>% 
  show_query()

SELECT
  LN(profit) AS v1,
  SQRT(profit) AS v2,
  SIN(profit) AS v3,
  FLOOR(sales / profit) AS v4
FROM store_sales

     v1    v2     v3    v4
  <dbl> <dbl>  <dbl> <dbl>
1  3.40  5.48 -0.988     5
2  3.53  5.83  0.529     5
3  3.30  5.20  0.956     5
4  3.47  5.66  0.551     5
5  3.33  5.29  0.271    NA
...

キャスト関数

キャスト関数は、SQL に変換される際にそれぞれ対応する関数にマッピングされます。
DuckDB では、次の例のように CAST() を用いた式に変換されます。

db_sales %>% 
  mutate(
    v1 = as.integer(profit), 
    v2 = as.numeric(month), 
    v3 = as.double(month), 
    v4 = as.character(month), 
    v5 = as.Date("2025-04-01"), 
    .keep = "used"
  ) %>% 
  show_query()

SELECT
  "month",
  profit,
  CAST(profit AS INTEGER) AS v1,
  CAST("month" AS NUMERIC) AS v2,
  CAST("month" AS NUMERIC) AS v3,
  CAST("month" AS TEXT) AS v4,
  CAST('2025-04-01' AS DATE) AS v5
FROM store_sales

  month profit    v1    v2    v3 v4    v5        
  <int>  <dbl> <int> <dbl> <dbl> <chr> <date>    
1     4     30    30     4     4 4     2025-04-01
2     5     34    34     5     5 5     2025-04-01
3     6     27    27     6     6 6     2025-04-01
...

文字列関数

基本的な文字列関数は次の例のように SQL に変換できます。
stringr パッケージの一部の関数にも対応しています。

db_master %>% 
  mutate(
    len = nchar(pref), 
    upp = toupper(pref), 
    sub = substr(name, 6, 6), 
    p = paste(name, pref, sep = "-"), 
    .keep = "used"
  ) %>% 
  show_query()

SELECT
  "name",
  pref,
  LENGTH(pref) AS len,
  UPPER(pref) AS upp,
  SUBSTR("name", 6, 1) AS sub,
  CONCAT_WS('-', "name", pref) AS p
FROM store_master

  name   pref       len upp      sub   p              
  <chr>  <chr>    <dbl> <chr>    <chr> <chr>          
1 storeA Tokyo        5 TOKYO    A     storeA-Tokyo   
2 storeB Osaka        5 OSAKA    B     storeB-Osaka   
3 storeC Kanagawa     8 KANAGAWA C     storeC-Kanagawa
4 storeD Fukuoka      7 FUKUOKA  D     storeD-Fukuoka 

日付関数

基本的な日付関数は次の例のように SQL に変換できます。
lubridate パッケージの一部の関数にも対応しています。

db_master %>% 
  mutate(
    ymd = lubridate::as_date("2025-04-01"), 
    .keep = "none"
  ) %>% 
  head(1) %>% 
  mutate(
    month = lubridate::month(ymd), 
    add = ymd + lubridate::days(7L), 
    .keep = "used"
  ) %>% 
  show_query(cte = TRUE)

WITH q01 AS (
  SELECT CAST('2025-04-01' AS DATE) AS ymd
  FROM store_master
  LIMIT 1
)
SELECT
  q01.*,
  EXTRACT(MONTH FROM ymd) AS "month",
  ymd + TO_DAYS(CAST(7 AS INTEGER)) AS "add"
FROM q01

  ymd        month add                
  <date>     <dbl> <dttm>             
1 2025-04-01     4 2025-04-08 00:00:00

パターンマッチング

stringr::str_detect()、grepl() は filter() との組み合わせで、WHERE 句によるパターンマッチングを実行する式を生成します。

db_master %>% 
  filter(
    stringr::str_detect(pref, "ka$")
  ) %>% 
  show_query()

SELECT store_master.*
FROM store_master
WHERE (REGEXP_MATCHES(pref, 'ka$'))

  store name   pref   
  <chr> <chr>  <chr>  
1 S002  storeB Osaka  
2 S004  storeD Fukuoka

`is.na()`

is.na() は IS NULL 句を生成します。

db_sales %>% 
  filter(is.na(sales)) %>% 
  show_query()

SELECT store_sales.*
FROM store_sales
WHERE ((sales IS NULL))

  store month sales profit
  <chr> <int> <dbl>  <dbl>
1 S002      4    NA     28

`if_else()`

if_else() は CASE 式を生成します。

db_sales %>% 
  mutate(
    profit_size = 
      if_else(sales > 150, "big", "small", "none"), 
    .keep = "used"
  ) %>% 
  show_query()

1
2
3
4
5
6
7
8
SELECT
  sales,
  CASE 
    WHEN (sales > 150.0) THEN 'big' 
    WHEN NOT (sales > 150.0) THEN 'small' 
    WHEN ((sales > 150.0) IS NULL) THEN 'none' 
  END AS profit_size
FROM store_sales

  sales profit_size
  <dbl> <chr>      
1   150 small      
2   170 big        
3   140 small      
4   160 big        
5    NA none       
...

SQL の 6行目は、ELSE 'none' とリライトするとより簡潔になります。

集約関数 (`summarise()`内)

mean() などの集約関数は、summarise() 内で使用すると、SQL に変換される際にそれぞれ対応する集約関数にマッピングされます。

db_sales %>% 
  summarise(
    n = n(), 
    n_store = n_distinct(store), 
    avg = mean(sales), 
    per50 = median(sales)
  ) %>% 
  show_query()

SELECT
  COUNT(*) AS n,
  COUNT(DISTINCT row(store)) AS n_store,
  AVG(sales) AS avg,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sales) AS per50
FROM store_sales

      n n_store   avg per50
  <dbl>   <dbl> <dbl> <dbl>
1     8       2 151.4   150

ウィンドウ関数

次に、ウィンドウ関数の SQL 変換について解説します。
R のウィンドウ関数は以下の4種類に分類されます。

集約関数:
n()、sum()、 mean()、max()、sd() など
シフト関数:
lag()、lead()
ランキング関数:
min_rank()、dense_rank()、ntile() など
累積関数:
cumsum()、cummean()、cummax()、cumall() など

SQL では、AVG(sales) OVER () のように、ウィンドウ (範囲) を指定した集約関数などの式をウィンドウ関数と呼びます。

ウィンドウ関数は、次の形式で記述されます。

[式] OVER ([パーティション句] [順序句] [フレーム句])

ここで、[式] は関数と変数名の組み合わせです。(例: AVG(sales))
表現方法が豊富なため、変換後のクエリはこれまでのものよりも複雑になる傾向があります。

集約関数 (`mutate()`内)

mean() などの集約関数は mutate() 内で使用するとウィンドウ関数として適用され、
AVG(sales) OVER () のようなウィンドウ関数を生成します。

db_sales %>% 
  mutate(
    n = n(), 
    avg = mean(sales), 
    max = max(sales)
  ) %>% 
  show_query()

SELECT
  store_sales.*,
  COUNT(*) OVER () AS n,
  AVG(sales) OVER () AS avg,
  MAX(sales) OVER () AS max
FROM store_sales

  store month sales profit     n   avg   max
  <chr> <int> <dbl>  <dbl> <dbl> <dbl> <dbl>
1 S001      4   150     30     8 151.4   170
2 S001      5   170     34     8 151.4   170
3 S001      6   140     27     8 151.4   170
...

group_by() を併用すると以下のように変換されます。

db_sales %>% 
  group_by(month) %>% 
  mutate(
    n = n(), 
    avg = mean(sales), 
    max = max(sales)
  ) %>% 
  show_query()

SELECT
  store_sales.*,
  COUNT(*) OVER (PARTITION BY "month") AS n,
  AVG(sales) OVER (PARTITION BY "month") AS avg,
  MAX(sales) OVER (PARTITION BY "month") AS max
FROM store_sales

  store month sales profit     n   avg   max
  <chr> <int> <dbl>  <dbl> <dbl> <dbl> <dbl>
S001      4   150     30     2   150   150
S002      4    NA     28     2   150   150
S001      6   140     27     2   135   140
S002      6   130     27     2   135   140
S001      7   160     32     2   155   160
S002      7   150     28     2   155   160
S001      5   170     34     2   165   170
S002      5   160     31     2   165   170

ウィンドウ関数では、group_by(month) により GROUP BY 句ではなく OVER () 内に
PARTITION BY "month" が生成されます。

また、window_order() と window_frame() を併用すると、以下のように変換されます。

db_sales %>% 
  group_by(store) %>% 
  window_order(month) %>% 
  window_frame(-1, 1) %>% 
  mutate(
    avg_win = mean(sales)
  ) %>% 
  show_query()

SELECT
  store_sales.*,
  AVG(sales) OVER (
    PARTITION BY store 
    ORDER BY "month" 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_win
FROM store_sales

# Ordered by: month
  store month sales profit avg_win
  <chr> <int> <dbl>  <dbl>   <dbl>
S001      4   150     30   160  
S001      5   170     34   153.3
S001      6   140     27   156.7
S001      7   160     32   150  
S002      4    NA     28   160  
S002      5   160     31   145  
S002      6   130     27   146.7
S002      7   150     28   140  

AVG(sales) OVER () の内部については、window_order(month) により
ORDER BY "month" が生成され、window_frame(-1, 1) により
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING という ROWS 句が生成されています。

シフト関数 (`lag()`、`lead()`)

lag()、lead() を用いた場合の SQL 変換については以下のようになります。

db_sales %>% 
  group_by(store) %>% 
  window_order(month) %>% 
  mutate(
    lag_p = lag(profit, 1L), 
    lead_p = lead(profit, 1L)
  ) %>% 
  show_query()

SELECT
  store_sales.*,
  LAG(profit, 1, NULL) OVER (PARTITION BY store ORDER BY "month") AS lag_p,
  LEAD(profit, 1, NULL) OVER (PARTITION BY store ORDER BY "month") AS lead_p
FROM store_sales

  store month sales profit lag_p lead_p
  <chr> <int> <dbl>  <dbl> <dbl>  <dbl>
S002      4    NA     28    NA     31
S002      5   160     31    28     27
S002      6   130     27    31     28
S002      7   150     28    27     NA
S001      4   150     30    NA     34
S001      5   170     34    30     27
S001      6   140     27    34     32
S001      7   160     32    27     NA

OVER () の内部については、group_by(store) と window_order(month) により
PARTITION BY store ORDER BY "month" が生成されています。
arrange() を用いても OVER 句に ORDER BY は生成されない点にご注意ください。

累積関数

累積関数の SQL 変換についてですが、cumsum() を用いた場合の例を以下に示します。

db_sales %>% 
  group_by(store) %>% 
  window_order(month) %>% 
  mutate(
    cum = cumsum(profit)
  ) %>% 
  show_query()

SELECT
  store_sales.*,
  SUM(profit) OVER (
    PARTITION BY store ORDER BY "month" 
    ROWS UNBOUNDED PRECEDING
  ) AS cum
FROM store_sales

  store month sales profit   cum
  <chr> <int> <dbl>  <dbl> <dbl>
S001      4   150     30    30
S001      5   170     34    64
S001      6   140     27    91
S001      7   160     32   123
S002      4    NA     28    28
S002      5   160     31    59
S002      6   130     27    86
S002      7   150     28   114

SUM(profit) OVER () の内部については、group_by(store) と window_order(month) により
PARTITION BY store ORDER BY "month" が生成され、
最初の行から現在の行までを累積の対象とするために
ROWS UNBOUNDED PRECEDING という ROWS 句が生成されています。

dbplyr が認識できない式

dbplyr が認識できない式については、そのまま SQL に残されます。
これにより、dplyr でカバーされていないデータベース関数を直接記述することができます。

プレフィックス関数

dbplyr が SQL への変換方法を知らない関数は、そのまま SQL に残されます。

SQL 関数は一般的に大文字・小文字を区別しないため、R コード内で SQL 関数を記述する際は大文字を使うことを推奨します。これにより、通常の R の関数と区別しやすくなります。

以下は、SQL 関数 CEIL() (小数を切り上げる) と EVEN() (数値を偶数に丸める) を使用した例です。

db_sales %>% 
  mutate(
    v1 = CEIL(profit / sales), 
    v2 = EVEN(month)
  ) %>% 
  show_query()

SELECT 
  store_sales.*, 
  CEIL(profit / sales) AS v1, 
  EVEN("month") AS v2
FROM store_sales

  store month sales profit    v1    v2
  <chr> <int> <dbl>  <dbl> <dbl> <dbl>
1 S001      4   150     30     1     4
2 S001      5   170     34     1     6
3 S001      6   140     27     1     6
4 S001      7   160     32     1     8
5 S002      4    NA     28    NA     4
...

このように dbplyr を活用すれば、dplyr の記法を維持しながらデータベース特有の関数を使うことができます。

インフィックス関数

x %in% y のように、関数名が引数の間に挟まる形式の関数 (インフィックス関数) も変換されます。
これにより、次のように LIKE などの式を使用することができます。

db_master %>% 
  filter(
    pref %LIKE% "%ka"
  ) %>% 
  show_query()

SELECT store_master.*
FROM store_master
WHERE (pref LIKE '%ka')

  store name   pref   
  <chr> <chr>  <chr>  
1 S002  storeB Osaka  
2 S004  storeD Fukuoka

特殊な式 (SQL の構文を埋め込む)

SQL 関数は R よりも構文のバリエーションが多いため、R から直接変換できない式もあります。
これらをクエリに直接埋め込むには、sql() 内でリテラル SQL を使用します。

以下は、IFNULL() (NULL 値を変換)、REVERSE() (文字列を反転)、QUANTILE_CONT() (分位数) を用いた例です。

db_sales %>% 
  mutate(
    sales2 = sql("IFNULL(sales, 0.0)"), 
    store_rev = sql("REVERSE(store)"), 
    per25 = sql("QUANTILE_CONT(profit, 0.25) OVER ()")
  ) %>% 
  show_query()

SELECT
  store_sales.*,
  IFNULL(sales, 0.0) AS sales2,
  REVERSE(store) AS store_rev,
  QUANTILE_CONT(profit, 0.25) OVER () AS per25
FROM store_sales

  store month sales profit sales2 store_rev per25
  <chr> <int> <dbl>  <dbl>  <dbl> <chr>     <dbl>
1 S001      4   150     30    150 100S       27.8
2 S001      5   170     34    170 100S       27.8
3 S001      6   140     27    140 100S       27.8
4 S001      7   160     32    160 100S       27.8
5 S002      4    NA     28      0 200S       27.8
...

これにより、必要な SQL をほぼ自由に生成できるようになります。

注意
使用するデータベースに応じて適切な関数を選択してください。

dbplyr が認識できない関数をエラーにする

デフォルトでは、dbplyr は変換できない関数をそのまま SQL に渡そうとしますが、dplyr.strict_sql オプションを TRUE に設定すると、変換できない関数を使用した際に 強制的にエラーを発生させる ことができます。

options(dplyr.strict_sql = TRUE)

db_sales %>% mutate(v = EVEN(month))

Error in `EVEN()`:
! Don't know how to translate `EVEN()`

このオプションを有効にすると、誤った SQL クエリの生成を防ぎ、SQL 変換の正確性を高めるのに役立ちます。

まとめ

今回は、R のコードがどのように SQL クエリへと変換されるのかを解説しました。
dplyr を活用することで、SQL を直接記述せずとも直感的な R コードからクエリを自動生成でき、SQL の構造や書き方を効率的に習得できます。

また、SQL に変換できる形で R コードを書くことで、データ処理をデータベース側で実行できるため、パフォーマンスの向上も期待できます。

R と SQL を組み合わせることで、より柔軟かつ強力なデータ処理が可能になります。
ぜひ今回紹介した手法を活用し、実践を通じて SQL の理解を深めながら、さらに高度なデータ分析に挑戦してみてください。

参考リンク

R for Data Science (2e) - Databases (Hadley Wickham 他)
dbplyr (公式サイト)

リンク

はじめに

データベースへの接続とデータの準備

SQL 変換に関する補足

dplyr 操作全体と操作内の式について

SQL 変換の基となる SELECT 文

dplyr 操作全体の SQL 変換

単一テーブルの操作

列に影響を与える操作

select()、rename()、relocate()

mutate()

行に影響を与える操作

filter()

arrange()

head()

distinct()