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

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

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

演習問題

難易度

レシート明細（receipt）データの売上金額（amount）を顧客ID（customer_id）ごとに合計し、その合計金額の四分位点を求めよ。その上で、顧客ごとの売上金額合計に対して以下の基準でカテゴリ値を作成し、顧客ID、売上金額合計とともに10件表示せよ。カテゴリ値は順に1〜4とする。

最小値以上第1四分位未満・・・ 1を付与
第1四分位以上第2四分位未満・・・ 2を付与
第2四分位以上第3四分位未満・・・ 3を付与
第3四分位以上・・・ 4を付与

出力イメージ:

   customer_id    sum_amount pct_group
CS001113000004       1298 2        
CS001114000005        626 2        
CS001115000010       3044 3        
CS001205000004       1988 3        
CS001205000006       3337 3        
CS001211000025        456 1        
CS001212000027        448 1        
CS001212000031        296 1        
CS001212000046        228 1        
CS001212000070        456 1   

実行環境の構築について

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

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

amount が nullable (NULL を許容する) の場合の解答例を以下に示します。
各コードの出力結果を統一させるため、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
...

解答例と実行結果

cut() を使用してグループ分けを行う解答例です。

df_receipt %>%
  summarise(
    sum_amount = sum(amount, na.rm = TRUE), 
    .by = customer_id
  ) %>% 
  filter(sum_amount > 0.0) %>% 
  mutate(
    pct_group = 
      cut(
        sum_amount, 
        breaks = quantile(sum_amount), 
        labels = FALSE, 
        right = FALSE
      ) %>% 
      as.character()
  ) %>% 
  arrange(customer_id) %>% 
  head(10)

# A tibble: 10 × 3
   customer_id    sum_amount pct_group
   <chr>               <dbl> <chr>    
CS001113000004       1298 2        
CS001114000005        626 2        
CS001115000010       3044 3        
CS001205000004       1988 3        
CS001205000006       3337 3        
CS001211000025        456 1        
CS001212000027        448 1        
CS001212000031        296 1        
CS001212000046        228 1        
CS001212000070        456 1    

解説

1. 売上金額の合計とフィルタリング

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

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

結果は次のようなデータフレームになります。

# 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
...

2. 売上合計を基にグループ分け

mutate(
  pct_group = 
    cut(
      sum_amount, 
      breaks = quantile(sum_amount), 
      labels = FALSE, 
      right = FALSE, 
      include.lowest = TRUE
    ) %>% 
    as.character()
)

mutate() と cut() を使用して、sum_amount に基づいてグループ分けを行い、新しい列 pct_group に格納します。

cut()
- cut() は、連続した数値データ (ここでは sum_amount) を指定した区間で分け、カテゴリカルなデータ (区間) として返します。
- breaks = quantile(sum_amount)
  quantile(sum_amount) で売上金額（sum_amount）の四分位点を計算し、その値を区切りの基準として使用します。これにより、売上金額を 4 つの区間に分割します。
- labels = FALSE
  各区間に番号 (1, 2, 3, 4) を割り当てます。
- right = FALSE
  区間の左端 (下限) を含み右端 (上限) を含まないようにします。
  例えば、区間 [0, 25) では、0 は含まれますが、25 は含まれません。
- include.lowest = TRUE
  sum_amount の最大値を最後の区間 (番号:4) に含めるようにします。
as.character()
cut() によって返された整数のカテゴリを文字列に変換します。

3. 並び替えと 10 顧客の抽出

arrange(customer_id) %>% 
head(10)

arrange() で customer_id 順に並び替えます。
head(10) で結果の先頭 10 行を取得します。

R (データベース操作)

利用するデータ

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

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

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

db_receipt %>% select(customer_id, amount) %>% collect()

# 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
...

解答例と実行結果

cut() は dbplyr ではサポートされていないため、代わりに case_when() を使用してデータのグループ分けを行います。

db_sales_amount = db_receipt %>% 
  summarise(
    sum_amount = sum(amount, na.rm = TRUE), 
    .by = customer_id
  ) %>% 
  filter(!is.na(sum_amount))

db_sales_pct = db_sales_amount %>%
  summarise(
    p25 = quantile(sum_amount, 0.25), 
    p50 = quantile(sum_amount, 0.5), 
    p75 = quantile(sum_amount, 0.75)
  )

db_result = db_sales_amount %>% 
  cross_join(db_sales_pct) %>% 
  mutate(
    pct_group = case_when(
      (sum_amount < p25) ~ "1", 
      (sum_amount < p50) ~ "2", 
      (sum_amount < p75) ~ "3", 
      (sum_amount >= p75) ~ "4"
    )
  ) %>% 
  select(customer_id, sum_amount, pct_group) %>% 
  arrange(customer_id) %>% 
  head(10)
  
db_result %>% collect()

# A tibble: 10 × 3
   customer_id    sum_amount pct_group
   <chr>               <dbl> <chr>    
CS001113000004       1298 2        
CS001114000005        626 2        
CS001115000010       3044 3        
CS001205000004       1988 3        
CS001205000006       3337 3        
CS001211000025        456 1        
CS001212000027        448 1        
CS001212000031        296 1        
CS001212000046        228 1        
CS001212000070        456 1   

解説

1. 売上金額の合計とフィルタリング

db_sales_amount = db_receipt %>% 
  summarise(
    sum_amount = sum(amount, na.rm = TRUE), 
    .by = customer_id
  ) %>% 
  filter(!is.na(sum_amount))

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

db_sales_amount は次のようなデータになります。

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

2. 売上合計の四分位数の算出

db_sales_pct = db_sales_amount %>%
  summarise(
    p25 = quantile(sum_amount, 0.25), 
    p50 = quantile(sum_amount, 0.5), 
    p75 = quantile(sum_amount, 0.75)
  )

sum_amount の 第1四分位数 (25%)、中央値 (50%)、第3四分位数 (75%) を計算します。

db_sales_pct は次のようなデータになります。

  p25   p50   p75
<dbl> <dbl> <dbl>
548.5  1478  3651

3. 売上合計を基にグループ分け

db_result = db_sales_amount %>% 
  cross_join(db_sales_pct) %>% 
  mutate(
    pct_group = case_when(
      (sum_amount < p25) ~ "1", 
      (sum_amount < p50) ~ "2", 
      (sum_amount < p75) ~ "3", 
      (sum_amount >= p75) ~ "4"
    )
  ) %>% 
  select(customer_id, sum_amount, pct_group) %>% 
  arrange(customer_id) %>% 
  head(10)

cross_join(db_sales_pct) により、全レコードに四分位数情報を追加します。

  customer_id    sum_amount   p25   p50   p75
  <chr>               <dbl> <dbl> <dbl> <dbl>
1 CS003515000195       5412 548.5  1478  3651
2 CS014415000077      14076 548.5  1478  3651
3 CS026615000085       2885 548.5  1478  3651
...

case_when() で、以下のルールで顧客を 4 つのグループに分類します。
- 1: sum_amount < p25
- 2: p25 <= sum_amount < p50
- 3: p50 <= sum_amount < p75
- 4: p75 <= sum_amount
arrange() で customer_id 順に並び替えます。
head(10) で結果の先頭 10 件を取得します。

4. 結果の取得

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 customer_id, SUM(amount) AS sum_amount
  FROM receipt
  GROUP BY customer_id
  HAVING (NOT(((SUM(amount)) IS NULL)))
),
q02 AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sum_amount) AS p25,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sum_amount) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sum_amount) AS p75
  FROM q01
),
q03 AS (
  SELECT LHS.*, RHS.*
  FROM q01 LHS
  CROSS JOIN q02 RHS
)
SELECT
  customer_id,
  sum_amount,
  CASE
WHEN ((sum_amount < p25)) THEN '1'
WHEN ((sum_amount < p50)) THEN '2'
WHEN ((sum_amount < p75)) THEN '3'
WHEN ((sum_amount >= p75)) THEN '4'
END AS pct_group
FROM q03 q01
ORDER BY customer_id
LIMIT 10

解答例

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

SQL

WITH customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  GROUP BY 
    customer_id
  HAVING
    SUM(amount) IS NOT NULL
),
percentiles AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sum_amount) AS p25,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sum_amount) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sum_amount) AS p75
  FROM 
    customer_sales
)
SELECT
  cs.customer_id,
  cs.sum_amount,
  CASE
    WHEN cs.sum_amount < p.p25 THEN '1'
    WHEN cs.sum_amount < p.p50 THEN '2'
    WHEN cs.sum_amount < p.p75 THEN '3'
    ELSE '4'
  END AS pct_group
FROM 
  customer_sales cs
CROSS JOIN 
  percentiles p
ORDER BY 
  cs.customer_id
LIMIT 10

解説

1. `customer_sales` の作成

SQL

WITH customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  GROUP BY 
    customer_id
  HAVING
    SUM(amount) IS NOT NULL
)

SUM(amount) と GROUP BY の組み合わせにより、顧客ごとに売上金額の合計 (sum_amount) を計算します。
HAVING SUM(amount) IS NOT NULL により、売上金額合計が NULL でない顧客を抽出します。
HAVING は集計後に条件を適用するため、売上が NULL の顧客は除外されます。

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

  customer_id    sum_amount
  <chr>               <dbl>
1 CS028414000014       6222
2 CS040415000178       6149
3 CS040414000073       4715
4 CS012515000143       5659
5 CS018205000001       8739
...

2. `percentiles` の作成

SQL

percentiles AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sum_amount) AS p25,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sum_amount) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sum_amount) AS p75
  FROM 
    customer_sales
)

customer_sales で計算した売上金額合計 (sum_amount) を基に、四分位数 (p25, p50, p75) を求めます。
PERCENTILE_CONT() は連続的な分位点を計算します。ここでは、25%、50%、75% の四分位数を求めています。

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

  p25   p50   p75
<dbl> <dbl> <dbl>
548.5  1478  3651

3. メインクエリ

SQL

SELECT
  cs.customer_id,
  cs.sum_amount,
  CASE
    WHEN cs.sum_amount < p.p25 THEN '1'
    WHEN cs.sum_amount < p.p50 THEN '2'
    WHEN cs.sum_amount < p.p75 THEN '3'
    ELSE '4'
  END AS pct_group
FROM 
  customer_sales cs
CROSS JOIN 
  percentiles p
ORDER BY 
  cs.customer_id
LIMIT 10

CROSS JOIN により、percentiles (1レコード) を customer_sales の全てのレコードに結合します。
CASE 式を使用して、以下のルールで顧客を 4 つのグループに分類します。
- 1: sum_amount < p25
- 2: p25 <= sum_amount < p50
- 3: p50 <= sum_amount < p75
- 4: p75 <= sum_amount
ORDER BY で customer_id 順に並べ替えます。
LIMIT 10 で結果の先頭 10 件を取得します。

実行結果の確認

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

query = sql("
WITH customer_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM 
    receipt
  GROUP BY 
    customer_id
  HAVING
    SUM(amount) IS NOT NULL
),
percentiles AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sum_amount) AS p25,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sum_amount) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sum_amount) AS p75
  FROM 
    customer_sales
)
SELECT
  cs.customer_id,
  cs.sum_amount,
  CASE
    WHEN cs.sum_amount < p.p25 THEN '1'
    WHEN cs.sum_amount < p.p50 THEN '2'
    WHEN cs.sum_amount < p.p75 THEN '3'
    ELSE '4'
  END AS pct_group
FROM 
  customer_sales cs
CROSS JOIN 
  percentiles p
ORDER BY 
  cs.customer_id
LIMIT 10
"
)

query %>% db_get_query(con)

# A tibble: 10 × 3
   customer_id    sum_amount pct_group
   <chr>               <dbl> <chr>    
CS001113000004       1298 2        
CS001114000005        626 2        
CS001115000010       3044 3        
CS001205000004       1988 3        
CS001205000006       3337 3        
CS001211000025        456 1        
CS001212000027        448 1        
CS001212000031        296 1        
CS001212000046        228 1        
CS001212000070        456 1   

リンク