SQLite Part Two

Download

  • ดาวน์โหลดไฟล์ example_tables.sql สำหรับวีดีโอที่ 19

Key Concept

  • SQL Query พื้นฐานสำหรับ data analyst มีสาม clauses คือ select, where และ join
  • select ใช้ดึงคอลัมน์ที่เราต้องการ ถ้าต้องการพรีวิวข้อมูลให้ใช้คู่กับ limit
  • where ใช้ฟิลเตอร์ข้อมูลที่เราต้องการด้วยเงื่อนไข
  • join ใช้สำหรับดึงข้อมูลจากหลายๆตารางพร้อมกัน เราเชื่อม (join tables) ด้วย key หรือคอลัมน์ id
  • union ดึงข้อมูลจากสองตารางมาเรียงต่อกัน top/ bottom
  • Aggregate Functions ใช้สรุปผลสถิติเบื้องต้น avg sum min max count
  • วิธีการ export result เป็นไฟล์ .csv ทำได้สองวิธีคือ .excel และ .output

Review

รีวิวบทเรียน และ commands ที่เราใช้ใน module นี้ด้านล่าง

SQL ย่อมาจากคำว่า structured query language เป็นภาษาทางการที่เราใช้ทำงานกับ relational databases มาตั้งแต่ปี 1970s โดย SQL ใช้คำศัพท์ภาษาอังกฤษง่ายๆในการเขียน statement/ query

Select Columns

เราใช้ select clause เพื่อดึงคอลัมน์จากตารางในฐานข้อมูล * (อ่านว่า star) คือการดึงทุกคอลัมน์จากตารางนั้นๆ ในตัวอย่างด้านล่างถ้าเราต้องการเฉพาะคอลัมน์ name และ gpa ให้เขียนชื่อคอลัมน์ที่ต้องการใน select ได้เลย

-- select columns
SELECT * FROM student;
SELECT name, gpa FROM student;

เราใช้ limit ต่อท้ายชื่อตารางเพื่อระบุจำนวนแถวที่ต้องการดึงออกมาสำหรับพรีวิวข้อมูล

-- limit number of records
SELECT name, gpa FROM student LIMIT 5;

Create New Columns

เราสามารถสร้างคอลัมน์ใหม่ด้วย select clause ตัวอย่างด้านล่างเราดึงคอลัมน์ original gpa และสร้างคอลัมน์ new_gpa ด้วยสูตร gpa + 0.1 จากตาราง student ตั้งชื่อคอลัมน์ใหม่ด้วย as

-- create new columns
SELECT gpa, gpa + 0.1 AS new_gpa FROM student;

as เป็น optional clause เราไม่จำเป็นต้องเขียน as แค่เคาะ space bar หนึ่งครั้งหลัง gpa + 0.1 เพื่อตั้งชื่อคอลัมน์ใหม่ได้เลยตามตัวอย่างถัดไป

-- omit as
SEELCT gpa, gpa + 0.1 new_gpa FROM student;

SQL as a Calculator

เราสามารถใช้ select เพื่อคำนวณเลขง่ายๆได้ด้วย

SELECT 100 * 2;
SELECT 100 / 4;
SELECT 100 + 200;
SELECT 500 - 200;

Filter Records

มารีวิวการฟิลเตอร์ตารางกันบ้าง เราใช้ where clause เพื่อเขียนเงื่อนไขในการฟิลเตอร์ เบื้องต้นคือเราสามารถใช้ logical operator > < <= >= หรือไม่เท่ากับ <>

  • ฟิลเตอร์เฉพาะนักเรียนชื่อ “Anna” สำหรับคอลัมน์แบบ text ต้องใช้เครื่องหมาย double quote ด้วย
  • ฟิลเตอร์เฉพาะนักเรียนที่มี gpa มากกว่า 3.0
-- filter table using a condition
SELECT * FROM student WHERE name = "Anna";
SELECT * FROM student WHERE gpa > 3.0;

ฟิลเตอร์มากกว่าหนึ่งเงื่อนไขด้วย and และ or operator

  • ฟิลเตอร์เฉพาะนักเรียนที่ชื่อ “David” และเรียนคณะ “Economics”
  • ฟิลเตอร์เฉพาะนักเรียนที่เรียนคณะ “Economics” หรือ “Marketing”
-- filter table using two or more conditions
SELECT * FROM student
WHERE name = "David" AND major = "Economics";

-- filter students from Economics or Marketing
SELECT * FROM student
WHERE major = "Economics" OR major = "Marketing";

ถ้าเราต้องเขียนฟิลเตอร์ที่ใช้ or เยอะๆ เราสามารถเปลี่ยนมาใช้ in operator แทนได้ ทั้งสองวิธีได้ผลเหมือนกัน ตัวอย่างด้านล่างเราฟิลเตอร์เฉพาะนักเรียนที่เรียนคณะ “Economics” หรือ “Marketing”

เราเขียนชื่อคณะที่ต้องการอยู่ในวงเล็บ แต่ละชื่อคั่นด้วยเครื่องหมาย comma สำหรับโปรแกรมเมอร์เราเรียกข้อมูลที่อยู่ในวงเล็บแบบนี้ว่า “tuple” e.g. (“apple”, “banana”, “tomato”)

-- IN operator
SELEC * FROM student
WHERE major IN ("Economics", "Marketing");

Filter Range

เราใช้ between .. and .. เพื่อฟิลเตอร์ข้อมูลเป็นช่วงแบบ inclusive ตัวอย่างด้านล่างเราฟิลเตอร์เฉพาะนักเรียนที่ได้ gpa อยู่ระหว่าง 3.0 – 3.5 เทียบเท่ากับการเขียน gpa >= 3.0 and gpa <= 3.5

-- BETWEEN .. AND ..
SELECT * FROM student
WHERE gpa BETWEEN 3.0 AND 3.5;

-- identical result
SELECT * FROM student
WHERE gpa >= 3.0 AND gpa <= 3.5;

Pattern Matching

like คือ operator ที่เราใช้ทำ pattern matching หรือการหา pattern ที่เราต้องการ เช่น นักเรียนที่ชื่อขึ้นต้นด้วยตัว “J” หรือคณะที่ชื่อขึ้นต้นด้วยตัว “E” เราสามารถเขียน like กับ wildcard % ใน where clause ตามตัวอย่างด้านล่าง

  • "E%" ดึงชื่อคณะที่ขึ้นต้นด้วยตัว E
  • "%s" ดึงชื่อคณะที่ลงท้ายด้วยตัว s
-- filter major name starts with E
SELECT * FROM student WHERE major LIKE "E%";

-- filter major name ends with s
SELECT * FROM student WHERE major LIKE "%s";

Note – % ใช้ match ตัวอักษรอะไรก็ได้ และ like ไม่สนใจ case แปลว่า E% หรือ e% จะได้ผลลัพธ์เหมือนกัน

VLOOKUP in Excel

ก่อนเราจะเรียนวิธีการ join สองตารางเข้าด้วยกัน มาลองทบทวนฟังก์ชัน vlookup() ของ Excel กันก่อน (ความจริงคือ join ทำงานเหมือนกับ vlookup เลย) คลิกที่วีดีโอเพื่อดูแบบ HD

ดาวน์โหลดไฟล์ vlookup excel ในวีดีโอตัวอย่างได้ที่นี่

วีดีโอนี้ใน Excel มีข้อมูลสองตารางคือ student และ address ถ้าเราต้องการจะสร้างตาราง result ทางด้านขวาสุด โดยดึงคอลัมน์ city, country จากตาราง address มาใส่ในคอลัมน์ L และ M ตามลำดับ vlookup() หรือ vertical lookup values คือฟังก์ชันที่เกิดมาเพื่องานนี้

=VLOOKUP(id, address, 2, FALSE)
=VLOOKUP(id, address, 3, FALSE)

vlookup() จะใช้ตัวเลขในคอลัมน์ id ไปค้นหา (matching) กับคอลัมน์ id ของตาราง address ถ้าเจอ id ที่เหมือนกันมันจะดึงคอลัมน์ 2 และ 3 ของตาราง address มาใส่ในคอลัมน์ L และ M

ตาราง result คือผลรวมที่เราได้จากตาราง student + address โดยเราดึงคอลัมน์ที่ต้องการออกมา 4 คอลัมน์คือ student.id, student.name, address.city, address.country

โอเค! ตอนนี้เราพอเข้าใจ vlookup() บ้างแล้ว มาลองดูตัวอย่างการเขียน vlookup() หรือ join ใน sql กันบ้าง

Join Tables

ถ้าเราต้องการจะดึงข้อมูลจากสองตารางพร้อมกัน (หรือมากกว่าสองตารางก็ได้) ด้วยการ match คอลัมน์ id เราจะใช้ join และ on clauses ลองศึกษาตัวอย่างการเขียนด้านล่าง

  • ดึงข้อมูลทุกคอลัมน์จากตาราง student และ address โดยเชื่อมสองตารางเข้าด้วยกันด้วยคอลัมน์ id
  • syntax การเขียนเงื่อนไขใน on clause คือ table1.column = table2.column เราเขียนชื่อตาราง ดอท ตามด้วยชื่อคอลัมน์
-- join tables
SELECT * 
FROM student
JOIN address 
ON student.id = address.id;

ถ้าเราต้องการดึงข้อมูลเฉพาะคอลัมน์ที่เราต้องการ ให้เขียนชื่อคอลัมน์ใน select ด้วย syntax table.column ตัวอย่างด้านล่างเราดึงข้อมูลสามคอลัมน์คือ name จากตาราง student และ city, country จากตาราง address

-- select columns in joined table
SELECT student.name, address.city, address.country
FROM student 
JOIN address 
ON student.id = address.id;

เราสามารถเขียน join กี่ตารางก็ได้ แต่ยิ่งจอยหลายตาราง query ของเราก็จะรันนานขึ้น ลองดู pattern การเขียน join หลายตารางด้านล่าง

-- join more than two tables
SELECT * FROM table1
JOIN table2 ON table1.id = table2.id
JOIN table3 ON table2.id = table3.id
JOIN table4 ON table3.id = table4.id;

ในทางเทคนิค เราเรียกคอลัมน์ที่ใช้ในการ join ตารางว่า “key” ในตัวอย่างด้านบนคือคอลัมน์ id เป็น key ที่เราใช้ match rows ของตารางต่างๆ

Union

ถ้าเรามีสอง query ที่ต้องการนำมาเรียงต่อกัน เราจะใช้ union clause เงื่อนไขหลักๆในการเขียน union มีสองข้อ

  • จำนวนคอลัมน์ทั้งสอง query ที่เราเขียนต้องเท่ากัน
  • ประเภทข้อมูลในแต่ละคอลัมน์ต้องเหมือนกัน เช่น ถ้าคอลัมน์แรกของตารางที่หนึ่งเป็น text คอลัมน์แรกของตารางที่สองต้องเป็น text เหมือนกัน
SELECT * FROM student
UNION
SELECT * FROM new_student;

Tip – เราสามารถเขียน create table เพื่อสร้างตารางใหม่จากการ union ได้ด้วย syntax นี้ หลังจากรัน query จะมีตาราง all_student ให้เราเรียกใช้งานได้

CREATE TABLE all_student AS
  SELECT * FROM student UNION SELECT * FROM new_student;

Sort Rows

เราสามารถเรียงข้อมูลตามแถวด้วย order by clause หลังจากที่เรา union ตาราง student และ new_student เข้าด้วยกันแล้ว เราอาจจะอยาก sort คะแนน gpa จากน้อยไปมาก (หรือมากไปน้อย) แค่เขียน order by ต่อท้าย query ได้เลย โดย default จะเป็นการเรียงจากน้อยไปมากแบบ ascending order

ถ้าต้องการจะเรียงจากมากไปน้อย (descending order) ให้ใส่ desc ต่อท้ายชื่อคอลัมน์ด้วย

SELECT * FROM student
UNION 
SELECT * FROM new_student
ORDER BY gpa DESC;

Aggregate Functions

SQL มีฟังก์ชันสำหรับทำงานสถิติพื้นฐาน (descriptive statistics) เรียกว่า aggregate functions โดย 5 ฟังก์ชันหลักคือ avg sum min max และ count ลองดูตัวอย่างการใช้งานฟังก์ชันเหล่านี้ด้านล่าง

  • เราเรียน aggregate functions ใน select clause
  • วิธีการใช้งานฟังก์ชันเหมือนกับโปรแกรม Excel เลย แค่ใส่ชื่อคอลัมน์ที่ต้องการวิเคราะห์
  • ตั้งชื่อค่าสถิติด้วย as
-- apply aggregate functions
SELECT AVG(gpa), SUM(gpa), MIN(gpa), MAX(gpa), COUNT(gpa) FROM student;

-- rename columns
SELECT 
  AVG(gpa) AS avg_gpa,
  SUM(gpa) AS sum_gpa,
  MIN(gpa) AS min_gpa,
  MAX(gpa) AS max_gpa,
  COUNT(gpa) AS count_gpa
FROM student;

Export CSV File

เราสามารถ export ข้อมูลที่เราดึงขึ้นมาเป็นไฟล์ csv ได้สองวิธีคือ .excel และ .output ทั้งสองวิธีต้องตั้งค่าก่อนเรารัน query เสมอ โดย .excel จะเปิดไฟล์ด้วยโปรแกรม Excel ทันทีหลังจากรัน query

.mode csv
.headers on
.excel
SELECT * FROM student;

เวลาเราใช้ .output จะตามด้วยชื่อไฟล์ .csv ที่เราต้องการเซฟใน working directory หลังจากรัน query ตัว result จะถูกเขียนไปที่ไฟล์นั้นแทน (write out)

.mode csv
.headers on
.output result.csv
SELECT * FROM student;

Formative Quiz

🏆 รีวิวความรู้ที่ได้เรียนใน module นี้ คลิกเพื่อเริ่มทำแบบทดสอบ 20 ข้อ นักเรียนสามารถทำแบบทดสอบซ้ำกี่รอบก็ได้ จนกว่าจะได้คะแนนเต็ม

เสร็จแล้วแอดรบกวนช่วยตอบแบบสอบถามความพอใจการเรียน module นี้ด้วยนะครับ -/\- ขอบคุณทุกคนมากๆครับ แล้วพบกันในบทต่อไปนะ

Quiz
Feedback

Keep Learning

Well Done Friends! เมื่อพร้อมแล้ว คลิกเพื่อเริ่มเรียนบทต่อไป

Programming Course Menu