Fungsi di SQL

Pendahuluan

Buat jadi analis data itu belajarnya banyak banget. Kemarin aku baru saja kelar belajar tentang ‘Python’, tapi kata Senja, aku masih perlu tahu soal SQL atau Structure Query Language. SQL ini nantinya akan jadi tools yang membantuku dalam menganalisis data perusahaan.

Apalagi, sebentar lagi perusahaan bakal merilis data penjualan tahun lalu. Jadi butuh sekali analis data untuk mengolahnya menjadi data berbentuk agregasi, bukan lagi raw data. Mendengarnya saja aku masih bingung.

“Senja, I really need your help!” kataku akhirnya setelah membaca isi modul.

“Oke, jadi kamu bingung di bagian apa? Biar saya bantu jelaskan,” ujar Senja.

Untungnya aku punya mentor sebaik Senja.

Fungsi Scalar vs Fungsi Aggregate

“Bagian fungsi nih, Nja. Boleh kasih tahu perannya apa dalam pengolahan data?” Aku mulai bertanya.

“Oke, begini Aksara. Fungsi adalah metode yang digunakan untuk melakukan operasi data di database. Operasi ini bisa berupa kalkulasi numerik seperti sum, count, avg, etc; atau operasi non-numerik seperti string concatenations dan sub-strings. SQL Function dapat dibagi ke dalam 2 kategori, yaitufungsi scalar dan fungsi aggregate.”

Penjelasan Senja mengundang pertanyaan baru di benakku.

“Hm, apa aja sih bedanya fungsi skalar dan fungsi aggregate?”

“Fungsi skalar dalam SQL digunakan untuk mengembalikan nilai tunggal (single value) dari suatu nilai input yang diberikan, sedangkan fungsi agregat dalam SQL digunakan untuk melakukan perhitungan pada sekelompok nilai dan kemudian mengembalikan nilai tunggal. Nah, biar lebih mudah dipahami mari kita bahas dan praktekkan fungsi-fungsi dari kedua kategori ini.”

Aku mengangguk. “Oke, Nja. Sejauh ini paham. Aku coba pelajari lagi yah modulnya. Kalau ada yang bingung, boleh kutanya lagi?”

“Silakan

Fungsi Skalar Matematika

“Nja, ini aku nemu jenis fungsi lain. Fungsi skalar matematika. Ini apa yah?” sahutku sekali lagi.

“Kamu sudah belajar sampai fungsi skalar ya, Aksara. Oke kalau begitu fungsi skalar pertama yang akan kita bahas adalah fungsi skalar untuk numerik value. Fungsi ini umumnya digunakan jika kita ingin melakukan operasi matematika di SQL secara cepat dan efektif. Di SQL sendiri ada banyak fungsi matematika,” jelas Senja rinci.

“Memangnya fungsi-fungsi apa saja yang bisa dilakukan di SQL?”

“Untuk mengecek fungsi-fungsi apa saja yang bisa dilakukan di SQL, kita bisa membuka dokumentasi fungsi SQL di sini: https://www.postgresql.org/docs/9.5/functions-math.html, untuk postgresql database dan di sini: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html, untuk mysql database. Nah, sebagai bahan praktik kamu agar lebih paham kamu bisa coba beberapa fungsi saja yang umumnya digunakan seperti ini,” ujar Senja sambil mengajakku menyimak isi tampilan layar laptopnya:

Untuk memudahkan pemahaman, aku diberikan Senja tabel dummy berisi nilai siswa semester 1 dan 2 di suatu sekolah. Berikut contoh penggunaan fungsi skalar dengan menggunakan tabel dummy: Tabel: students

Fungsi Skalar Matematika - ABS()

Fungsi ABS( )

Syntax:

SELECT ABS(ColumnName) FROM TableName;

Fungsi Skalar Matematika - CEILING()

Fungsi CEILING()

Syntax:

SELECT CEILING(ColumnName) FROM TableName;

Fungsi Skalar Matematika - FLOOR()

Fungsi FLOOR()

Syntax:

SELECT FLOOR(ColumnName) FROM TableName;

Fungsi Skalar Matematika - ROUND()

Fungsi ROUND()

Syntax:

SELECT ROUND(ColumnName) FROM TableName;

Fungsi Skalar Matematika - SQRT()

Fungsi SQRT()

Syntax:

SELECT SQRT(ColumnName) FROM TableName;

Tugas Peraktek

Fungsi Text di SQL

Fungsi Text

“Aku sudah cukup paham soal fungsi skalar matematika, Nja. Thank you!”

“Sip, tapi jangan senang dulu, karena masih ada fungsi lain. Sekarang kita akan membahas tentang fungsi skalar untuk text/string value. Fungsi ini digunakan jika kita ingin melakukan operasi pada text atau karakter di SQL, misalnya, mengubah huruf kecil ke huruf besar, menghitung jumlah karakter dari text, dll. Fungsi skalar text di SQL juga cukup banyak.”

Aku menarik napas panjang, kukira fungsi skalar hanya tadi saja.

“Jadi, aku bisa cek di mana untuk fungsi skalar text?”

“Sama seperti fungsi skalar matematika, kita juga bisa mengecek fungsi - fungsi skalar text di dokumentasi postgresql: https://www.postgresql.org/docs/9.1/functions-string.html; dan dokumentasi mysql: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html. Aku kirim ya link ini ke kamu, Aksara.”

Aku segera mengecek kotak masuk emailku.

“Untuk bahan praktik, kita akan mencoba beberapa fungsi saja yang sering digunakan saja, Aksara, yang ini,” tambah Senja sembari menunjuk tabel fungsinya untukku:

Fungsi Text - COUNCAT()

Fungsi CONCAT()

Syntax:

SELECT CONCAT(ColumnName1, ColumnName2, ColumnNameN) FROM TableName;

Fungsi Text - SUBSTRING_INDEX()

Syntax:

SELECT SUBSTRING_INDEX(column, delimiter, index to return) FROM TableName;

Keterangan:

• _column _--> merupakan nama kolom yang akan dipecah text-nya,
• _delimiter _--> karakter atau gabungan beberapa karakter untuk pemecah text pada kolom bersangkutan,
• _index_to_return _--> indeks dari pecahan text yang akan diambil.

Fungsi Text - SUBSTR()

Fungsi Text SUBSTR()

Syntax:

SELECT SUBSTR(columnName, Start Index, Number of string to be extract) FROM TableName;

Keterangan:

• _columnName _--> nama kolom yang akan dicari substring-nya
• Start Index --> indeks dari text yang dimiliki (dimulai dari 1)
• Number of string to be extract --> jumlah karakter atau beberapa karakter yang akan diambil.

Fungsi Text - LENGTH()

Syntax:

SELECT LENGTH(ColumnName) FROM TableName;

Fungsi Text - REPLACE()

Syntax:

SELECT REPLACE(ColumnName, Character/String to be change, New String/Character) FROM TableName;

Keterangan:

• _ColumnName _--> nama kolom yang akan diganti isi tiap record/barisnya berdasarkan string/karakter tertentu
• Character/String to be change --> string/karakter yang dimiliki untuk diganti
• New String/Character --> string/karakter baru pengganti string/karakter sebelumnya

Tugas Praktek

Fungsi Aggregate dan Group By

Fungsi Aggregate

“Aksara, sepertinya aku lupa menunjukkan tabel fungsi aggregate padamu. Sebentar.”

Aku mengalihkan perhatianku dari layar laptop dan mendorong bangku mendekati Senja. “Ya?”

“Seperti yang sudah dijelaskan bahwa fungsi aggregate ini digunakan untuk melakukan perhitungan pada sekelompok nilai. Biar lebih jelas kita akan mempraktikkan penggunaan fungsi aggregate di SQL dengan menggunakan fungsi - fungsi aggregate yang umum digunakan. Tapi sebelumnya, kamu simak saja dulu tabel fungsinya ini.”

Fungsi Aggregate - SUM()

Fungsi Aggregate - COUNT()

Fungsi Aggregate - AVG()

Tugas Praktek

Pengenalan GROUP BY

“Nja, kamu perlu bilang kalau kita akan mengolah data penjualan tahun lalu bukan?”

“Iya, ada apa?”

“Kalau sudah ada datanya, kita pasti butuh mengelompokkannya dan menghitungnya berdasarkan penjualan setiap provinsi maupun dikelompokkan per bulan. Kalau kayak begitu kasusnya, pakai SQL bagaimana caranya ya?”

“Gambaranmu bagus sekali, Aksara. Saya bantu jelaskan ya. Untuk mengelompokkan data di SQL kita menggunakan GROUP BY Statement. GROUP BY statement akan mengelompokkan data yang bernilai sama ke dalam satu group, dan dengan menggunakan fungsi aggregate seperti (COUNT, MAX, MIN, SUM, AVG) kita bisa melakukan agregasi untuk untuk setiap group atau kelompok yang terbentuk. Biar lebih jelas, coba pelajari penulisan syntax-nya,” jelas Senja.

Aku pun menyimak syntax yang diberikan:

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

Hal penting yang perlu diperhatikan adalah:

1. GROUP BY digunakan dengan SELECT, artinya kolom yang digunakan di GROUP BY statement, juga perlu ditempatkan di SELECT.
2. GROUP BY ditempatkan setelah WHERE, tetapi jika tidak menggunakan WHERE maka langsung ditempatkan setelah FROM.
3. Jika menggunakan ORDER BY, maka GROUP BY ditempatkan sebelum ORDER BY.

“Apakah kita bisa menggunakan group by untuk mengelompokkan data menggunakan 2 kriteria atau 2 kolom, Nja?” tanyaku penasaran.

“Tentu saja bisa. Group by bisa dilakukan dengan single column ataupun multiple column. Seperti ini contohnya:”

• Group by Single Column, data dikelompokkan menggunakan kriteria dari satu kolom saja, misalnya mengelompokkan data berdasarkan provinsi saja.
• Group by Multiple Column, data dikelompokkan menggunakan kriteria dari dua kolom atau lebih, misalnya mengelompokkan data berdasarkan province dan brand.

Group by Single Column

Fungsi Group by Single Column memastikan data dapat dikelompokkan menggunakan kriteria dari satu kolom saja, misalnya mengelompokkan data berdasarkan provinsi saja.

Group by Multiple Column

Dengan fungsi Group by Multiple Column, data dapat dikelompokkan menggunakan kriteria dari dua kolom atau lebih, misalnya mengelompokkan data berdasarkan province dan brand.

Fungsi Aggregate dengan Grouping

“Tambahan lagi, Aksara. Kamu masih ingatkan fungsi agregasi yang kita pelajari dan praktikkan sebelumnya? Pada fungsi itu kita belum menggunakan group by sehingga hasil SUM dan COUNT kita adalah hasil SUM dan COUNT dari seluruh baris yang ada di tabel data penjualan.”

“Ada data penjualannya kah, Nja? Biar lebih mudah membayangkan dan mempraktikkannya,” usulku.

Senja membuka data penjualan perusahaan tahun lalu untukku. Wow!

“Kalau begitu, kita praktik langsung saja. Sekarang coba kamu gunakan fungsi agregasi dan GROUP BY untuk menghitung total penjualan dari setiap provinsi di tahun 2019, dan kita bandingkan dengan hasil fungsi agregasi tanpa menggunakan group by,” pinta Senja.

Contoh aggregate dengan grouping:

Penggunaan CASE ... WHEN ..

“Wah, terima kasih Senja, aku sudah mulai paham menggunakan fungsi aggregate di SQL. Tapi aku mau tanya deh, kalau aku diminta untuk menambahkan kolom rekomendasi atau remark dari hasil agregasi data, misalnya nilai penjualan bulan Maret 2019 lebih dari 30M, maka diberikan rekomendasi/remark “Target Achieved”, kalau kurang diberi remark “Follow Up”. Bagaimana melakukannya di SQL ya?

“Itu mudah saja, Aksara. Itu bisa kamu lakukan dengan CASE - statement di SQL. CASE - statement ini sama dengan IF - THEN - ELSE di pemrograman pada umumnya. Syntax dari CASE – statement seperti ini. Coba lihat.”

CASE-statement akan mengevaluasi kondisi yang sudah ditentukan, dimulai dari condition1, dan akan mengembalikan hasil (result1), jika condition1 terpenuhi (TRUE). Jika tidak, maka condition2 akan dievaluasi, dan akan mengembalikan result2 jika condition2 terpenuhi, dst. Apabila tidak ada kondisi yang terpenuhi, maka result pada bagian ELSE yang akan dikembalikan.

Nah, silahkan praktikkan penggunaan CASE-Statement untuk membuat remark pada data aggregasi penjualan.

Tugas Praktek

Dengan menggunakan data sales_retail_2019, buatlah syntax query yang menggunakan fungsi skalar MONTH() untuk mengubah order_date dari tanggal ke bulan, fungsi aggregate SUM() untuk menjumlahkan kolom item_price.

Tambahkan kolom remark menggunakan CASE… WHEN… statement. Jika sum(item_price) ">=" 30.000.000.000, maka remark-nya 'Target Achieved'; Jika sum(item_price) ">=" 25.000.000.000 maka remark-nya 'Less performed'; Selain itu, beri remark 'Follow Up'.

Tugas:

Mini Project

Pendahuluan

Seperti dugaanku, setelah selesai memahami modul dan mempraktikkan latihan-latihannya dengan lancar, aku akan diberi proyek pekerjaan oleh Senja. Walaupun kadang menyebalkan karena bikin aku pulang telat, tapi ini caranya untuk mengajakku berkontribusi bagi perusahaan. Aku harus lebih semangat! Aku pun membuka email proyek dari Senja sambil menyeruput boba milk tea favoritku. Aksara, saya senang dengan perkembanganmu belakangan ini. Saya mau minta tolong agar kamu melakukan analisis penjualan di suatu store. Adapun laporan yang diminta sebagai berikut:

1. Total jumlah seluruh penjualan (total/revenue).
2. Total quantity seluruh produk yang terjual.
3. Total quantity dan total revenue untuk setiap kode produk.
4. Rata - Rata total belanja per kode pelanggan.
5. Selain itu, jangan lupa untuk menambahkan kolom baru dengan nama "kategori" yang mengkategorikan total/revenue ke dalam 3 kategori: High: > 300K; Medium: 100K - 300K; Low: kurang 100K.

Proyek Pekerjaan - Analisis Penjualan Part 1

Aku pun membuka email proyek dari Senja sambil menyeruput boba milk tea favoritku.

Aksara, saya senang dengan perkembanganmu belakangan ini. Saya mau minta tolong agar kamu melakukan analisis penjualan di suatu store. Adapun laporan yang diminta sebagai berikut:

1. Total jumlah seluruh penjualan (total/revenue).
2. Total quantity seluruh produk yang terjual.
3. Total quantity dan total revenue untuk setiap kode produk.

Proyek Pekerjaan - Analisis Penjualan Part2

(lanjutan ...)

Saya mau minta tolong agar kamu melakukan analisis penjualan di suatu store. Adapun laporan yang diminta sebagai berikut: Rata - Rata total belanja per kode pelanggan. Selain itu, jangan lupa untuk menambahkan kolom baru dengan nama ‘kategori’ yang mengkategorikan total/revenue ke dalam 3 kategori: High: > 300K; Medium: 100K - 300K; Low: kurang dari 100K.

Hasil Belajarku

Aku bangga dengan diriku sendiri! Dari modul Fundamental SQL Using FUNCTION and GROUP BY yang aku pelajari, aku telah memahami dan mampu mempraktikkan:

• Penggunaan fungsi skalar dan fungsi aggregate dalam operasi string dan numerik di SQL database
• Penggunaan GROUP BY dalam mengelompokkan data dan memahami konsep menggabungkan GROUP BY dengan fungsi aggregate
• Penggunaan CASE Statement untuk struktur pengambilan keputusan.
• Mengerjakan mini project yang merupakan integrasi keseluruhan materi dan tentunya materi-materi pada modul-modul sebelumnya untuk menyelesaikan persoalan bisnis.

Dengan kemampuan ini, aku lebih pede untuk mengolah data dengan SQL. Keterampilan ini sendiri adalah 60% aktivitas awal yang akan dilakukan seorang analis. Keep Fighting!

My certificate of completion