Aktifkan package yang diperlukan, yaitu tidyverse, RSQLite, DBI, dan dplyr
library(tidyverse)## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.4 v dplyr 1.0.7
## v tidyr 1.1.3 v stringr 1.4.0
## v readr 2.0.1 v forcats 0.5.1## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()library(RSQLite)
library(DBI)
library(dplyr)Misal, menggunakan data Chinook.dbyang telah tersimpan di folder yang sama dengan working directory ini, maka dapat menggunakan syntax di bawah
chinook<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/chinook.db")
class(chinook)## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"selanjutnya, fungsi dbListTables(database) dapat digunakan untuk melihat tabel yang ada di dalam chinook dan northwind
RSQLite::dbListTables(chinook)## [1] "albums" "artists" "customers" "employees"
## [5] "genres" "invoice_items" "invoices" "media_types"
## [9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
## [13] "tracks"atau hanya dengan fungsi dbListTables(database) seperti di bawah saja, karena sebelumnya sudah mengaktifkan package RSQLite
dbListTables(chinook)## [1] "albums" "artists" "customers" "employees"
## [5] "genres" "invoice_items" "invoices" "media_types"
## [9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
## [13] "tracks"untuk mengakses tabel yang yang ada di dalam data base, bisa menggunakan fungsi tbl(database,"table")
albums <- dplyr::tbl(chinook,"albums") #mengakses tabel albums di dalam data chinook
class(albums)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"albums## # Source: table<albums> [?? x 3]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId
## <int> <chr> <int>
## 1 1 For Those About To Rock We Salute You 1
## 2 2 Balls to the Wall 2
## 3 3 Restless and Wild 2
## 4 4 Let There Be Rock 1
## 5 5 Big Ones 3
## 6 6 Jagged Little Pill 4
## 7 7 Facelift 5
## 8 8 Warner 25 Anos 6
## 9 9 Plays Metallica By Four Cellos 7
## 10 10 Audioslave 8
## # ... with more rowsartists <- dplyr::tbl(chinook,"artists") #mengakses tabel artists di dalam data chinook
class(artists)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"artists## # Source: table<artists> [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## ArtistId Name
## <int> <chr>
## 1 1 AC/DC
## 2 2 Accept
## 3 3 Aerosmith
## 4 4 Alanis Morissette
## 5 5 Alice In Chains
## 6 6 Antônio Carlos Jobim
## 7 7 Apocalyptica
## 8 8 Audioslave
## 9 9 BackBeat
## 10 10 Billy Cobham
## # ... with more rowsBerikut ini adalah isi dari ojek albums dan artists
artists## # Source: table<artists> [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## ArtistId Name
## <int> <chr>
## 1 1 AC/DC
## 2 2 Accept
## 3 3 Aerosmith
## 4 4 Alanis Morissette
## 5 5 Alice In Chains
## 6 6 Antônio Carlos Jobim
## 7 7 Apocalyptica
## 8 8 Audioslave
## 9 9 BackBeat
## 10 10 Billy Cobham
## # ... with more rowsalbums## # Source: table<albums> [?? x 3]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId
## <int> <chr> <int>
## 1 1 For Those About To Rock We Salute You 1
## 2 2 Balls to the Wall 2
## 3 3 Restless and Wild 2
## 4 4 Let There Be Rock 1
## 5 5 Big Ones 3
## 6 6 Jagged Little Pill 4
## 7 7 Facelift 5
## 8 8 Warner 25 Anos 6
## 9 9 Plays Metallica By Four Cellos 7
## 10 10 Audioslave 8
## # ... with more rowsUntuk melakukan simulasi ini, akan digunakan tabel albums dan artistsdari data base chinook.db
dbListTables(chinook)## [1] "albums" "artists" "customers" "employees"
## [5] "genres" "invoice_items" "invoices" "media_types"
## [9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
## [13] "tracks"albums dari database chinookalbums <- dplyr::tbl(chinook,"albums") #mengakses tabel albums di dalam data chinook
albums## # Source: table<albums> [?? x 3]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId
## <int> <chr> <int>
## 1 1 For Those About To Rock We Salute You 1
## 2 2 Balls to the Wall 2
## 3 3 Restless and Wild 2
## 4 4 Let There Be Rock 1
## 5 5 Big Ones 3
## 6 6 Jagged Little Pill 4
## 7 7 Facelift 5
## 8 8 Warner 25 Anos 6
## 9 9 Plays Metallica By Four Cellos 7
## 10 10 Audioslave 8
## # ... with more rowsartists dari database chinookartists <- dplyr::tbl(chinook,"artists") #mengakses tabel albums di dalam data chinook
artists## # Source: table<artists> [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## ArtistId Name
## <int> <chr>
## 1 1 AC/DC
## 2 2 Accept
## 3 3 Aerosmith
## 4 4 Alanis Morissette
## 5 5 Alice In Chains
## 6 6 Antônio Carlos Jobim
## 7 7 Apocalyptica
## 8 8 Audioslave
## 9 9 BackBeat
## 10 10 Billy Cobham
## # ... with more rowsFungsi yang akan digunakan dalam penggabungan Tables ini berasal dari package dplyr
inner_join()inner_join() menghasilkan semua baris pada table x yang memiliki kesamaan nilai dengan table y, dan semua kolom dari x dan y
inner_join(albums,artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 1 For Those About To Rock We Salute You 1 AC/DC
## 2 2 Balls to the Wall 2 Accept
## 3 3 Restless and Wild 2 Accept
## 4 4 Let There Be Rock 1 AC/DC
## 5 5 Big Ones 3 Aerosmith
## 6 6 Jagged Little Pill 4 Alanis Morissette
## 7 7 Facelift 5 Alice In Chains
## 8 8 Warner 25 Anos 6 Antônio Carlos Jobim
## 9 9 Plays Metallica By Four Cellos 7 Apocalyptica
## 10 10 Audioslave 8 Audioslave
## # ... with more rowsatau dengan syntax di bawah ini
albums %>% inner_join(artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 1 For Those About To Rock We Salute You 1 AC/DC
## 2 2 Balls to the Wall 2 Accept
## 3 3 Restless and Wild 2 Accept
## 4 4 Let There Be Rock 1 AC/DC
## 5 5 Big Ones 3 Aerosmith
## 6 6 Jagged Little Pill 4 Alanis Morissette
## 7 7 Facelift 5 Alice In Chains
## 8 8 Warner 25 Anos 6 Antônio Carlos Jobim
## 9 9 Plays Metallica By Four Cellos 7 Apocalyptica
## 10 10 Audioslave 8 Audioslave
## # ... with more rowsleft_Join()left_join() menghasilkan semua baris pada table x, dan semua kolom pada x dan y, dimana untuk baris pada x yang tidak memiliki kesamaan dengan y diisi dengan nilai NApada kolom yang baru.
left_join(albums,artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 1 For Those About To Rock We Salute You 1 AC/DC
## 2 2 Balls to the Wall 2 Accept
## 3 3 Restless and Wild 2 Accept
## 4 4 Let There Be Rock 1 AC/DC
## 5 5 Big Ones 3 Aerosmith
## 6 6 Jagged Little Pill 4 Alanis Morissette
## 7 7 Facelift 5 Alice In Chains
## 8 8 Warner 25 Anos 6 Antônio Carlos Jobim
## 9 9 Plays Metallica By Four Cellos 7 Apocalyptica
## 10 10 Audioslave 8 Audioslave
## # ... with more rowsright_join()right_join() menghasilkan semua baris pada table y, dan semua kolom pada x dan y, dimana untuk baris pada y yang tidak memiliki kesamaan dengan x akan diisi dengan nilai NA pada kolom yang baru
right_join(albums,artists) #x:albums, dan y:artists## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 1 For Those About To Rock We Salute You 1 AC/DC
## 2 4 Let There Be Rock 1 AC/DC
## 3 2 Balls to the Wall 2 Accept
## 4 3 Restless and Wild 2 Accept
## 5 5 Big Ones 3 Aerosmith
## 6 6 Jagged Little Pill 4 Alanis Morissette
## 7 7 Facelift 5 Alice In Chains
## 8 8 Warner 25 Anos 6 Antônio Carlos Jobim
## 9 34 Chill: Brazil (Disc 2) 6 Antônio Carlos Jobim
## 10 9 Plays Metallica By Four Cellos 7 Apocalyptica
## # ... with more rowsfull_join()full_join() menghasilkan semua baris dan kolom dari x dan y. Jika ada nilai yang tidak sama (match) antara x dan y, maka akan bernilai NA
full_join(albums,artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 NA <NA> 25 Milton Nascimento & Bebeto
## 2 NA <NA> 26 Azymuth
## 3 NA <NA> 28 João Gilberto
## 4 NA <NA> 29 Bebel Gilberto
## 5 NA <NA> 30 Jorge Vercilo
## 6 NA <NA> 31 Baby Consuelo
## 7 NA <NA> 32 Ney Matogrosso
## 8 NA <NA> 33 Luiz Melodia
## 9 NA <NA> 34 Nando Reis
## 10 NA <NA> 35 Pedro Luís & A Parede
## # ... with more rowssemi_join()semi_join() menghasilkan semua baris pada table x yang memiliki kesamaan nilai dengan table y, dan semua kolom dari x. Ini mirip dengan inner_join(), bedanya inner_join() mengembalikan semua kolom dari x dan y.
semi_join(albums,artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 3]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## AlbumId Title ArtistId
## <int> <chr> <int>
## 1 1 For Those About To Rock We Salute You 1
## 2 2 Balls to the Wall 2
## 3 3 Restless and Wild 2
## 4 4 Let There Be Rock 1
## 5 5 Big Ones 3
## 6 6 Jagged Little Pill 4
## 7 7 Facelift 5
## 8 8 Warner 25 Anos 6
## 9 9 Plays Metallica By Four Cellos 7
## 10 10 Audioslave 8
## # ... with more rowsanti_join()anti_join() menghasilkan semua baris dari x yang TIDAK memiliki kesamaan dengan y, dan semua kolom yang berasal dari x.
anti_join(albums,artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 3]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook.db]
## # ... with 3 variables: AlbumId <int>, Title <chr>, ArtistId <int>