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

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

店舗ごとの最頻商品の取得

目次

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

演習問題

難易度
レシート明細データ（receipt）に対し、店舗コード（store_cd）ごとに商品コード（product_cd）の最頻値を求め、10件表示させよ。

出力イメージ:

   store_cd product_cd     n
 1 S14027   P060303001   152
 2 S14012   P060303001   142
 3 S14028   P060303001   140
 4 S12030   P060303001   115
 5 S13031   P060303001   115
 6 S12013   P060303001   107
 7 S13044   P060303001    96
 8 S14024   P060303001    96
 9 S12029   P060303001    92
10 S13004   P060303001    88

実行環境の構築について

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

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

最も頻出する商品コードが複数存在する場合、それらを全て抽出する解答例を以下に示します。
各コードの出力結果を統一させるため、n (最頻値)の降順と store_cd の昇順でソートしています。

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

利用するデータ

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

  • df_receipt の店舗コード (store_cd) と商品コード (product_cd)

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

R
df_receipt %>% select(store_cd, product_cd)
# A tibble: 104,681 × 2
  store_cd product_cd
  <chr>    <chr>     
1 S14006   P070305012
2 S13008   P070701017
3 S14028   P060101005
4 S14042   P050301001
5 S14025   P060102007
6 S13003   P050102002
7 S14024   P080101005
...

解答例 (1)

解答コードと実行結果

R
df_receipt %>% 
  count(store_cd, product_cd) %>% 
  filter(n == max(n), .by = store_cd) %>% 
  arrange(desc(n), store_cd) %>% 
  head(10)
# A tibble: 10 × 3
   store_cd product_cd     n
   <chr>    <chr>      <int>
 1 S14027   P060303001   152
 2 S14012   P060303001   142
 3 S14028   P060303001   140
 4 S12030   P060303001   115
 5 S13031   P060303001   115
 6 S12013   P060303001   107
 7 S13044   P060303001    96
 8 S14024   P060303001    96
 9 S12029   P060303001    92
10 S13004   P060303001    88

解説

  • count(store_cd, product_cd)
    df_receipt に対し、店舗 (store_cd) と商品 (product_cd) の組み合わせごとに出現回数 (n) を集計します。
  • filter(n == max(n), .by = store_cd)
    • 各店舗ごとに最頻出の商品を抽出します。
    • 同じ max(n) の値を持つ商品が複数ある場合、それらが全て結果に含まれます。
  • arrange(desc(n), store_cd)
    n の降順で結果を並べ替え、同じ出現回数の店舗については store_cd の昇順に並べます。

解答例 (2)

解答コードと実行結果

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

R
df_receipt %>% 
  count(store_cd, product_cd) %>% 
  slice_max(n, n = 1, with_ties = TRUE, by = store_cd) %>% 
  arrange(desc(n), store_cd) %>% 
  head(10)
# A tibble: 10 × 3
   store_cd product_cd     n
   <chr>    <chr>      <int>
 1 S14027   P060303001   152
 2 S14012   P060303001   142
 3 S14028   P060303001   140
 4 S12030   P060303001   115
 5 S13031   P060303001   115
 6 S12013   P060303001   107
 7 S13044   P060303001    96
 8 S14024   P060303001    96
 9 S12029   P060303001    92
10 S13004   P060303001    88

解説

  • slice_max(n, n = 1, with_ties = TRUE, by = store_cd)
    • 各店舗 (store_cd) ごとに出現回数 n が最大となる商品 (product_cd) を抽出します。
    • with_ties = TRUE の指定により、該当する商品が複数ある場合、それらが全て抽出されます。

R (データベース操作)

利用するデータ

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

  • db_receipt の店舗コード (store_cd) と商品コード (product_cd)

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

R
db_receipt %>% select(store_cd, product_cd) %>% collect()
# A tibble: 104,681 × 2
  store_cd product_cd
  <chr>    <chr>     
1 S14006   P070305012
2 S13008   P070701017
3 S14028   P060101005
4 S14042   P050301001
5 S14025   P060102007
6 S13003   P050102002
7 S14024   P080101005
...

解答例 (1)

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

R
db_result = db_receipt %>% 
  count(store_cd, product_cd) %>% 
  filter(n == max(n), .by = store_cd) %>% 
  arrange(desc(n), store_cd) %>% 
  head(10)

db_result %>% collect()
# A tibble: 10 × 3
   store_cd product_cd     n
   <chr>    <chr>      <dbl>
 1 S14027   P060303001   152
 2 S14012   P060303001   142
 3 S14028   P060303001   140
 4 S12030   P060303001   115
 5 S13031   P060303001   115
 6 S12013   P060303001   107
 7 S13044   P060303001    96
 8 S14024   P060303001    96
 9 S12029   P060303001    92
10 S13004   P060303001    88
  • db_result %>% collect()
    データベース操作の結果を R のデータフレーム (tibble) として取得します。

解答例 (2)

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

R
db_result = db_receipt %>% 
  count(store_cd, product_cd) %>% 
  slice_max(n, n = 1, with_ties = TRUE, by = store_cd) %>% 
  arrange(desc(n), store_cd) %>% 
  head(10)

db_result %>% collect()
# A tibble: 10 × 3
   store_cd product_cd     n
   <chr>    <chr>      <dbl>
 1 S14027   P060303001   152
 2 S14012   P060303001   142
 3 S14028   P060303001   140
 4 S12030   P060303001   115
 5 S13031   P060303001   115
 6 S12013   P060303001   107
 7 S13044   P060303001    96
 8 S14024   P060303001    96
 9 S12029   P060303001    92
10 S13004   P060303001    88

SQL

利用するデータ

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

  • receipt テーブルの店舗コード (store_cd) と商品コード (product_cd)

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

R
"SELECT store_cd, product_cd FROM receipt" %>% db_get_query(con)
# A tibble: 104,681 × 2
  store_cd product_cd
  <chr>    <chr>     
1 S14006   P070305012
2 S13008   P070701017
3 S14028   P060101005
4 S14042   P050301001
5 S14025   P060102007
6 S13003   P050102002
7 S14024   P080101005
...

解答例 (1)

自動生成された SQL クエリ

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

R
db_result %>% show_query(cte = TRUE)
SQL
WITH q01 AS (
  SELECT store_cd, product_cd, COUNT(*) AS n
  FROM receipt
  GROUP BY store_cd, product_cd
),
q02 AS (
  SELECT q01.*, MAX(n) OVER (PARTITION BY store_cd) AS col01
  FROM q01
)
SELECT store_cd, product_cd, n
FROM q02 q01
WHERE (n = col01)
ORDER BY n DESC, store_cd
LIMIT 10

中間テーブル名および列名の q01、q02、col01 は dbplyr によって生成されたエイリアス名です。

解答クエリ

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

SQL
WITH product_num AS (
  SELECT 
    store_cd, 
    product_cd, 
    COUNT(*) AS n
  FROM 
    receipt
  GROUP BY 
    store_cd, product_cd
),
product_max AS (
  SELECT 
    store_cd,
    product_cd,
    n, 
    MAX(n) OVER (PARTITION BY store_cd) AS max_n
  FROM 
    product_num
)
SELECT 
  store_cd, 
  product_cd, 
  n
FROM 
  product_max
WHERE 
  n = max_n
ORDER BY 
  n DESC, store_cd
LIMIT 10

解説

  • product_num の作成
    receipt テーブルから、店舗 (store_cd) と商品 (product_cd) の組み合わせごとに出現回数 (n) をカウントします。

  • product_max の作成
    MAX(n) OVER (PARTITION BY store_cd) ウィンドウ関数により、店舗ごとの最頻出値を計算し、max_n 列として格納します。

  • メインクエリ

    • n = max_n の条件により、各店舗ごとに最頻出となる商品を抽出します。
    • ORDER BY n DESC, store_cd により、n の降順で結果を並べ替え、同じ出現回数の店舗については store_cd で昇順に並べます。

実行結果の確認

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

R
query = sql("
WITH product_num AS (
  SELECT 
    store_cd, 
    product_cd, 
    COUNT(*) AS n
  FROM 
    receipt
  GROUP BY 
    store_cd, product_cd
),
product_max AS (
  SELECT 
    store_cd,
    product_cd,
    n, 
    MAX(n) OVER (PARTITION BY store_cd) AS max_n
  FROM 
    product_num
)
SELECT 
  store_cd, 
  product_cd, 
  n
FROM 
  product_max
WHERE 
  n = max_n
ORDER BY 
  n DESC, store_cd
LIMIT 10
"
)

query %>% db_get_query(con)
# A tibble: 10 × 3
   store_cd product_cd     n
   <chr>    <chr>      <dbl>
 1 S14027   P060303001   152
 2 S14012   P060303001   142
 3 S14028   P060303001   140
 4 S12030   P060303001   115
 5 S13031   P060303001   115
 6 S12013   P060303001   107
 7 S13044   P060303001    96
 8 S14024   P060303001    96
 9 S12029   P060303001    92
10 S13004   P060303001    88

解答例 (2)

自動生成された SQL クエリ

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

R
db_result %>% show_query(cte = TRUE)
SQL
WITH q01 AS (
  SELECT store_cd, product_cd, COUNT(*) AS n
  FROM receipt
  GROUP BY store_cd, product_cd
),
q02 AS (
  SELECT q01.*, RANK() OVER (PARTITION BY store_cd ORDER BY n DESC) AS col01
  FROM q01
)
SELECT store_cd, product_cd, n
FROM q02 q01
WHERE (col01 <= 1)
ORDER BY n DESC, store_cd
LIMIT 10

解答クエリ

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

SQL
WITH product_num AS (
  SELECT 
    store_cd,
    product_cd,
    COUNT(*) AS n
  FROM 
    receipt
  GROUP BY 
    store_cd, product_cd
),
product_rank AS (
  SELECT 
    store_cd,
    product_cd,
    n, 
    RANK() OVER (
      PARTITION BY store_cd
      ORDER BY n DESC
    ) AS rank
  FROM 
    product_num
)
SELECT 
  store_cd,
  product_cd,
  n
FROM 
  product_rank
WHERE
  rank = 1
ORDER BY 
  n DESC, store_cd
LIMIT 10

解説

解答例 (1) との相違点は以下の箇所です。

  • product_rank の作成
    RANK() OVER(...) ウィンドウ関数を使い、各店舗 (store_cd) ごとに商品 (product_cd) を出現回数 (n) の高い順でランク付けし、rank 列として格納します。

  • メインクエリ
    rank = 1 の条件により、各店舗ごとに最頻出となる商品を抽出します。

実行結果の確認

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

R
query = sql("
WITH product_num AS (
  SELECT 
    store_cd,
    product_cd,
    COUNT(*) AS n
  FROM 
    receipt
  GROUP BY 
    store_cd, product_cd
),
product_rank AS (
  SELECT 
    store_cd,
    product_cd,
    n, 
    RANK() OVER (
      PARTITION BY store_cd
      ORDER BY n DESC
    ) AS rank
  FROM 
    product_num
)
SELECT 
  store_cd,
  product_cd,
  n
FROM 
  product_rank
WHERE
  rank = 1
ORDER BY 
  n DESC, store_cd
LIMIT 10
"
)

query %>% db_get_query(con)
# A tibble: 10 × 3
   store_cd product_cd     n
   <chr>    <chr>      <dbl>
 1 S14027   P060303001   152
 2 S14012   P060303001   142
 3 S14028   P060303001   140
 4 S12030   P060303001   115
 5 S13031   P060303001   115
 6 S12013   P060303001   107
 7 S13044   P060303001    96
 8 S14024   P060303001    96
 9 S12029   P060303001    92
10 S13004   P060303001    88