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

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

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

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

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

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

演習問題

難易度

レシート明細データ（receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、売上金額合計を最小値0、最大値1に正規化して顧客ID、売上金額合計とともに10件表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。

出力イメージ:

   customer_id    sum_amount norm_amount
CS001113000004       1298   0.053354 
CS001114000005        626   0.024157 
CS001115000010       3044   0.12921  
CS001205000004       1988   0.083333 
CS001205000006       3337   0.14194  
CS001211000025        456   0.016771 
CS001212000027        448   0.016423 
CS001212000031        296   0.0098193
CS001212000046        228   0.0068648
CS001212000070        456   0.016771 

実行環境の構築について

R コード実行環境の構築に必要な手順はこちらから確認できます。セットアップが完了すると、必要なパッケージとデータを使用できるようになります。

解答例を以下に示します。
各コードの出力結果を統一させるため、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, na.rm = TRUE), 
    .by = "customer_id"
  ) %>% 
  mutate(
    norm_amount = scales::rescale(sum_amount, to = c(0, 1))
  ) %>% 
  arrange(customer_id) %>% 
  head(10)

# A tibble: 10 × 3
   customer_id    sum_amount norm_amount
   <chr>               <dbl>       <dbl>
CS001113000004       1298   0.053354 
CS001114000005        626   0.024157 
CS001115000010       3044   0.12921  
CS001205000004       1988   0.083333 
CS001205000006       3337   0.14194  
CS001211000025        456   0.016771 
CS001212000027        448   0.016423 
CS001212000031        296   0.0098193
CS001212000046        228   0.0068648
CS001212000070        456   0.016771 

解説

データのフィルタリング
```
filter(!str_detect(customer_id, "^Z"))
```
- customer_id が "Z" から始まるレコード (＝非会員) を除外します。
- これにより、正規の会員データだけが対象になります。

顧客ごとに売上金額合計を算出
```
summarise(sum_amount = sum(amount, na.rm = TRUE), .by = "customer_id")
```
- customer_id ごとに amount (売上金額) を合計します。
- 欠損値 (NA) は除外 (na.rm = TRUE) して集計されます。

0〜1の範囲にスケーリング (正規化)
```
mutate(norm_amount = scales::rescale(sum_amount, to = c(0, 1)))
```
- 売上金額合計を正規化 (0〜1 の範囲にスケーリング) します。
- これは Min-Max スケーリング に該当し、次の式で計算されます：

$$ \text{norm\_x} = \frac{\text{x} - \min(\text{x})}{\max(\text{x}) - \min(\text{x})} $$

customer_id で昇順に並べ替え、先頭10件を表示
```
arrange(customer_id) %>% head(10)
```
- 結果を customer_id の昇順に並べ替え、先頭の10件を取得します。

R (データベース操作)

利用するデータ

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

db_receipt の顧客ID (customer_id)、売上金額 (amount)

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

db_receipt %>% select(customer_id, amount)

   customer_id    amount
   <chr>           <dbl>
CS006214000001    158
CS008415000097     81
CS028414000014    170
ZZ000000000000     25
CS025415000050     90
CS003515000195    138
CS024514000042     30
...

解答例と実行結果

db_result = db_receipt %>% 
  filter(!customer_id %LIKE% "Z%") %>% 
  summarise(
    sum_amount = sum(amount), 
    .by = "customer_id"
  ) %>% 
  mutate(
    min_amount = min(sum_amount), 
    max_amount = max(sum_amount), 
    norm_amount = case_when(
      (max_amount -  min_amount) > 0.0 ~ 
      (sum_amount - min_amount) / (max_amount -  min_amount), 
      .default = 0.5
    )
  ) %>% 
  select(-c(min_amount, max_amount)) %>% 
  arrange(customer_id) %>% 
  head(10)

db_result %>% collect()

# A tibble: 10 × 3
   customer_id    sum_amount norm_amount
   <chr>               <dbl>       <dbl>
CS001113000004       1298   0.053354 
CS001114000005        626   0.024157 
CS001115000010       3044   0.12921  
CS001205000004       1988   0.083333 
CS001205000006       3337   0.14194  
CS001211000025        456   0.016771 
CS001212000027        448   0.016423 
CS001212000031        296   0.0098193
CS001212000046        228   0.0068648
CS001212000070        456   0.016771 

解説

データのフィルタリング
```
filter(!customer_id %LIKE% "Z%")
```
- customer_id が "Z" から始まるレコード (＝非会員) を除外します。
- %LIKE% は dbplyr が認識できないインフィックス関数なので、慣例に従い大文字で記述します。詳しくは dbplyr が認識できない式を参照してください。

顧客ごとに売上金額合計を算出
```
summarise(sum_amount = sum(amount), .by = "customer_id")
```
- customer_id ごとに amount (売上金額) を合計します。

0〜1の範囲にスケーリング (正規化)

mutate(
  min_amount = min(sum_amount),
  max_amount = max(sum_amount),
  norm_amount = case_when(
   (max_amount -  min_amount) > 0.0 ~ 
   (sum_amount - min_amount) / (max_amount -  min_amount), 
   .default = 0.5
 )
)

全体の最小・最大を求めて Min-Max スケーリング (正規化) します。差が 0 の場合 (すべての sum_amount が同一値)、ここでは 0.5 を設定しています。

一時列を削除・並び替え・先頭10件を抽出

select(-c(min_amount, max_amount)) %>% 
arrange(customer_id) %>% 
head(10)

データフレームとして取得
```
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.*,
    MIN(sum_amount) OVER () AS min_amount,
    MAX(sum_amount) OVER () AS max_amount
  FROM q02 q01
)
SELECT
  customer_id,
  sum_amount,
  CASE
WHEN ((max_amount - min_amount) > 0.0) THEN ((sum_amount - min_amount) / (max_amount - min_amount))
ELSE 0.5
END AS norm_amount
FROM q03 q01
ORDER BY customer_id
LIMIT 10

解答例

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

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
),
customer_sales_with_stats AS (
  SELECT 
    customer_id, 
    sum_amount, 
    MIN(sum_amount) OVER () AS min_amount,
    MAX(sum_amount) OVER () AS max_amount
  FROM 
    customer_sales
)
SELECT
  customer_id,
  sum_amount,
  CASE 
    WHEN max_amount - min_amount > 0.0 THEN 
      (sum_amount - min_amount) / (max_amount - min_amount)
    ELSE 0.5
  END AS norm_amount
FROM 
  customer_sales_with_stats
ORDER BY 
  customer_id
LIMIT 10

解説

1. `customer_sales` の作成

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

customer_id が Z で始まるレコード (＝非会員) を除外します。
receipt テーブルから customer_id ごとに amount (売上金額) を合計し、sum_amount 列に格納します。

2. `customer_sales_with_stats` の作成

customer_sales_with_stats AS (
  SELECT 
    customer_id, 
    sum_amount, 
    MIN(sum_amount) OVER () AS min_amount,
    MAX(sum_amount) OVER () AS max_amount
  FROM 
    customer_sales
)

customer_sales に対し MIN(sum_amount) OVER () ウィンドウ関数を使用して、全体の 最小値 (min_amount) を各行に付与します。
最大値についても同様の処理を行います。

3. メインクエリ

SELECT
  customer_id,
  sum_amount,
  CASE 
    WHEN max_amount - min_amount > 0.0 THEN 
      (sum_amount - min_amount) / (max_amount - min_amount)
    ELSE 0.5
  END AS norm_amount
FROM 
  customer_sales_with_stats
ORDER BY 
  customer_id
LIMIT 10

正規化計算 (Min-Max スケーリング) を CASE 文で安全に実行します。
ここでは、分母が 0 (すべて同じ金額) の場合は 0.5 に設定しています。
customer_id の昇順で並び替え、先頭の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
),
customer_sales_with_stats AS (
  SELECT 
    customer_id, 
    sum_amount, 
    MIN(sum_amount) OVER () AS min_amount,
    MAX(sum_amount) OVER () AS max_amount
  FROM 
    customer_sales
)
SELECT
  customer_id,
  sum_amount,
  CASE 
    WHEN max_amount - min_amount > 0.0 THEN 
      (sum_amount - min_amount) / (max_amount - min_amount)
    ELSE 0.5
  END AS norm_amount
FROM 
  customer_sales_with_stats
ORDER BY 
  customer_id
LIMIT 10
"
)

query %>% db_get_query(con)

# A tibble: 10 × 3
   customer_id    sum_amount norm_amount
   <chr>               <dbl>       <dbl>
CS001113000004       1298   0.053354 
CS001114000005        626   0.024157 
CS001115000010       3044   0.12921  
CS001205000004       1988   0.083333 
CS001205000006       3337   0.14194  
CS001211000025        456   0.016771 
CS001212000027        448   0.016423 
CS001212000031        296   0.0098193
CS001212000046        228   0.0068648
CS001212000070        456   0.016771 

リンク