![Featured image of post [R & SQL] データサイエンス100本ノック＋α - R-047 (解説)](/images/ds-drills/free/cover-drills-free_R-047.png)
参考記事:
[R & SQL] データサイエンス100本ノック＋α - 概要・導入
- 設問文は データサイエンス100本ノック（構造化データ加工編） より引用しています。
- すべてのコードはコピー＆ペーストでそのまま実行できます。
演習問題
出力イメージ:
receipt_no receipt_sub_no sales_ymd
1 112 1 2018-11-03
2 1132 2 2018-11-18
3 1102 1 2017-07-12
4 1132 1 2019-02-05
5 1102 2 2018-08-21
6 1112 1 2019-06-05
7 1102 2 2018-12-05
8 1102 1 2019-09-22
9 1112 2 2017-05-04
10 1102 1 2019-10-10実行環境の構築について
R コード実行環境の構築に必要な手順は こちら から確認できます。
セットアップが完了すると、必要なパッケージとデータを使用できるようになります。
解答例を以下に示します。
利用するデータ
以下のデータを利用します。
df_receiptのレシート番号 (receipt_no)、レシートサブ番号 (receipt_sub_no)、売上日 (sales_ymd)
主要なカラムを抜き出してデータの一部を確認します。
df_receipt %>% select(receipt_no, receipt_sub_no, sales_ymd)# A tibble: 104,681 × 3
receipt_no receipt_sub_no sales_ymd
<int> <int> <int>
1 112 1 20181103
2 1132 2 20181118
3 1102 1 20170712
4 1132 1 20190205
5 1102 2 20180821
6 1112 1 20190605
7 1102 2 20181205
...R (データフレーム操作)
解答例と実行結果
df_receipt %>%
select(receipt_no, receipt_sub_no, sales_ymd) %>%
mutate(
sales_ymd = sales_ymd %>%
as.character() %>%
lubridate::fast_strptime("%Y%m%d") %>%
lubridate::as_date()
) %>%
head(10)# A tibble: 10 × 3
receipt_no receipt_sub_no sales_ymd
<int> <int> <date>
1 112 1 2018-11-03
2 1132 2 2018-11-18
3 1102 1 2017-07-12
4 1132 1 2019-02-05
5 1102 2 2018-08-21
6 1112 1 2019-06-05
7 1102 2 2018-12-05
8 1102 1 2019-09-22
9 1112 2 2017-05-04
10 1102 1 2019-10-10解説
select()
必要な 3 列 (receipt_no,receipt_sub_no,sales_ymd) を抽出します。mutate()sales_ymdを文字列に変換後、fast_strptime("%Y%m%d")でPOSIXlt型に変換し、as_date()でDate型に変換します。head(10)
先頭 10 行を取得します。
R (データベース操作)
解答例と実行結果
fast_strptime() は dbplyr ではサポートされていないため、代わりに SQL の strptime 関数を使用します。
db_result = db_receipt %>%
select(receipt_no, receipt_sub_no, sales_ymd) %>%
mutate(
sales_ymd = sales_ymd %>%
as.character() %>%
STRPTIME("%Y%m%d") %>%
lubridate::as_date()
) %>%
head(10)
db_result %>% collect()# A tibble: 10 × 3
receipt_no receipt_sub_no sales_ymd
<int> <int> <date>
1 112 1 2018-11-03
2 1132 2 2018-11-18
3 1102 1 2017-07-12
4 1132 1 2019-02-05
5 1102 2 2018-08-21
6 1112 1 2019-06-05
7 1102 2 2018-12-05
8 1102 1 2019-09-22
9 1112 2 2017-05-04
10 1102 1 2019-10-10解説
strptimeは慣例に従い大文字で記述します。
(詳しくは dbplyr が認識できない式 を参照してください。)db_result %>% collect()
データベース操作の結果を R のデータフレーム (tibble) として取得します。
SQL
利用するデータ
以下のデータを利用します。
receiptテーブルのレシート番号 (receipt_no)、レシートサブ番号 (receipt_sub_no)、売上日 (sales_ymd)
主要なカラムを抜き出してデータの一部を確認します。
"SELECT receipt_no, receipt_sub_no, sales_ymd FROM receipt" %>% db_get_query(con)# A tibble: 104,681 × 3
receipt_no receipt_sub_no sales_ymd
<int> <int> <int>
1 112 1 20181103
2 1132 2 20181118
3 1102 1 20170712
4 1132 1 20190205
5 1102 2 20180821
6 1112 1 20190605
7 1102 2 20181205
...自動生成された SQL クエリ
データベース操作による結果 (db_result) に基づき、自動生成された SQLクエリを show_query() で確認できます。
db_result %>% show_query()SELECT
receipt_no,
receipt_sub_no,
CAST(STRPTIME(CAST(sales_ymd AS TEXT), '%Y%m%d') AS DATE) AS sales_ymd
FROM receipt
LIMIT 10解答例
このクエリをフォーマットすると、次のようになります。
SELECT
receipt_no,
receipt_sub_no,
CAST(
STRPTIME(
CAST(sales_ymd AS TEXT), '%Y%m%d'
) AS DATE
) AS sales_ymd
FROM
receipt
LIMIT 10解説
sales_ymd を INTEGER から DATE 型に変換します。
CAST(sales_ymd AS TEXT)sales_ymdをTEXT型 (VARCHARのエイリアス) に変換します。- 例:
20250412→'20250412'
STRPTIME()- 指定した書式 (
'%Y%m%d') に基づき文字列を日時型 (TIMESTAMP) に変換します。 - 例:
'20250412'→2025-04-12 00:00:00
- 指定した書式 (
CAST(... AS DATE)TIMESTAMPをDATE型 (YYYY-MM-DD) に変換します。- 例:
2025-04-12 00:00:00→2025-04-12
LIMIT 10
結果から 10 件 を取得します。
実行結果の確認
この SQLクエリの実行結果は、次のようにして確認できます。
query = sql("
SELECT
receipt_no,
receipt_sub_no,
CAST(
STRPTIME(
CAST(sales_ymd AS TEXT), '%Y%m%d'
) AS DATE
) AS sales_ymd
FROM
receipt
LIMIT 10
"
)
query %>% db_get_query(con)# A tibble: 10 × 3
receipt_no receipt_sub_no sales_ymd
<int> <int> <date>
1 112 1 2018-11-03
2 1132 2 2018-11-18
3 1102 1 2017-07-12
4 1132 1 2019-02-05
5 1102 2 2018-08-21
6 1112 1 2019-06-05
7 1102 2 2018-12-05
8 1102 1 2019-09-22
9 1112 2 2017-05-04
10 1102 1 2019-10-10