ฝึกเขียน 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 ถัดไป

  1. นับจำนวนพนักงานทั้งหมดใน table employees
  2. นับจำนวนเพลงทั้งหมดใน table tracks ที่ composer ชื่อขึ้นต้นด้วยตัว A (hint – ใช้ where และ like เพื่อฟิลเตอร์ record ที่เราต้องการ)
  3. นับจำนวนรายการใน 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 ข้อนี้

  1. จงหาค่าเฉลี่ย และผลรวมของคอลั่ม bytes ใน table tracks
  2. จงหาค่าเฉลี่ย ผลรวม ค่าต่ำสุด ค่ามากสุดของคอลั่ม 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 แรก)

result

อ่านเพิ่มเติมเรื่องการใช้ 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 ที่แล้ว

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

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

Solutions

cats
ขอบคุณรูปภาพสวยๆจาก unsplash.com

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

Leave a Reply