第7章結合

library(conflicted)
library(arrow)
library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.1     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2

hotel       <- read_parquet("https://github.com/ghmagazine/awesomebook_v2/raw/main/data/hotel.parquet")
customer    <- read_parquet("https://github.com/ghmagazine/awesomebook_v2/raw/main/data/customer.parquet")
reservation <- read_parquet("https://github.com/ghmagazine/awesomebook_v2/raw/main/data/reservation.parquet")
campaign    <- read_parquet("https://github.com/ghmagazine/awesomebook_v2/raw/main/data/campaign.parquet")

7-1 1対1または多対1の関係のテーブルの結合

Q: ビジネスホテルかつ宿泊人数が1名の予約履歴の抽出

Not Awesome

pd_reservationとpd_hotelをhotel_id列をキーとして内部結合
結合したデータからビジネスホテルかつ宿泊1名の行を抽出

reservation |>
  inner_join(hotel, by = "hotel_id") |>
  dplyr::filter(hotel_type == "ビジネスホテル" & people_num == 1)

## # A tibble: 101,028 × 49
##    reservation_id hotel_id customer_id reserved_at         checkin_date       
##             <int>    <int>       <int> <dttm>              <dttm>             
##  1             72     2706      100573 2014-01-08 14:32:58 2015-01-01 00:00:00
##  2            176     1870       40419 2014-01-14 02:33:55 2015-01-02 00:00:00
##  3            190     1801        4819 2014-01-14 13:47:22 2015-01-01 00:00:00
##  4            191     3422       53534 2014-01-14 14:22:49 2015-01-04 00:00:00
##  5            194      521      138201 2014-01-14 15:39:34 2015-01-07 00:00:00
##  6            240      986       17817 2014-01-16 20:41:57 2014-12-31 00:00:00
##  7            289     3062       25934 2014-01-18 08:57:06 2015-01-06 00:00:00
##  8            290     1032      306397 2014-01-18 12:59:36 2015-01-10 00:00:00
##  9            302     3610      167932 2014-01-19 04:11:01 2015-01-07 00:00:00
## 10            320     3141      486795 2014-01-19 16:37:50 2015-01-19 00:00:00
## # ℹ 101,018 more rows
## # ℹ 44 more variables: checkout_date <dttm>, length_of_stay <int>,
## #   people_num <int>, total_price <int>, status <chr>, canceled_at <dttm>,
## #   hotel_name <chr>, hotel_type <chr>, address_prefecture <chr>,
## #   address_city <chr>, address_town <chr>, address_zipcode <chr>,
## #   unit_price <int>, user_rating <dbl>, tag_001 <int>, tag_002 <int>,
## #   tag_003 <int>, tag_004 <int>, tag_005 <int>, tag_006 <int>, …

Awesome

people_numが1人のデータのみ抽出
pd_hotelからビジネスホテルの行のみ抽出
ビジネスホテルのみのマスタを内部結合

reservation |>
  dplyr::filter(people_num == 1) |>
  inner_join(
    hotel |>
      dplyr::filter(hotel_type == "ビジネスホテル") |>
      select(hotel_id),
    by = "hotel_id"
    )

## # A tibble: 101,028 × 11
##    reservation_id hotel_id customer_id reserved_at         checkin_date       
##             <int>    <int>       <int> <dttm>              <dttm>             
##  1             72     2706      100573 2014-01-08 14:32:58 2015-01-01 00:00:00
##  2            176     1870       40419 2014-01-14 02:33:55 2015-01-02 00:00:00
##  3            190     1801        4819 2014-01-14 13:47:22 2015-01-01 00:00:00
##  4            191     3422       53534 2014-01-14 14:22:49 2015-01-04 00:00:00
##  5            194      521      138201 2014-01-14 15:39:34 2015-01-07 00:00:00
##  6            240      986       17817 2014-01-16 20:41:57 2014-12-31 00:00:00
##  7            289     3062       25934 2014-01-18 08:57:06 2015-01-06 00:00:00
##  8            290     1032      306397 2014-01-18 12:59:36 2015-01-10 00:00:00
##  9            302     3610      167932 2014-01-19 04:11:01 2015-01-07 00:00:00
## 10            320     3141      486795 2014-01-19 16:37:50 2015-01-19 00:00:00
## # ℹ 101,018 more rows
## # ℹ 6 more variables: checkout_date <dttm>, length_of_stay <int>,
## #   people_num <int>, total_price <int>, status <chr>, canceled_at <dttm>

7-2 1対多の関係のテーブルの結合

Q: ホテルマスタにホテルの売上と予約数を付与

Not Awesome

hotel_id列を結合キーとしてhotelにreservationを左外部結合
未キャンセルかつcheckout_dateの年が2019の行のみ抽出
集計結果以外に結果に残す列を全て結合キーに指定してgroup by

hotel |>
  left_join(reservation, by = "hotel_id") |>
  dplyr::filter(status != "canceled", year(checkout_date) == 2019) |>
  summarise(
    total_price_sum = sum(total_price),
    total_count = n(),
    .by = c(
      hotel_id, hotel_name, hotel_type, address_prefecture, address_city,
      address_town, address_zipcode, unit_price, user_rating
      )
    )

## # A tibble: 4,527 × 11
##    hotel_id hotel_name   hotel_type address_prefecture address_city address_town
##       <int> <chr>        <chr>      <chr>              <chr>        <chr>       
##  1        1 北飯岡ホテル ビジネス…  岩手県             盛岡市       北飯岡      
##  2        3 小屋敷ペン…  民宿       青森県             黒石市       小屋敷      
##  3        4 中後町民宿   民宿       愛知県             碧南市       中後町      
##  4        5 鵜沼台ホテル ビジネス…  岐阜県             各務原市     鵜沼台      
##  5        6 新町ホテル   ビジネス…  熊本県             宇土市       新町        
##  6        8 大山町ペン…  民宿       神奈川県           相模原市緑区 大山町      
##  7        9 新道ホテル   ビジネス…  北海道             上磯郡木古…  新道        
##  8       10 下奈良上ノ…  旅館       京都府             八幡市       下奈良上ノ段
##  9       11 前波町ペン…  民宿       福井県             福井市       前波町      
## 10       12 岩出山重蔵…  旅館       宮城県             大崎市       岩出山重蔵  
## # ℹ 4,517 more rows
## # ℹ 5 more variables: address_zipcode <chr>, unit_price <int>,
## #   user_rating <dbl>, total_price_sum <int>, total_count <int>

Awesome

reservationから未キャンセルかつcheckout_dateの年が2019の行のみ抽出し、hotel_idを集約キーとしてgroup by集計
hotel_id列を結合キーとして 1. の集計結果を左外部結合
結合されなかった行の数値を0埋め

hotel |>
  left_join(
    reservation |>
      dplyr::filter(status != "canceled", year(checkout_date) == 2019) |>
      summarise(
        sum = sum(total_price), 
        size = n(),
        .by = hotel_id
        ),
    by = "hotel_id"
    ) |>
  replace_na(list(sum = 0, size = 0))

## # A tibble: 5,000 × 41
##    hotel_id hotel_name   hotel_type address_prefecture address_city address_town
##       <int> <chr>        <chr>      <chr>              <chr>        <chr>       
##  1        1 北飯岡ホテル ビジネス…  岩手県             盛岡市       北飯岡      
##  2        2 西二条南温…  リゾート…  北海道             中川郡美深町 西二条南    
##  3        3 小屋敷ペン…  民宿       青森県             黒石市       小屋敷      
##  4        4 中後町民宿   民宿       愛知県             碧南市       中後町      
##  5        5 鵜沼台ホテル ビジネス…  岐阜県             各務原市     鵜沼台      
##  6        6 新町ホテル   ビジネス…  熊本県             宇土市       新町        
##  7        7 七ツ山リゾ…  リゾート…  宮崎県             東臼杵郡諸…  七ツ山      
##  8        8 大山町ペン…  民宿       神奈川県           相模原市緑区 大山町      
##  9        9 新道ホテル   ビジネス…  北海道             上磯郡木古…  新道        
## 10       10 下奈良上ノ…  旅館       京都府             八幡市       下奈良上ノ段
## # ℹ 4,990 more rows
## # ℹ 35 more variables: address_zipcode <chr>, unit_price <int>,
## #   user_rating <dbl>, tag_001 <int>, tag_002 <int>, tag_003 <int>,
## #   tag_004 <int>, tag_005 <int>, tag_006 <int>, tag_007 <int>, tag_008 <int>,
## #   tag_009 <int>, tag_010 <int>, tag_011 <int>, tag_012 <int>, tag_013 <int>,
## #   tag_014 <int>, tag_015 <int>, tag_016 <int>, tag_017 <int>, tag_018 <int>,
## #   tag_019 <int>, tag_020 <int>, tag_021 <int>, tag_022 <int>, …

また、right_joinを使うと思考の流れの通りに書ける。

reservation |>
  dplyr::filter(status != "canceled", year(checkout_date) == 2019) |>
  summarise(
    sum = sum(total_price),
    size = n(),
    .by = hotel_id
    ) |>
  right_join(hotel, by = "hotel_id") |>
  replace_na(list(sum = 0, size = 0))

## # A tibble: 5,000 × 41
##    hotel_id     sum  size hotel_name  hotel_type address_prefecture address_city
##       <int>   <int> <int> <chr>       <chr>      <chr>              <chr>       
##  1     2776 5785000   104 北郷ペンシ… 民宿       長野県             長野市      
##  2     1908 3470100    74 仲手原ビジ… ビジネス…  神奈川県           横浜市港北区
##  3     2093 1822800    55 浦島丘ビジ… ビジネス…  神奈川県           横浜市神奈… 
##  4     2446 8297900   124 前ケ須町民… 民宿       愛知県             弥富市      
##  5     4780 7170600   151 大手町民宿  民宿       東京都             千代田区    
##  6     2419 5762500   124 本江東温泉… 旅館       富山県             射水市      
##  7     4781 2724200   129 堀津町ペン… 民宿       岐阜県             羽島市      
##  8     2127 3641400    87 下小田民宿  民宿       静岡県             焼津市      
##  9     4902 2457000    73 犀川生立民… 民宿       福岡県             京都郡みや… 
## 10     4335 2336000    93 斗賀ペンシ… 民宿       青森県             三戸郡南部町
## # ℹ 4,990 more rows
## # ℹ 34 more variables: address_town <chr>, address_zipcode <chr>,
## #   unit_price <int>, user_rating <dbl>, tag_001 <int>, tag_002 <int>,
## #   tag_003 <int>, tag_004 <int>, tag_005 <int>, tag_006 <int>, tag_007 <int>,
## #   tag_008 <int>, tag_009 <int>, tag_010 <int>, tag_011 <int>, tag_012 <int>,
## #   tag_013 <int>, tag_014 <int>, tag_015 <int>, tag_016 <int>, tag_017 <int>,
## #   tag_018 <int>, tag_019 <int>, tag_020 <int>, tag_021 <int>, …

7-3 多対多の関係のテーブルの結合

Q: 顧客マスタに対して、顧客のホテル種別ごとの予約数を付与

Awesome

reservationにhotelを左外部結合
ホテル種別ごとのデータ数カウント用の列を作成
customer_idごとにホテル種別ごとのデータ数をカウント
customerに 1. 〜 3. の集計結果を左外部結合
結合されなかった行の数値を0埋め

customer |>
  left_join(
    reservation |>
      left_join(
        hotel |>
          select(hotel_id, hotel_type),
        by = "hotel_id"
        ) |>
      mutate(
        ryokan_cnt = hotel_type == "旅館",
        resort_hotel_cnt = hotel_type == "リゾートホテル",
        business_hotel_cnt = hotel_type == "ビジネスホテル",
        minsyuku_cnt = hotel_type == "民宿"
        ) |>
      summarise(
        across(
          c(ryokan_cnt, resort_hotel_cnt, business_hotel_cnt, minsyuku_cnt),
          sum
          ),
        .by = customer_id
        ),
    by = "customer_id"
    ) |>
  replace_na(
    list(
      ryokan_cnt = 0,
      resort_hotel_cnt = 0,
      business_hotel_cnt = 0,
      minsyuku_cnt = 0
      )
    )

## # A tibble: 500,000 × 12
##    customer_id name       age sex   address_prefecture address_city address_town
##          <int> <chr>    <int> <chr> <chr>              <chr>        <chr>       
##  1           1 山田 裕…    75 <NA>  岐阜県             岐阜市       鷺山清洲町  
##  2           2 藤井 稔     83 M     大阪府             豊能郡能勢町 地黄        
##  3           3 青木 太…    62 M     佐賀県             佐賀市       本庄町袋    
##  4           4 渡辺 裕…    28 M     福島県             喜多方市     豊川町高堂太
##  5           5 渡辺 明…    62 F     兵庫県             西宮市       津門西口町  
##  6           6 西村 知…    66 F     秋田県             仙北郡美郷町 佐野        
##  7           7 斉藤 七…    34 F     高知県             長岡郡大豊町 日浦        
##  8           8 村上 明…    81 F     北海道             夕張市       南部青葉町  
##  9           9 鈴木 直…    57 <NA>  京都府             京都市左京区 上高野石田町
## 10          10 佐藤 舞     59 F     神奈川県           伊勢原市     下糟屋      
## # ℹ 499,990 more rows
## # ℹ 5 more variables: address_zipcode <chr>, ryokan_cnt <int>,
## #   resort_hotel_cnt <int>, business_hotel_cnt <int>, minsyuku_cnt <int>

7-4 すべての結合の組み合わせの生成

Q: 顧客ごとの月別の売上を計算（売上のない月も出力）

Awesome

1ヶ月間隔の時系列を生成
customerに対して月の時系列をクロス結合し、customer_idごとに全ての月の行を生成
未キャンセルデータを抽出
checkout_dateを月に変換
customer_idとmonthごとにtotal_priceの総和を計算
1. のデータに対して 3. ～ 5. の集計結果を左外部結合
結合されなかった行の数値を0埋め

tibble(
  month = seq(ymd("2019-01-01"), ymd("2019-12-01"), by = "1 month") |>
    floor_date("month")
  ) |>
  cross_join(customer) |>
  left_join(
    reservation |>
      dplyr::filter(status != "canceled") |>
      mutate(month = floor_date(checkout_date, "month")) |>
      summarise(
        total_price = sum(total_price),
        .by = c(customer_id, month)
        ),
    by = c("customer_id", "month")
    ) |>
  mutate(total_price = replace_na(total_price, 0))

## # A tibble: 6,000,000 × 10
##    month               customer_id name        age sex   address_prefecture
##    <dttm>                    <int> <chr>     <int> <chr> <chr>             
##  1 2019-01-01 00:00:00           1 山田 裕太    75 <NA>  岐阜県            
##  2 2019-01-01 00:00:00           2 藤井 稔      83 M     大阪府            
##  3 2019-01-01 00:00:00           3 青木 太一    62 M     佐賀県            
##  4 2019-01-01 00:00:00           4 渡辺 裕太    28 M     福島県            
##  5 2019-01-01 00:00:00           5 渡辺 明美    62 F     兵庫県            
##  6 2019-01-01 00:00:00           6 西村 知実    66 F     秋田県            
##  7 2019-01-01 00:00:00           7 斉藤 七夏    34 F     高知県            
##  8 2019-01-01 00:00:00           8 村上 明美    81 F     北海道            
##  9 2019-01-01 00:00:00           9 鈴木 直子    57 <NA>  京都府            
## 10 2019-01-01 00:00:00          10 佐藤 舞      59 F     神奈川県          
## # ℹ 5,999,990 more rows
## # ℹ 4 more variables: address_city <chr>, address_town <chr>,
## #   address_zipcode <chr>, total_price <int>

7-5 不等式条件での結合

Q: 予約履歴データにキャンペーン情報を付与

Not Awesome

reservationにcampaignをクロス結合
reservationのreserved_atがcampaignのstarts_atとends_atの間にある行のみ抽出
不要な列を削除

reservation |>
  cross_join(campaign) |>
  dplyr::filter(reserved_at >= starts_at & reserved_at <= ends_at) |>
  select(!c(starts_at, ends_at))

## # A tibble: 1,108,427 × 12
##    reservation_id hotel_id customer_id reserved_at         checkin_date       
##             <int>    <int>       <int> <dttm>              <dttm>             
##  1         195407     1314      302805 2015-01-01 00:01:39 2015-12-22 00:00:00
##  2         195408     2432      200606 2015-01-01 00:07:04 2015-07-05 00:00:00
##  3         195409     4988      366673 2015-01-01 00:07:20 2015-08-14 00:00:00
##  4         195410     2805      384310 2015-01-01 00:09:55 2015-08-12 00:00:00
##  5         195411     1522      373894 2015-01-01 00:10:07 2015-03-13 00:00:00
##  6         195412     4659      215655 2015-01-01 00:10:49 2015-03-07 00:00:00
##  7         195413     1899      448346 2015-01-01 00:11:20 2015-05-26 00:00:00
##  8         195414     3294      461204 2015-01-01 00:12:33 2015-04-08 00:00:00
##  9         195415      814       72390 2015-01-01 00:13:07 2015-10-26 00:00:00
## 10         195416     2499      263925 2015-01-01 00:13:52 2015-03-18 00:00:00
## # ℹ 1,108,417 more rows
## # ℹ 7 more variables: checkout_date <dttm>, length_of_stay <int>,
## #   people_num <int>, total_price <int>, status <chr>, canceled_at <dttm>,
## #   campaign_name <chr>

Awesome

campaignのstarts_atとends_atから、その間の日付の時系列を作成し、reserve_date列に割り当てます。
reserve_dateを結合キーとしてキャンペーンマスタと結合
不要な列を削除

campaign_expanded <- campaign |>
  rowwise() |>
  mutate(
    reserve_date = list(seq(date(starts_at), date(ends_at), by="day"))
    ) |>
  unnest(reserve_date)

reservation |>
  mutate(reserve_date = date(reserved_at)) |>
  left_join(
    campaign_expanded,
    by = "reserve_date",
    relationship = "many-to-many"
    ) |>
  select(!reserve_date)

## # A tibble: 2,000,000 × 14
##    reservation_id hotel_id customer_id reserved_at         checkin_date       
##             <int>    <int>       <int> <dttm>              <dttm>             
##  1              1     2460       53431 2013-12-31 07:00:14 2014-12-31 00:00:00
##  2              2      962      488390 2013-12-31 08:23:35 2014-12-31 00:00:00
##  3              3      558      341335 2013-12-31 09:02:05 2014-12-31 00:00:00
##  4              4     3666      398981 2013-12-31 23:44:54 2014-12-31 00:00:00
##  5              5     2180      220381 2014-01-01 02:47:50 2014-12-31 00:00:00
##  6              6      974        1494 2014-01-01 07:56:58 2015-01-01 00:00:00
##  7              7     2260       24104 2014-01-01 13:17:06 2014-12-30 00:00:00
##  8              8      314      124883 2014-01-01 14:22:01 2014-12-31 00:00:00
##  9              9     2211       45282 2014-01-01 14:59:04 2014-12-31 00:00:00
## 10             10      333      390595 2014-01-01 20:24:34 2014-12-30 00:00:00
## # ℹ 1,999,990 more rows
## # ℹ 9 more variables: checkout_date <dttm>, length_of_stay <int>,
## #   people_num <int>, total_price <int>, status <chr>, canceled_at <dttm>,
## #   campaign_name <chr>, starts_at <dttm>, ends_at <dttm>

以上です。

第8章分割

第6章集約

Top

第7章結合

Osamu Morimoto

2024-06-02

7-1 1対1または多対1の関係のテーブルの結合

Q: ビジネスホテルかつ宿泊人数が1名の予約履歴の抽出

Not Awesome

Awesome

7-2 1対多の関係のテーブルの結合

Q: ホテルマスタにホテルの売上と予約数を付与

Not Awesome

Awesome

7-3 多対多の関係のテーブルの結合

Q: 顧客マスタに対して、顧客のホテル種別ごとの予約数を付与

Awesome

7-4 すべての結合の組み合わせの生成

Q: 顧客ごとの月別の売上を計算（売上のない月も出力）

Awesome

7-5 不等式条件での結合

Q: 予約履歴データにキャンペーン情報を付与

Not Awesome

Awesome

第7章 結合

Osamu Morimoto

2024-06-02

7-1 1対1または多対1の関係のテーブルの結合

Q: ビジネスホテルかつ宿泊人数が1名の予約履歴の抽出

Not Awesome

Awesome

7-2 1対多の関係のテーブルの結合

Q: ホテルマスタにホテルの売上と予約数を付与

Not Awesome

Awesome

7-3 多対多の関係のテーブルの結合

Q: 顧客マスタに対して、顧客のホテル種別ごとの予約数を付与

Awesome

7-4 すべての結合の組み合わせの生成

Q: 顧客ごとの月別の売上を計算（売上のない月も出力）

Awesome

7-5 不等式条件での結合

Q: 予約履歴データにキャンペーン情報を付与

Not Awesome

Awesome

第7章結合