Jumlah Items tiap Product Scale

# Memuat paket yang diperlukan untuk koneksi database dan visualisasi
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.2

library(scales)

## Warning: package 'scales' was built under R version 4.4.2

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)

# Mendapatkan data yang diperlukan

data1 <- dbGetQuery(con, "SELECT productScale,
                    COUNT(*) AS totalItems
                    FROM products
                    GROUP BY productScale")

data1

# Membuat barchart untuk banyaknya item tiap product scale
ggplot(data1, aes(x = productScale, y = totalItems)) +
geom_bar(stat = "identity", fill = "blue", alpha = 1) +
theme_minimal() +
labs(title = "Total Item per Skala Produk",
x = "Skala Produk",
y = "Total Item") +
theme(axis.text.x = element_text(angle = 45, hjust = 1))

## Don't know how to automatically pick scale for object of type <integer64>.
## Defaulting to continuous.

###############################
##########  LATIHAN ###########
###############################

query_nordic <- "SELECT 
                  o.orderNumber,
                  c.country,
                  SUM(od.quantityOrdered * od.priceEach)                        AS orderValue
                  FROM customers c
                  JOIN orders o ON c.customerNumber =                           o.customerNumber
                  JOIN orderdetails od ON o.orderNumber =                       od.orderNumber
                  WHERE c.country IN             ('Denmark','Finland','Norway','Sweden')
                  GROUP BY o.orderNumber, c.country"
data_nordic <- dbGetQuery(con, query_nordic)
data_nordic

ggplot(data_nordic, aes(x = orderValue)) +
  geom_histogram(aes(fill = after_stat(count)), bins = 15, color = "white") +
  stat_bin(bins = 15, geom = "text", aes(label = after_stat(count)), vjust = -0.5, size = 3) +
  scale_fill_gradient(low = "#132B43", high = "#56B1F7") +
  scale_x_continuous(labels = scales::label_dollar()) +
  scale_y_continuous(expand = 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")

```