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

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

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

演習問題

難易度

レシート明細データ（receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、前回売上があった日からの売上金額増減を計算せよ。そして結果を10件表示せよ。

出力イメージ:

   sales_ymd amount pre_sales_ymd pre_amount diff_amount
20170101  33723            NA         NA          NA
20170102  24165      20170101      33723       -9558
20170103  27503      20170102      24165        3338
20170104  36165      20170103      27503        8662
20170105  37830      20170104      36165        1665
20170106  32387      20170105      37830       -5443
20170107  23415      20170106      32387       -8972
20170108  24737      20170107      23415        1322
20170109  26718      20170108      24737        1981
20170110  20143      20170109      26718       -6575

実行環境の構築について

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

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

amount が nullable (NULL を許容する) の場合の解答例を以下に示します。
各コードの出力結果を統一させるため、sales_ymd の昇順でソートしています。

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

利用するデータ

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

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

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

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

解答例と実行結果

df_receipt データフレームから、売上金額の合計を日別に集計し、前回との売上金額の差分を求めます。

df_receipt %>% 
  summarise(
    amount = sum(amount, na.rm = TRUE), .by = sales_ymd
  ) %>% 
  filter(amount > 0.0) %>% 
  arrange(sales_ymd) %>% 
  mutate(
    pre_sales_ymd = lag(sales_ymd), 
    pre_amount = lag(amount), 
    diff_amount = amount - pre_amount
  ) %>% 
  head(10)

# A tibble: 10 × 5
   sales_ymd amount pre_sales_ymd pre_amount diff_amount
       <int>  <dbl>         <int>      <dbl>       <dbl>
20170101  33723            NA         NA          NA
20170102  24165      20170101      33723       -9558
20170103  27503      20170102      24165        3338
20170104  36165      20170103      27503        8662
20170105  37830      20170104      36165        1665
20170106  32387      20170105      37830       -5443
20170107  23415      20170106      32387       -8972
20170108  24737      20170107      23415        1322
20170109  26718      20170108      24737        1981
20170110  20143      20170109      26718       -6575

解説

summarise() で売上合計を計算
- sales_ymd ごとに amount の合計を算出します。
- na.rm = TRUE により、NA 値を除外します。
filter(amount > 0.0) で不要なデータを除外
amount が 0.0 の行を除外します。
arrange(sales_ymd) で日付順に並び替え
- sales_ymd の昇順に並べ替えます。
mutate() で前回の売上金額の差分を計算
- lag(sales_ymd) により、前回の売上日 pre_sales_ymd を取得します。
- lag(amount) により、前回の売上 pre_amount を取得します。
- amount - pre_amount により、前回との差分 diff_amount を計算します。
head(10) で先頭10件を取得
- 計算結果の最初の10件を取得します。

R (データベース操作)

利用するデータ

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

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

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

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

解答例と実行結果

db_receipt テーブル参照から、売上金額の合計を日別に集計し、前回との売上金額の差分を求めます。

db_result = db_receipt %>% 
  summarise(
    amount = sum(amount, na.rm = TRUE), .by = sales_ymd
  ) %>% 
  filter(!is.na(amount)) %>% 
  window_order(sales_ymd) %>% 
  mutate(
    pre_sales_ymd = lag(sales_ymd), 
    pre_amount = lag(amount), 
    diff_amount = amount - pre_amount
  ) %>% 
  arrange(sales_ymd) %>% 
  head(10)

db_result %>% collect()

# A tibble: 10 × 5
   sales_ymd amount pre_sales_ymd pre_amount diff_amount
       <int>  <dbl>         <int>      <dbl>       <dbl>
20170101  33723            NA         NA          NA
20170102  24165      20170101      33723       -9558
20170103  27503      20170102      24165        3338
20170104  36165      20170103      27503        8662
20170105  37830      20170104      36165        1665
20170106  32387      20170105      37830       -5443
20170107  23415      20170106      32387       -8972
20170108  24737      20170107      23415        1322
20170109  26718      20170108      24737        1981
20170110  20143      20170109      26718       -6575

解説

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

filter(!is.na(amount)) で不要なデータを除外
amount が NA となるレコードを除外します。
window_order(sales_ymd) でウィンドウ関数の順序を指定
lag() で前回のデータを取得する際に、sales_ymd の昇順で処理を行うように指定します。
db_result %>% collect()
データベース操作の結果を R のデータフレーム (tibble) として取得します。

SQL

利用するデータ

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

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

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

"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() で確認できます。

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, 1, NULL) OVER (ORDER BY sales_ymd) AS pre_sales_ymd,
    LAG(amount, 1, NULL) OVER (ORDER BY sales_ymd) AS pre_amount
  FROM q01
)
SELECT q01.*, amount - pre_amount AS diff_amount
FROM q02 q01
ORDER BY sales_ymd
LIMIT 10

解答例

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

SQL

WITH sales_by_date AS (
  SELECT 
    sales_ymd, 
    SUM(amount) AS amount
  FROM 
    receipt
  GROUP BY 
    sales_ymd
  HAVING 
    SUM(amount) IS NOT NULL
),
sales_by_date_with_lag AS (
  SELECT 
    sales_ymd, 
    amount, 
    LAG(sales_ymd) OVER win AS pre_sales_ymd,
    LAG(amount) OVER win AS pre_amount
  FROM 
    sales_by_date
  WINDOW win AS (ORDER BY sales_ymd)
)
SELECT 
  sales_ymd, 
  amount, 
  pre_sales_ymd, 
  pre_amount, 
  amount - pre_amount AS diff_amount
FROM 
  sales_by_date_with_lag
ORDER BY 
  sales_ymd
LIMIT 10

解説

receipt テーブルから、売上金額の合計を日別に集計し、前回との売上金額の差分を求めます。

`sales_by_date` の作成

SQL

WITH sales_by_date AS (
  SELECT 
    sales_ymd, 
    SUM(amount) AS amount
  FROM 
    receipt
  GROUP BY 
    sales_ymd
  HAVING 
    SUM(amount) IS NOT NULL
)

SUM(amount) と GROUP BY により sales_ymd ごとに amount の合計を算出します。
HAVING SUM(amount) IS NOT NULL により、売上データが存在しない日を除外します。

`sales_by_date_with_lag` の作成

SQL

sales_by_date_with_lag AS (
  SELECT 
    sales_ymd, 
    amount, 
    LAG(sales_ymd) OVER win AS pre_sales_ymd,
    LAG(amount) OVER win AS pre_amount
  FROM 
    sales_by_date
  WINDOW win AS (ORDER BY sales_ymd)
)

LAG(sales_ymd) OVER win ウィンドウ関数により、前回の売上日 pre_sales_ymd を取得します。
LAG(amount) OVER win ウィンドウ関数により、前回の売上 pre_amount を取得します。
WINDOW win AS (ORDER BY sales_ymd)
- win というウィンドウフレームを定義し、sales_ymd の昇順で処理を行うよう指定します。
- これにより、LAG() 関数が sales_ymd の昇順に基づいて前回のデータを取得できるようになります。

メインクエリ

SQL

SELECT 
  sales_ymd, 
  amount, 
  pre_sales_ymd, 
  pre_amount, 
  amount - pre_amount AS diff_amount
FROM 
  sales_by_date_with_lag
ORDER BY 
  sales_ymd
LIMIT 10

amount - pre_amount AS diff_amount により、前回との差分 diff_amount を計算します。
ORDER BY sales_ymd により、売上日の昇順に並べ替えます。
LIMIT 10 により、最初の10件を取得します。

実行結果の確認

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

query = sql("
WITH sales_by_date AS (
  SELECT 
    sales_ymd, 
    SUM(amount) AS amount
  FROM 
    receipt
  GROUP BY 
    sales_ymd
  HAVING 
    SUM(amount) IS NOT NULL
),
sales_by_date_with_lag AS (
  SELECT 
    sales_ymd, 
    amount, 
    LAG(sales_ymd) OVER win AS pre_sales_ymd,
    LAG(amount) OVER win AS pre_amount
  FROM 
    sales_by_date
  WINDOW win AS (ORDER BY sales_ymd)
)
SELECT 
  sales_ymd, 
  amount, 
  pre_sales_ymd, 
  pre_amount, 
  amount - pre_amount AS diff_amount
FROM 
  sales_by_date_with_lag
ORDER BY 
  sales_ymd
LIMIT 10
"
)

query %>% db_get_query(con)

# A tibble: 10 × 5
   sales_ymd amount pre_sales_ymd pre_amount diff_amount
       <int>  <dbl>         <int>      <dbl>       <dbl>
20170101  33723            NA         NA          NA
20170102  24165      20170101      33723       -9558
20170103  27503      20170102      24165        3338
20170104  36165      20170103      27503        8662
20170105  37830      20170104      36165        1665
20170106  32387      20170105      37830       -5443
20170107  23415      20170106      32387       -8972
20170108  24737      20170107      23415        1322
20170109  26718      20170108      24737        1981
20170110  20143      20170109      26718       -6575

リンク