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; |
ถ้าต้องการนับจำนวน unique records ที่อยู่ใน table customers ก็แค่เพิ่มคำว่า DISTINCT ไปในฟังชั่น COUNT ได้เลย result จะออกมาเท่ากับ 24 แปลว่าลูกค้าของบริษัทเรามีทั้งหมด 59 คน มาจาก 24 ประเทศ Awesome !!
| -- count only distinct country in table customers | |
| SELECT COUNT(DISTINCT country) | |
| FROM customers; |
ลองทำแบบฝึกหัด 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; |
เราสามารใช้ 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; |
จะได้ 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 – ต้อง
JOINgenres กับ tracks เพื่อตอบคำถามข้อนี้) - อยากรู้ว่ายอดขาย SUM(invoices.total) เพลงแบ่งตามประเทศ (hint – ต้อง
JOINinvoices กับ 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; |
ตอนนี้ทุกคนเข้าใจ concept ของ SQL เบื้องต้นแล้ว อย่าลืมว่า practice makes perfect ลองหาโจทย์ database อื่นๆมาฝึกฝีมือเพิ่มเติม เช่น Yelp dataset หรือของ Microsoft ก็ได้ มีให้เลือกหลายตัวเลย
อยากเรียน SQL ให้ลึกกว่านี้ หรืออยากลอง R Python สมัครเรียนฟรีกับเราได้ที่ https://datarockieblog.wordpress.com
