บันทึกการใช้งาน SQL 102 สำหรับนักวิทยาศาสตร์ข้อมูล Data Science - sprint 02
SQL - 102
เราใช้ where clause เวลาที่ต้อวงการจะ filter เฉพาะข้อมูลแถวที่เราต้องการจาก database tables โดยเรามักจะใช้ where เสมอ ถ้าเรายิ่งใช้ถนัดมากเท่าไรก็จะช่วยวิเคราะห์ และ ตอบโจทย์มากขึ้นเท่านั้น
SELECT * FROM customers WHERE coutry = 'USA';
LIKE
คือ การเขียน pattern matching
%
ใช้ mathch any character กี่ตัวก็ได้_
ใช้ match single character ตัวเดียว
SELECT * from customers WHERE country = 'USA';
SELECT * from customers WHERE country = 'USA' AND state = 'CA';
SELECT * from customers WHERE country = 'USA' OR country = 'United Kingdom';
SELECT * from customers WHERE country in ('USA', 'United Kingdom');
SELECT * from customers WHERE country not in ('USA', 'United Kingdom');
SELECT * from customers WHERE email like '%gmail.com';
SELECT * from customers WHERE email not like '%gmail.com';
SELECT * from customers WHERE company is NULL;
SELECT * from customers WHERE company is not NULL;
SELECT * from customers WHERE customerid BETWEEN 10 and 15
Filter Data
มี lower()
function จะช่วยให้ไม่สน sensitive case
SELECT * from customers
Where LOWER(country) = 'united kingdom';
ไม่ต้องการประเทศเหล่านี้มาแสดง
SELECT * from customers
Where Not (country = 'USA' or country = 'Canada' OR country = 'Franch');
สังเกตว่าเวลาเรา query ข้อความ query string จะยาวมาก
SELECT * from customers
WHERE country = 'Brazil' or country = 'Germany' or country = 'Norway';
มันมี IN
operator เข้ามาช่วยให้สั้นลง
SELECT * from customers
WHERE country in ('Brazil', 'Germany', 'Norwat');
หากไม่ต้องการก็ใส่ not
เข้าไป
SELECT * from customers
WHERE country not in ('Brazil', 'Germany', 'Norwat');
หากเราต้องการจะเลือก Range ของข้อมูล
SELECT * from customers
WHERE customerid >= 5 and customerid <= 10;
แต่จะเห็นว่าหลัง where จะยาวและดูเงื่อนไขซับซ้อน เราสามารถใช้ between เข้ามาช่วยได้
SELECT * from customers
WHERE customerid BETWEEN 5 AND 10;
เราสามารถใช้ between
กับ Date ได้ด้วย
SELECT invoicedate from invoices
where invoicedate BETWEEN '2009-01-01 00:00:00' And '2009-01-19 00:00:00';
เราสามารถคัดกรอง ข้อมูลที่เป็น Null ได้เลยเช่นกัน
SELECT * from customers
WHERE company IS NULL;
ถ้าไม่ต้องการ Null ก็ใส่ not เข้าไป
SELECT * from customers
WHERE company IS not NULL;
หากเราต้องการจะ filter ด้วย pattern เราจะใช้ LIKE
มาช่วย
-- parttern maching
SELECT firstname, lastname, country, email from customers
WHERE email LIKE '%@gmail.com';
หรือ ไม่ต้องการ gmail.com
SELECT firstname, lastname, country, email from customers
WHERE email LIKE '%@gmail.com';
ถ้ามี % ปิดท้ายก็จะ match เฉพาะ ที่อยู่ใน % %
SELECT firstname, lastname, country, email, phone from customers
WHERE phone LIKE '%99%';
เรามาดู Operator ที่ใช้ LIKE
อีกตัว คือ under score ‘_’
สมมติว่าเราจะหาชื่อคนๆหนึ่งเช่น Leonie แต่เรารู้แค่ Leon แล้วพอค้นหาแล้วไม่เจอ
SELECT firstname, lastname, country, email, phone from customers
WHERE firstname LIKE 'leon';
เราจะใช้ under score เข้ามาช่วย
SELECT firstname, lastname, country, email, phone from customers
WHERE firstname LIKE 'leon__';
Coalesce
การจัดการค่า NUll ด้วย COALESCE
Function เป็นการทำความสะอาดข้อมูลด้วย SQL มันจะไปแทนที่ค่า Null ที่หาเจอใน Column ที่ต้องการค้นหา
SELECT
company,
COALESCE(company, 'End Customer') AS 'company Clean'
FROM customers;
เรายังสามารถใส่เงื่อนไขเพื่อแปลงค่าลงไปได้อีกเพื่อจัดกลุ่มข้อมูล
SELECT
company,
COALESCE(company, 'End Customer') AS 'company Clean',
CASE WHEN company is null THEN 'End Customer'
ELSE ' Corporate'
END as ' segment'
FROM customers;
Join Data with where clause
วิธี Join ค่าจากหลายตารางโดยที่ไม่ได้ใช้คำสั่ง Join แต่ผลลัพท์เทียบเท่ากับการใช้คำสั่ง Join
SELECT * FROM artists, albums
WHERE artists.artistid = albums.artistid;
แต่จะเห็น column ที่ไม่ต้องการอยู่ เราจะต้องเลือก column มาแสดง
SELECT
artists.artistid,
artists.name AS artists_name,
albums.title AS albums_name
FROM artists, albums
WHERE artists.artistid = albums.artistid -- pk == fk
AND artists.ArtistId in (7, 120,130);
ถ้าเราอยากจะ join เพิ่มด้วย where เช่น
SELECT
artists.artistid,
artists.name AS artists_name,
albums.title AS albums_name,
tracks.name AS song_name
FROM artists, albums, tracks
WHERE artists.artistid = albums.artistid
AND albums.albumid = tracks.albumid
AND artists.ArtistId in (7, 120,130);
Aggregate Function
Aggregate Functions คือ ฟังก์ชันสถิติเบื้องต้นไว้สรุปผลข้อมูล ฟังก์ชันที่เราเป็นประจำใน standard SQL มีอยู่ 5 function คือ
- COUNT
- AVG
- SUM
- MIN
- MAX
Aggregate function ไม่สนใจค่า NULL ใน Column นั้นๆ
SELECT COUNT(company) FROM customers;
ถ้าข้อมูลใน customers table มีทั้งหมด 59 แถว แต่เรา COUNT(company) จะได้แค่ 10 แถว แสดงว่า company มีค่า NULL ทั้งหมด 49 แถว
สมมติว่าเราอยากรู้เพลงไหนมีความยาวเพลงเท่าไร
SELECT
ROUND(AVG(milliseconds),2) average,
SUM(milliseconds) sum ,
MIN(milliseconds) min ,
MAX(milliseconds) max ,
COUNT(milliseconds) count_mill
FROM tracks;
เราสามารถตั้งชื่อ column ได้โดยไม่ต้องใส่ AS
ก็ได้
Count Distinct
การนับแบบไม่ซ้ำกันเลย
SELECT DISTINCT country from customers;
หากอยากจะนับจำนวนทั้งหมด
SELECT COUNT(DISTINCT country) from customers;
เราสามารถที่ Count จำนวน row ด้วย * ได้
SELECT COUNT(DISTINCT country), COUNT(*) from customers;
Group by
เราใช้ group by กับ aggregate function เพื่อสรุปข้อมูลแบ่งตามกลุ่มที่เราต้องการ ตัวอย่างเช่น กาารนับจำนวนลูกค้าในแต่ละประเทศ
SELECT country, COUNT(*) FROM customer GROUP BY country;
Column ไหนที่อยู่ใน Group By ให้เราเขียน Column นั้นใน SELECT ด้วย
SELECT country, count(*) AS count_country from customers
group by country;
สมมติว่า เราอยากจะรู้ว่าประเภทของเพลงมีอยู่กี่เพลง โดยจะต้องเชื่อมกับ table อื่นๆ
SELECT genres.name, count(*) AS count_song from genres, tracks
WHERE genres.genreid = tracks.genreid
group by genres.name;
having
Having เขียนเหมือนกับ where clause
แต่เราใช้ having ในการกรองข้อมูลที่ผ่านการ group by มาแล้วเท่านั้น
สรุปก็คือ การกรองกลุ่มที่เราต้องการอีกทีนั้นเอง
SELECT
genres.name,
count(*) AS count_song
from genres, tracks
WHERE genres.genreid = tracks.genreid
group by genres.name
HAVING count(*) >= 100;
order by and limit
การ sort data เรียงจากน้อยไปมาก (asc) หรือ มากไปน้อย (desc)
SELECT genres.name, count(*) from genres, tracks
WHERE genres.genreid = tracks.genreid
group by genres.name
order by count(*) desc;
ถ้าอยากจะดูจำนวน 5 อันดับแรก ก็ใส่ limit
SELECT genres.name, count(*) from genres, tracks
WHERE genres.genreid = tracks.genreid
group by genres.name
order by count(*) LIMIT 5;
หรือจะใช้ join แทน where ก็ได้
SELECT genres.name, count(*) from genres
join tracks
on genres.genreid = tracks.genreid
group by genres.name
order by count(*) LIMIT 5;
คอร์สนี้ดีมากกกก (ไก่ ล้านตัว) ใครอ่านจบ แนะนำว่าให้ไปสมัครเรียน ติดตามได้ที่ link ด้านล่างนี้เลย
Course Online DATA ROCKIE Bootcamp