ฝึกเขียน SQL ใน 10 นาที - ตอนที่ 2

SQL ตอนที่ 1 – Understand Basic SQL in 10 Minutes

tutorial วันนี้เราจะมาสอนใช้งาน Aggregate Functions ใน SQL เพื่อสรุปผลสถิติง่ายๆ โดยฟังชั่นหลักๆที่เราใช้กันทุกวันมีอยู่ 5 ฟังชั่น – COUNT AVG SUM MIN MAX

บทความวันนี้ใช้เวลาแค่ 10 นาทีเหมือนเดิม! พร้อมแล้วก็เริ่มกันเลย

COUNT

2 นาที – นับจำนวน records ใน table ที่เราต้องการด้วยฟังชั่น COUNT

query แรกเราต้องการจะนับจำนวนแถว (records) ทั้งหมดที่อยู่ใน table customers จะได้คำตอบเท่ากับ 59

	— count number of rows in table customers
	SELECT COUNT(*)
	FROM customers;

view raw .sql hosted with ❤ by GitHub

ถ้าต้องการนับจำนวน unique records ที่อยู่ใน table customers ก็แค่เพิ่มคำว่า DISTINCT ไปในฟังชั่น COUNT ได้เลย result จะออกมาเท่ากับ 24 แปลว่าลูกค้าของบริษัทเรามีทั้งหมด 59 คน มาจาก 24 ประเทศ Awesome !!

	— count only distinct country in table customers
	SELECT COUNT(DISTINCT country)
	FROM customers;

view raw .sql hosted with ❤ by GitHub

ลองทำแบบฝึกหัด 3 ข้อนี้ก่อนไป session ถัดไป

นับจำนวนพนักงานทั้งหมดใน table employees
นับจำนวนเพลงทั้งหมดใน table tracks ที่ composer ชื่อขึ้นต้นด้วยตัว A (hint – ใช้ where และ like เพื่อฟิลเตอร์ record ที่เราต้องการ)
นับจำนวนรายการใน table invoices ที่เกิดขึ้นในเดือน ม.ค. – ก.พ. 2009 (hint – ใช้ where และ between เพื่อฟิลเตอร์ record ที่เราต้องการ e.g. BETWEEN ‘2008-01-01’ AND ‘2008-01-31’)

AVG, SUM, MIN, MAX

5 นาที – สำหรับฟังชั่นที่เหลือเพื่อนๆน่าจะพอเดาได้ว่ามันใช้ทำอะไร aggregate functions คือการหาค่าสถิติง่ายๆของคอลั่มที่เราต้องการ ลองดูตัวอย่าง query ด้านล่าง

	— Find key statistics for column total in table invoices
	SELECT
	COUNT(total),
	AVG(total),
	SUM(total),
	MIN(total),
	MAX(total)
	FROM invoices;

	— use AS to rename columns
	SELECT
	COUNT(total) AS count_invoice,
	AVG(total) AS average_invoice,
	SUM(total) AS sum_invoice,
	MIN(total) AS min_invoice,
	MAX(total) AS max_invoice
	FROM invoices;

view raw .sql hosted with ❤ by GitHub

เราสามารใช้ AS (ย่อมาจากคำว่า Alias) เพื่อเปลี่ยนชื่อคอลั่มอย่างที่เราต้องการได้เลย query ที่สองในตัวอย่างด้านบนเขียนเหมือนเดิมทุกอย่าง แค่เพิ่ม AS เพื่อตั้งชื่อคอลั่มใหม่

อย่าลืมว่าใน SELECT clause แต่ละคอลั่มที่เราเขียนต้องแยกด้วย comma เสมอ สำหรับเพื่อนๆที่เพิ่งเขียน SQL ใหม่ๆ มักเจอข้อผิดพลาดนี้ได้ง่ายๆ ลืมเขียน comma !!

อ่านเพิ่มเติมเรื่อง Aggregate Functions ได้ที่นี่ และลองทำแบบฝึก 2 ข้อนี้

จงหาค่าเฉลี่ย และผลรวมของคอลั่ม bytes ใน table tracks
จงหาค่าเฉลี่ย ผลรวม ค่าต่ำสุด ค่ามากสุดของคอลั่ม milliseconds ใน table tracks

GROUP BY

3 นาที – เขียน aggregate functions คู่กับ GROUP BY clause เพื่อคำนวณค่าสถิติตามกลุ่ม (group) เช่น นับจำนวนลูกค้าจับกลุ่มตามประเทศ หรือ นับจำนวนเพลงที่อยู่ในแต่ละ genre เป็นต้น

มาลองดู syntax การเขียน GROUP BY ด้วยกัน ตัวอย่าง query ด้านล่างเรา select ออกมาสองคอลั่ม คือ country และ count(*) และบอกให้ SQL จับกลุ่มค่าสถิติด้วยคอลั่ม country

	— count number of customers by country
	SELECT
	country,
	COUNT(*) AS n
	FROM customers
	GROUP BY country;

view raw .sql hosted with ❤ by GitHub

จะได้ result set ออกมาหน้าตาแบบนี้ (อันนี้เราตัดมาให้ดูแค่ 10 rows แรก)

อ่านเพิ่มเติมเรื่องการใช้ GROUP BY ได้ที่นี่ และทำแบบฝึกหัดข้อนี้ก่อนไป session ถัดไป – ลองจับกลุ่มพนักงานตามเมือง (city) ที่เค้าอยู่จาก table employees

Summary

tutorial นี้ เราเรียนการเขียน basic query ที่เป็นหัวใจสำคัญของการทำ data analysis ด้วย SQL

Aggregate functions ที่เราใช้ประจำมี count, avg, sum, min, max
GROUP BY ใช้คู่กับ aggregate function เสมอ
เราสามารถเขียน JOIN หลายๆ tables ก่อนและใช้ GROUP BY + aggregate functions เพื่อวิเคราะห์ข้อมูลเชิงลึกได้เช่นกัน
Optional – สำหรับ database บางตัว เช่น postgreSQL มีฟังชั่นสถิติอื่นๆนอกเหนือจากที่เราสอนวันนี้ด้วย ลองอ่านเพิ่มเติมได้ที่นี่

Practices

Are you ready !! แบบฝึกหัดสุดท้ายมีทั้งหมด 2 ข้อ ใช้สิ่งที่เราเรียนมาทั้งหมดตั้งแต่ tutorial ที่แล้ว

นับจำนวนเพลงที่อยู่ในแต่ละ genre (hint – ต้อง JOIN genres กับ tracks เพื่อตอบคำถามข้อนี้)
อยากรู้ว่ายอดขาย SUM(invoices.total) เพลงแบ่งตามประเทศ (hint – ต้อง JOIN invoices กับ customers เพื่อตอบคำถามข้อนี้)

เฉลยอยู่ในหัวข้อถัดไป อย่าเพิ่งเลื่อนไปดู ให้ลองทำเองก่อน Don’t cheat 😛

Solutions

That’s a tough one !!

เพื่อนๆที่ทำแบบฝึกหัดเสร็จแล้วแล้ว สามารถเช็คคำตอบของตัวเองได้ด้านล่าง

	— QUIZ 1
	— count number of songs by genre
	SELECT
	genres.name,
	COUNT(*) AS n
	FROM genres
	JOIN tracks
	ON genres.genreid = tracks.genreid
	GROUP BY genres.name;

	— QUIZ 2
	— sum invoices by country
	SELECT
	customers.country,
	SUM(total) AS sum_invoice
	FROM customers
	JOIN invoices
	ON customers.customerid = invoices.customerid
	GROUP BY customers.country;

view raw .sql hosted with ❤ by GitHub

ตอนนี้ทุกคนเข้าใจ concept ของ SQL เบื้องต้นแล้ว อย่าลืมว่า practice makes perfect ลองหาโจทย์ database อื่นๆมาฝึกฝีมือเพิ่มเติม เช่น Yelp dataset หรือของ Microsoft ก็ได้ มีให้เลือกหลายตัวเลย

อยากเรียน SQL ให้ลึกกว่านี้ หรืออยากลอง R Python สมัครเรียนฟรีกับเราได้ที่ https://datarockieblog.wordpress.com

DataRockie