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

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



SQL - 101


image by datarockie bootcamp

file database sample

ER Diagram ย่อมาจาก Entity-Relationship Diagram เป็นเหมือนแผนที่ ที่บอกเราว่าข้อมูลใน Database มีอะไรบ้าง และ ความสัมพันธ์ของแต่ละ table เป็นอย่างไร

รูปแบบความสัมพันธ์ที่เราใช้ใน database มีสามแบบหลักๆคือ

  1. One to One
  2. One to Many
  3. 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

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

Categories: Tutorial Tags: #Data Science , 637