SQL

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

tutorial นี้มาลองดูวิธีการใช้งาน aggregate functions คู่กับ GROUP BY clause ซึ่งเป็นหัวใจสำคัญของการทำ data analysis ด้วย SQL

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://datarockie.com

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.