Mengolah Data E-Commerce Menggunakan PostgreSQL
SQL (Structured Query Language) adalah bahasa pemrograman khusus yang digunakan untuk mengelola, mengambil, dan memanipulasi relational database. Relational database adalah sebuah data dalam bentuk tabel yang saling terhubung melalui primary key dan foreign key. Banyak software komputer yang dapat digunakan untuk mengelola, menyimpan, atau memanipulasi data yang biasa disebut DBMS(Database Management System). Contoh DBMS diantaranya adalah MySQL, PostgreSQL, Microsoft SQL Server, Oracle, dan sistem database lainnya. Kali ini akan dibahas beberapa query beserta contoh persoalan dengan menggunakan PostgreSQL.
Pembahasan pada artikel ini akan menjelaskan bagaimana penggunaan SQL pada sebuah studi kasus menggunakan beberapa perintah SQL seperti select, where, group by, order by, inner join dan yang lainnya berdasarkan pengetahuan yang sudah saya pelajari sebelumnya.
Mari kita mulai!.
Data preparation
pada pembahasan ini dataset yang akan digunakan merupakan data E-commerce yang terdiri dari empat table yang saling terhubung. Dataset ini berisi data transaksi konsumen pada sebuah E-Commerce, dataset yang digunakan terdiri dari tabel order_detail, customer_detail, sku_detail, dan payment detail. Berikut merupakan skema tabel yang menunjukan bagaimana tabel saling terhubung satu sama lain.
Setelah mengenal dataset yang akan digunakan tahap pertama adalah melakukan import data, jika sudah berhasil melakukan import data akan lebih baik data dilakukan pengecekan data terlebih dahulu.
Pengecekan data dapat dilakukan menggunakan perintah SQL berikut query yang digunakan.
SELECT * FROM order_detail
SELECT * FROM customer_detail
SELECT * FROM sku_detail
SELECT * FROM payment_detailBerikut penjelasan mengenai perintah yang digunakan pada query di atas.
SELECT: perintah ini digunakan untuk memilih kolom apa yang akan ditampilkan pada hasil query
*: tanda bintang berarti bahwa akan dipilih semua kolom yang terdapat pada tabel
FROM: digunakan untuk menunjukan sumber tabel mana yang akan diekstrak datanya
Jika data tampil setelah menggunakan query tersebut berarti data sudah berhasil diimport dan siap diolah.
Persoalan dan Pembahasan
Selanjutnya data tersebut akan diolah berdasarkan permintaan user sebagai berikut.
1. Selama transaksi yang terjadi selama 2021, pada bulan apa total nilai transaksi setelah diskon paling besar yang sudah melakukan pembayaran?
Pada permintaan tersebut diminta untuk mengetahui transaksi paling besar per bulan selama tahun 2021 dimana konsumennya sudah melakukan pembayaran, bisa menggunakan query berikut.
SELECT
Extract(MONTH FROM order_date) AS bulan,
SUM(after_discount) AS total_nilai_transaksi
FROM order_detail
WHERE is_valid = 1 AND EXTRACT(YEAR FROM order_date) = 2021
GROUP BY bulan
ORDER BY total_nilai_transaksi DESC;Berikut penjelasan query yang digunakan.
SELECT: perintah ini digunakan untuk memilih kolom apa yang akan ditampilkan pada hasil query, pada query ini dipilih bulan dan total nilai transaksi untuk ditampilkan.
EXTRACT: digunakan untuk mengekstrak tanggal sesuai kebutuhan, pada query ini dipilih bulan karena ingin menampilkan bulan
SUM: merupakan fungsi agregasi untuk menjumlahkan, pada query ini digunakan kolom after discount karena ingin mengetahui total nilai transaksi setelah diskon.
AS: digunakan untuk memberi alias pada kolom sehingga mudah dibaca.
FROM: digunakan untuk menunjukan sumber tabel mana yang akan diekstrak datanya, pada query ini menggunakan tabel order detail.
WHERE: digunakan untuk memberikan kondisi yang harus dipenuhi dimana pada query ini digunakan is_valid = 1 untuk memfilter konsumen yang sudah melakukan pembayaran dan EXTRACT(YEAR FROM order_date) = 2021 untuk memfilter transaksi tahun 2021.
GROUP BY: digunakan untuk mengelompokkan data jika terdapat fungsi agregasi pada bagian SELECT.
ORDER BY: digunakan untuk mengurutkan data berdasarkan kolom yang dipilih, pada query ini total nilai transaksi dipilih karena ingin melihat nilai transaksi dari yang terbesar hingga yang terkecil.
DESC: digunakan jika ingin mengurutkan data secara descending atau dari nilai terbesar ke terkecil.
Pada hasil query tersebut dapat dilihat bahwa transaksi pada bulan ke 8 (Agustus) yang memiliki nilai paling besar selama tahun 2021.
2. Selama transaksi pada tahun 2022, kategori apa yang menghasilkan nilai transaksi paling besar?
Pada permintaan ini diminta untuk menampilkan transaksi paling besar selama tahun 2021 berdasarkan kategori, karena pada tabel order_detail tidak terdapat data kategori sehingga harus menggunakan 2 tabel yaitu order_detail dan sku_detail. Dapat menggunakan query berikut.
SELECT
sd.category AS kategori,
SUM(after_discount) AS total_transaksi
FROM sku_detail AS sd
JOIN order_detail AS od
ON sd.id = od.sku_id
WHERE od.is_valid = 1 AND EXTRACT(YEAR FROM od.order_date) = 2022
GROUP BY kategori
ORDER BY total_transaksi DESCPada query diatas digunakan statement SELECT untuk menampilkan category dan SUM total transaksi FROM tabel sku_detail digabungkan menggunakan INNER JOIN dengan order_detail ON sku_id dan diberi alias agar mudah dipanggil. Kemudian menggunakan statement WHERE untuk memfilter kondisi dimana is_valid = 1 dan EXTRACT(YEAR) untuk mengambil data tahun 2022, karena menggunakan fungsi agregasi pada statement SELECT maka diperlukan GROUP BY berdasarkan category kemudian diurutkan menggunakan statement ORDER BY berdasarkan total transaksi secara DESC dari yang terbesar ke yang terkecil.
Didapatkan hasil sebagai berikut.
Dari hasil tersebut dapat disimpulkan bahwa nilai transaksi paling besar berdasarkan kategori ada Mobiles & Tablets.
3. Bandingkan nilai transaksi dari masing-masing kategori pada tahun 2021 dengan 2022. Sebutkan kategori apa saja yang mengalami peningkatan dan kategori apa yang mengalami penurunan nilai transaksi dari tahun 2021 ke 2022?
Pada permintaan ini diminta untuk menampilkan peningkatan dan penurunan nilai berdasarkan kategori dari tahun 2022 ke 2021, pada permintaan ini menggunakan 2 tabel antara order_detail dan sku_detail. Dapat menggunakan query berikut.
WITH tahun_2021 AS
(
SELECT
sd.category AS category,
SUM(sd.base_price * od.qty_ordered) AS total_transaksi
FROM sku_detail AS sd
JOIN order_detail AS od
ON sd.id = od.sku_id
WHERE od.is_valid = 1 AND EXTRACT(YEAR FROM od.order_date) = 2021
GROUP BY sd.category, EXTRACT(YEAR FROM od.order_date)
),
tahun_2022 AS (
SELECT
sd.category AS category,
SUM(sd.base_price * od.qty_ordered) AS total_transaksi
FROM sku_detail AS sd
JOIN order_detail AS od
ON sd.id = od.sku_id
WHERE od.is_valid = 1 AND EXTRACT(YEAR FROM od.order_date) = 2022
GROUP BY sd.category, EXTRACT(YEAR FROM od.order_date)
)
SELECT
t21.category as category,
t21.total_transaksi AS transaksi_2021,
t22.total_transaksi AS transaksi_2022,
(t22.total_transaksi - t21.total_transaksi) AS selisih_transaksi
FROM tahun_2021 AS t21
JOIN tahun_2022 AS t22
ON t21.category = t22.category
ORDER BY categoryPada case ini menggunakan CTE dengan statement WITH AS untuk membuat hasil sementara sehingga data mudah dikelola. pertama, membuat CTE dengan nama tahun_2021 dimana di dalamnya terdapat statement SELECT untuk menampilkan tahun dan category dari tabel sku_detail dan order_detail menggunakan INNER JOIN berdasarkan sku_id. kemudian, data difilter menggunakan WHERE is_valid = 1 dan EXTRACT(YEAR) = 2021. Mengingat penggunaan fungsi agregasi dalam SELECT, maka diperlukanlah GROUP BY berdasarkan tahun, hal yang sama dilakukan pada CTE dengan nama tahun_2022 akan tetapi filter tahun menggunakan 2022. setelah CTE dibuat selanjutnya gunakan statement SELECT untuk menampilkan category, transaksi 2022, transaksi 20221 dan selisih antara transaksi 2022 dan 2021, menggunakan INNER JOIN untuk menggabungkan total transaksi 2022 dan 2021 berdasarkan category kemudian menggunakan ORDER BY category untuk mengurutkan sesuai alphabet.
Pada hasil query disamping dapat dilihat berdasarkan selisih transaksi dimana nilai yang berada di atas 0 berarti mengalami kenaikan dan nilai yang berada di bawah 0 berarti mengalami penurunan.
4. Tampilkan top 5 metode pembayaran yang paling populer digunakan selama 2022(berdasarkan total unique order).
Pada permintaan ini diminta untuk menampilkan 5 metode pembayaran terpopuler tahun 2022, berikut query yang dapat digunakan.
SELECT
pd.payment_method,
COUNT(distinct od.id) AS jumlah_metode_pembayaran
FROM order_detail AS od
JOIN payment_detail AS pd
ON od.payment_id = pd.id
WHERE od.is_valid = 1 AND EXTRACT(YEAR FROM od.order_date) = 2022
GROUP BY pd.payment_method
ORDER BY jumlah_metode_pembayaran DESC
LIMIT 5Berikut penjelasan query yang digunakan.
Menggunakan statement SELECT untuk mengambil kolom payment_method dan jumlah transaksi berdasarkan metode pembayaran menggunakan COUNT (DISTINCT od.id) dari tabel order_detail dan payment_detail berdasarkan payment_id. Kemudian menggunakan statement WHERE untuk filter data dengan kondisi is_valid = 1 dan EXTRACT(YEAR) 2022. karena menggunakan fungsi agregasi pada statement select maka diperlukan GROUP BY payment_method selanjutnya diurutkan menggunakan ORDER BY jumlah metode pembayaran secara DESC dari yang terbesar ke yang terkecil dan menggunakan LIMIT 5 untuk menampilkan 5 data teratas
Berdasarkan hasil query 5 metode pembayaran paling populer adalah cod, pay axis, customer credit, easy pay, dan jazz wallet
5. Urutkan dari ke-5 produk ini berdasarkan nilai transaksinya (Samsung, Apple, Sony, Huawei, Lenovo).
Kolom sku_name di dalam tabel sku_detail, product name belum secara langsung dikelompokkan dalam 1 kategori brand sehingga perlu di gunakan statement CASE untuk mengelompokkan kalimat menjadi kata sesuai dengan kriteria.
Dapat menggunakan query berikut.
WITH a AS (
SELECT
CASE
WHEN LOWER (sd.sku_name) LIKE '%samsung%' THEN 'Samsung'
WHEN LOWER (sd.sku_name) LIKE '%apple%'
OR LOWER (sd.sku_name) LIKE '%macbook%'
OR LOWER (sd.sku_name) LIKE '%iphone%'
OR LOWER (sd.sku_name) LIKE '%ipad%' THEN 'Apple'
WHEN LOWER (sd.sku_name) LIKE '%sony%' THEN 'Sony'
WHEN LOWER (sd.sku_name) LIKE '%huawei%' THEN 'Huawei'
WHEN LOWER (sd.sku_name) LIKE '%lenovo%' THEN 'Lenovo'
END nama_produk,
SUM(od.after_discount) AS total_sales
FROM order_detail AS od
JOIN sku_detail AS sd
ON od.sku_id = sd.id
WHERE is_valid = 1
GROUP BY nama_produk
)
SELECT * from a
WHERE nama_produk IS NOT NULL
ORDER BY total_sales DESCBerikut penjelasan query yang digunakan.
Menggunakan CTE dengan pernyataan WITH AS, dinamakan a, untuk membentuk hasil sementara. Digunakanlah pernyataan SELECT untuk menampilkan produk sesuai permintaan. Kemudian, dilakukan penggunaan pernyataan CASE untuk mengimplementasikan logika berdasarkan kondisi.Dalam hal ini, digunakan WHEN LOWER untuk mengambil semua character tidak memperdulikan character tersebut dalam uppercase atau lower case, digunakan pernyataan LIKE untuk mencocokkan nilai kolom yang mengandung ‘%samsung%’. Penambahan persentase di kedua sisi kata “samsung” ini memungkinkan pengambilan nilai yang mengandung kata tersebut di mana pun kata itu muncul dalam sebuah kalimat. THEN untuk menampilkan hasil dari logika jika semua syaratnya terpenuhi, berlaku untuk 4 kategori lainnya.
Data tersebut diambil dari tabel order_detail dan sku_detail digabungkan menggunakan INNER JOIN berdasarkan sku_id. kemudian, difilter menggunakan WHERE is_valid = 1 dikelompokan menggunakan GROUP BY nama_produk.
JIka CTE sudah dibuat selanjuyanya tinggal dipanggil menggunakan statement SELECT semua kolom pada tabel a WHERE nama produk IS NOT NULL. IS NOT NULL digunakan untuk mengambil data yang tidak kosong kemudian diurutkan menggunakan ORDER BY berdasrkan total_sales secara DESC dari yang terbesar ke yang terkecil.
Hasilnya sku_name yang awalnya berupa nama produk secara spesifik, berhasil dikelompokkan kedalam brandnya masing masing.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
Demikian pembahasan dari beberapa persoalan analisis data dengan beberapa perintah, fungsi, kata kunci, maupun klausa dalam SQL menggunakan PostgreSQL. Semoga dasar pembelajaran ini dapat bermanfaat bagi yang membacanya serta mencobanya. Terima kasih!
