🔥 ปลุกพลัง SQL จาก DataRockie BootCamp12 Sprint SQL 🐤💛

หวัดดีคับทุกคนวันนี้ฟลุคกลับมาเขียนบทความที่ได้แรงบันดาลใจจาก BootCampนอนน้อยแต่นอนนะ! เรียนจนร้องชีวิต ไม่มีสอนเลท มีแต่สอนแถม 555+ สอนเสร็จวันเสาร์ นัดเพิ่มวันอาทิตย์🤣

โดย Stack วันนี้คือ การรวมพลังระหว่าง R กับ SQL เพื่อสร้างสรรค์ผลงานที่มีความหมาย ถ้าพี่ ๆ น้อง ๆ คนไหนอยากมี Portfolio เจ๋ง ๆ แบบนี้ หลังไมค์ฟลุคได้ 555+

ในเนื้อหาวันนี้ ฟลุคจะใช้ Data จาก Chinook Database ซึ่งคำสั่ง SQL มีหน้าที่หลัก ๆ ที่นอยมใช้กันมี 2 อย่างคือ:

  1. Retrieve & Filter: ดึง และ กรองข้อมูล (Rows) ตามเงื่อนไขที่ต้องการ
  2. Summarize & Grouping: สรุปผล และ จัดกลุ่มข้อมูล (Aggregation) เพื่อหาค่ารวม ค่าเฉลี่ย หรือจำนวนนับือจำนวนนับ

SQL Clause ในการดึง Data

โดยจะเจาะลึกคำสั่งพื้นฐานและขั้นสูงเค้าว่ากัน555+ ตามลำดับการประมวลผลของ Database Engine ซึ่งเป็น💛สำคัญของการเขียนคิวรี ที่ Data Analyst ต้องรู้:

  1. FROM & JOIN: คือการเลือก Tableและการรวม Data จากหลายแหล่งมาอยู่ในที่เดียว.
  2. WHERE: คือการ Filter แถว (Rows) ตามเงื่อนไข (ก่อน Grouping).
  3. GROUP BY & AGGREGATE FUNCTION(): คือการ Grouping และการสรุปผลข้อมูลด้วยฟังก์ชันต่างๆ เช่น COUNT(), SUM(), AVG() พูดง่ายๆคือการเปลี่ยนข้อมูลที่เป็นก้อนใหญ่ๆให้เหลือค่าๆเดียว.
  4. HAVING: การ Grouping เหมือน WHERE แค่ HAVING ทำทีหลัง.
  5. SELECT: เลือก Columns.
  6. ORDER BY: การ SORTING .
  7. CASE WHEN: คือ Control Flow (If-Then-Else) ของ SQL ใช้ในการสร้่างคอลัมน์ใหม่.
  8. Common Table Expressions (CTEs): เทคนิคขั้นสูงเค้าบอกกัน555+ เพื่อจัดโครงสร้างคิวรีที่ complex อ่านง่าย.

โดย Sheet นี้ใช้พลังของ R + SQL เพื่อให้สามารถทำเป็น (Portfolio) ที่ทรงพลังถึงแม้ตอนนี้เพื่อนชาว Bootcamp12 จะยังเรียนไม่ถึง Sprint R ฟลุคตั้งชื่อว่าGeneralist's SQL 555+เพราะว่า Skills in combination are more powerful than individual skills เฉียบ

  • โค้ด SQL ที่รันได้จริง (Executable Code): หลังไมค์มาขอ 555+
  • ผลลัพธ์แบบInteractiveและ Dynamic : ผลลัพธ์จะแสดงในรูปแบบตารางที่สามารถ ค้นหา และ จัดเรียง ได้ด้วยแพ็คเกจ DT เด็ดดดดดดด

ลุยกัน เย้!

1. Environment Setup

โดยในส่วนนี้จะเป็นการที่ฟลุคพาไปเชื่อมต่อฐานข้อมูล Chinnook กันก่อนเลยเย้

  • ติดตั้งไลบรารี R ที่จำเป็น (DBI, RSQLite, DT).
  • ดาวน์โหลด และ สร้างการเชื่อมต่อ ไปยังฐานข้อมูลตัวอย่าง Chinook เพื่อให้ R สามารถส่ง คำสั่ง SQL ที่เราเขียนเข้าไปในฐานข้อมูลเพื่อให้มันประมวลผลและดึงข้อมูลกลับมาแสดงใน R ได้
# Install packages if they are not already installed
if (!require(DBI)) install.packages("DBI")
if (!require(RSQLite)) install.packages("RSQLite")
if (!require(DT)) install.packages("DT") # For interactive tables

# Load libraries
library(DBI)
library(RSQLite)
library(DT)

# Download the Chinook database if it doesn't exist
db_file <- "chinook.db"
if (!file.exists(db_file)) {
  download.file("https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip", "chinook.zip", mode = "wb")
  unzip("chinook.zip")
}

# Establish a connection to the database
con <- dbConnect(RSQLite::SQLite(), db_file)

2. JOIN Clause

มาต่อกันที่คำสั่ง JOIN ใช้สำหรับรวมแถวจากหลายตารางเข้าด้วยกัน โดยอ้างอิงจากคอลัมน์ที่มีความสัมพันธ์กัน. คำสั่งคิวรีนี้สร้างภาพรวมที่สมบูรณ์ของเพลง โดยการรวมสี่ตารางเข้าด้วยกัน ได้แก่: (Artists), (Albums), (Tracks), และ (Genres) โดยฟลุคจะเริ่มต้นด้วยคำถามแทร็กเพลง 100 อันดับแรกที่มีรายได้สูงสุดมีอะไรบ้าง และมันจะช่วยให้เราตั้งราคาของประเภทเพลง (Genre) ได้อย่างไร? เศรษฐศาสตร์ +Data 555+”

SELECT
    ar.Name AS ArtistName,
    al.Title as AlbumTitle,
    tr.Name AS TrackName,
    ge.Name AS Genre
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
LIMIT 100;

Result:

datatable(join_result,
          options = list(pageLength = 5, autoWidth = TRUE),
          caption = "Interactive table of joined music data.",
          rownames = FALSE)

โดยจาก result ที่เราได้ทำการ JOIN ตารางข้อมูลการขายและรายละเอียดเพลงเพื่อระบุ 100 แทร็กทำเงินสูงสุด โดยที่เราสามารถจัดเรียงหรือกรองข้อมูลได้ทันทีเพื่อเปรียบเทียบราคาเฉลี่ยระหว่างประเภทเพลง เช่น Rock กับ Pop จะเห็นว่าพอเรารู้ราคาแล้วสิ่งนี้จะช่วยให้เราตัดสินได้ดีขึ้นในเรื่องการตั้งราคา

SQL JOIN Types (ประเภทของการ JOIN)

ความนี้หลังจากเราได้เรียนรู้กันแลัวว่า JOIN เอาไว้ทำอะไร แต่จริงๆแล้ว JOIN นั้นสามารถทำได้หลายแบบโดยฟลุคได้สรุปไว้ดังตารางข้างล่างนี้

JOIN Type คำอธิบายโดยย่อ ข้อมูลที่ถูกคืนค่า (Retained Rows)
INNER JOIN คืนค่าเฉพาะแถวที่มีค่าที่ตรงกันในทั้งสองตาราง เฉพาะแถวที่ตรงกัน (Intersection)
LEFT JOIN คืนค่าทุกแถวจากตารางซ้าย และแถวที่ตรงกันจากตารางขวา ถ้าไม่ตรงกันเป็น NULL แถวทั้งหมดจากซ้าย + แถวที่ตรงกัน
RIGHT JOIN คืนค่าทุกแถวจากตารางขวา และแถวที่ตรงกันจากตารางซ้าย ถ้าไม่ตรงกันเป็น NULL แถวทั้งหมดจากขวา + แถวที่ตรงกัน
FULL JOIN คืนค่าทุกแถวจากทั้งสองตาราง ใส่ NULL ในคอลัมน์ที่ไม่มีคู่ แถวทั้งหมดจากซ้าย Union แถวทั้งหมดจากขวา
SELF JOIN JOIN ตารางกับตัวเอง การตั้งชื่อ (Alias) ขึ้นกับประเภท JOIN ที่ใช้

Note: SQLite รองรับแค่คำสั่ง INNER และ LEFT OUTER โดยตรง ส่วน RIGHT/FULL มักต้องต้องใช้เทคนิคอื่น ๆ แทนเทคนิคลับ555+🤣

JOIN Types with Chinook

คราวนี้เรามาลองมาลอง run JOIN ชนิดต่างกัน

INNER JOIN (Customers × Invoices)

SELECT
  c.CustomerId,
  c.FirstName || ' ' || c.LastName AS CustomerName,
  i.InvoiceId,
  DATE(i.InvoiceDate) AS InvoiceDate
FROM Customers c
INNER JOIN Invoices i ON c.CustomerId = i.CustomerId
ORDER BY i.InvoiceDate
LIMIT 20;
datatable(inner_join_demo,
          options = list(pageLength = 5, autoWidth = TRUE),
          caption = "INNER JOIN: Customers with their invoices (first 20).",
          rownames = FALSE)

LEFT JOIN (Customers ⟕ Invoices)

SELECT
  c.CustomerId,
  c.FirstName || ' ' || c.LastName AS CustomerName,
  i.InvoiceId,
  DATE(i.InvoiceDate) AS InvoiceDate
FROM Customers c
LEFT JOIN Invoices i ON c.CustomerId = i.CustomerId
ORDER BY c.CustomerId, i.InvoiceDate
LIMIT 40;
datatable(left_join_demo,
          options = list(pageLength = 5, autoWidth = TRUE),
          caption = "LEFT JOIN: All customers, invoices as available (first 40 rows).",
          rownames = FALSE)

RIGHT JOIN (Emulated) (Invoices ⟖ Customers)

SELECT
  c.CustomerId,
  c.FirstName || ' ' || c.LastName AS CustomerName,
  i.InvoiceId,
  DATE(i.InvoiceDate) AS InvoiceDate
FROM Invoices i
LEFT JOIN Customers c ON i.CustomerId = c.CustomerId
ORDER BY i.InvoiceDate
LIMIT 40;
datatable(right_join_demo,
          options = list(pageLength = 5, autoWidth = TRUE),
          caption = "RIGHT JOIN emulation: All invoices, customers when matched.",
          rownames = FALSE)

FULL JOIN (Emulated with UNION)

-- Simplified FULL JOIN emulation using LEFT JOIN only
-- This shows all customers with their invoices (when available)
SELECT
  c.CustomerId,
  c.FirstName || ' ' || c.LastName AS CustomerName,
  i.InvoiceId,
  DATE(i.InvoiceDate) AS InvoiceDate
FROM Customers c
LEFT JOIN Invoices i ON c.CustomerId = i.CustomerId
ORDER BY c.CustomerId, i.InvoiceDate
LIMIT 50;
datatable(full_join_demo,
          options = list(pageLength = 5, autoWidth = TRUE),
          caption = "FULL JOIN simplified: All customers with their invoices (first 50 rows).",
          rownames = FALSE)

SELF JOIN (Employees ↔︎ Managers)

SELECT
  e.EmployeeId,
  e.FirstName || ' ' || e.LastName AS Employee,
  m.FirstName || ' ' || m.LastName AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ReportsTo = m.EmployeeId
ORDER BY Manager IS NULL DESC, Manager, Employee
LIMIT 100;
datatable(self_join_demo,
          options = list(pageLength = 10, autoWidth = TRUE),
          caption = "SELF JOIN: Employees and their managers (from Chinook).",
          rownames = FALSE)

3. Aggregate Functions

โดยมาต่อฟังก์ชัน Aggregate Function อย่าง COUNT() นับทั้งหมด COUNT(*) และ นับค่าที่ไม่ซ้ำ COUNT(DISTINCT ColumnName) Key Word ใช้สรุปข้อมูลจากหลายแถวให้เหลือค่าเดียว .

SELECT
    COUNT(*) as TotalCustomers,
    COUNT(Company) as B2BCustomers
FROM Customers;

Result:❓ คำถาม: บริษัทเรามีลูกค้าทั้งหมดกี่คน และมีลูกค้าที่เป็น B2B (มีข้อมูลบริษัท) อยู่กี่ราย?

datatable(count_result, 
          options = list(dom = 't'), ## 't' means table only, no search/paging
          caption = "Summary of total vs. B2B customers.",
          rownames = FALSE)

4. Advanced Aggregation (GROUP BY, HAVING)

ความนี้มาถึงการ Filter GROUP BY และ HAVING เพื่อการวิเคราะห์ที่ทรงพลังและFilter โดยคำสั่งคิวรีนี้จะค้นหาประเทศที่ไม่ใช่ สหรัฐอเมริกา (USA) ที่มีลูกค้า 5 รายขึ้นไป แล้วทำ Sorting

SELECT
    Country,
    COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country <> 'USA'
GROUP BY Country
HAVING COUNT(*) >= 5
ORDER BY NumberOfCustomers DESC;

Result: A ranked list of top customer countries (excluding the USA).

datatable(filtered_agg_result,
          options = list(pageLength = 10),
          caption = "Countries (non-USA) with 5 or more customers.",
          rownames = FALSE)

5. Conditional Logic (CASE WHEN)

มันคือ If clause ใน SQL ใช้แยกลูก B2B และ B2C แค่นั้นเลย

SELECT
    FirstName || ' ' || LastName AS FullName,
    Company,
    Country,
    CASE
        WHEN Company IS NULL THEN 'B2C (Consumer)'
        ELSE 'B2B (Business)'
    END AS Segment
FROM Customers
LIMIT 100;

Result: The table below includes the dynamically generated Segment column. Try searching for “B2B” or “B2C”.

datatable(case_result,
          options = list(pageLength = 5),
          caption = "Customer segmentation using CASE WHEN.",
          rownames = FALSE)

6. Common Table Expression (WITH)

Common Table Expressions (CTEs) คือวิธีที่ทำให้คำสั่งคิวรีที่ complex อ่านและเข้าใจง่าย มีความเป็นระเบียบเหมือนมี ‘โมดูล’ โดย concept คือ ตารางจำลอง ที่ใช้ได้แค่ในคิวรีนั้น ๆ

Comparison: Standard Join vs. CTE

The Hard Way (Complex Join) ไม่ต้องเลือกทางยากนะ555+

โดยในส่วนนี้จะมาแสดงให้ดูละให้ทุกคนลองตั้งคำถามโค้ดอันไหนอ่านหรือง่ายกว่ากัน555+ เขียนคิวรีสองแบบที่resultเหมือนกัน แต่วิธีการเขียนไม่เหมือนกัน

SELECT
    SUM(i.Total) as total_revenue_usa_cust_2009
FROM Customers c
JOIN Invoices i ON c.CustomerId = i.CustomerId
WHERE
    c.Country = 'USA' AND
    STRFTIME('%Y', i.InvoiceDate) = '2009';

The Better Way (with CTE)

อันนี้คือโค้ดที่อ่านง่านกว่าโดยใช้ concept CTE

WITH usa_customers AS (
    SELECT * FROM Customers
    WHERE Country = 'USA'
), 
invoices_y2009 AS (
    SELECT * FROM Invoices
    WHERE STRFTIME('%Y', InvoiceDate) = '2009'
)
SELECT
    SUM(t2.Total) as TotalRevenueUsa2009
FROM usa_customers t1
JOIN invoices_y2009 t2
    ON t1.CustomerId = t2.CustomerId;

Query Result

Both queries produce the same result, but the CTE approach is far more maintainable. แปลresult เดียวกันแต่รักษาละอ่านง่ายกว่าเฉียบบบบบเราจะเลือกอันไหน555+

# Format the result nicely
cte_result$TotalRevenueUsa2009 <- paste0("$", round(cte_result$TotalRevenueUsa2009, 2))

datatable(cte_result,
          options = list(dom = 't'),
          caption = "Total 2009 revenue from USA customers.",
          rownames = FALSE)

สุดท้ายนี้ ขอขอบคุณทุกคนที่ให้ความสนใจกับบทความ SQL Showcase นี้ เย้! ขอบคุณเพื่อนๆ พี่ๆ น้องๆ ทุกคนที่มุ่งมั่นตั้งใจที่เข้ามาอ่านจนจบ อ่านจบแล้วเข้าใจมากขึ้นเลย และที่สำคัญที่สุดคือครอบครัวที่พี่ทอย DataRockie ที่คอยซัพพอร์ตเราเสมอมา ฟลุคหวังว่าทุกคนจะมีความสุขกับการเป็นตัวเองในเวอร์ชันที่ดีขึ้นในทุกๆ วัน (Generalist ก็มา 555!) และหวังว่าการทำความเข้าใจหลักการพื้นฐานของ SQL จะช่วยให้เราสามารถสร้างสรรค์สิ่งใหม่ๆ ได้ไม่สิ้นสุดครับ เย้! กราบบบบบ ปิดท้ายขอบคุณ ฉบับฟลุคใน SQL version ที่เป้าหมายอยากให้เพื่อนๆ น้องๆ ได้เห็น Portfolio ง่ายๆ ที่ทรงพลัง ฟลุครักคน🐤💛