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

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

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

演習問題

難易度

顧客データ（customer）とレシート明細データ（receipt）から、顧客ごとの売上金額合計を求め、10件表示せよ。ただし、売上実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが"Z"から始まるもの）は除外すること。

出力イメージ:

   customer_id    sum_amount
CS001112000009          0
CS001112000019          0
CS001112000021          0
CS001112000023          0
CS001112000024          0
CS001112000029          0
CS001112000030          0
CS001113000004       1298
CS001113000010          0
CS001114000005        626

実行環境の構築について

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

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

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

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

利用するデータ

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

df_customer の顧客ID (customer_id) と性別コード (gender_cd)

df_customer %>% select(customer_id, gender_cd)

# A tibble: 21,971 × 2
  customer_id    gender_cd
  <chr>          <chr>    
1 CS021313000114 1        
2 CS037613000071 9        
3 CS031415000172 1        
4 CS028811000001 1        
5 CS001215000145 1        
6 CS020401000016 0        
7 CS015414000103 1  
...

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_customer_sales = df_receipt %>% 
  summarise(
    sum_amount = sum(amount, na.rm = TRUE), 
    .by = "customer_id"
  )

df_customer %>% 
  filter(
    gender_cd == "1" & !str_detect(customer_id, "^Z")
  ) %>% 
  select(customer_id) %>% 
  left_join(
    df_customer_sales, by = "customer_id"
  ) %>% 
  tidyr::replace_na(list(sum_amount = 0.0)) %>% 
  arrange(customer_id) %>% 
  head(10)

# A tibble: 10 × 2
   customer_id    sum_amount
   <chr>               <dbl>
CS001112000009          0
CS001112000019          0
CS001112000021          0
CS001112000023          0
CS001112000024          0
CS001112000029          0
CS001112000030          0
CS001113000004       1298
CS001113000010          0
CS001114000005        626

解説

顧客ごとの売上金額合計を算出

df_customer_sales = df_receipt %>% 
  summarise(
    sum_amount = sum(amount, na.rm = TRUE), 
    .by = "customer_id"
  )

df_receipt から、顧客 (customer_id) ごとの売上金額の合計 (sum_amount) を計算します。
sum(amount, na.rm = TRUE) により、NA を除外して合計します。

これにより、df_customer_sales は次のようなデータフレームになります。

# A tibble: 8,307 × 2
  customer_id    sum_amount
  <chr>               <dbl>
1 CS006214000001       7364
2 CS008415000097       1895
3 CS028414000014       6222
4 ZZ000000000000   12395003
5 CS025415000050       5736
...

特定の顧客を抽出し、売上データと結合

df_customer %>% 
  filter(
    gender_cd == "1" & !str_detect(customer_id, "^Z")
  ) %>% 
  select(customer_id) %>% 
  left_join(
    df_customer_sales, by = "customer_id"
  ) %>% 
  tidyr::replace_na(list(sum_amount = 0.0)) %>% 
  arrange(customer_id) %>% 
  head(10)

filter()
- gender_cd が "1" の顧客 (女性) を抽出します。
- customer_id の先頭が "Z" で始まるものを除外します。
select(customer_id)
- customer_id 列のみを抽出します。
left_join()
- customer_id をキーとして df_customer_sales を左外部結合(left_join) し、sum_amount 列を追加します。
- 購入履歴がない場合、sum_amount は欠損値 NA になります。
replace_na(list(sum_amount = 0.0))
sum_amount が欠損値の箇所を 0.0 に置き換えます。
arrange(customer_id) %>% head(10)
- customer_id の昇順に並べ替えます。
- 先頭 10 件を取得します。

R (データベース操作)

解答例と実行結果

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

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

db_customer_sales = db_receipt %>% 
  summarise(
    sum_amount = sum(amount, na.rm = TRUE), 
    .by = "customer_id"
  )

db_result = db_customer %>% 
  filter(
    gender_cd == "1" & !(customer_id %LIKE% "Z%")
  ) %>% 
  select(customer_id) %>% 
  left_join(
    db_customer_sales, by = "customer_id"
  ) %>% 
  tidyr::replace_na(list(sum_amount = 0.0)) %>% 
  arrange(customer_id) %>% 
  head(10)

db_result %>% collect()

# A tibble: 10 × 2
   customer_id    sum_amount
   <chr>               <dbl>
CS001112000009          0
CS001112000019          0
CS001112000021          0
CS001112000023          0
CS001112000024          0
CS001112000029          0
CS001112000030          0
CS001113000004       1298
CS001113000010          0
CS001114000005        626

解説

データフレーム操作との相違点は以下の箇所です。

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

SQL

利用するデータ

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

customer テーブルの顧客ID (customer_id) と性別コード (gender_cd)

"SELECT customer_id, gender_cd FROM customer" %>% db_get_query(con)

# A tibble: 21,971 × 2
  customer_id    gender_cd
  <chr>          <chr>    
1 CS021313000114 1        
2 CS037613000071 9        
3 CS031415000172 1        
4 CS028811000001 1        
5 CS001215000145 1        
6 CS020401000016 0        
7 CS015414000103 1        
...

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 customer_id
  FROM customer
  WHERE (gender_cd = '1' AND NOT((customer_id LIKE 'Z%')))
),
q02 AS (
  SELECT customer_id, SUM(amount) AS sum_amount
  FROM receipt
  GROUP BY customer_id
),
q03 AS (
  SELECT LHS.customer_id AS customer_id, sum_amount
  FROM q01 LHS
  LEFT JOIN q02 RHS
    ON (LHS.customer_id = RHS.customer_id)
)
SELECT customer_id, COALESCE(sum_amount, 0.0) AS sum_amount
FROM q03 q01
ORDER BY customer_id
LIMIT 10

解答例

このクエリをより簡潔な形に書き直すと、次のようになります。

SQL

WITH filtered_customers AS (
  SELECT 
    customer_id
  FROM 
    customer
  WHERE 
    gender_cd = '1' 
    AND customer_id NOT LIKE 'Z%'
),
customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  GROUP BY 
    customer_id
)
SELECT 
  fc.customer_id, 
  COALESCE(cs.sum_amount, 0.0) AS sum_amount
FROM 
  filtered_customers fc
LEFT JOIN 
  customer_sales cs 
USING (customer_id)
ORDER BY 
  fc.customer_id
LIMIT 10

解説

`filtered_customers` の作成

SQL

WITH filtered_customers AS (
  SELECT 
    customer_id
  FROM 
    customer
  WHERE 
    gender_cd = '1' 
    AND customer_id NOT LIKE 'Z%'
)

customer テーブルから、gender_cd が '1' の顧客 (女性) を抽出します。
customer_id NOT LIKE 'Z%' により、customer_id の先頭が 'Z' の顧客を除外します。
条件に合致する customer_id のみを抽出し、一時テーブル filtered_customers を作成します。

これにより、filtered_customers は次のような結果を返します。

  customer_id
  <chr>
1 CS021313000114
2 CS031415000172
3 CS028811000001
4 CS001215000145
5 CS015414000103
...

`customer_sales` の作成

SQL

customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  GROUP BY 
    customer_id
)

receipt テーブルを customer_id ごとに集計し、sum_amount (売上金額合計) を計算します。
SUM(amount) と GROUP BY customer_id の組み合わせにより、各 customer_id の売上金額の合計を求めています。

これにより、customer_sales は次のような結果を返します。

  customer_id    sum_amount
  <chr>               <dbl>
1 CS003515000195       5412
2 CS014415000077      14076
3 CS026615000085       2885
4 CS015415000120       4106
5 CS008314000069       5293
...

メインクエリ

SQL

SELECT 
  fc.customer_id, 
  COALESCE(cs.sum_amount, 0.0) AS sum_amount
FROM 
  filtered_customers fc
LEFT JOIN 
  customer_sales cs 
USING (customer_id)
ORDER BY 
  fc.customer_id
LIMIT 10

LEFT JOIN により、customer_id をキーとして filtered_customers と customer_sales を左外部結合します。
COALESCE() を用いて、NULL を 0.0 に置き換えます。
ORDER BY で customer_id の昇順に並べ、LIMIT 10 で 10 件取得します。

実行結果の確認

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

query = sql("
WITH filtered_customers AS (
  SELECT 
    customer_id
  FROM 
    customer
  WHERE 
    gender_cd = '1' 
    AND customer_id NOT LIKE 'Z%'
),
customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  GROUP BY 
    customer_id
)
SELECT 
  fc.customer_id, 
  COALESCE(cs.sum_amount, 0.0) AS sum_amount
FROM 
  filtered_customers fc
LEFT JOIN 
  customer_sales cs 
USING (customer_id)
ORDER BY 
  fc.customer_id
LIMIT 10
"
)

query %>% db_get_query(con)

# A tibble: 10 × 2
   customer_id    sum_amount
   <chr>               <dbl>
CS001112000009          0
CS001112000019          0
CS001112000021          0
CS001112000023          0
CS001112000024          0
CS001112000029          0
CS001112000030          0
CS001113000004       1298
CS001113000010          0
CS001114000005        626

リンク