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

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

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

演習問題

難易度

顧客データ（customer）の郵便番号（postal_cd）に対し、東京（先頭3桁が100〜209のもの）を1、それ以外のものを0に二値化せよ。さらにレシート明細データ（receipt）と結合し、全期間において売上実績のある顧客数を、作成した二値ごとにカウントせよ。

出力イメージ:

  tokyo n_customer
1     1       4400
2     0       3906

実行環境の構築について

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

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

解答例を以下に示します。
各コードの出力結果を統一させるため、n_customer の降順でソートしています。

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

利用するデータ

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

df_customer の顧客ID (customer_id) と郵便番号 (postal_cd)

df_customer %>% select(customer_id, postal_cd)

# A tibble: 21,971 × 2
   customer_id    postal_cd
   <chr>          <chr>    
 1 CS021313000114 259-1113 
 2 CS037613000071 136-0076 
 3 CS031415000172 151-0053 
 4 CS028811000001 245-0016 
 5 CS001215000145 144-0055 
 6 CS020401000016 174-0065 
 7 CS015414000103 136-0073 
 ...

df_receipt の顧客ID (customer_id)

df_receipt %>% select(customer_id)

# A tibble: 104,681 × 1
   customer_id   
   <chr>         
 1 CS006214000001
 2 CS008415000097
 3 CS028414000014
 4 ZZ000000000000
 5 CS025415000050
 6 CS003515000195
 7 CS024514000042
 ...

解答例 (1)

解答コードと実行結果

df_cust = df_customer %>% 
  select(customer_id, postal_cd) %>% 
  mutate(
    tokyo = if_else(
      between(
        stringr::str_sub(postal_cd, 1L, 3L), "100", "209"
      ), 
      1L, 0L
    )
  )

df_rec = df_receipt %>% distinct(customer_id)

df_cust %>% 
  inner_join(df_rec, by = "customer_id") %>% 
  count(tokyo, name = "n_customer", sort = TRUE)

# A tibble: 2 × 2
  tokyo n_customer
  <int>      <int>
1     1       4400
2     0       3906

解説

1. `tokyo` フラグの作成

df_cust = df_customer %>% 
  select(customer_id, postal_cd) %>% 
  mutate(
    tokyo = if_else(
      between(
        stringr::str_sub(postal_cd, 1L, 3L), "100", "209"
      ), 
      1L, 0L
    )
  )

東京か否かを 1/0 で二値化し、tokyo 列として作成します。

select() で df_customer から customer_id と postal_cd を抽出します。
str_sub(postal_cd, 1L, 3L) で、postal_cd の先頭 3 桁を取得します。
between(..., "100", "209") により、先頭 3 桁が 100 〜 209 の範囲内であれば TRUE、そうでなければ FALSE を返します。
ifelse() により、条件を満たす場合は tokyo = 1、満たさない場合は tokyo = 0 とします。

2. 購入履歴のある顧客リストの作成

df_rec = df_receipt %>% distinct(customer_id)

df_receipt から customer_id をユニークに抽出します。
これにより、購入履歴のある顧客リスト (df_rec) が作成されます。

3. `tokyo` フラグ別の顧客数のカウント

df_cust %>% 
  inner_join(df_rec, by = "customer_id") %>% 
  count(tokyo, name = "n_customer", sort = TRUE)

inner_join() により、 df_cust を基準に customer_id をキーとして df_rec を内部結合し、購入履歴のある顧客に絞り込みます。
count(tokyo, name = "n_customer") により、tokyo ごとの顧客数を集計し、結果を n_customer 列に格納します。
sort = TRUE で、n_customer の降順に並び替えます。

解答例 (2)

解答コードと実行結果

inner_join() の代わりに semi_join() を使用した解答例です。

df_cust %>% 
  semi_join(df_rec, by = "customer_id") %>% 
  count(tokyo, name = "n_customer", sort = TRUE)

# A tibble: 2 × 2
  tokyo n_customer
  <int>      <int>
1     1       4400
2     0       3906

解説

df_cust %>% semi_join(df_rec, by = "customer_id") により、
df_cust の customer_id が df_rec に存在する行だけを残します。
これにより、購入履歴のある顧客に絞り込まれます。

R (データベース操作)

解答例 (1)

データフレーム操作の解答例 (1) はデータベース操作にも適用できます。
データフレーム df_customer、df_receipt をテーブル参照 db_customer、db_receipt に置き換えることで、同様の操作を実行できます。

db_cust = db_customer %>% 
  select(customer_id, postal_cd) %>% 
  mutate(
    tokyo = if_else(
      between(
        stringr::str_sub(postal_cd, 1L, 3L), "100", "209"
      ), 
      1L, 0L
    )
  )

db_rec = db_receipt %>% distinct(customer_id)

db_result = db_cust %>% 
  inner_join(db_rec, by = "customer_id") %>% 
  count(tokyo, name = "n_customer", sort = TRUE)

db_result %>% collect()

# A tibble: 2 × 2
  tokyo n_customer
  <int>      <dbl>
1     1       4400
2     0       3906

db_result %>% collect()
データベース操作の結果を R のデータフレーム (tibble) として取得します。

解答例 (2)

データフレーム操作の解答例 (2) は同様にデータベース操作に適用できます。
(inner_join() の代わりに semi_join() を使用した解答例です。)

db_result = db_cust %>% 
  semi_join(db_rec, by = "customer_id") %>% 
  count(tokyo, name = "n_customer", sort = TRUE)

db_result %>% collect()

# A tibble: 2 × 2
  tokyo n_customer
  <int>      <dbl>
1     1       4400
2     0       3906

SQL

利用するデータ

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

customer テーブルの顧客ID (customer_id) と郵便番号 (postal_cd)

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

# A tibble: 21,971 × 2
  customer_id    postal_cd
  <chr>          <chr>    
1 CS021313000114 259-1113 
2 CS037613000071 136-0076 
3 CS031415000172 151-0053 
4 CS028811000001 245-0016 
5 CS001215000145 144-0055 
6 CS020401000016 174-0065 
7 CS015414000103 136-0073 
...

receipt テーブルの顧客ID (customer_id)

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

# A tibble: 104,681 × 1
  customer_id   
  <chr>         
1 CS006214000001
2 CS008415000097
3 CS028414000014
4 ZZ000000000000
5 CS025415000050
6 CS003515000195
7 CS024514000042
...

解答例 (1)

自動生成された SQL クエリ

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

db_result %>% show_query(cte = TRUE)

SQL

WITH q01 AS (
  SELECT
    customer_id,
    postal_cd,
    CASE WHEN (SUBSTR(postal_cd, 1, 3) BETWEEN '100' AND '209') THEN 1 WHEN NOT (SUBSTR(postal_cd, 1, 3) BETWEEN '100' AND '209') THEN 0 END AS tokyo
  FROM customer
),
q02 AS (
  SELECT DISTINCT customer_id
  FROM receipt
),
q03 AS (
  SELECT LHS.*
  FROM q01 LHS
  INNER JOIN q02 RHS
    ON (LHS.customer_id = RHS.customer_id)
)
SELECT tokyo, COUNT(*) AS n_customer
FROM q03 q01
GROUP BY tokyo
ORDER BY n_customer DESC

解答クエリ (1-1)

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

SQL

WITH customer_region AS (
  SELECT
    customer_id,
    CASE 
      WHEN SUBSTR(postal_cd, 1, 3) BETWEEN '100' AND '209' THEN 1 
      ELSE 0 
    END AS tokyo
  FROM 
    customer
),
active_customers AS (
  SELECT 
    c.*
  FROM 
    customer_region c
  INNER JOIN 
    (SELECT DISTINCT customer_id FROM receipt) r
  USING (customer_id)
)
SELECT 
  tokyo, 
  COUNT(*) AS n_customer
FROM 
  active_customers
GROUP BY 
  tokyo
ORDER BY 
  n_customer DESC

解答クエリ (1-2)

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

SQL

SELECT 
  CASE 
    WHEN SUBSTR(c.postal_cd, 1, 3) BETWEEN '100' AND '209' THEN 1 
    ELSE 0 
  END AS tokyo, 
  COUNT(*) AS n_customer
FROM 
  customer c
INNER JOIN 
  (SELECT DISTINCT customer_id FROM receipt) r 
USING (customer_id)
GROUP BY 
  tokyo
ORDER BY 
  n_customer DESC

解説

1. 購入履歴のある顧客の抽出

SQL

SELECT 
  ...
FROM 
  customer c
INNER JOIN
  (SELECT DISTINCT customer_id FROM receipt) r
USING (customer_id)

receipt テーブルに存在するユニークな customer_id を取得し、customer テーブルを基準に customer_id で INNER JOIN (内部結合) します。
これにより、購入履歴がある顧客のみが抽出されます。

2. 東京都の判定

SQL

CASE
  WHEN SUBSTR(c.postal_cd, 1, 3) BETWEEN '100' AND '209' THEN 1
  ELSE 0
END AS tokyo

postal_cd の先頭 3 桁を SUBSTR(c.postal_cd, 1, 3) で取得します。
'100' から '209' の範囲にある場合、tokyo = 1（東京都）、それ以外は tokyo = 0 とします。

3. 顧客数の集計

SQL

COUNT(*) AS n_customer

GROUP BY との組み合わせにより、tokyo ごとに顧客数を集計します。

4. グループ化と並び替え

SQL

GROUP BY 
  tokyo
ORDER BY 
  n_customer DESC

tokyo ごとにグループ化ます。
n_customer の降順に並び替えます。

実行結果の確認

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

query = sql("
SELECT 
  CASE 
    WHEN SUBSTR(c.postal_cd, 1, 3) BETWEEN '100' AND '209' THEN 1 
    ELSE 0 
  END AS tokyo, 
  COUNT(*) AS n_customer
FROM 
  customer c
INNER JOIN 
  (SELECT DISTINCT customer_id FROM receipt) r 
USING (customer_id)
GROUP BY 
  tokyo
ORDER BY 
  n_customer DESC
"
)

query %>% db_get_query(con)

# A tibble: 2 × 2
  tokyo n_customer
  <int>      <dbl>
1     1       4400
2     0       3906

解答例 (2)

自動生成された SQL クエリ

データベース操作の解答例 (2) による操作結果 (db_result) に基づき、自動生成された SQLクエリを show_query() で確認できます。
(INNER JOIN の代わりに WHERE EXISTS を使用した解答例です。)

db_result %>% show_query(cte = TRUE)

SQL

WITH q01 AS (
  SELECT
    customer_id,
    postal_cd,
    CASE WHEN (SUBSTR(postal_cd, 1, 3) BETWEEN '100' AND '209') THEN 1 WHEN NOT (SUBSTR(postal_cd, 1, 3) BETWEEN '100' AND '209') THEN 0 END AS tokyo
  FROM customer
),
q02 AS (
  SELECT DISTINCT customer_id
  FROM receipt
),
q03 AS (
  SELECT LHS.*
  FROM q01 LHS
  WHERE EXISTS (
    SELECT 1 FROM q02 RHS
    WHERE (LHS.customer_id = RHS.customer_id)
  )
)
SELECT tokyo, COUNT(*) AS n_customer
FROM q03 q01
GROUP BY tokyo
ORDER BY n_customer DESC

解答クエリ (2-1)

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

SQL

WITH customer_region AS (
  SELECT
    customer_id,
    CASE 
      WHEN SUBSTR(postal_cd, 1, 3) BETWEEN '100' AND '209' THEN 1 
      ELSE 0 
    END AS tokyo
  FROM 
    customer
),
active_customers AS (
  SELECT 
    c.*
  FROM 
    customer_region c
  WHERE EXISTS (
    SELECT 1 
    FROM 
      (SELECT DISTINCT customer_id FROM receipt) r
    WHERE 
      c.customer_id = r.customer_id
  )
)
SELECT 
  tokyo, 
  COUNT(*) AS n_customer
FROM 
  active_customers
GROUP BY 
  tokyo
ORDER BY 
  n_customer DESC

解答クエリ (2-2)

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

SQL

SELECT 
  CASE 
    WHEN SUBSTR(c.postal_cd, 1, 3) BETWEEN '100' AND '209' THEN 1 
    ELSE 0 
  END AS tokyo, 
  COUNT(*) AS n_customer
FROM 
  customer c
WHERE EXISTS (
  SELECT 1 
  FROM 
    (SELECT DISTINCT customer_id FROM receipt) r
  WHERE 
    c.customer_id = r.customer_id
)
GROUP BY 
  tokyo
ORDER BY 
  n_customer DESC

解説

解答クエリ (1-2) との相違点は以下の箇所です。

SQL

SELECT 
  ...
FROM 
  customer c
WHERE EXISTS (
  SELECT 1 
  FROM 
    (SELECT DISTINCT customer_id FROM receipt) r
  WHERE 
    c.customer_id = r.customer_id
)

customer テーブルから、receipt テーブルに同じ customer_id が存在するレコードのみを抽出します。

サブクエリ (SELECT DISTINCT ...)
receipt テーブルから customer_id を DISTINCT で取得し、重複を排除します。これにより、receipt テーブルの中で一意の customer_id が抽出されます。
WHERE EXISTS
サブクエリ内 (SELECT 1 ...) で、customer テーブルの各レコードに対して、receipt テーブルに同じ customer_id が存在するかをチェックします。
存在すれば EXISTS は TRUE が返し、その customer_id を持つ customer テーブルのレコードが結果に含まれます。

実行結果の確認

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

query = sql("
SELECT 
  CASE 
    WHEN SUBSTR(c.postal_cd, 1, 3) BETWEEN '100' AND '209' THEN 1 
    ELSE 0 
  END AS tokyo, 
  COUNT(*) AS n_customer
FROM 
  customer c
WHERE EXISTS (
  SELECT 1 
  FROM 
    (SELECT DISTINCT customer_id FROM receipt) r
  WHERE 
    c.customer_id = r.customer_id
)
GROUP BY 
  tokyo
ORDER BY 
  n_customer DESC
"
)
query %>% db_get_query(con)

# A tibble: 2 × 2
  tokyo n_customer
  <int>      <dbl>
1     1       4400
2     0       3906

リンク