10 Query SQL Yang Perlu Dipahami Sebagai Data Analyst/Scientist

SQL adalah salah satu skill yang wajib diketahui oleh seorang Data Analyst dan Data Scientist. Saya coba berbagi 10 Query dasar dan lanjutan yang sering digunakan dalam ilmu data.

Catatan: query ditulis dalam PostgreSQL, data dan scheme bisa Anda download di sini

1) Select All Columns

Jika ingin memilih semua kolom yang tersedia dalam tabel, gunakan sintaks berikut: * akan menampilkan semua semua kolom dalam tabel.

SELECT * FROM employee

Catatan: untuk mengimplementasikan dalam sebuah aplikasi, penggunaan * tidak direkomendasikan. Sebaiknya langsung memilih kolom yang akan ditampilkan.

2) Where Statement

Klausa WHERE digunakan untuk memfilter data sesuai dengan kebutuhan.

Contoh: tampilkan semua data dengan department_id yaitu d005

SELECT * FROM department_employee WHERE department_id = 'd005'

3) Group by dan Having Clause

Klausa GROUP BY mengelompokkan baris yang memiliki nilai yang sama.

Contoh: Berapa total gaji setiap employee ?

	SELECT 
		employee_id
		, sum(amount) AStotal_salary_emp 
	FROM 
		salary 
	GROUP BY 
		employee_id 

Perintah Group By akan mengelompokkan data setiap employee, kemudian akan menghitung jumlah salary.

Dalam SQL, fungsi agregasi seperti SUM, AVG, MAX, MIN, dan COUNT tidak dapat digunakan dalam klausa WHERE. Jika ingin memfilter tabel kita dengan fungsi agregasi, maka perlu menggunakan klausa HAVING.

Contoh: misalnya kita ingin menampilkan total gaji setiap employe lebih dari 900000

	SELECT 
		employee_id
		, sum(amount) AStotal_salary_emp 
	FROM 
		salary 
	GROUP BY 
		employee_id 
	HAVING 
	sum(amount) > 900000

4) Order By and Limit

Order By berfungsi untuk mengurutkan apakah secara Ascending (pengurutan dari yang terkecil ke yang terbesar) atau Descending (pengurutan yang terbesar ke yang terkecil). Sedangkan Limit membatasi jumlah data yang ingin ditampilkan.

Contoh: Urutkan besaran total gaji dari setiap employe

	SELECT 
		employee_id
		, sum(amount) AStotal_salary_emp 
	FROM 
		salary 
	GROUP BY 
		employee_id 
	ORDER BY 
		total_salary_emp DESC

Jika kita ingin melihat 5 employe dengan total gaji terbesar, maka kita gunakan fungsi LIMIT pada bagian akhir setelah ORDER BY.

Catatan: Klausa Default Order By mengurutkan hasil dalam urutan ASCENDING.

5) Date Functions

Di PostgreSQL, sangat mudah mengekstrak nilai dari kolom tanggal. Kita bisa menggunakan bebearpa fungsi tanggal yang paling sering digunakan di bawah ini.

SELECT 
		date_part('year',hire_date) asYEAR
		, date_part('month',hire_date) asMONTH
		, date_part('day',hire_date) asDAY
		, date_part('dow',hire_date) asdayofweek 
		, to_char(hire_date, 'Dy') asday_name
		, to_char(hire_date,'Month') asmonth_name
		, hire_date
FROM employee

6) Inner, Left or Right Joins

Klausa Inner Join menampilkan atau membuat tabel baru (bukan fisik) dengan menggabungkan baris yang memiliki nilai yang cocok dalam dua tabel atau lebih.

Contoh: Query semua informasi karyawan dan nama department.

	SELECT
		*
	FROM 
		department_employee de 
	INNER JOIN
		department d 
		ON 
		d.id = de.department_id 

Catatan: Kotak merah adalah tabel pertama dan kotak hijau adalah tabel kedua.

Klausa Left Join mengembalikan semua baris dari tabel kiri dan baris yang cocok dari tabel kanan. Jika tidak ada baris yang cocok ditemukan di tabel kanan, NULL digunakan. (sebaliknya untuk Right Join)

7) Subqueries

Subquery adalah query SQL yang bersarang di dalam query yang lebih besar. Subquery dapat terjadi dalam klausa SELECT, FROM, dan WHERE.

	SELECT 
		employee_id
		, amount
		, (SELECTmax(amount) FROM salary)
	FROM 
	salary s

8) Correlated Subqueries

Subquery berkorelasi adalah salah satu cara membaca setiap baris dalam tabel dan membandingkan nilai di setiap baris dengan data terkait. Ini digunakan setiap kali subquery harus mengembalikan hasil atau kumpulan hasil yang berbeda untuk setiap baris kandidat yang dipertimbangkan oleh kueri utama.

Contoh: Tampilkan nama depan, gaji, departemen, dan gaji rata-rata menurut departemen.

SELECT first_name
		, salary
		, department, round((
		SELECT AVG(salary) 
	    	FROM employees e2
	    	WHERE e1.department = e2.department
	    	GROUPBY department ))
		AS avg_salary_by_department
		FROM employees e1 
	WHERE salary > 
		(
		  SELECT AVG(salary) 
	    	FROM employees e2
	    	WHERE e1.department = e2.department
	    	GROUP BY department 
		)
ORDER BY salary

9) Case When Clause

Pernyataan CASE digunakan untuk mengimplementasikan logika di mana jika ingin menetapkan nilai satu kolom tergantung pada nilai di kolom lain. Hal ini mirip dengan pernyataan IF-ELSE.

Contoh: Query yang mencetak nama depan, gaji, dan gaji rata-rata serta kolom baru yang menunjukkan apakah gaji karyawan lebih tinggi dari rata-rata atau tidak.

SELECT 
		first_name
		, salary
		, (SELECT ROUND(AVG(salary)) 
			FROMemployees
			) ASaverage_salary,
		(
			CASE WHEN 
				salary > (SELECT AVG(salary) FROM employees) 
			THEN 'higher_than_average'
			ELSE 'lower_than_average' 
			END
		) AS Salary_Case
FROM employees

10) Window Functions

Window function menerapkan fungsi agregat dan ranking pada windows tertentu (kumpulan baris).

Klausa OVER digunakan dengan window function untuk mendefinisikan window itu sendiri.

Klausa OVER dapat melakukan dua hal:

Aggregation

Query berikut akan menampilkan gaji rata-rata untuk setiap departemen.

SELECT 
	e.first_name
	, e.last_name
	, d.dept_name
	, s.amount
	, ROUND(avg(s.amount) OVER(PARTITION BY d.id)) AS avg_sales_by_dept 	
FROM employee e 
JOIN salary s ONs.employee_id = e.id 
JOIN department_employee de ONde.employee_id = e.id 
JOIN department d ONd.id = de.department_id 
ORDER BY d.dept_name ASC

Ranking the Values

Fungsi Rank() adalah window function yang memberikan peringkat ke setiap baris dalam partisi dari kumpulan hasil.

Contoh berikut mengurutkan tabel berdasarkan gaji (menurun).

SELECT 
    e.first_name
	, e.last_name
	, d.dept_name
	, s.amount
	, RANK() OVER(ORDER BY s.amount DESC)	
FROM employee e 
JOIN salary s ON s.employee_id = e.id 
JOIN department_employee de ON de.employee_id = e.id 
JOIN department d ON d.id = de.department_id 
ORDER BY d.dept_name ASC

OK.. itulah 10 Query dasar yang perlu dipahami jika ingin menjadi Data Analyst atau Data Scientist. Tidak menutup kemungkinan masih banyak query lainnya yang harus dipahami. Silahkan komen dibawah jika ada yang ingin menambahkan query lainnya.

Selanjutnya saya akan mencoba membuat tulisan sebagai latihan untuk memperdalam Query SQL.

Happy ngoding.