Data Sci Boot Camp Batch#11 : SQL

สำหรับวันนี้ช่วงแรกมี Recap Googlesheet ผมมี Update ไว้ตามใน Blog ตอนของ Week ที่แล้วครับ ลองอ่านกันได้ครับ

มาต่อกันวันนี้ครับ จะเป็นการสอน SQL ตัวผมเองใช้ SQL มานานมากเหมือนกันครับ จนบางทีอาจจะหลงลืมได้ Blog ตอนนี้เลยมา Recap SQL จดมาดังนี้

SQL คือ อะไร

ภาษา SQL ( Structured Query Language) ที่ใช้เราดึงข้อมูลออกให้เราเห็นได้ โดยข้อมูลต้องจัดเก็บในรูปแบบตาราง และมีจัดความสัมพันธ์

โครงสร้างข้อมูลมันเป็นยังไง ?

นึกภาพตารางใน Excel / Googlesheet มันจะมีข้อมูลเก็บตาม Domain ของมัน มองความรู้ในแต่ละหมวด แต่ละหมวด Primary Key ที่บอกตำแหน่งของข้อมูลแถวนั้นๆ

ถ้าย้อนไปในสมัยที่ผมเรียน มันเรียกว่า ER-Diagram นั้นเอง แต่ทำงานจริงหายากเหลือเกิน 555 เห็นแอดทอยพูดก็สบายใจระดับนึงแล้ว ไม่มีเหมือนกัน 555 โดยส่วนหลักๆ ของ ER

• กล่องส้ม Table หรือ Excel / Googlesheet
• ในกล่องส้มๆ รูปกุญแจ 🔑 Primary Key ที่บอกตำแหน่งของข้อมูลแถวนั้นๆ
• แต่ละTable มันจะมีเส้นเชื่อมโยงไป จะเป็น Foreign Key แสดงความสัมพันธของตารางแต่ละแบบ
  - One to One
  - One to Many - artists ↔ albums
  - Many to Many มันจะไม่ได้แสดงตรงๆ ดูจาก playlists ↔ tracks แต่ตอน Design จริงมันจะได้ 3 Table นะ
  playlists ↔ playlist_track ↔ tracks เพราะ
  💡playlists มีได้หลาย tracks
  💡tracks ไปอยู่ได้หลาย playlists
• ข้อมูลที่เก็บลงในแต่ละมีชนิดของมัน (Data Type)
  - String
  - Date
  - Int Decimal
  Database แต่ละค่ายมี Data Type แตกต่างกันไป

คนที่เขียน SQL เก่ง คนที่เข้าใจ Business / Data Flow อย่างแรกต้องมาดู ER-Diagram วาดๆเอา ของจริงไม่มี หรือ Table ที่ใช้งานบ่อยๆ

เราเข้าไปที่ Database ได้ยังไง ?

ปกติแล้วการทำงานของ Database

• Server (ในที่นี้เป็น SQLLite) แต่จริงๆมีหลายตัว อย่างที่ผมใช้ประจำ Microsoft SQL Server / IBM DB2 และ PostgresQL
• Client ตัวที่ให้คนทั่วไปอย่างเราร้องขอข้อมูลจาก Server ตาม Business ต้องการ โดยภาษาที่ใช้ SQL

Tools Client มีหลายตัวเลย

• Beekeeper Studio ตัวนี้แอดทอยแนะนำ ผมเพิ่งรู้จักเหมือนกัน
• ตัวเสียเงินมี jetbrains datagrip

ตัวที่ผมใช้ประจำ DBeaver ชอบมากก >> [DB] DBeaver – เครื่องมือที่ช่วยให้การจัดการ Database เป็นเรื่องง่ายๆ

ลองดึงข้อมูลด้วย SQL

SQL มีหลายส่วนเลย แต่อันนี้แอดทอยจะเน้นส่วน SELECT โดยมีจะมีโครงสร้าง ประมาณนี้

SELECT <COLUMN ที่ต้องการ หรือ เอาหมด *> / ตรงนี้มี Function แบบใน Excel ช่วย เช่น Sum()/Average()
FROM <TABLE ที่สนใจ>
  JOIN <ถ้าต้องการข้อมูลจากอีกแหล่ง เอามาเชื่อมกัน ตาม Primary Key> มีหลายแบบ 
    - INNER JOIN
    - LEFT JOIN
    - RIGHT JOIN
    - FULL OUTER JOIN
    - CROSS JOIN 
WHERE <กรองข้อมูล ตามเงื่อนไขทาง Business>
GROUP BY <จัดกลุ่มข้อมูล> 
HAVING <กรองข้อมูลที่ได้จาก Group By>
ORDER BY <เรียงข้อมูล ASC ตามตัวอักษร หรือ DESC จากหลังมาหน้า>
LIMIT <กำหนดการดึงข้อมูล ว่าจะเอามาเท่าไหร่่

• Simple SELECT

SELECT * 
FROM customers 
WHERE country='USA';

• ถ้าจะกรองข้อมูลใช้ where แต่เราต้องมีเข้าใจคำถามทางธุรกิจ จะได้เขียนได้ เช่น อันนี้จะเอาข้อมูลเฉพาะคนที่อยู่ใน USA / France
  - ตรง cus เป็น alias ชื่อเล่น ให้เรียกใช้ง่ายๆ

SELECT cus.customerid as id
      , cus.firstname as name
      , cus.country as country
FROM customers cus 
WHERE cus.country IN ('USA', 'France');

-- หรือ ถ้าเอาตรงกันข้าม ใช้ NOT IN
SELECT cus.customerid as id
      , cus.firstname as name
      , cus.country as country
FROM customers cus 
WHERE cus.country NOT IN ('USA', 'France');

• หรือ ถ้าจะทำ Pattern Matching เช่น ขึ้นต้นด้วยตัว U ใช้ LIKE มันทมีหลายตัว แต่ผมเอาจากประสบการณ์ทำงานผมและกัน
  - % บอกว่าตัวหน้า หรือ หลังเป็นอะไรก็ได้
  - _ บอกว่าตัวนี้เป็นอะไรก็ได้

SELECT cus.customerid as id
      , cus.firstname as name
      , cus.country as country
FROM customers cus 
WHERE cus.country LIKE 'U%';      --ขึ้นต้นด้วยตัว U

SELECT cus.customerid as id
      , cus.firstname as name
      , cus.country as country
FROM customers cus 
WHERE cus.country LIKE 'U_A';     --ขึ้นต้นด้วย U ตัวถัดมาอะไรก็ได้ และลงท้ายด้วย A 

--LIKE + CREATE NEW COLUMNM
SELECT cus.customerid          as id
      , cus.firstname          as name
      , cus.country            as country
      , cus.email
      , 'ข้อมูลลูกค้า'            as ข้อมูลลูกค้า          --NEW COLUMNM เพิ่งรู้ว่าทำได้ 555
      , 'Email: ' || cus.email as mailfootter       --NEW COLUMNM
      , lower(firstname) || '@gmail.com' as newmail --NEW COLUMNM
FROM customers cus 
WHERE cus.email LIKE '%@yahoo.%'; --อันนี้บอกว่า ต้องเป็น Email @yahoo.

จริงๆ DB ค่ายอื่นๆ อย่าง MSSQL หรือ PostgreSQL เค้าจะเพิ่ม Feature ของเฉพาะของตัวเอง เช่น [] หรือ Regular Expression เป็นต้น

• ลอง Where Date

SELECT * FROM invoices inv
WHERE inv.InvoiceDate <= '2009-02-01'

• ลอง
  - สร้าง Column ใหม่ first_three_letter / คิด VAT มีใช้ Function Round
  - และลอง Where เป็น Between ช่วงเป็นเดือน //เอาจริงๆ ผมไม่ได้ใช้นานแล้วเหมือนกัน ใช้แต่ COLUMN > xx AND COLUMN <= yyy

SELECT inv.InvoiceDate,
       inv.BillingAddress,
       inv.BillingCity,
       SUBSTR(inv.BillingCity, 1,3) AS first_three_letter, --snake_case อีกแบบ camelCase
       inv.Total,
       ROUND(inv.total * 0.07,2) AS VAT
FROM invoices inv
WHERE inv.InvoiceDate BETWEEN '2009-01-01' AND '2009-01-31'

Recap ใน Where Operator น่าจะสามกลุ่มตามนี้

ถ้ามันใช้บ่อย ทำเป็น Virtual Table

Virtual Table เอาจริงๆ ผมไม่ค่อยคุ้นเลย ผมคุ้นกับคำว่า View มากกว่า โดยการสร้างใช้คำสั่งประมาณนี้

CREATE VIEW invoice_jan2009 AS
SELECT inv.InvoiceDate,
       inv.BillingAddress,
       inv.BillingCity,
       SUBSTR(inv.BillingCity, 1,3) AS first_three_letter, --snake_case อีกแบบ camelCase
       inv.Total,
       ROUND(inv.total * 0.07,2) AS VAT
FROM invoices inv
WHERE inv.InvoiceDate BETWEEN '2009-01-01' AND '2009-01-31'

DROP VIEW invoice_jan2009

UNION ALL / UNION

• UNION ALL - ต่อ Row โดยต้องมี Column เท่ากัน และ Data Type เหมือนกัน

SELECT cus.FirstName
     , cus.LastName
     , cus.Country
FROM customers cus
WHERE cus.Country = 'Brazil'
UNION ALL 
SELECT cus.FirstName
     , cus.LastName
     , cus.Country
FROM customers cus
WHERE cus.Country = 'Canada'
UNION ALL 
SELECT cus.FirstName
     , cus.LastName
     , cus.Country
FROM customers cus
WHERE cus.Country = 'Italy'

• UNION - UNION ALL + Remove Duplicate

SELECT cus.FirstName
     , cus.LastName
     , cus.Country
FROM customers cus
WHERE cus.Country = 'Brazil'
UNION 
SELECT cus.FirstName
     , cus.LastName
     , cus.Country
FROM customers cus
WHERE cus.Country = 'Canada' and cus.City = 'Toronto'
UNION 
SELECT cus.FirstName
     , cus.LastName
     , cus.Country
FROM customers cus
WHERE cus.Country = 'Italy'

JOIN เชื่อมโยงข้อมูล

สำหรับ JOIN มีหลายแบบ ตามรูปเลยครับ

จริงๆ มันเป็นการจับ Key ของ Table มาเชื่อมความสัมพันธ์กับ อารมณ์แบบ VLOOKUP ใน Excel โดย Join จะมีหลาย Type สรุปสั้นๆ ตามนี่

• INNER JOIN หรือ JOIN ฝั่งซ้าย และขวาต้องมีข้อมูล
• LEFT JOIN เอาทางซ้ายตั้ง หาที่ตรงกันมาเติม
• RIGHT JOIN เอาฝั่งขวาตั้ง หาที่ตรงกันมาเติม
• FULL JOIN เอาทั้งสองฝั่งมาเทรวม
• CROSS JOIN หรือ SQL CARTESIAN JOIN - ใช้เมื่อไม่มี Key ที่สามารถเขื่อมโยงตารางทั้งสองได้ เอามา Cross ทุก Possibility เลย

อันนี้ตัวอย่าง INNER JOIN

SELECT ar.ArtistId
     , ar.Name   as artists_name
     , al.Title
     , tr.Name   as track_name
     , ge.Name   as genres_name
FROM artists ar
  JOIN albums al 
     ON ar.ArtistId = al.ArtistId
  JOIN tracks tr 
     ON al.AlbumId = tr.AlbumId
  JOIN genres ge 
     ON tr.GenreId = ge.GenreId
WHERE ge.name IN ('Jazz', 'Rock', 'Pop')

ถ้าอยากรู้เรื่อง JOIN มา Blog นี้ได้ครับ

CASE WHEN

เอาไว้เขียนเงื่อนไข จัดการกับข้อมูล เช่น การจัด Segment

• COALESCE เอาไว้ Handle ค่ากรณีที่ Null ตัวอย่างบอก no data แต่สามารถเอาใช้แปลงค่า เพื่อ Join ก็ได้นะ
• CASE WHEN เอาไว้จัดการเงื่อนไขที่ซับซ้อน แบบเดียวกับ IF ใน Programing / Excek

SELECT cus.FirstName
     , COALESCE(cus.Company, 'no data') as COALESCE
     , CASE 
         WHEN cus.Company IS NULL THEN 'B2C'
         WHEN cus.Company IS NOT NULL THEN 'B2B'
         ELSE 'Other' 
       END AS SEGMENT
FROM customers cus

Group By & Aggregate

• Group by ใช้จัดกลุ่มข้อมูล ข้อกำหนดเงื่อนไขที่ Group ต้องเอามา SELECT ด้วย
• Aggregate เอามา เรียกใช้ Function ต่างๆ เช่น พวก COUNT() / SUM() / MIN() / MAX() / AVG() เป็นต้น

SELECT ge.Name         as genres_name
     , COUNT(tr.name)  as num_of_song
FROM artists ar
  JOIN albums al 
    ON ar.ArtistId = al.ArtistId
  JOIN tracks tr 
    ON al.AlbumId = tr.AlbumId
  JOIN genres ge 
    ON tr.GenreId = ge.GenreId
WHERE ge.name IN ('Jazz', 'Rock', 'Pop')
GROUP BY ge.Name
ORDER BY num_of_song DESC

นอกจากนี้พวก Count เอามาตรวจสอบ Data ได้ด้วยนะ อันนี้เปิดมุมมองเหมือนกัน ปกติจะเขียนท่าแปลก และ Sum มาตรวจ ท่า Count ง่ายดี สาย Data ตามงานแบบนี้สินะ เราสาย DEV 55

SELECT COUNT(*) AS n_customers
     , COUNT(cus.FirstName) as n_FirstName
     , COUNT(cus.Email) as n_email
     , COUNT(cus.Company) as n_company
FROM customers cus

ลอง Aggregate อีกแบบ

WHERE VS HAVING

• Where ทำก่อน อย่างแรก ลดข้อมูลที่เกี่ยวข้อง
• Having เลือกข้อมูลหลัง Group

SELECT cus.COUNTRY
     , COUNT(*) AS N_CUSTOMERS
FROM customers cus
WHERE cus.Country <> 'USA'     --1. ทำก่อน ตัด USA ออกไป 
GROUP BY cus.Country           --2. จัดกลุ่ม    
HAVING COUNT(*) >= 5           --3. นับในกลุ่ม

ลำดับการทำงานของ SQL ตาม info graphic นี้เลย

CTE COMMON TABLE EXPRESSION

• SUB QUERY เป็นงานส่วนย่อยๆ เอาให้ทำทดบนพื้นที่ของ DB Server
• CTE เหมือน SUB QUERY แหละ แต่จะมันจะดีกว่ามากๆ ถ้าใน Query เดียวกันเขียน Sub Query เดิมๆซ้ำกัน 10 รอบ ทำเป็นตัวแปร ให้ใช้งานจะดีกว่า

-- 1. sub query concept
SELECT firstname, email, country
FROM (
  SELECT * FROM customers
  )
WHERE country = 'Brazil'

--2. CTE
WITH INNER_QUERY AS (SELECT * FROM customers)
SELECT firstname, email, country
FROM INNER_QUERY
WHERE country = 'Brazil'

แต่ถ้า CTE หลายตัวมีตัวแปรซ้ำ อาจจะแนะนำไปใช้ Function / Store Proc ลองอ่านเพิ่มเติมได้ Blog เก่าเมื่อปี 2017 มีแนวทาง Clean SQL แนะนำด้วยนะครับ

อยากรู้เรื่อง SQL / Programming ลองมาตาม Blog Dev ที่ทำ APP + เขียน SQL ได้ครับ

มีหลายหมวดเลย

Reference

• SQLite Tutorial - An Easy Way to Master SQLite Fast
• 5 Infographics to Understand SQL Joins visually
• The Essential Guide to SQL’s Execution Order