SQL — Fuction AND Group By — Beginner
Fungsi adalah metode yang digunakan untuk melakukan operasi data di database. SQL Function dapat dibagi ke dalam 2 kategori, yaitu fungsi scalar 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.
Pada bagian pertama kita akan membahas fungsi skalar untuk numerik value. Untuk mengecek fungsi-fungsi apa saja yang bisa dilakukan di SQL, kita bisa membuka dokumentasi fungsi SQL untuk postgresql database dan untuk mysql database. 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.
Sebagai contoh untuk latihan kita kali ini, diberikan beberapa contoh fungsi yaitu sebagai berikut.
Sebagai data awal kita akan menggunakan data students yang aku pelajari melalui DQLab Academy. Data ini akan digunakan sepanjang latihan ini ya:) Stay focus teman-teman.
Fungsi — ABS( )
Syntax:
SELECT ABS(ColumnName)
FROM TableName;
Pada latihan ini kita akan mengembalikan nilai absolut pada kolom MarkGrowth dengan menggunakan syntax seperti gambar beikut.
Dapat kita lihat nilai MarkGrowth pada StudentID nomor 1 dan 3 berubah dari negatif (-) menjadi positif (+).
Fungsi — CEILING( )
Syntax:
SELECT CEILING(ColumnName)
FROM TableName;
Pada bagian ini kita akan mengembalikan nilai integer terbesar yang terdekat dengan nilai inputnya menggunakan kolom Semester1 dan Semester2. Teman-teman bisa memperhatikan perubahan yang terjadi pada bagian console.
Fungsi — FLOOR( )
Syntax:
SELECT FLOOR(ColumnName)
FROM TableName;
Sama seperti sebelumnya, kita menggunakan kolom Semester1 dan Semester2. Perbedaannya perubahan yang terjadi adalah nilai pada kolom tersebut berubah menjadi nilai integer terkecil yang terdekat dengan nilai inputnya. Perhatikan perubahan yang terjadi pada bagian console.
Jika diperhatikan, penggunaan fungsi ini sekilas memiliki kesamaan yaitu fungsi digunakan untuk membulatkan sebuah nilai. Secara sederhana fungsi CEILING nilai outputnya akan dibulatkan keatas sedangkan fungsi FLOOR nilai outputnya akan dibulatkan kebawah.
Fungsi — ROUND( )
Syntax:
SELECT ROUND(ColumnName)
FROM TableName;
Pada fungsi ini kita akan mengembalikan nilai pembulatan suatu desimal pada kolom Semester1 dan Semester 2. Untuk Semester1 kita menginginkan desimalnya hanya 1 dibelakang koma, sedangkan untuk Semester2 akan dibulatkan tanpa nilai desimal. Perhatikan output pada bagian console.
Fungsi — SQRT( )
Syntax:
SELECT SQRT(ColumnName)
FROM TableName;
Seperti yang kita tahu SQRT = SquareRoot yang artinya akar kuadrat. Jadi pada bagian ini kita akan mengubah nilai akar kuadrat dari kolom Semester1. Perhatikat gambar berikut ya teman-teman.
Fungsi — MOD( ) dan EXP( )
Syntax:
SELECT MOD(ColumnName), EXP(ColumnName)
FROM TableName;
Pada bagian ini saya akan menggunakan 2 fungsi sekaligus yaitu fungsi mod untuk mengembalikan sisa hasil pembagian dari nilai input kolom Semester1 dibagi dengan angka 2, dan fungsi exp untuk mengembalikan nilai eksponensial kolom MarkGrowth. Maka akan menghasilkan output seperti gambar berikut.
Wah, ringan banget kan teori dan prakteknya. Semoga kalian yang baca tidak merasa kebingungan ya :) Tapi masih banyak lagi fungsi yang akan kita bahas, so tetap keep on fire ya guys :)
— NEXT —
FUNGSI 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 — fungsi skalar text dapat dicek di dokumentasi postgresql dan dokumentasi mysql.
Sebagai contoh untuk latihan kita kali ini, diberikan beberapa contoh fungsi yaitu sebagai berikut.
Fungsi Text — CONCAT( )
Syntax:
SELECT CONCAT(ColumnName1, ColumnName2, ColumnNameN)
FROM TableName;
Pada bagian ini kita akan menggabungkan argumen pada kolom FirstName dan LastName sebagai Name dengan syntax sebagai berikut.
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.
Contoh:
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.
Pada bagian ini kita akan mengambil karakter ke-2 hingga selanjutnya sebanyak 3 karakter dari kolom Firstname dan output diberi nama sebagai Initial. Maka syntaxnya seperti gambar berikut.
Fungsi Text — LENGTH( )
Syntax:
SELECT LENGTH(ColumnName)
FROM TableName;
Pada bagian ini kita akan menghitung jumlah huruf pada setiap argumen di kolom Firstname. Seperti gambar berikut.
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.
Pada bagian ini kita akan mengubah alamat email ‘yahoo’ menjadi ‘gmail’ seperti gambar berikut.
Fungsi Text— UPPER( ) dan LOWER( )
Syntax:
SELECT UPPER(ColumnName1), LOWER(ColumnName2)
FROM TableName;
Pada bagian ini kita akan mengubah huruf pada kolom FirstName menjadi huruf kapital semua dan untuk kolom LastName menjadi huruf kecil semua dengan menggunakan syntax seperti gambar berikut.
Fungsi aggregate ini digunakan untuk melakukan perhitungan pada sekelompok nilai. Berikut tabel fungsinya:
Cara menggunakan syntax pada fungsi-fungsi ini sangat sederhana dan dapat langsung kalian praktekkan. Berikut penggunaan dan output yang dihasilkan.
Fungsi Aggregate — SUM( )
Fungsi Aggregate — COUNT( )
Fungsi Aggregate — AVG( )
Fungsi Aggregate — MIN( ) dan MAX( )
Untuk fungsi FIRST() dan LAST() aku harapkan kalian coba sendiri ya teman-teman :) Fighting dan goodluck.
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.
Hal penting yang perlu diperhatikan adalah:
- GROUP BY digunakan dengan SELECT, artinya kolom yang digunakan di GROUP BY statement, juga perlu ditempatkan di SELECT.
- GROUP BY ditempatkan setelah WHERE, tetapi jika tidak menggunakan WHERE maka langsung ditempatkan setelah FROM.
- Jika menggunakan ORDER BY, maka GROUP BY ditempatkan sebelum ORDER BY.
Dengan Group by Single Column, data dikelompokkan menggunakan kriteria dari satu kolom saja, misalnya mengelompokkan data berdasarkan provinsi saja.
Perhatikan syntax dan output penggunaan Group by Single Column pada gambar berikut.
Dengan Group by Multiple Column, data dikelompokkan menggunakan kriteria dari dua kolom atau lebih, misalnya mengelompokkan data berdasarkan province dan brand.
Perhatikan syntax dan output penggunaan Group by Multiple Column pada gambar berikut.
Dari kedua output yang kita peroleh dapat kita lihat bahwa data penjualan tahun 2019 dimunculkan untuk Group by Single Column hanya provinsi saja, sedangkan ketika kita menambahkan brand kedalam syntax kita maka hal tersebut sudah dikategorikan sebagai multiple column.
FUNGSI AGGREGATE DENGAN GROUPING
Masih ingat kan tentang materi ini? Kalau sudah lupa kalian bisa cek artikel medium sebelumnya ya :)
Pada bagian sebelumnya, kita hanya menggunakan fungsi aggregate tanpa Group by sehingga hasil SUM dan Count kita adalah hasil SUM dan COUNT dari seluruh baris yang ada di tabel data penjualan. Coba kita praktikkan dengan menghitung total penjualan dari setiap provinsi di tahun 2019.
Perhatikan syntax dan output penggunaan Fungsi Aggregate dengan Group by pada gambar berikut.
PENGGUNAAN CASE … WHEN …
CASE — Statement ditempatkan sesudah SELECT, berikut syntax-nya secara lengkap :
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.
Perhatikan syntax dan output penggunaan Case … When … pada gambar berikut.
Syntax tersebut menggunakan fungsi skalar MONTH() untuk mengubah order_date dari tanggal ke bulan, fungsi aggregate SUM() untuk menjumlahkan kolom item_price. Penggunaan Case … When … dapat dilihat dari hasil kolom remark. Sehingga kita dapat melihat di bulan mana saja penjualan yang mengalami peningkatan ataupun penurunan.
THE END
Saatnya kita selesaikan juga Projek dari DQLab Academy ya. Sebagai member saya juga ingin teman-teman melihat apa saja yang saya kerjakan diprojek ini. Kalian juga bisa ya belajar disini. Pembelajarannya mudah dipahami dan tersedia live code editor sehingga kita tidak perlu repot meng-install aplikasi yang diperlukan. click this link to get your personal online course, DQLab Academy.
— Analisis Penjualan Part 1 —
Adapun laporan yang diminta sebagai berikut:
- Total jumlah seluruh penjualan (total/revenue).
- Total quantity seluruh produk yang terjual.
- Total quantity dan total revenue untuk setiap kode produk.
Pada projek kali ini kita akan menggunakan tabel : tr_penjualan.
Dengan mengikuti arahan dari DQLab Academy dan materi yang telah saya tuliskan di artikel ini maka syntax final yang saya gunakan seperti pada gambar berikut.
Setelah diklik run maka outputnya adalah sebagai berikut.
— Analisis Penjualan Part 2—
Adapun laporan yang diminta sebagai berikut:
4. Rata — Rata total belanja per kode pelanggan.
5. Menambahkan kolom baru dengan nama ‘kategori’ yang mengkategorikan total(revenue) ke dalam 3 kategori: High: > 300K; Medium: 100K — 300K; Low: <100K.
Dengan mengikuti arahan dari DQLab Academy dan materi yang telah saya tuliskan di artikel ini maka syntax final yang saya gunakan seperti pada gambar berikut.
Setelah diklik run maka outputnya adalah sebagai berikut.
Terimakasih buat kalian yang sudah membaca artikel yang panjang ini hingga akhir :) Semoga apa yang saya pelajari dan saya tuliskan kembali di artikel ini dapat kalian ikuti juga dengan mudah ya. See you in the next article :)
