Pendahuluan

Laporan ini menyajikan distribusi nilai pesanan (order value) yang diterima dari pelanggan di negara-negara Nordic. Proses ini mengintergrasikan SQL untuk pengambilan data dan R untuk visualisasi.

Koneksi Database dan Pengambilan Data

library(DBI)
## Warning: package 'DBI' was built under R version 4.4.2
library(ggplot2)
## Warning: package 'ggplot2' was built under R version 4.4.3
library(scales)
## Warning: package 'scales' was built under R version 4.4.3
con <- DBI::dbConnect(odbc::odbc(),
                      Driver = "MySQL ODBC 8.0 ANSI Driver",
                      Server = "127.0.0.1",
                      Database = "classicmodels",
                      UID = "root",
                      PWD = "root", 
                      Port = 3306)

Gunakan blok SQL untuk menghitung total nilau setiap pesanan dengan menggabungkan tabel orders, orderdetaiks, dan customers. Gunakan output.var untuk menyimpan kueri langsung dalam data frame R.

dt31 = dbGetQuery(con, "SELECT
                  o.ordernumber,
                  c.country,
                  SUM(od.quantityOrdered*od.priceEach) AS total_value
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
JOIN customers c ON o.customerNumber = c.customerNumber
WHERE c.country IN ('Denmark', 'Finland','Norway','Sueden')
GROUP BY o.orderNumber")

dt31
##    ordernumber  country total_value
## 1        10103   Norway    50218.95
## 2        10105  Denmark    53959.21
## 3        10141  Finland    29716.86
## 4        10151  Finland    32723.04
## 5        10155  Finland    37602.48
## 6        10158   Norway     1491.38
## 7        10161  Denmark    36164.46
## 8        10181 Norway      55069.55
## 9        10188 Norway      29954.91
## 10       10238  Denmark    28211.70
## 11       10239  Finland    16212.59
## 12       10247  Finland    28394.54
## 13       10256  Denmark     4710.73
## 14       10284 Norway      32260.16
## 15       10289 Norway      12538.01
## 16       10299  Finland    34341.08
## 17       10301 Norway      36798.88
## 18       10309   Norway    17876.32
## 19       10314  Denmark    53745.34
## 20       10325   Norway    34638.14
## 21       10327  Denmark    20564.86
## 22       10363  Finland    45785.34
## 23       10373  Finland    46770.52
## 24       10377  Finland    23602.90
## 25       10406  Denmark    21638.62

Visualisasi : Histogram Nilai Pesanan

Gunakan Library ggplot2 untuk membuat histogram. Gunakan fungsi geom_histogram untuk melihat distribusi frekuensi dari data total value.

ggplot(dt31, aes(x=total_value))+
  geom_histogram(aes(fill=after_stat(count)), bins=4, color="white" )+
  stat_bin(bins = 4, geom = "text", aes(label = after_stat(count)), vjust= -0.5, size=3)+
  scale_fill_gradient(low = "#132B43", high = "#56b1f7")+
  scale_x_continuous(labels = label_dollar())+
  scale_y_continuous(expansion(mult = c(0, 0.15)))+
  theme_minimal()+
  labs(title = "Distribusi Nilai Pesanan di Negara Nordic",
       subtitle = "Negara : Denmark, Finland, Norway, Sweden",
       x = "Nilai Pesanan (USD)",
       y = "Frekuensi (Jumlah Order)")+
  theme(legend.position = "none")