[R & SQL] データサイエンス100本ノック＋α - 概要・導入

参考記事:

はじめに

当サイトでは、データサイエンス100本ノックの R と SQL をベースにした演習問題を解説します。
特に、R を用いたデータベース操作についての補足解説も加え、より実務に近い形で学べる内容としています。

シリーズ構成
- データサイエンス100本ノック＋α
  100本ノックの中から約 30 本の練習問題を選び、R と SQL を用いて解説します。
- オリジナル問題集
  オリジナルの演習問題を作成し、より多くの構文を用いたデータ処理を紹介する予定です。

解説方針
各問題について、以下の 3 種類のコードを紹介し解説します。
- Rコード (データフレーム操作)
- Rコード (データベース操作)
- SQLクエリ

サンプルコードは、可読性と効率を重視し、できるだけエレガントな記述を心がけています。
R と SQL を用いたコーディングの練習問題としてご活用ください。

環境構築

このシリーズでは、RStudio や VSCode などの R 開発環境 を使って演習問題に取り組むことを想定しています。その他の特別なツールは必要ありません。

環境構築のために、以下の 2 つのセットアップ方法を用意しています。
目的に応じて、適した方法を選んでください。

簡易セットアップ
ローカルにスクリプトやデータを保存せず、すぐに試したい方におすすめです。
標準セットアップ
git コマンドに慣れている方におすすめです。

簡易セットアップ

次の R コードを .R ファイル (例: setup.R) にコピー＆ペーストして実行します。

R コード (簡易セットアップ)

# pacman を使用してパッケージを管理 ------------
if (!require("pacman")) {
  install.packages("pacman")
  library("pacman")
}

# 必要なパッケージのロード ------------
# 存在しない場合は自動でインストールした後にロードする.
pacman::p_load(
  # tidyverse: 
  magrittr, fs, tibble, dplyr, tidyr, stringr, lubridate, forcats, 
  DBI, dbplyr, duckdb,      # for database
  rsample, recipes, themis, # tidymodels
  vroom, tictoc, jsonlite, withr, janitor, skimr, epikit, 
  install = TRUE,  # 存在しないパッケージをインストールする
  update = FALSE   # 既存のパッケージの更新は行わない
)

# CSVファイルをデータフレームとして読み込む ------------
my_vroom = function(file, col_types) {
  data_url = "https://raw.githubusercontent.com/katsu-ds-lab/ds-drills//main/work/data/"
  tictoc::tic(file)
  on.exit(tictoc::toc())
  on.exit(cat("\n"), add = TRUE)
  csv_url = data_url %>% paste0(file)
  print(csv_url); flush.console()
  d = csv_url %>% 
    vroom::vroom(col_types = col_types) %>% 
    janitor::clean_names() %>% 
    dplyr::glimpse() %T>% 
    { cat("\n") }
  return(d)
}

# customer.birth_day は Dateクラス
df_customer = "customer.csv" %>% my_vroom(col_types = "ccccDiccccc")
# receipt.sales_ymd は integer
df_receipt = "receipt.csv" %>% my_vroom(col_types = "iiciiccnn")
df_store = "store.csv" %>% my_vroom(col_types = "cccccccddd")
df_product = "product.csv" %>% my_vroom(col_types = "ccccnn")
df_category = "category.csv" %>% my_vroom(col_types = "cccccc")
df_geocode = "geocode.csv" %>% my_vroom(col_types = "cccccccnn")

# インメモリモードで一時的な DuckDB 環境を作成する ------------
con = duckdb::duckdb(dbdir = "") %>% duckdb::dbConnect()

# データフレームを DuckDB にテーブルとして書き込む ------------
con %>% DBI::dbWriteTable("customer", df_customer, overwrite = TRUE)
con %>% DBI::dbWriteTable("receipt", df_receipt, overwrite = TRUE)
con %>% DBI::dbWriteTable("store", df_store, overwrite = TRUE)
con %>% DBI::dbWriteTable("product", df_product, overwrite = TRUE)
con %>% DBI::dbWriteTable("category", df_category, overwrite = TRUE)
con %>% DBI::dbWriteTable("geocode", df_geocode, overwrite = TRUE)

# DuckDB のテーブルを dplyr で参照する ------------
db_customer = con %>% dplyr::tbl("customer")
db_receipt = con %>% dplyr::tbl("receipt")
db_store = con %>% dplyr::tbl("store")
db_product = con %>% dplyr::tbl("product")
db_category = con %>% dplyr::tbl("category")
db_geocode = con %>% dplyr::tbl("geocode")

# 関数の定義 ------------

# db_get_query() の定義
# SQLクエリを実行し, データフレーム(tibble)を返す
db_get_query = function(
    statement, con, convert_tibble = TRUE, params = NULL, ...
  ) {
  d = DBI::dbGetQuery(con, statement = statement, params = params, ...)
  if (convert_tibble) d %<>% tibble::as_tibble()
  return(d)
}

# sql_render_ext() の定義
# dbplyr::sql_render のラッパー
# デフォルトでは, バッククォート(`)を削除する
sql_render_ext = function(
    query, con = NULL, 
    cte = TRUE, 
    qualify_all_columns = TRUE, 
    use_star = TRUE, 
    sql_op = 
      dbplyr::sql_options(
        cte = cte, 
        use_star = use_star, 
        qualify_all_columns = qualify_all_columns
      ), 
    subquery = FALSE, lvl = 0, 
    pattern = "`", replacement = ""
  ) {
  ret = tryCatch(
    {
      query %>% 
        dbplyr::sql_render(
          con = con, sql_options = sql_op, subquery = subquery, lvl = lvl
        )
    }, 
    error = function(e) {
      # CTE を生成しない処理に cte = TRUE が指定された場合の措置
      sql_op = 
        dbplyr::sql_options(
          cte = FALSE, 
          use_star = use_star, 
          qualify_all_columns = qualify_all_columns
        )
      query %>% 
        dbplyr::sql_render(
          con = con, sql_options = sql_op, subquery = subquery, lvl = lvl
        )
    }
  )
  if (!is.null(pattern)) {
    ret %<>% gsub(pattern, replacement, .)
  }
  return(ret)
}

実行後、DuckDB データベースがメモリ上に作成されます。
R セッションを再開した場合は、再度このスクリプトを実行してください。

標準セットアップ

~/projects にファイルを展開する場合の例を以下に示します。

1. GitHub リポジトリをクローン

次のコマンドを実行して、リポジトリをクローンします。

shell

cd ~/projects
git clone https://github.com/katsu-ds-lab/ds-drills.git

実行後、ds-drills ディレクトリが作成され、以下のような構成になります。

ds-drills

work
  ├── database            # DuckDB データベース保存先
  ├── data                # CSVファイル, ER図
  │   ├── category.csv
  │   ├── customer.csv
  │   ├── geocode.csv
  │   ├── product.csv
  │   ├── receipt.csv
  │   ├── store.csv
  │   ├── 100knocks_ER.pdf
  │   └── LICENSE
  ├── init.R              # セットアップスクリプト
  ├── data_setup.R
  ├── env_setup.R
  └── functions.R

2. init.R を実行

RStudio などで init.R を開いて実行します。
実行後、database に DuckDB データベースファイル supermarket.duckdb が作成されます。

work
  └── database
      └── supermarket.duckdb

6 個の CSVファイル (data/*.csv) を読み込み、それぞれテーブルとして supermarket.duckdb に保存しています。

DuckDB データベースをメモリ上に作成する場合は、data_setup.R の以下の箇所のコメントアウトを外して init.R を実行してください。

data_setup.R

# 一時的にメモリ上に作成する場合は, 以下の行のコメントアウトを外してください.
is_fbmode = FALSE

R セッションを再開した場合は、再度 init.R を実行してください。
2 回目からは、パッケージのインストールは不要なため、セットアップは約 5 秒で完了します。

トラブルシューティング

もしエラーが発生した場合は、作業ディレクトリの設定が原因となっている可能性が高いです。

現在の作業ディレクトリを確認するには、次のコマンドを実行してください。

getwd()

init.R の以下の箇所で作業ディレクトリを設定しています。

work_dir_path = init_path |> dirname()

作業ディレクトリとして init.R と同じ場所 (ds-drills/work) を設定する必要がありますので、ここを以下のように変更して再実行してみてください。

work_dir_path = "~/projects/ds-drills/work"

この変更を行うことで、エラーが解消される可能性があります。

利用可能なリソース

環境構築後、以下のリソースを利用できます。

1. 主な R パッケージ

dplyr
magrittr
tidyr
tibble
stringr
lubridate
forcats
DBI
dbplyr
duckdb
その他 rsample など

2. R オブジェクト

データベース接続

con

データフレーム

df_customer
df_receipt
df_product
df_store
df_category
df_geocode

データベースのテーブル参照

db_customer
db_receipt
db_product
db_store
db_category
db_geocode

便利な関数

db_get_query()
sql_render_ext()

3. DuckDB データベースファイル

work/database/supermarket.duckdb

(＊簡易セットアップでは、DuckDB データベースはメモリ上に作成されます。)

4. ER図 (データの構造)

work/data/100knocks_ER.pdf

6 個のテーブルの関係を示す ER 図です。

− データサイエンス100本ノック（構造化データ加工編）- ER図より引用

簡易セットアップを行なった場合は、上記リンクから 100knocks_ER.pdf を保存しておくと便利です。

データベース操作の補足事項

R によるデータベース操作や SQL クエリの自動生成について、以下の記事で紹介しています。

データベース操作に関連するその他の補足事項を db_get_query()、sql_render_ext() の使い方と共に以下で説明します。
環境構築が完了していれば、本セクションのコードはコピー＆ペーストでそのまま実行できます。

DuckDB を使用するメリット

本シリーズでは、軽量かつ高速なデータベースエンジン DuckDB を使用します。
DuckDB はデータサイエンス向けに設計された高性能データベースで、次のような特長があります。

ローカル環境でのデータ分析や R との統合に適している
PostgreSQL との互換性が高く、学習した内容を他のデータベースにも応用しやすい
ファイルベースで手軽に扱え、インメモリモードを利用すればさらに高速な処理が可能

特に、教育目的や小規模データセットの分析に最適なため、本シリーズでは DuckDB を採用しています。

SQL クエリを直接実行する

`dbGetQuery()`

DBI::dbGetQuery() を使用すると、指定した SQL クエリをデータベースで実行し、結果をデータフレーム (data.frame クラス) として取得できます。
SQL を直接記述して実行する際に便利な関数です。

query = 
  "SELECT sales_ymd, product_cd, amount FROM receipt"
d = DBI::dbGetQuery(con, query)
d %>% head(5)

  sales_ymd product_cd amount
20181103 P070305012    158
20181118 P070701017     81
20170712 P060101005    170
20190205 P050301001     25
20180821 P060102007     90

sql() と組み合わせると、複雑なクエリの記述が容易になります。

query = sql("
SELECT product_cd, SUM(amount) AS total_sales
FROM receipt
WHERE (sales_ymd >= 20180101)
GROUP BY product_cd
ORDER BY total_sales DESC
"
)

DBI::dbGetQuery(con, query)

  product_cd total_sales
P071401001     1233100
P071401002      429000
P071401003      371800
P060303001      346320
P071401012      305800
...

引数 n を指定すると、取得するレコード数を制限できます。

DBI::dbGetQuery(con, query, n = 3)

  product_cd total_sales
P071401001     1233100
P071401002      429000
P071401003      371800

また、params 引数を使用して、SQL のバインドパラメータを活用することができ、柔軟かつ安全にクエリを実行できます。

query = sql("
SELECT product_cd, SUM(amount) AS total_sales
FROM receipt
WHERE (sales_ymd >= ?)
GROUP BY product_cd
ORDER BY total_sales DESC
"
)

DBI::dbGetQuery(con, query, params = list(20190401))

  product_cd total_sales
P071401001      376200
P071401002      158400
P071401003      123200
P060303001      117216
P071401013      110000
...

`db_get_query()`

独自関数 db_get_query() は dbGetQuery() のラッパー関数で、次の点を変更しています。

デフォルトで結果を tibble として返す。
クエリを第 1 引数として渡す。

使用例は以下の通りです :

query = sql("
SELECT product_cd, SUM(amount) AS total_sales
FROM receipt
WHERE (sales_ymd >= 20180101)
GROUP BY product_cd
ORDER BY total_sales DESC
"
)

query %>% db_get_query(con, n = 5)

実行結果は、次のように tibble として返されます。

# A tibble: 5 × 2
  product_cd total_sales
  <chr>            <dbl>
1 P071401001     1233100
2 P071401002      429000
3 P071401003      371800
4 P060303001      346320
5 P071401012      305800

convert_tibble = FALSE を指定すると、data.frame クラスのデータフレームが返されますが、通常は tibble を使用することをお勧めします。

異なるデータベースの SQL を確認する

`sql_render()`

sql_render() を データベースシミュレーター simulate_*() と組み合わせて使用すると、異なるデータベース向けの SQL クエリを確認できます。

例えば、PostgreSQL の SQL をシミュレーションする 場合は simulate_postgres() を使います。

db_result = db_customer %>% 
  mutate(
    m = birth_day %>% lubridate::month(), 
    .keep = "used"
  ) %>% 
  head(5)

db_result %>% sql_render(con = simulate_postgres())

<SQL> SELECT `birth_day`, EXTRACT(MONTH FROM `birth_day`) AS `m`
FROM customer
LIMIT 5

このように、実際のデータベース接続なしで SQL を確認できるので便利です。

MySQL/MariaDB、Snowflake、Oracle、SQL server での SQL のシミュレーションは、以下のようになります。

MySQL/MariaDB

db_result %>% sql_render(con = simulate_mysql())

<SQL> SELECT `birth_day`, EXTRACT(month FROM `birth_day`) AS `m`
FROM customer
LIMIT 5

Snowflake

db_result %>% sql_render(con = simulate_snowflake())

<SQL> SELECT `birth_day`, EXTRACT('month', `birth_day`) AS `m`
FROM customer
LIMIT 5

Oracle

db_result %>% sql_render(con = simulate_oracle())

<SQL> SELECT `birth_day`, EXTRACT(month FROM `birth_day`) AS `m`
FROM customer
FETCH FIRST 5 ROWS ONLY

SQL server

db_result %>% sql_render(con = simulate_mssql())

<SQL> SELECT TOP 5 `birth_day`, DATEPART(MONTH, `birth_day`) AS `m`
FROM customer

対応しているデータベース一覧は、以下の公式ページで確認できます。

https://dbplyr.tidyverse.org/reference/index.html#built-in-database-backends

`sql_render_ext()`

独自関数 sql_render_ext() は sql_render() のラッパー関数で、以下の点を変更しています。

識別子のバッククォート (`) を制御。
cte などの SQL オプション指定を簡略化。

通常、sql_render() の出力する SQL では、テーブル名やカラム名の識別子がバッククォート (`) で囲まれます。

db_result = db_customer %>% 
  left_join(
    db_receipt %>% select(customer_id, amount), 
    by = "customer_id"
  ) %>% 
  group_by(customer_id) %>% 
  summarise(sum_amount = sum(amount, na.rm = TRUE)) %>% 
  arrange(customer_id)

db_result %>% sql_render(
    con = simulate_mysql(), 
    sql_options = sql_options(cte = TRUE)
  )

<SQL> WITH `q01` AS (
  SELECT `customer`.*, `amount`
  FROM customer
  LEFT JOIN receipt
    ON (`customer`.`customer_id` = `receipt`.`customer_id`)
)
SELECT `customer_id`, SUM(`amount`) AS `sum_amount`
FROM `q01`
GROUP BY `customer_id`
ORDER BY `customer_id`

識別子の囲みをなくすことで SQL の可読性が向上するため、sql_render_ext() はデフォルトでバッククォートを削除します。

db_result %>% 
  sql_render_ext(con = simulate_mysql(), cte = TRUE)

<SQL> WITH q01 AS (
  SELECT customer.*, receipt.amount AS amount
  FROM customer
  LEFT JOIN receipt
    ON (customer.customer_id = receipt.customer_id)
)
SELECT customer_id, SUM(amount) AS sum_amount
FROM q01
GROUP BY customer_id
ORDER BY customer_id

また、replacement 引数に "\"" を指定すると、識別子をダブルクォートで囲みます。

db_result %>% sql_render_ext(
    con = simulate_mysql(), cte = TRUE, 
    replacement = "\""
  )

<SQL> WITH "q01" AS (
  SELECT "customer".*, "receipt"."amount" AS "amount"
  FROM customer
  LEFT JOIN receipt
    ON ("customer"."customer_id" = "receipt"."customer_id")
)
SELECT "customer_id", SUM("amount") AS "sum_amount"
FROM "q01"
GROUP BY "customer_id"
ORDER BY "customer_id"

また、cte などの SQL オプションを簡単に指定できます。以下の 2 つのコードは等価です。

# sql_render
db_result %>% 
  sql_render(
    sql_options = 
      sql_options(cte = TRUE, use_star = FALSE, qualify_all_columns = FALSE)
  )
# sql_render_ext
db_result %>% 
  sql_render_ext(cte = TRUE, use_star = FALSE, qualify_all_columns = FALSE)

謝辞

当サイトは、データサイエンティスト協会様が作成された素晴らしい教育コンテンツを、更なる発展を目指して作成しました。

また、当サイトで使用している以下のリソースは、データサイエンティスト協会スキル定義委員会様によって提供された「データサイエンス100本ノック（構造化データ加工編）」の GitHub リポジトリにて公開されているものを使用させていただいています。

データ
GitHub リポジトリ - データ
ER 図
GitHub リポジトリ - ER 図
「データサイエンス100本ノック＋α」の各演習問題
- GitHub リポジトリ - 演習問題
- 書籍📘『データサイエンス100本ノック構造化データ加工編ガイドブック』

より多くの方々がデータサイエンスのスキルをさらに高める一助となれば幸いです。

演習問題 :

[R & SQL] 演習問題一覧

リンク