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

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



SQL - 103


join with select multiple table advance


select * from artists, albums
WHERE artists.artistid = albums.artistid;

สามารถตั้งชื่อได้

select * from artists A, albums B
WHERE A.artistid = B.artistid;
select 
	A.ArtistId,
    A.name artistsName ,
    B.title albumsName 
from artists A, albums B
WHERE A.artistid = B.artistid AND A.name Like 'C%';


convert where to Inner join

วิธีการ join clause จะเทียบเท่ากับ where clause ที่ join ในหัวข้อที่แล้ว

select 
	A.ArtistId,
    A.name artistsName ,
    B.title albumsName 
from artists A inner JOIN albums B
ON A.artistid = B.artistid 
WHERE A.name Like 'C%';

คราวนี้เราต้องการ join เพิ่มอีก 1 table

select 
	A.ArtistId,
    A.name artistsName ,
    B.title albumsName ,
    C.name trackName
from artists A 
inner JOIN albums B ON A.artistid = B.artistid 
inner join tracks C ON B.AlbumId = C.albumid
WHERE A.name Like 'Aerosmith';


Review Join Types




image by datarockie bootcamp


Inner join


เมื่อค่าที่ต้องการหามีตรงกันทั้ง 2 table ก็จะมาแสดงที่ result เช่น

1,2,5 ที่ตรงกันทั้ง 2 table


image by datarockie bootcamp

SELECT 
	A.Title ,
    B.Name
from albums A
INNER join artists B ON A.ArtistId = B.ArtistId;


Left join


จะมี table ซ้ายเป็นตัวตั้ง แล้วดูว่า table ขวาตรงกันหรือไม่

กรณีที่ table ทางด้านขวา ไม่มีหรือไม่ตรงกับ table ขวา มันจะมีค่าเป็น Null โดยค่าใน table ซ้ายก็ยังดึงมาเหมือนเดิม


image by datarockie bootcamp

SELECT 	
	A.Name ,
	B.Title 
from artists  A
LEFT join albums B ON A.ArtistId = B.ArtistId;


Full join

จะ merge กันทั้ง 2 table เลยโดยที่ไม่สนว่าจะ match หรือไม่ถ้าตรงกันก็จะมี ค่าทั้ง 2 coumn แต่ถ้าข้างไหนไม่มีก็จะเป็นค่า Null


image by datarockie bootcamp

Cross Join (Cartesian)


image by datarockie bootcamp


มันคือการ คูณกันของ 2 table โดยที่ table ซ้ายเป็นตัวตั้งแล้ว คูณเข้าไปที่ table ทางด้านขวาทุก record

ยกตัวอย่างการสร้าง table ด้วย cross join ชุดไพ่ขึ้นมา

CREATE TABLE ranks (rank TEXT);

CREATE TABLE suits (suit TEXT);

INSERT into ranks
VALUES ('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('J'),('Q'),('K'),('A');

INSERT INTO suits
VALUES ('Clubs'),('Diamonds'),('Hearts'),('Spades');
SELECT * from ranks;
SELECT * from suits;

แล้ว เรียก table

SELECT * from ranks , suits;

จะเห็นว่ามันไม่เรียง ก็ใส่ order by เข้าไป

SELECT * from ranks , suits order by 2;

หรือ ได้ค่าเหมือนกัน

SELECT * from ranks CROSS JOIN suits order by 2;


self join



image by datarockie bootcamp

usecase การ join employee

สมมติว่ามีพนักงานอยู่ 4 คนแล้วเรา จะส่ง report หาเจ้านายคนไหนบ้าง ถ้าดูจากตารางก็จะเห็นว่า 2,3,4 ส่งหา 1

ถ้าอยากจะดึงชื่อของพนักงานและชื่อของเจ้านาย เทคนิคนี้จะใช้ self join

create table employee (
  id INT, 
  name TEXT,
  level TEXT,
  manager_id
);

insert into employee VALUES
(1,'David', 'CEO', NULL),
(1,'John', 'SVP', 1),
(1,'Mary', 'VP', 2),
(1,'Adam', 'VP', 2),
(1,'Scott', 'MANAGER', 3),
(1,'Louise', 'MANAGER', 3),
(1,'Kevin', 'MANAGER', 4),
(1,'Takesin', 'MANAGER', 4),
(1,'Joe', 'AM', 6),
(1,'Anna', 'AM', 7);

SELECT * from employee;

คราวนี้จะมาทำ join

SELECT * from employee e1, employee e2
WHERE e1.manager_id = e2.id

แต่จะเห็นว่าจะมีทุก column ที่ join เราจะเลือกมาเฉพาะที่ต้องการ

SELECT 
	e1.name AS staff,
    e1.level as staff_level,
    e2.name as manager,
    e2.level as manager_level
from employee e1, employee e2
WHERE e1.manager_id = e2.id

คราวนี้เราก็จะเห็นว่าแล้ว ใครส่ง report หาใคร แต่เพื่อให้ table เข้าใจง่ายมากขึ้น เราจะเพิ่มไปอีก 1 คอลัมน์

SELECT 
	e1.name AS staff,
    e1.level as staff_level,
    e2.name as manager,
    e2.level as manager_level,
    e1.name || ' report to ' || e2.name as comment
from employee e1, employee e2
WHERE e1.manager_id = e2.id


Intersect & Except

  • intersect คือ จะส่งค่าที่ตรงกันกลับไป คล้ายกับ inner join แต่ต่างกันที่จะไม่ดึงข้อมูลจาก table ทางด้านขวา จะเอาข้อมูลเฉพาะ table ซ้ายมือออกไปเท่านั้น
  • except คือ จะส่งค่าที่อยู่ใน table ซ้ายมือแต่ไม่ได้อยู่ใน table ขวามือ



image by datarockie bootcamp

สมมติว่าเราอยากรู้ว่า หนังสือเล่มไหนมันตรงกันระหว่าง table book_shop กับ table favorite_book บ้าง

select id from book_shop
INTERSECT
SELECT id from favorite_book;


จะไม่สามารถใช้ * ได้


ถ้าอยากรู้ว่ามีหนังสือเล่มไหนที่ไม่ได้อยู่ favorite_book ให้ใช้ except


select id from book_shop
EXCEPT
SELECT id from favorite_book;


Union & Union All

เป็นการรวม table 2 table เข้าด้วยกัน

  • ในกรณีที่ table 2 table มีค่าที่ตรงกัน ถ้าใช้คำว่า UNION มันจะ remove duplicates row ทิ้งไป
  • ในกรณีที่ ต้องการเก็บ row duplicates เอาไว้ให้ใช้คำสั่ง UNION ALL



image by datarockie bootcamp

drop table if exists book_shop;

create table book_shop (
  id INT,
  name TEXT,
  release_year INT
);

insert into book_shop VALUES
(6, 'Business Data Science', 2020),
(7, 'Subliminal', 2019);
  

drop table if exists book_shop_new;

create table book_shop_new (
  id INT,
  name TEXT,
  release_year INT
);

insert into book_shop_new VALUES
(6, 'Business Data Science', 2020),
(7, 'Subliminal', 2019),
(8, 'Good Strategy', 2015);

จะเห็นว่ามีค่าซ้ำแต่ไม่เอามาด้วย

SELECT * from book_shop
union 
select * from book_shop_new

แต่ถ้าลองเก็บ ทุก row

SELECT * from book_shop
union ALL
select * from book_shop_new


Subqueries

เป็นเทคนิคการเขียน select ซ้อน select


image by datarockie bootcamp


เวลามันจะ run select มันจะเริ่มที่ inner query ก่อนแล้วค่อยไล่ run select outer ออกมาเรื่อยๆ

เราสามารถเรียกด้วย order by แบบนี้ได้

select * from tracks order by milliseconds DESC LIMIT 1;

แต่ถ้าเราไม่ต้องการเขียนแบบนี้ ก็เขียนแบบนี้ได้ แค่ค่อนข้างเสียเวลา

SELECT max(milliseconds) from tracks;
SELECT * from tracks WHERE milliseconds = 5286953;

จริงๆเราสามารถ ใส่ select ซ้อนกันได้เลย อยู่ในเงื่อนไข where

SELECT * from tracks WHERE milliseconds = (SELECT max(milliseconds) from tracks);

อีกตัวอย่าง ถ้าเราต้องการ ดูลูกค้าที่อยู่ USA และต้องการจะดึงเฉพาะ ชื่อ นามสกุล และ ประเทศ

SELECT firstname, lastname, country FROM
(SELECT * from customers
WHERE country = 'USA');


คอร์สนี้ดีมากกกก (ไก่ ล้านตัว)  ใครอ่านจบ แนะนำว่าให้ไปสมัครเรียน ติดตามได้ที่ link ด้านล่างนี้เลย 

Course Online DATA ROCKIE Bootcamp

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

Categories: Tutorial Tags: #Data Science , 451