Bab 3 Perhitungan & Statistik Dasar

Pada dasarnya SQL bukan saja hanya dapat dipergunakan untuk melakukan manajemen database. Tetapi, dapat juga melakukan perhitungan matematika dan statistika dasar yang pada akhirnya dapat disimpan pada database untuk keperluan analisa. Secara spesifik, pelatihan ini akan membahas beberapa diantaranya yakni mengenai penggunaan fungsi sklar dan fungsi agregat.

3.1 Fungsi Skalar vs Agregat

Fungsi skalar atau fungsi math dalam SQL digunakan untuk mengembalikan nilai tunggal (single value) dari suatu nilai input yang diberikan pada perintah sebelumnya. Sedangkan, Fungsi agregat dalam SQL digunakan untuk melakukan perhitungan matematika pada sekelompok nilai dan kemudian mengembalikan nilai tunggal.

3.1.1 Perhitungan Skalar

Sebelum kita memulai melakukan beberapa perhitungan skalar pada data yang kita pilih dari database. Pertama-tama, mari kita lihat dulu isi data pada tabel berikut:

from sqlalchemy import create_engine
import pymysql
import pandas as pd

# membuat engine
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}"
                       .format(user="bakti",
                               pw="123",
                               db="jne2"))
# engine koneksi ke database
db_enco = engine.connect()

# Perintah SQL 
sql = "SELECT * FROM inventory_items"
       
# Eksekusi perintah  SQL dengan Python (panda)
pd.read_sql(sql,db_enco)

Berikut ini adalah beberapa contoh dan deskripsi Fungsi Skalar yang terdapat dalam SQL:

Fungsi `ABS()`

Fungsi ABS() adalah sebagai untuk menampilkan nilai absolut dari suatu bilangan bulat. Sebelum memulai menggunakan fungsi ABS(), perlu untuk membuat objek koneksi dan kursor eksekusinya terlebih dahulu.

# import module 
import mysql.connector

# koneksi salah satu database MySQl ke Python
db1 = mysql.connector.connect(
  host='localhost',
  user='bakti',
  passwd='123',
  database='jne2'
)

# membuat objek 'cursor` sebagai kata kunci eksekusi SQL
cursor1 = db1.cursor()

Selanjutnya, berikut ini diperlihatkan contoh penerapan fungsi ABS().

# Perintah SQL 
sql = "SELECT ABS(cost) \
       FROM  inventory_items \
       WHERE product_id=16898"
       
# Eksekusi perintah  SQL
cursor1.execute(sql)

# menampilkan daftar tabel
for data in cursor1:
  print(data)

Dari hasil diatas diperlihatkan beberapa hasil yang sama. Untuk menangani hal tersebut, perhatikan koding berikut:

# Perintah SQL 
sql = "SELECT DISTINCT ABS(cost) \
       FROM  inventory_items \
       WHERE product_id=16898"
       
# Eksekusi perintah  SQL
cursor1.execute(sql)

# menampilkan daftar tabel
for data in cursor1:
  print(data)

3.1.2 Perhitungan Agregat

Berikut ini adalah beberapa contoh dan deskripsi Fungsi Agregat yang terdapat dalam SQL:

Fungsi `COUNT(*)`

Fungsi COUNT(*) mengembalikan jumlah baris yang dikembalikan oleh pernyataan SELECT, termasuk NULL dan duplikat, sedangkan COUNT(column) mengembalikan jumlah baris yang dikembalikan oleh klausa SELECT.

# Perintah SQL 
sql = "SELECT COUNT(product_category) \
       FROM inventory_items \
       WHERE cost<5"
       
# Eksekusi perintah  SQL
cursor1.execute(sql)

# menampilkan daftar tabel
for data in cursor1:
  print(data)

Tugas Mandiri

Apakah hasil Kueri diatas duah benar? Perhatikan Strukrur tabel Cost!
Cobalah untuk melakukan penjumlahan total pada product_category, dimana Cost<5. Paparkan temuan anda!

Fungsi `COUNTD(*)`

Fungsi COUNTD() adalah fungsi yang digunakan untuk menghitung jumlah baris secara bersusun dengan hanya berfungsi pada karakter yang berbeda. Misal jika terdapat tabel dengan baris 1,2,3,4,4,5,6,6,7 bersusun, maka yang terhitung jumlah barisnya hanya ada 7, karena hanya ada 7 buah karakter yang unik. Syntax fungsi COUNTD() secara umum adalah sebagai berikut :

Tugas Mandiri:

Pada jenis data apakah kita dapat menggunaka fungsi SUM()?
Jika dalam fungsi SUM() menggunakan opsi DISTINCT, apakah yang tejadi?

3.2 Perhitungan `GROUP BY`

Jika ingin memunculkan rata-rata dari cost untuk setiap product_department?

# Perintah SQL 
sql = "SELECT product_department, \
          AVG(cost) \
        FROM inventory_items \
        GROUP BY product_category;"
       
# Eksekusi perintah  SQL
cursor1.execute(sql)

# menampilkan daftar tabel
for data in cursor1:
  print(data)

Notes: Perhatikan hasilnya

Group By dapat digunakan untuk mengelompokkan data berdasarkan nilai dari barisnya. Lebih dari sekedar mengelompokkan data, Group By biasanya juga disandingkan dengan Fungsi Aggregate. Fungsi-fungsi Aggregate ini merupakan fungsi yang sudah dipelajari sebelumnya, seperti: SUM(), AVG(), COUNT() dan lain sebagainya.

Tugas Kelompok

Tampilkan jumlah brand yang ada pada pada tabel products dan urutkan dari besar ke kecil!

3.3 Perhitungan `HAVING`

Jika WHERE digunakan di conditional statement yang diaplikasikan pada kolom, maka HAVING digunakan untuk conditional statement di level group. HAVING dapat digunakan untuk memutuskan grup mana yang akan dipilih atau difilter pada hasil akhir. HAVING dapat digunakan dengan clause SUM(), AVG(), COUNT() dan fungsi agregat yang lain, tetapi tidak untuk WHERE.

# Perintah SQL 
sql = "SELECT brand, COUNT(brand) AS Jumlah_brand\
        FROM products \
        GROUP BY brand \
        HAVING (brand) = 2 "
       
# Eksekusi perintah  SQL
cursor1.execute(sql)

# menampilkan daftar tabel
for data in cursor1:
  print(data)

3.4 Perhitungan `CASE WHEN`

CASE digunakan layaknya seperti IF-ELSE pada beberapa bahasa pemrograman yang lain. Berikut ini adalah gambaran bagaimana CASE bekerja

3.4.1 Buat Struktur Tabel

# Perintah SQL 
sql = "CREATE TABLE jadwal \
  (kegiatan varchar(20), \
   senin varchar(10),  \
   selasa varchar(10), \
   rabu varchar(10), \
   kamis varchar(10), \
   jumat varchar(10), \
   sabtu varchar(10)) "
       
# Eksekusi perintah  SQL
cursor1.execute(sql)

# menampilkan daftar tabel
for data in cursor1:
  print(data)

3.4.2 Insert Data

# Perintah SQL 
sql = "INSERT INTO jadwal values \
('olahraga','ya','tidak','ya','tidak','ya','tidak'), \
('diskusi','tidak','ya','tidak','tidak','tidak','ya'), \
('rapat','ya','tidak','tidak','tidak','tidak','tidak'), \
('ulangan','tidak','ya','ya','ya','tidak','tidak')"
       
# Eksekusi perintah  SQL
cursor1.execute(sql)
db1.commit()

# menampilkan daftar tabel
for data in cursor1:
  print(data)

3.4.3 Lihat Data Tabel

# Perintah SQL 
sql = "SELECT * FROM jadwal;"
       
# Eksekusi perintah  SQL
cursor1.execute(sql)

# menampilkan daftar tabel
for data in cursor1:
  print(data)

3.4.4 Perhitungan `CASE`

sql = "SELECT jadwal.*, \
  CASE WHEN senin='ya' THEN 1 ELSE 0 END \
  + CASE WHEN selasa='ya' THEN 1 ELSE 0 END \
  + CASE WHEN rabu='ya' THEN 1 ELSE 0 END \
  + CASE WHEN kamis='ya' THEN 1 ELSE 0 END \
  + CASE WHEN jumat='ya' THEN 1 ELSE 0 END \
  + CASE WHEN sabtu='ya' THEN 1 ELSE 0 END AS digunakan, \
  CASE WHEN senin='tidak' THEN 1 ELSE 0 END \
  + CASE WHEN selasa='tidak' THEN 1 ELSE 0 END \
  + CASE WHEN rabu='tidak' THEN 1 ELSE 0 END \
  + CASE WHEN kamis='tidak' THEN 1 ELSE 0 END \
  + CASE WHEN jumat='tidak' THEN 1 ELSE 0 END \
  + CASE WHEN sabtu='tidak' THEN 1 ELSE 0 END AS tidak_digunakan \
  FROM jadwal;"
       
# Eksekusi perintah SQL
cursor1.execute(sql)

# Menampilkan daftar tabel
for data in cursor1:
    print(data)

3.5 Tugas Kelompok (Selesai duluan C, A, B)

Gunakan tabel order_items untuk mengetahui total dari beberapa status pengiriman
Pada tabel inventory_items. Tampilkan informasi kategori, nama produk dan biaya (dengan kondisi pembulatan dua angka dibelakang koma) tanpa duplikat dengan memfilter berdasarkan kategori produk (bebas pilih)
Pada tabel products, urutkan dari yang terbesar rata-rata harga retail berdasarkan jenis brand
Kelompokkan inventory_items berdasarkan kategori produk lalu, filter Total Biaya lebih dari 150
Kelompokkan inventory_items berdasarkan nama dan department produk dengan kasus total biaya untuk department laki-laki kurang dari 200 dan department perempuan lebih dari 200. Lalu urutkan dari yang terbesar

Bab 3 Perhitungan & Statistik Dasar

3.1 Fungsi Skalar vs Agregat

3.1.1 Perhitungan Skalar

Fungsi ABS()

3.1.2 Perhitungan Agregat

Fungsi COUNT(*)

Fungsi COUNTD(*)

3.2 Perhitungan GROUP BY

3.3 Perhitungan HAVING

3.4 Perhitungan CASE WHEN