[R & SQL] データサイエンス100本ノック＋α - R-035 (解説)

「データサイエンス100本ノック（構造化データ加工編）」の R と SQL 実装を丁寧に解説するシリーズです。
本編にはない、R によるデータベース操作の方法についても、あわせて紹介しています。

R のデータ加工は dplyr をベースとしており、データフレーム操作とデータベース操作の両面から学べる構成になっています。

RStudio や VSCode など、一般的な R の開発環境でそのまま実行できるようにしているので、実践的なスキルを身につけたい方におすすめです。

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

設問文はデータサイエンス100本ノック（構造化データ加工編）より引用しています。
すべてのコードはコピー＆ペーストでそのまま実行できます。

演習問題

難易度

レシート明細データ（receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求め、平均以上に買い物をしている顧客を抽出し、10件表示せよ。ただし、顧客IDが"Z"から始まるものは非会員を表すため、除外して計算すること。

出力イメージ:

   customer_id    sum_amount
CS017415000097      23086
CS015415000185      20153
CS031414000051      19202
CS028415000007      19127
CS001605000009      18925
CS010214000010      18585
CS006515000023      18372
CS016415000141      18372
CS011414000106      18338
CS038415000104      17847

実行環境の構築について

R コード実行環境の構築に必要な手順はこちらから確認できます。

セットアップが完了すると、必要なパッケージとデータを使用できるようになります。

amount が non-nullable (NULL を許容しない) の場合の解答例を以下に示します。
各コードの出力結果を統一させるため、sum_amount の降順と customer_id の昇順でソートしています。

R (データフレーム操作)

利用するデータ

以下のデータを利用します。

df_receipt の顧客ID (customer_id) と売上金額 (amount)

主要なカラムを抜き出してデータの一部を確認します。

df_receipt %>% select(customer_id, amount)

# A tibble: 104,681 × 2
  customer_id    amount
  <chr>           <dbl>
1 CS006214000001    158
2 CS008415000097     81
3 CS028414000014    170
4 ZZ000000000000     25
5 CS025415000050     90
6 CS003515000195    138
7 CS024514000042     30
...

解答例と実行結果

df_receipt %>% 
  filter(!str_detect(customer_id, "^Z")) %>% 
  summarise(sum_amount = sum(amount), .by = customer_id) %>% 
  filter(sum_amount >= mean(sum_amount)) %>% 
  arrange(desc(sum_amount), customer_id) %>% 
  head(10)

# A tibble: 10 × 2
   customer_id    sum_amount
   <chr>               <dbl>
CS017415000097      23086
CS015415000185      20153
CS031414000051      19202
CS028415000007      19127
CS001605000009      18925
CS010214000010      18585
CS006515000023      18372
CS016415000141      18372
CS011414000106      18338
CS038415000104      17847

解説

filter(!str_detect(customer_id, "^Z"))
customer_id が "Z" で始まる顧客を除外します。
summarise(sum_amount = sum(amount), .by = customer_id)
各顧客の売上合計を計算し、新しい列 sum_amount を作成します。
filter(sum_amount >= mean(sum_amount))
sum_amount の平均以上の顧客を抽出します。これにより、平均以上の売上を上げた顧客だけが結果に残ります。
arrange(desc(sum_amount), customer_id)
sum_amount の降順で結果を並べ替え、同じ売上の顧客については customer_id で昇順に並べます。

R (データベース操作)

データベースでの操作も同様の流れです。
データフレーム df_receipt をテーブル参照 db_receipt に置き換えることで、同様の操作を実行できます。

以下は、filter(!str_detect(...)) の代わりに %LIKE% を用いた構文で簡略化した解答例です。

db_result = db_receipt %>% 
  filter(!(customer_id %LIKE% "Z%")) %>% 
  summarise(sum_amount = sum(amount), .by = customer_id) %>% 
  filter(sum_amount >= mean(sum_amount)) %>% 
  arrange(desc(sum_amount), customer_id) %>% 
  head(10)

db_result %>% collect()

# A tibble: 10 × 2
   customer_id    sum_amount
   <chr>               <dbl>
CS017415000097      23086
CS015415000185      20153
CS031414000051      19202
CS028415000007      19127
CS001605000009      18925
CS010214000010      18585
CS006515000023      18372
CS016415000141      18372
CS011414000106      18338
CS038415000104      17847

%LIKE% は dbplyr が認識できないインフィックス関数なので、慣例に従い大文字で記述します。
(詳しくは dbplyr が認識できない式を参照してください。)
db_result %>% collect()
データベース操作の結果を R のデータフレーム (tibble) として取得します。

SQL

利用するデータ

以下のデータを利用します。

receipt テーブルの顧客ID (customer_id) と売上金額 (amount)

主要なカラムを抜き出してデータの一部を確認します。

"SELECT customer_id, amount FROM receipt" %>% db_get_query(con)

# A tibble: 104,681 × 2
  customer_id    amount
  <chr>           <dbl>
1 CS006214000001    158
2 CS008415000097     81
3 CS028414000014    170
4 ZZ000000000000     25
5 CS025415000050     90
6 CS003515000195    138
7 CS024514000042     30
...

自動生成された SQL クエリ

データベース操作による結果 (db_result) に基づき、自動生成された SQLクエリを show_query() で確認できます。

db_result %>% show_query(cte = TRUE)

SQL

WITH q01 AS (
  SELECT receipt.*
  FROM receipt
  WHERE (NOT((customer_id LIKE 'Z%')))
),
q02 AS (
  SELECT customer_id, SUM(amount) AS sum_amount
  FROM q01
  GROUP BY customer_id
),
q03 AS (
  SELECT q01.*, AVG(sum_amount) OVER () AS col01
  FROM q02 q01
)
SELECT customer_id, sum_amount
FROM q03 q01
WHERE (sum_amount >= col01)
ORDER BY sum_amount DESC, customer_id
LIMIT 10

解答例

このクエリを以下のようにして、より簡潔な形に書き直します。

WITH 句の定義を1つ (customer_sales) にまとめ、不要な CTE を削減します。
平均値の計算を WHERE 句内のサブクエリで処理し、AVG() のウィンドウ関数を不要にします。

SQL

WITH customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  WHERE 
    customer_id NOT LIKE 'Z%'
  GROUP BY 
    customer_id
)
SELECT 
  customer_id, 
  sum_amount
FROM 
  customer_sales
WHERE 
  sum_amount >= (
    SELECT AVG(sum_amount) FROM customer_sales
  )
ORDER BY 
  sum_amount DESC, customer_id
LIMIT 10

解説

1. `customer_sales` の作成

SQL

WITH customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  WHERE 
    customer_id NOT LIKE 'Z%'
  GROUP BY 
    customer_id
)

この部分では、receipt テーブルから customer_id ごとの売上合計を計算しています。

WHERE customer_id NOT LIKE 'Z%'
customer_id が "Z" で始まる顧客を除外します。
SUM(amount) AS sum_amount
各顧客の売上合計を求め、sum_amount 列として格納します。
GROUP BY customer_id
customer_id ごとに集計を行います。

2. 平均以上の顧客の抽出

SQL

SELECT 
  customer_id, 
  sum_amount
FROM 
  customer_sales
WHERE 
  sum_amount >= (
    SELECT AVG(sum_amount) FROM customer_sales
  )

ここでは、customer_sales を参照して、売上合計が全体の平均以上の顧客を抽出します。

SELECT AVG(sum_amount) FROM customer_sales
このサブクエリでは、customer_sales で計算した sum_amount の平均値を求めています。

3. 結果の並べ替えと制限

SQL

ORDER BY 
  sum_amount DESC, customer_id
LIMIT 10

ORDER BY sum_amount DESC, customer_id
売上合計が高い順に並べ替えます。売上合計が同じ顧客については customer_id の昇順で並べ替えます。
LIMIT 10
上位 10 件のみを取得します。

実行結果の確認

この SQLクエリの実行結果は、次のようにして確認できます。

query = sql("
WITH customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  WHERE 
    customer_id NOT LIKE 'Z%'
  GROUP BY 
    customer_id
)
SELECT 
  customer_id, 
  sum_amount
FROM 
  customer_sales
WHERE 
  sum_amount >= (
    SELECT AVG(sum_amount) FROM customer_sales
  )
ORDER BY 
  sum_amount DESC, customer_id
LIMIT 10
"
)

query %>% db_get_query(con)

# A tibble: 10 × 2
   customer_id    sum_amount
   <chr>               <dbl>
CS017415000097      23086
CS015415000185      20153
CS031414000051      19202
CS028415000007      19127
CS001605000009      18925
CS010214000010      18585
CS006515000023      18372
CS016415000141      18372
CS011414000106      18338
CS038415000104      17847

リンク