Library
## Warning: package 'odbc' was built under R version 4.0.5
## Warning: package 'DBI' was built under R version 4.0.5
library(RSQLite)
library(kableExtra)
library(dplyr)
##
## Attaching package: 'dplyr'
## The following object is masked from 'package:kableExtra':
##
## group_rows
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
library(ggplot2)
chinook<-DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
Retrieving Tables
Tabel-tabel yang akan digunakan pada percobaan mergin tabel adalah tabel albums,artists,playlists,playlist_track,tracks,customers,invoices.
albums = dbGetQuery(chinook, "SELECT * FROM albums")
artist = dbGetQuery(chinook, "SELECT * FROM artists")
playlist = dbGetQuery(chinook, "SELECT * FROM playlists")
playlist_track = dbGetQuery(chinook, "SELECT * FROM playlist_track")
tracks = dbGetQuery(chinook, "SELECT * FROM tracks")
customers = dbGetQuery(chinook, "SELECT * FROM customers")
invoices = dbGetQuery(chinook, "SELECT * FROM invoices")
Tabel albums
|
AlbumId
|
Title
|
ArtistId
|
|
1
|
For Those About To Rock We Salute You
|
1
|
|
2
|
Balls to the Wall
|
2
|
|
3
|
Restless and Wild
|
2
|
Tabel artists
|
ArtistId
|
Name
|
|
1
|
AC/DC
|
|
2
|
Accept
|
|
3
|
Aerosmith
|
Tabel playlists
|
PlaylistId
|
Name
|
|
1
|
Music
|
|
2
|
Movies
|
|
3
|
TV Shows
|
Tabel playlist tracks
|
PlaylistId
|
TrackId
|
|
1
|
3402
|
|
1
|
3389
|
|
1
|
3390
|
Tabel tracks
|
TrackId
|
Name
|
AlbumId
|
MediaTypeId
|
GenreId
|
Composer
|
Milliseconds
|
Bytes
|
UnitPrice
|
|
1
|
For Those About To Rock (We Salute You)
|
1
|
1
|
1
|
Angus Young, Malcolm Young, Brian Johnson
|
343719
|
11170334
|
0.99
|
|
2
|
Balls to the Wall
|
2
|
2
|
1
|
NA
|
342562
|
5510424
|
0.99
|
|
3
|
Fast As a Shark
|
3
|
2
|
1
|
F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman
|
230619
|
3990994
|
0.99
|
Tabel customers
|
CustomerId
|
FirstName
|
LastName
|
Company
|
Address
|
City
|
State
|
Country
|
PostalCode
|
Phone
|
Fax
|
Email
|
SupportRepId
|
|
1
|
Luís
|
Gonçalves
|
Embraer - Empresa Brasileira de Aeronáutica S.A.
|
Av. Brigadeiro Faria Lima, 2170
|
São José dos Campos
|
SP
|
Brazil
|
12227-000
|
+55 (12) 3923-5555
|
+55 (12) 3923-5566
|
luisg@embraer.com.br
|
3
|
|
2
|
Leonie
|
Köhler
|
NA
|
Theodor-Heuss-Straße 34
|
Stuttgart
|
NA
|
Germany
|
70174
|
+49 0711 2842222
|
NA
|
leonekohler@surfeu.de
|
5
|
|
3
|
François
|
Tremblay
|
NA
|
1498 rue Bélanger
|
Montréal
|
QC
|
Canada
|
H2G 1A7
|
+1 (514) 721-4711
|
NA
|
ftremblay@gmail.com
|
3
|
Tabel invoices
|
InvoiceId
|
CustomerId
|
InvoiceDate
|
BillingAddress
|
BillingCity
|
BillingState
|
BillingCountry
|
BillingPostalCode
|
Total
|
|
1
|
2
|
2009-01-01 00:00:00
|
Theodor-Heuss-Straße 34
|
Stuttgart
|
NA
|
Germany
|
70174
|
1.98
|
|
2
|
4
|
2009-01-02 00:00:00
|
Ullevålsveien 14
|
Oslo
|
NA
|
Norway
|
0171
|
3.96
|
|
3
|
8
|
2009-01-03 00:00:00
|
Grétrystraat 63
|
Brussels
|
NA
|
Belgium
|
1000
|
5.94
|
Analisis Sederhana
Akan dialkukan analisis sederhana pada tabel invoices
result = invoices %>% group_by(BillingCountry) %>% summarise(Banyaknya_pejualan = n(),Total_pejualan = sum(Total),rata_rata_pejualan = mean(Total),maximum_pejualan = max(Total),minimum_pejualan = min(Total))
result %>% kbl(caption = "Tabel analisis sederhana")%>% kable_styling()
Tabel analisis sederhana
|
BillingCountry
|
Banyaknya_pejualan
|
Total_pejualan
|
rata_rata_pejualan
|
maximum_pejualan
|
minimum_pejualan
|
|
Argentina
|
7
|
37.62
|
5.374286
|
13.86
|
0.99
|
|
Australia
|
7
|
37.62
|
5.374286
|
13.86
|
0.99
|
|
Austria
|
7
|
42.62
|
6.088571
|
18.86
|
0.99
|
|
Belgium
|
7
|
37.62
|
5.374286
|
13.86
|
0.99
|
|
Brazil
|
35
|
190.10
|
5.431429
|
13.86
|
0.99
|
|
Canada
|
56
|
303.96
|
5.427857
|
13.86
|
0.99
|
|
Chile
|
7
|
46.62
|
6.660000
|
17.91
|
0.99
|
|
Czech Republic
|
14
|
90.24
|
6.445714
|
25.86
|
0.99
|
|
Denmark
|
7
|
37.62
|
5.374286
|
13.86
|
0.99
|
|
Finland
|
7
|
41.62
|
5.945714
|
13.86
|
0.99
|
|
France
|
35
|
195.10
|
5.574286
|
16.86
|
0.99
|
|
Germany
|
28
|
156.48
|
5.588571
|
14.91
|
0.99
|
|
Hungary
|
7
|
45.62
|
6.517143
|
21.86
|
0.99
|
|
India
|
13
|
75.26
|
5.789231
|
13.86
|
1.98
|
|
Ireland
|
7
|
45.62
|
6.517143
|
21.86
|
0.99
|
|
Italy
|
7
|
37.62
|
5.374286
|
13.86
|
0.99
|
|
Netherlands
|
7
|
40.62
|
5.802857
|
13.86
|
0.99
|
|
Norway
|
7
|
39.62
|
5.660000
|
15.86
|
0.99
|
|
Poland
|
7
|
37.62
|
5.374286
|
13.86
|
0.99
|
|
Portugal
|
14
|
77.24
|
5.517143
|
13.86
|
0.99
|
|
Spain
|
7
|
37.62
|
5.374286
|
13.86
|
0.99
|
|
Sweden
|
7
|
38.62
|
5.517143
|
13.86
|
0.99
|
|
United Kingdom
|
21
|
112.86
|
5.374286
|
13.86
|
0.99
|
|
USA
|
91
|
523.06
|
5.747912
|
23.86
|
0.99
|
Plot Sederhana
Akan dialkukan plot untuk total penjualan per negara
p<-ggplot(data=result, aes(x=BillingCountry, y=Total_pejualan)) +
geom_bar(stat="identity")
p+ theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust=1))
