บันทึกการใช้งาน SQL 101 สำหรับนักวิทยาศาสตร์ข้อมูล Data Science - sprint 02
SQL - 101
image by datarockie bootcamp
file database sample
ER Diagram ย่อมาจาก Entity-Relationship Diagram เป็นเหมือนแผนที่ ที่บอกเราว่าข้อมูลใน Database มีอะไรบ้าง และ ความสัมพันธ์ของแต่ละ table เป็นอย่างไร
รูปแบบความสัมพันธ์ที่เราใช้ใน database มีสามแบบหลักๆคือ
- One to One
- One to Many
- Many to Many
SQL For Data analyst - 101
create new column
SELECT firstname, lastname,
firstname || ' ' || lastname AS Fullname,
LOWER(firstname) || '@company.com' AS email
from customers LIMIT 10;
transform data
SELECT
name,
round(milliseconds / 60000.0,2) AS minute,
round(bytes / (1024*1024.0),2) AS mb
FROM tracks;
ROUND() เป็นฟังก์ชันสำหรับจัดการเลขทศนิยม
CASE WHEN
CASE WHEN ใช้ในการเขียนเงื่อนไข สร้างคอลัมน์ใหม่ เราจะเขียน CASE WHEN ใน SELECT clause เราสามารถเขียนได้มากกว่าหนึ่งเงื่อนไข ได้ผลลัพท์เหมือนกกับฟังก์ชัน =IFS() ของ Google Sheets / EXCEL ตัวอย่างการเขียน CASE WHEN เพื่อสร้างคอลัมน์ region
CASE WHEN เอามาช่วยในเรื่องอะไร ยกตัวอย่าง ข้อมูล Table ที่มีข้อมูลเป็น Null แล้วเราไม่อยากได้ Null มาแสดงผลด้วย เราจะใช้ Case When มาช่วยจัดการข้อมูลเหล่านั้น
CASE
เป็นตัวเปิดว่าจะมีเงื่อนไข แล้วใส่ WHEN
เข้าไปใน block ของ CASE
ส่วน block WHEN
จะเป็นเงื่อนไข แล้วตามด้วย THEN
ถ้าเงื่อนไขใน Block WHEN
เป็นจริง แต่ถ้าเป็นเท็จ ให้แสดงใน Block ELSE
แล้วปิดด้วย END
ดัง code ด้านล่าง (ส่วน AS
เป็นการตั้งชื่อให้ Column)
SELECT
company ,
CASE
when company is not NULL THEN 'Coperate'
else 'End Customer'
END AS segment
FROM
customers;
DATE TIME
สมมติว่าเราอยากจะนำ format ข้อมูล Date Time ที่อยู่ในรูป 2009-01-01 00:00:00 ใน Column Datetime แยกออกมาเป็น column ใหม่เป็น Column YEAR, MONTH, DAY ได้โดยการใช้ feature engineering
extract information , feature engineer : การสร้าง column ใหม่ที่ base on ข้อมูลที่อยู่ใน column เก่า
SELECT
invoicedate ,
strftime('%Y', invoicedate) AS year,
strftime('%m', invoicedate) AS month,
strftime('%d', invoicedate) AS day,
strftime('%Y-%m', invoicedate) AS monthid
from invoices
WHERE year = '2010';
ถ้าสังเกตในส่วนของ where ข้อมูล year จะต้องใส่ข้อมูล filter เป็น single qoute เพราะ data type มองว่าเป็น TEXT
กรณีถ้าเราไม่ต้องการจะใส่เราสามารถเปลี่ยน Data Type ได้โดยใช้ฟังก์ชัน CAST()
SELECT
invoicedate ,
cast(strftime('%Y', invoicedate) AS INT) AS year,
strftime('%m', invoicedate) AS month,
strftime('%d', invoicedate) AS day,
strftime('%Y-%m', invoicedate) AS monthid
from invoices
WHERE year = 2010;
Select Data From Multiple Tables
ถ้าเราอยากจะดึงข้อมูลหลายๆ table พร้อมๆกัน จะต้อง join 2 table
table 1 artist
SELECT * from artists;
table 2 album
SELECT * from albums;
เมื่อต้องการ join กันใช้ code นี้ได้เลย
SELECT * from artists
join albums on artists.artistid = albums.artistid
WHERE artists.artistid = 50;
INNER JOIN VS. LEFT JOIN
image by datarockie bootcamp
โดยปกติเวลาที่ Join เราจะใช้ตัวย่อด้วย เพื่อช่วยระบุชื่อให้รู้ว่าเป็นชื่อของ Table ไหน
image by datarockie bootcamp
inner join จะ return เฉพาะ ที่มัน over lap กัน
left join จะ return เฉพาะ เอา table ซ้ายมือเป็นตัวตั้ง ถ้ามี key ที่ map กันได้ก็จะดึงออกมา
Inner join
image by datarockie bootcamp
จะเห็นว่า table 2 table ที่มี 01,02 เหมือนกัน จึงได้ผลลัพท์ 2 table รวมกัน
left join
image by datarockie bootcamp
ส่วน left join จะตั้งต้นด้วย table ทางซ้ายมือ แล้วไปดูที่ table ขวา ถ้า match กัน ก็จะดึงไปแปะ ถ้าค่าไหนไม่มีก็จะเป็นค่า NULL
SELECT
A.artistid,
A.name,
B.title
from
artists AS A
inner join albums AS B
on A.artistid = B.artistid;
select
A.artistid,
A.name,
B.title
FROM
artists AS A
LEFT JOIN albums AS B
ON A.ArtistId = B.artistid;
คราวนี้มาดูการ join 3 table เข้าด้วยกัน
select * from artists
join albums
on artists.artistid = albums.artistid
join tracks
on albums.albumid = tracks.albumid;
แต่ code ด้านบนจะได้ column ที่ไม่ต้องการออกมาด้วย แต่ ถ้าเราต้องการ column ที่ต้องการเท่านั้นให้แก้ตรง select พร้อมตั้งชื่อให้กับ table ด้วยเป็นชื่อย่อ
select
art.ArtistId,
art.name,
alb.title,
tck.name,
tck.composer
from artists AS art
join albums AS alb
on art.artistid = alb.artistid
join tracks AS tck
on alb.albumid = tck.albumid
WHERE art.name = 'Aerosmith';
RANDOM Function
SELECT Random();
เป็นการประยุกต์สุ่มตัวอย่างด้วย ฟังก์ชัน Random()
คอร์สนี้ดีมากกกก (ไก่ ล้านตัว) ใครอ่านจบ แนะนำว่าให้ไปสมัครเรียน ติดตามได้ที่ link ด้านล่างนี้เลย
Course Online DATA ROCKIE Bootcamp