![Featured image of post [R & SQL] データサイエンス100本ノック＋α - R-045 (解説)](/images/ds-drills/free/cover-drills-free_R-045.png)
参考記事:
[R & SQL] データサイエンス100本ノック＋α - 概要・導入
- 設問文は データサイエンス100本ノック（構造化データ加工編） より引用しています。
- すべてのコードはコピー＆ペーストでそのまま実行できます。
演習問題
出力イメージ:
customer_id birth_day
1 CS021313000114 19810429
2 CS037613000071 19520401
3 CS031415000172 19761004
4 CS028811000001 19330327
5 CS001215000145 19950329
6 CS020401000016 19740915
7 CS015414000103 19770809
8 CS029403000008 19730817
9 CS015804000004 19310502
10 CS033513000180 19620711 実行環境の構築について
R コード実行環境の構築に必要な手順は こちら から確認できます。
セットアップが完了すると、必要なパッケージとデータを使用できるようになります。
解答例を以下に示します。
R (データフレーム操作)
利用するデータ
以下のデータを利用します。
df_customerの顧客ID (customer_id) と⽣年⽉⽇ (birth_day)
主要なカラムを抜き出してデータの一部を確認します。
df_customer %>% select(customer_id, birth_day)# A tibble: 21,971 × 2
customer_id birth_day
<chr> <date>
1 CS021313000114 1981-04-29
2 CS037613000071 1952-04-01
3 CS031415000172 1976-10-04
4 CS028811000001 1933-03-27
5 CS001215000145 1995-03-29
6 CS020401000016 1974-09-15
7 CS015414000103 1977-08-09
...解答例と実行結果
df_customer %>%
mutate(birth_day = strftime(birth_day, "%Y%m%d")) %>%
select(customer_id, birth_day) %>%
head(10)# A tibble: 10 × 2
customer_id birth_day
<chr> <chr>
1 CS021313000114 19810429
2 CS037613000071 19520401
3 CS031415000172 19761004
4 CS028811000001 19330327
5 CS001215000145 19950329
6 CS020401000016 19740915
7 CS015414000103 19770809
8 CS029403000008 19730817
9 CS015804000004 19310502
10 CS033513000180 19620711 解説
strftime() は、日付データを指定したフォーマットの文字列に変換します。
birth_day = strftime(birth_day, "%Y%m%d")birth_day(Date型) を YYYYMMDD の文字列形式に変換します。select(customer_id, birth_day)customer_idと変換後のbirth_dayのみを抽出します。head(10)
先頭の 10 行を取得します。
R (データベース操作)
解答例と実行結果
データベースでの操作も同様の流れです。
データフレーム df_customer をテーブル参照 db_customer に置き換えることで、同様の操作を実行できます。
db_result = db_customer %>%
mutate(birth_day = STRFTIME(birth_day, "%Y%m%d")) %>%
select(customer_id, birth_day) %>%
head(10)
db_result %>% collect()# A tibble: 10 × 2
customer_id birth_day
<chr> <chr>
1 CS021313000114 19810429
2 CS037613000071 19520401
3 CS031415000172 19761004
4 CS028811000001 19330327
5 CS001215000145 19950329
6 CS020401000016 19740915
7 CS015414000103 19770809
8 CS029403000008 19730817
9 CS015804000004 19310502
10 CS033513000180 19620711 解説
strftime()は dbplyr ではサポートされていないため、代わりに SQL のstrftime関数を使用します。strftimeは慣例に従い大文字で記述します。
(詳しくは dbplyr が認識できない式 を参照してください。)db_result %>% collect()
データベース操作の結果を R のデータフレーム (tibble) として取得します。
SQL
利用するデータ
以下のデータを利用します。
customerテーブルの顧客ID (customer_id) と⽣年⽉⽇ (birth_day)
主要なカラムを抜き出してデータの一部を確認します。
"SELECT customer_id, birth_day FROM customer" %>% db_get_query(con)# A tibble: 21,971 × 2
customer_id birth_day
<chr> <date>
1 CS021313000114 1981-04-29
2 CS037613000071 1952-04-01
3 CS031415000172 1976-10-04
4 CS028811000001 1933-03-27
5 CS001215000145 1995-03-29
6 CS020401000016 1974-09-15
7 CS015414000103 1977-08-09
...自動生成された SQL クエリ
データベース操作による結果 (db_result) に基づき、自動生成された SQLクエリを show_query() で確認できます。
db_result %>% show_query()SELECT customer_id, STRFTIME(birth_day, '%Y%m%d') AS birth_day
FROM customer
LIMIT 10解答例
このクエリをフォーマットすると、次のようになります。
SELECT
customer_id,
STRFTIME(birth_day, '%Y%m%d') AS birth_day
FROM
customer
LIMIT 10解説
STRFTIME() は、日付データを指定したフォーマットの文字列に変換します。
STRFTIME(birth_day, '%Y%m%d')birth_day(DATE型) を YYYYMMDD の文字列形式に変換します。SELECT customer_id, birth_daycustomer_idと変換後のbirth_dayのみを取得します。LIMIT 10
結果から 10 件 を取得します。
実行結果の確認
この SQLクエリの実行結果は、次のようにして確認できます。
query = sql("
SELECT
customer_id,
STRFTIME(birth_day, '%Y%m%d') AS birth_day
FROM
customer
LIMIT 10
"
)
query %>% db_get_query(con)# A tibble: 10 × 2
customer_id birth_day
<chr> <chr>
1 CS021313000114 19810429
2 CS037613000071 19520401
3 CS031415000172 19761004
4 CS028811000001 19330327
5 CS001215000145 19950329
6 CS020401000016 19740915
7 CS015414000103 19770809
8 CS029403000008 19730817
9 CS015804000004 19310502
10 CS033513000180 19620711