บันทึกการใช้งาน SQL 102 สำหรับนักวิทยาศาสตร์ข้อมูล Data Science - sprint 02

Image placeholder
แวะมาทักทายกันได้


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

แวะมาทักทายกันได้
donate

Categories: Tutorial Tags: #Data Science , 386