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

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

日別売上金額の集計と過去売上データの結合

目次

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

演習問題

難易度
レシート明細データ（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、各日付のデータに対し、前回、前々回、3回前に売上があった日のデータを結合せよ。そして結果を10件表示せよ。

出力イメージ:

   sales_ymd amount lag_sales_ymd lag_amount
 1  20170102  24165      20170101      33723
 2  20170103  27503      20170101      33723
 3  20170103  27503      20170102      24165
 4  20170104  36165      20170101      33723
 5  20170104  36165      20170102      24165
 6  20170104  36165      20170103      27503
 7  20170105  37830      20170102      24165
 8  20170105  37830      20170103      27503
 9  20170105  37830      20170104      36165
10  20170106  32387      20170103      27503

amount が nullable (NULL を許容する) の場合で、各日付ごとに縦持ちのデータとして取得する解答例を以下に示します。
各コードの出力結果を統一させるため、sales_ymd と lag_sales_ymd (3 回前までの売上日) でソートしています。

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

利用するデータ

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

  • df_receipt の売上年⽉⽇ (sales_ymd) と売上金額 (amount)

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

R
df_receipt %>% select(sales_ymd, amount)
# A tibble: 104,681 × 2
  sales_ymd amount
      <int>  <dbl>
1  20181103    158
2  20181118     81
3  20170712    170
4  20190205     25
5  20180821     90
6  20190605    138
7  20181205     30
8  20190922    128
9  20170504    770
...

解答例と実行結果

R
n_lag = 3L

df_sales_with_lag = df_receipt %>% 
  summarise(
    amount = sum(amount, na.rm = TRUE), .by = "sales_ymd"
  ) %>% 
  filter(amount > 0.0) %>% 
  arrange(sales_ymd) %>% 
  mutate(
    lag_ymd = lag(sales_ymd, n = n_lag, default = -1L)
  )

.by = join_by(
    between(y$sales_ymd, x$lag_ymd, x$sales_ymd, bounds = "[)")
  )

df_result = df_sales_with_lag %>% 
  inner_join(
    df_sales_with_lag, by = .by, suffix = c("", ".y")
  ) %>% 
  select(
    sales_ymd, amount, lag_sales_ymd = sales_ymd.y, lag_amount = amount.y
  ) %>% 
  arrange(sales_ymd, lag_sales_ymd)

先頭の 10 行の出力 :

R
df_result %>% head(10)
# A tibble: 10 × 4
   sales_ymd amount lag_sales_ymd lag_amount
       <int>  <dbl>         <int>      <dbl>
 1  20170102  24165      20170101      33723
 2  20170103  27503      20170101      33723
 3  20170103  27503      20170102      24165
 4  20170104  36165      20170101      33723
 5  20170104  36165      20170102      24165
 6  20170104  36165      20170103      27503
 7  20170105  37830      20170102      24165
 8  20170105  37830      20170103      27503
 9  20170105  37830      20170104      36165
10  20170106  32387      20170103      27503

最後の 6 行の出力 :

R
df_result %>% tail(6)
# A tibble: 6 × 4
  sales_ymd amount lag_sales_ymd lag_amount
      <int>  <dbl>         <int>      <dbl>
1  20191030  26602      20191027      37484
2  20191030  26602      20191028      40161
3  20191030  26602      20191029      36091
4  20191031  25216      20191028      40161
5  20191031  25216      20191029      36091
6  20191031  25216      20191030      26602

解説

ステップ1: 売上データの集計

R
n_lag = 3L

df_sales_with_lag = df_receipt %>% 
  summarise(
    amount = sum(amount, na.rm = TRUE), .by = "sales_ymd"
  ) %>% 
  filter(amount > 0.0) %>% 
  arrange(sales_ymd) %>% 
  mutate(
    lag_ymd = lag(sales_ymd, n = n_lag, default = -1L)
  )

n_lag = 3L により、何回分前までの売上データを結合するかを柔軟に変更できるようにしてます。

  1. summarise() を使用し、df_receipt の sales_ymd (売上日) ごとに 売上金額 amount の合計 を計算します。
  2. filter(amount > 0) により、売上が発生していない sales_ymd のデータを除外します。
  3. arrange(sales_ymd) で、売上日 sales_ymd の 昇順 に並べ替えます。
  4. mutate() で lag() を使い、n_lag = 3 回前の sales_ymd を lag_ymd 列に格納します。
    • lag_ymd には、sales_ymd の n_lag 回前の値が入ります。
    • n_lag 回分のデータが存在しない場合 (最初の n_lag 回分) は、-1L を格納します。

以上により、df_sales_with_lag は次のようなデータフレームとなります。

# A tibble: 1,034 × 3
   sales_ymd amount  lag_ymd
       <int>  <dbl>    <int>
 1  20170101  33723       -1
 2  20170102  24165       -1
 3  20170103  27503       -1
 4  20170104  36165 20170101
 5  20170105  37830 20170102
 6  20170106  32387 20170103
 7  20170107  23415 20170104
 8  20170108  24737 20170105
 9  20170109  26718 20170106
 ...

ステップ2: 結合条件の定義

R
.by = join_by(
    between(y$sales_ymd, x$lag_ymd, x$sales_ymd, bounds = "[)")
  )
  1. join_by() を使用して、結合の条件を定義します。
  2. between(y$sales_ymd, x$lag_ymd, x$sales_ymd, bounds = "[)") により、結合範囲を指定します。
    • y$sales_ymd が x$lag_ymd 以上 x$sales_ymd 未満 の範囲にある場合に結合を行います。
    • bounds = "[)" は、左端 (x$lag_ymd) を含み、右端 (x$sales_ymd) を含まない 範囲を表します。

ステップ3: データの結合

R
df_result = df_sales_with_lag %>% 
  inner_join(
    df_sales_with_lag, by = .by, suffix = c("", ".y")
  ) %>% 
  select(
    sales_ymd, amount, lag_sales_ymd = sales_ymd.y, lag_amount = amount.y
  ) %>% 
  arrange(sales_ymd, lag_sales_ymd)
  1. inner_join() を使用して、df_sales_with_lag を 自身と結合 (自己結合) します。
    • 左側の df_sales_with_lag に対して、右側の sales_ymd.y が lag_ymd 以上 sales_ymd 未満の範囲にあるレコードを結合します。
  2. select() により、必要なカラムを抽出・リネームします。
    • sales_ymd : 現在の売上日
    • amount : 現在の売上金額
    • lag_sales_ymd = sales_ymd.y : n_lag 回前までの売上日
    • lag_amount = amount.y : n_lag 回前までの売上金額
  3. arrange(sales_ymd, lag_sales_ymd) で売上日の昇順に並び替えます。

R (データベース操作)

利用するデータ

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

  • db_receipt の売上年⽉⽇ (sales_ymd) と売上金額 (amount)

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

R
db_receipt %>% select(sales_ymd, amount) %>% collect()
# A tibble: 104,681 × 2
  sales_ymd amount
      <int>  <dbl>
1  20181103    158
2  20181118     81
3  20170712    170
4  20190205     25
5  20180821     90
6  20190605    138
7  20181205     30
8  20190922    128
9  20170504    770
...

解答例と実行結果

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

R
n_lag = 3L

db_sales_with_lag = db_receipt %>% 
  summarise(
    amount = sum(amount), na.rm = TRUE, .by = "sales_ymd"
  ) %>% 
  filter(!is.na(amount)) %>% 
  window_order(sales_ymd) %>% 
  mutate(
    lag_ymd = lag(sales_ymd, n = n_lag, default = -1L)
  )

.by = join_by(
    between(y$sales_ymd, x$lag_ymd, x$sales_ymd, bounds = "[)")
  )

db_result = db_sales_with_lag %>% 
  inner_join(
    db_sales_with_lag, by = .by, suffix = c("", ".y")
  ) %>% 
  select(
    sales_ymd, amount, lag_sales_ymd = sales_ymd.y, lag_amount = amount.y
  ) %>% 
  arrange(sales_ymd, lag_sales_ymd)

先頭の 10 行の出力 :

R
db_result %>% collect() %>% head(10)
# A tibble: 10 × 4
   sales_ymd amount lag_sales_ymd lag_amount
       <int>  <dbl>         <int>      <dbl>
 1  20170102  24165      20170101      33723
 2  20170103  27503      20170101      33723
 3  20170103  27503      20170102      24165
 4  20170104  36165      20170101      33723
 5  20170104  36165      20170102      24165
 6  20170104  36165      20170103      27503
 7  20170105  37830      20170102      24165
 8  20170105  37830      20170103      27503
 9  20170105  37830      20170104      36165
10  20170106  32387      20170103      27503

最後の 6 行の出力 :

R
db_result %>% collect() %>% tail(6)
# A tibble: 6 × 4
  sales_ymd amount lag_sales_ymd lag_amount
      <int>  <dbl>         <int>      <dbl>
1  20191030  26602      20191027      37484
2  20191030  26602      20191028      40161
3  20191030  26602      20191029      36091
4  20191031  25216      20191028      40161
5  20191031  25216      20191029      36091
6  20191031  25216      20191030      26602

解説

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

  • filter(!is.na(amount)) で不要なデータを除外
    amount が NA となるレコードを除外します。

  • window_order(sales_ymd) でウィンドウ関数の順序を指定
    lag() で過去のデータを取得する際に、sales_ymd の昇順で処理を行うように指定します。

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

SQL

利用するデータ

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

  • receipt テーブルの売上年⽉⽇ (sales_ymd) と売上金額 (amount)

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

R
"SELECT sales_ymd, amount FROM receipt" %>% db_get_query(con)
# A tibble: 104,681 × 2
  sales_ymd amount
      <int>  <dbl>
1  20181103    158
2  20181118     81
3  20170712    170
4  20190205     25
5  20180821     90
6  20190605    138
7  20181205     30
8  20190922    128
9  20170504    770
...

自動生成された SQL クエリ

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

R
db_result %>% show_query(cte = TRUE)
SQL
WITH q01 AS (
  SELECT sales_ymd, SUM(amount) AS amount
  FROM receipt
  GROUP BY sales_ymd
  HAVING (NOT(((SUM(amount)) IS NULL)))
),
q02 AS (
  SELECT q01.*, LAG(sales_ymd, 3, -1) OVER (ORDER BY sales_ymd) AS lag_ymd
  FROM q01
),
q03 AS (
  SELECT q01.*, LAG(sales_ymd, 3, -1) OVER (ORDER BY sales_ymd) AS lag_ymd
  FROM q01
),
q04 AS (
  SELECT
    LHS.sales_ymd AS sales_ymd,
    LHS.amount AS amount,
    RHS.sales_ymd AS lag_sales_ymd,
    RHS.amount AS lag_amount
  FROM q02 LHS
  INNER JOIN q03 RHS
    ON (LHS.lag_ymd <= RHS.sales_ymd AND LHS.sales_ymd > RHS.sales_ymd)
)
SELECT q01.*
FROM q04 q01
ORDER BY sales_ymd, lag_sales_ymd

q02 と q03 が同じクエリとなっていますが、これは、同じテーブル参照同士 (db_sales_with_lag) を結合したためです。

解答例

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

SQL
WITH sales_data AS (
  SELECT 
    sales_ymd, 
    SUM(amount) AS amount,
    LAG(sales_ymd, 3, -1) OVER (ORDER BY sales_ymd) AS lag_ymd
  FROM 
    receipt
  GROUP BY 
    sales_ymd
  HAVING 
    SUM(amount) IS NOT NULL
)
SELECT 
  L.sales_ymd,
  L.amount,
  R.sales_ymd AS lag_sales_ymd,
  R.amount AS lag_amount
FROM 
  sales_data L
INNER JOIN 
  sales_data R
ON (
  L.lag_ymd <= R.sales_ymd 
  AND L.sales_ymd > R.sales_ymd
)
ORDER BY 
  L.sales_ymd, R.sales_ymd

解説

receipt テーブルに基づいて、各売上日に対して 3 回前までの売上データを結合するクエリです。

sales_data の作成

SQL
WITH sales_data AS (
  SELECT 
    sales_ymd, 
    SUM(amount) AS amount,
    LAG(sales_ymd, 3, -1) OVER (ORDER BY sales_ymd) AS lag_ymd
  FROM 
    receipt
  GROUP BY 
    sales_ymd
  HAVING 
    SUM(amount) IS NOT NULL
)

  1. sales_ymd (売上日) 別の集計

    • SUM(amount) により、売上金額 (amount) を合計します。
    • GROUP BY との組み合わせにより、各売上日 (sales_ymd) ごとに集計します。

  2. LAG(sales_ymd, 3, -1) OVER (ORDER BY sales_ymd)

    • このウィンドウ関数により、3 回前の sales_ymd を lag_ymd 列として格納します。
    • 最初の 3 回分は -1 を格納します。
    • OVER (ORDER BY sales_ymd) により、sales_ymd の昇順で順序を付けてウィンドウ関数を適用しています。

  3. GROUP BY sales_ymd
    sales_ymd 別にデータをグループ化し、各グループごとに SUM(amount) を計算します。

  4. HAVING SUM(amount) IS NOT NULL
    集計結果として、売上金額の合計が NULL でないデータのみを対象とします。

以上により、sales_data は次のような結果を返します。

sales_ymd amount  lag_ymd
 20170101  33723       -1
 20170102  24165       -1
 20170103  27503       -1
 20170104  36165 20170101
 20170105  37830 20170102
 20170106  32387 20170103
 20170107  23415 20170104
 20170108  24737 20170105
 20170109  26718 20170106
 ...

メインクエリ

SQL
SELECT 
  L.sales_ymd,
  L.amount,
  R.sales_ymd AS lag_sales_ymd,
  R.amount AS lag_amount
FROM 
  sales_data L
INNER JOIN 
  sales_data R
ON (
  L.lag_ymd <= R.sales_ymd 
  AND L.sales_ymd > R.sales_ymd
)
ORDER BY 
  L.sales_ymd, R.sales_ymd

  1. INNER JOIN による自己結合
    sales_data CTE (共通テーブル式) を L と R の 2 つのエイリアスを用いて自己結合します。
    L は左側のテーブル、R は右側のテーブルを指します。

  2. ON による結合条件の定義
    L に対して、R の sales_ymd が lag_ymd 以上 sales_ymd 未満の範囲にあるレコードを結合の対象にします。

  3. select(...)
    必要なカラムを抽出・リネームします。

    • L.sales_ymd: 現在の売上日
    • L.amount: 現在の売上金額
    • R.sales_ymd AS lag_sales_ymd: 3 回前までの売上日
    • R.amount AS lag_amount: 3 回前までの売上金額

  4. ORDER BY L.sales_ymd, R.sales_ymd
    売上日 L.sales_ymd と R.sales_ymd の昇順に並べ替えます。

実行結果の確認

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

R
query = sql("
WITH sales_data AS (
  SELECT 
    sales_ymd, 
    SUM(amount) AS amount,
    LAG(sales_ymd, 3, -1) OVER (ORDER BY sales_ymd) AS lag_ymd
  FROM 
    receipt
  GROUP BY 
    sales_ymd
  HAVING 
    SUM(amount) IS NOT NULL
)
SELECT 
  L.sales_ymd,
  L.amount,
  R.sales_ymd AS lag_sales_ymd,
  R.amount AS lag_amount
FROM 
  sales_data L
INNER JOIN 
  sales_data R
ON (
  L.lag_ymd <= R.sales_ymd 
  AND L.sales_ymd > R.sales_ymd
)
ORDER BY 
  L.sales_ymd, R.sales_ymd
"
)

query %>% db_get_query(con) %>% head(10)
# A tibble: 10 × 4
   sales_ymd amount lag_sales_ymd lag_amount
       <int>  <dbl>         <int>      <dbl>
 1  20170102  24165      20170101      33723
 2  20170103  27503      20170101      33723
 3  20170103  27503      20170102      24165
 4  20170104  36165      20170101      33723
 5  20170104  36165      20170102      24165
 6  20170104  36165      20170103      27503
 7  20170105  37830      20170102      24165
 8  20170105  37830      20170103      27503
 9  20170105  37830      20170104      36165
10  20170106  32387      20170103      27503