Ruby's Duckie

สวัสดีจ้าาาา ruby’s duckie คนดีคนเดิม ✨ วันนี้มาฝึกทำ project ต่อ เดียวไม่ได้งาน แงๆ 😅 แต่วันนี้จะมาฝึกใช้ทักษะ SQL เพียวๆ เลย เพื่อวิเคราะห์ข้อมูลง่ายๆ ง่ายนิดเดียว ที่เหลือยากหมด 555+ เอาละ มาเคาะ skill กันหน่อยหลังจากเรียน course online จาก datarockie มา ซึ่งเป็นโรงเรียนที่โหดมากกกกก ไม่ได้นอนกันเลย 555+

Introduction !!

SQL คืออะไร ชื่อเต็มๆคือ Structured Query Languag ไว้สำหรับ จัดการ และ ดึงข้อมูล จาก Relational Database ทำหน้าที่เป็นเสมือนตัวกลางในการพูดคุยกับฐานข้อมูล เช่น ค้นหาข้อมูล เพิ่มข้อมูล แก้ไขข้อมูล หรือลบข้อมูล ออกจากระบบ (ruby search google มาเลย 5555+ เยี่ยม ขอบคุณ google เย๊ ✨)

ข้อมูลที่เอามาฝึกวันนี้ได้มาจาก kaggle database เกี่ยวกับ YouTube Trending Videos of the Day 📺 โดยตัว database จะมีทั้งหมด 4 table ด้วยกัน เพื่อนๆ สามารถโหลดได้เลยที่ youtube trnding video 2020-2026 🔥

หลังจากที่ import file เข้า DB Browser แล้ว ได้ทำการเปลี่ยนชื่อตารางเพื่อให้ง่ายต่อการ query ข้อมูล

category_summay as tb1_category
country_summary as tb_country
trending_videos as tb3_trending
yearly_trends as tb4_ytrands

เริ่มจาก basic ก่อนเลยแล้วกัน ถือว่าเป็นการทบทวนไปด้วยเลย เพราะความจริงก็มีลืมไปแล้วบ้าง จากข้อมูลที่ได้จะเห็นมาที่ทั้ง category, trending country, video trends, year trends และดูจาก database structure แล้วก็เห็นภาพความสัมพันธ์เพื่อใช้ในการ JOIN ตาราง

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

Practics SQL (level 1)

1. จงหาวิดีโอที่วิวสูงสุด 5 อันดับแรก ? อันนี้ง่ายยั๊งง

SELECT title, channel_name, views 
FROM tb3_trending 
ORDER BY views DESC
LIMIT 5;

เราจะ query ข้อมูลมาจะเห็นว่า เราใช้ SELECT ตามด้วย Column name และตามด้วย FROM เป็นตัวที่จะบอกเราว่าไปดึงมาจากตารางไหน ง่ายใช่ไหม 🍌

แปลภาษา SQL ง่ายๆคือ จงไปดึง column titlt channel และ views มาจากตาราง tb3_trending จากนั้นให้ Order By หรือ เรียงตามคอลัมน์ views แบบ Dessending (Z-A) เอามาแค่ 5 แถวพอ จะเห็น top 5 ที่ ได้ยอดวิวสูงที่สุด และ อันดับ 1 คือ Kai Cenat plays minecraft fo the first time

2. จงนับจำนวนวิดีโอติดเทรนด์แยกตามหมวดหมู่ ✨

SELECT category, count(video_id) As total_videos
FROM tb3_trending
GROUP BY category
ORDER BY total_videos DESC;

สังเกตว่าจะมี COUNT() เป็น Aggregate function ที่จะเข้ามาช่วยในการนับ video_id จาก tb3_trending แล้วทุก id จะโดน GROUP BY ด้วย category ซึ่งนอกจาก COUNT แล้วยังมีตัวอื่นๆ

• COUNT(): นับจำนวนแถว (Rows) ทั้งหมด หรือนับเฉพาะแถวที่ข้อมูลไม่เป็นค่าว่าง (NULL)
• SUM(): คำนวณหาผลรวมของข้อมูลที่เป็นตัวเลขทั้งหมด
• AVG(): หาค่าเฉลี่ยเลขคณิตของข้อมูลตัวเลข
• MAX(): ค้นหาค่าที่มากที่สุดในกลุ่มข้อมูล
• MIN(): ค้นหาค่าที่น้อยที่สุดในกลุ่มข้อมูล

ผลลัพธ์ที่ได้จะเห็นว่าหมวดหมู่ Music > Entertainment > Gaming .. etc ตามลำดับเลย เพราะเราได้เรียงมาเเล้ว ถ้าจำได้ เราเรียงด้วย Order by เย๊

3. อยากรู้ว่าวิดีโอติดเทรนด์ช่องไหนที่มีจำนวนวิดีโอติดเทรนด์มากที่สุด 5 อันดับแรก !! มา ลุ๊ยย

เรายังอยู่กับ table 3 เหมือนเดิม แต่รอบนี้เราจะดูว่า channel ไหนนะ ? ที่สามารถติดเทรนได้ใน 5 อันดับแรก คล้ายๆ ข้อ 2 เลย ปรับนิดหน่อย

SELECT channel_name, COUNT(*) AS total_videos
FROM tb3_trending
GROUP BY channel_name
ORDER BY total_videos DESC
LIMIT 5;

SQL จะวิ่งไปดูชื่อช่องที่เหมือนกัน เอามามัดรวมกันเป็นกลุ่ม แล้วใช้ COUNT(*) นับจำนวนแถวในกลุ่มนั้นๆ ออกมาเป็นตัวเลข

4. อยากดูรายชื่อวิดีโอที่มีความยาว ไม่เกิน 60 วินาที (1 นาที) และมียอดวิวมากกว่า 10,00,000 วิว

SELECT title, duration_seconds / 60.0 AS duration_minute, views
FROM tb3_trending
WHERE duration_seconds < 60 AND views > 10000000
ORDER BY views DESC;

เราใช้ Where เข้ามาช่วยในการ filter หลายๆ เงื่อนไข ด้วย AND หรือ OR ซึ่งเป็นตรรกศาสตร์เบื้องต้นนู้นน 🐶 นอกจากกนี้การเอา duration_seconds มาหารด้วย 60.0 เลยจะทำให้เราเห็นภาพชัดว่าเราททำอะไรกับ column นั้น แต่ๆ เดี๊ยวก๊อนน เรามัน generalist ! จะมารู้สูตรเดียวไม่ได้

• เราสามารถใช้ ROUND() เพื่อปัดทศนิยมได้ เช่น ROUND(duration_seconds / 60.0, 1)
• ใช้ฟังก์ชัน FLOOR() หรือ CAST() ไว้ตัดเอาเฉพาะ นาที ไม่เอาเศษวินาที เช่น CAST(duration_seconds / 60 AS INT)
• แยก column นาที กับ วินาที ก็ได้ เผือหัวหน้าอยากเห็น ! เฉียบบบบบ เย๊

SELECT title, 
       (duration_seconds / 60) AS mins,         
       (duration_seconds % 60) AS secs          
FROM tb3_trending;

Practics SQL (level 2)

เดินทางมาสู่ level 2 ละ เรียกว่าเป็นระดับกลางแล้วกัน กะว่าอยากจะลองไปเล่นกับ table อื่นๆ บ้างที่รออยู่ อิอิ 😜 แล้วการที่เราจะเชื่อมๆ หลายๆ table คงไม่พ้น JOIN ! มาเริ่มเลยยย ลุย 🚀

1. จงดึงรายงานวิดีโอติดเทรนด์มาดูหน่อยสิ อยากรู้ว่ามีวิดีโอตัวไหนบ้างที่กระแสแรงเป็นพิเศษ ทำยอดวิวได้สูงทะลุค่าเฉลี่ยมาตรฐานของวิดีโอประเภทเดียวกัน? เห้ยยยยย ยากยัง 🍌

ถ้ายังจำได้ เรามี table ที่ชื่อว่า tb1_category จะมี column (foreign key) ที่มีสามารถเชื่อมกับ primary key ของ table หลักของเราได้

จากโจทย์ให้ดึงวิดีโอที่ติดเทรน เราจะเห็นว่าอยู่ใน tb3_trending ตอนเข้าไปดึง จะสามารถทำได้ดังนี้ tb3_trending.title , tb3_trending.category, tb3_trending.views แปลว่าไปดึงเอา title & category & views ใน tb3 มาซิ่ ! จากนั้นเอาไป INNER JOIN กับ tb1_category เพื่อดูว่ามี title ไหนบ้างใน category เดียวกันที่มีค่าเฉลี่ยสูงกว่าค่ามาตฐานของวิดีโอ tb1)category.avg_views

ที่ไหนมีเงื่อนไหน ที่นั่นมี WHERE 😜

พอรู้ concept คร่าวๆ แล้ว เบื้องต้นเดียวจะเปลี่ยนชื่อตารางก่อน โดย tb3_trending AS t และ tb1_category AS c จะได้อ่านง่ายๆ ไม่รกมาก

SELECT 
    t.title, 
    t.category, 
    t.views AS video_views,        -- ยอดวิวของวิดีโอ
    c.avg_views AS category_average  -- ยอดวิวเฉลี่ยของหมวดหมู่นั้นจาก tb1_category
FROM tb3_trending AS t
JOIN tb1_category AS c 
    ON t.category = c.category
WHERE t.views > c.avg_views         -- views > avg
ORDER BY t.views DESC
LIMIT 10;

ผลลัพธ์ที่ได้จะดึงมาแค่ 10 อันดับแรก อันนี้คือสามารถ plot ใน DB Browser เลยนะ เท่มากกก !

จะเห็นว่ายอดวิวของหมวด Game มียอดคนที่ดูสูงเกินมาตรฐานมากกว่ากลุ่มอื่นๆ ถ้าหากรู้ว่ากี่ % สามารถเพิ่มในส่วนของ ROUND(((t.views – c.avg_views) / c.avg_views) * 100, 2) AS pct_above_average ใน SELECT ได้ ก็คือสูตรหา % ปกติเลย 🚀 ง่ายไหม อิอิ

2. จงลิสต์รายการวิดีโอที่กำลังฮิต แต่ขอคัดมาเฉพาะจากกลุ่มประเทศที่เป็นตลาดใหญ่ (High-volume markets) ที่มีจำนวนวิดีโอติดเทรนด์สะสมเยอะๆ เกิน 500 คลิปขึ้นไปนะ ส่วนประเทศเล็กๆ ที่ข้อมูลน้อยๆ ยังไม่ต้องดึงมา! (เอ้ยสั่งหรอ ไม่ทำๆ หยอกๆ 😜)

🧠 มาดึงสติในการอ่านโจทย์กันอีกรอบ เพื่อวิเคราะห์ว่าโจทย์ต้องการอะไร วัตถุดิบอะไรบ้างที่เราต้องเอามาเพื่อให้ได้ตอบคำถามข้อนี้ มา 🙌🏻

จงไปเอาเหมือนเดิมเลย คล้ายๆ โจทย์ข้อแรก คือ ดึง t.title & tb2_country ในคำถามมี keyword 1 อย่าง 🔥 : จำนวน videos ติด trend สะสมเยอะๆ > 500 videos และ กลุ่มตลาดใหญ่ นี่แหละๆ

SELECT 
    t.title,
    t.trending_country,
    co.total_videos AS country_total_videos,
	t.engagement_score AS engagement
FROM tb3_trending AS t
JOIN tb2_country AS co
    ON t.trending_country = co.trending_country
WHERE co.total_videos > 500 AND t.engagement_score > 25
ORDER BY t.engagement_score  DESC;

แอบ filter ดู engagement > 20 ด้วยเพื่อเจาะดูเลยว่า Large market & more engagement คือประเทศไหน

3. ไปค้นหาหน่อยว่า มีคลิปไหนบ้างของปี 2024 ที่ มีคนดูชอบมากเป็นพิเศษจนยอดกดไลก์พุ่งสูงเกินค่าเฉลี่ยยอดไลก์รวมของทั้งปี 2024 อยากได้รายชื่อคลิป TOP 5 พร้อมยอดไลก์จริงและค่าเฉลี่ยปีมาเทียบกัน 🤯 อันนี้ level 2 จริ๊งมั้ยยย

เห็นอะไรบางอย่างไหม ? อยากได้รายชื่อคลิป พร้อมยอดไลก์จริงและค่าเฉลี่ยปี 2024 แปลว่าเราต้องไป JOIN กับ tb4_ytrends เพราะดูจาก schema แล้วมี avg_likes และเชื่อมกันด้วย year พอรู้แล้วเรามาเริ่มกันเลย

SELECT 
    t.title,
    t.likes,
    y.avg_likes,
    y.avg_views,
    t.year
FROM tb3_trending AS t
JOIN tb4_ytrends AS y
    ON t.year = y.year
WHERE t.year = 2024 
  AND t.likes > y.avg_likes
ORDER BY t.likes DESC
LIMIT 5;

เราจะได้รายชื่อรายการ 5 อันดับแรกที่มีคนดูชอบมากเป็นพิเศษจนยอดกดไลก์พุ่งสูงเกินค่าเฉลี่ยยอดไลก์รวมของทั้งปี 2024 ดังนี้

เย้ จบไปแล้วกับ level 2 ทำไปทำมาก็สนุกเหมือนกันนะเนี่ย 55+ มี error บ้างแหละแหม้ มือใหม่ๆ อิอิ อย่ายอมแพ้ที่จะไปต่อ level ถัดไป

ทำสิ่งที่ยาก เพื่อให้วันพรุ่งนี้เป็นสิ่งนี้เป็นสิ่งที่ง่าย แล้วทำเรื่องที่ยากต่อไปอีก เราจะเก่งแบบไม่มีใครมาหยุดได้ (เราจะได้พักตอนไหน 😆) – Les Brown

Practics SQL (level 3)

มาลุยกันต่อใน level 3 กันเลย เดียวจะเอาเป็นระดับ Advance เลย เหมือนมีลูกค้ามานั่งทำงานด้วยเลย เพื่อให้เห็นภาพเหมือนกัน เรามาเริ่มกันเลย เย้ 🥳

1. การจัดอันดับแบบกลุ่ม (Top Performer per Category)

สถานการณ์จริง: หัวหน้าอยากได้ The Best ของแต่ละหมวดหมู่ เพื่อเอาไปโชว์ในสไลด์สรุปรายไตรมาส (จัดไป เด้อ 🥳)

จากโจทย์ จงไปค้นหาชื่อวิดีโอ (title), หมวดหมู่ (category), และยอดวิว (views) ของวิดีโอที่มียอดวิวสูงที่สุด เป็นอันดับ 1 ของแต่ละหมวดหมู่ มาให้ นี่แหละ แปลง่ายๆ เลย ซึ่งเราจะใช้ window function มาช่วยในการจัดอันดับ ซึ่งก็คือ ROW_NUMBER() นั่นเอง เย๊

SELECT 
	title,
	category,
	views,
	ROW_NUMBER() OVER (PARTITION BY category ORDER BY views DESC) AS ranking
FROM tb3_trending;

ROW_NUMBER() จะมาช่วยในการรันลำดับ 1, 2, 3 … ไปเรื่อยๆ (ห้ามซ้ำ แม้ค่าจะเท่ากัน) จากนั้นสังเกตว่าเราทำการแบ่งกลุ่มย่อย ๆ ด้วย PARTITION BY cetegory แล้วเอาไปเรียงจากมากไปน้อยตาม views นั่นเอง แต่ๆ ยังไม่จบ หัวหน้าบอกว่า อันดับ 1 ของแต่ละหมวดหมู่ ใช่ไหมล่ะ ? แต่เราไม่สามารถใช้ WHERE ได้ เพราะ WHERE จะถูก run ก่อน SELECT เสมอ ดังนั้นเราจึงต้องใช้สิ่งที่เรียกว่า Subquery หรือ CTE (Common Table Expression) เข้ามาช่วย วุ้ยย Advance จริงๆ 😱

WITH RankingVideos AS (
SELECT 
	title,
	category,
	views,
	ROW_NUMBER() OVER (PARTITION BY category ORDER BY views DESC) AS ranking
FROM tb3_trending
) SELECT * FROM RankingVideos
WHERE ranking = 1;

สังเกตว่าเราจะใช้ WITH temp_table AS () เข้ามาช่วยในการสร้างตารางชั่วคราวก่อน จากนั้นก็ทำการดึงตารางชั่วคราวอันนั้นแหละดึงเอาเฉพาะอันดับ 1 มา

เป็นไง ข้อแรกก็ยากแล้วนะ 555+ แต่เดียวก็ชินเวลาที่ทำบ่อย ๆ ดังนั้นฝึกกันต่อ ลุย !!

2. วิเคราะห์การเติบโตแบบรายปี (Year-over-Year Growth)

สถานการณ์จริง: ผู้บริหารถามว่า คุณ Ruby ไปเช็คให้หน่อยซิ่ ปีที่ผ่านมา ผลประกอบการเราโตขึ้นหรือตกลงกี่เปอร์เซ็นต์เมื่อเทียบกับปีก่อน? แล้วส่ง raw data มา 🫪📊

แปลง่ายๆ จงไปดึงข้อมูล year, avg_views ของปีนี้ 2026 นี่แหละ แล้วสร้างคอลัมน์ใหม่ที่แสดง avg_views ของปีที่แล้วเพื่อหาเปอร์เซ็นต์การเติบโต ไปดึงได้เลยในตาราง tb4_ytrends

SELECT 
    year,
    avg_views, 
    LAG(avg_views, 1, 0) OVER (ORDER BY year) AS prev_year_avg_views
FROM tb4_ytrends;

เราใช้ LAG มาช่วยในการย้อนเวลาไปดูข้อมูลในแถวก่อนหน้าได้โดยไม่ต้องเขียนคำสั่ง JOIN เลย ใน LAG อ่านว่าให้ใช้ column avg_views แล้วให้ย้อนเวลาไป 1 แถว ถ้าไม่มีให้ใส่ 0 อ่านตรงเลย 555+ อะต่อ จากนั้นก็ ORDER BY year เลย ง่ายๆ แต่รู้ไหม จริงๆแล้วเรายังไม่เสร็จ เรายังเหลือการหา % Growth ส่งไปแบบนี้ โดนฟาดแน่นอน 55+ 🔥

เหมือนเดิมเลย เราใช้ Subquery อีกแล้ววว อิอิ เราเอาจากที่คำนวณจากก่อนหน้ามาทำต่อได้เลย โดยเราจะหา %Growth จาก (ปีนี้ – ปีที่แล้ว) / ปีที่แล้ว) * 100 ง่ายๆ เลย อิอิ 🚀

WITH YearGrowth AS (
    SELECT 
        year,
        avg_views AS current_year_avg_views,
        LAG(avg_views, 1) OVER (ORDER BY year) AS prev_year_avg_views
    FROM tb4_ytrends
)
SELECT 
    year,
    current_year_avg_views,
    prev_year_avg_views,
    -- คำนวณ % การเติบโต: ((ปีนี้ - ปีที่แล้ว) / ปีที่แล้ว) * 100 --
    ROUND(((current_year_avg_views - prev_year_avg_views) / prev_year_avg_views) * 100, 2) AS pct_growth
FROM YearGrowth;

จากผลการวิเคราะห์จะเห็นว่าเมื่อเทียบกับปีที่แล้ว ยอด views ของบริษัทเพิ่มขึ้นสวนทางกับปีที่แล้วเลย แต่ภาพรวมข้อมูลย้อนหลัง 5 ปี (2020-2024) เราพบว่ายอดวิวเฉลี่ยรายปีมีการผันผวนอย่างเห็นได้ชัดเลย อาจจะต้องไปหาดูว่า why ?

3. หา “กลุ่มดาวรุ่ง” ที่ทำผลงานเกินเป้าสม่ำเสมอ (Complex Aggregation)

สถานการณ์จริง: ทีมการตลาดต้องการรู้ว่าใครบ้างที่มีคลิปที่ติดเทรนเยอะๆ ซัก 3 คลิปที่ติดเทรน แถมยังมี engegment เฉลี่ยแล้วมากกว่า 8 แต่ต้องทำคอนเทนต์ได้ปังทั้งในตลาด US และ IN เท่านั้น

แปลง่ายๆ ไปดึง channel_name & trending_country & Engegment_score มาดูซิ่ ใครแน่ที่สุด อย่าลืม แค่ US และ IN เท่านั้นเด้อ

SELECT channel_name
FROM tb3_trending
WHERE trending_country = 'US'
  AND channel_name IN (
      SELECT channel_name
      FROM tb3_trending
      WHERE trending_country = 'IN'
      GROUP BY channel_name
      HAVING COUNT(*) > 3 AND AVG(engagement_score) > 8
  )
GROUP BY channel_name
HAVING COUNT(*) > 3 AND AVG(engagement_score) > 8;

เราใช่ท่ามาตฐานเลยในการ Join US กับ IN 🫪 555+ จริงๆ มันสามารถดึงข้อมูลแยกแล้าเอามา INTESECT กันได้เลย แต่ใน DB browser ที่ทำอยู่ตอนนี้มันไม่รองรับ เลยกลับไปท่าปกติเลยแล้วกัน 55555 เอา มาดูผลลัพธ์กันดีกว่า ว่ามีใครบ้างที่ได้ไปต่อ !

อันนี้แอบง่ายกว่าอันแรกนิดหน่อย แต่ง่ายนิดหน่อยจริงๆ ที่เหลือยากหมด 555555 ไปข้อต่อไปเลยจ้า

🎧 แถมนิดหน่อย ถ้าเกิดเราอยากรู้ channel_name, AVG(engagement) และ trending_country เราจะสามารถปรับได้โดยการไป join ตารางเดิมที่เราทำก่อนหน้า

-- additional
SELECT 
    t.channel_name,
    t.trending_country,
    ROUND(AVG(t.engagement_score), 2) AS avg_engagement
FROM tb3_trending AS t
WHERE t.channel_name IN (
    -- หาช่องที่ผ่านเกณฑ์ทั้งสองตลาด --
    SELECT channel_name
    FROM tb3_trending
    WHERE trending_country IN ('US', 'GB')
    GROUP BY channel_name
    HAVING COUNT(DISTINCT trending_country) = 2 
       AND COUNT(*) > 2 
       AND AVG(engagement_score) > 10
)
AND t.trending_country IN ('US', 'GB')
GROUP BY t.channel_name, t.trending_country
ORDER BY t.channel_name, avg_engagement DESC;

ลอง copy ไปรันเองดูได้เลย ได้ผลลัพธ์ยังไงมาบอกกันด้วยนะ เย๊ 🐶

4. วิเคราะห์พฤติกรรมผู้ใช้ในระยะเวลา (Time-based Trend)

สถานการณ์จริง: ทีมต้องการรู้ว่าช่วงเวลาไหนของสัปดาห์ที่คนดูเข้ามาดูเยอะที่สุด?

ก็คือเราต้องไปดึง avg_views มา แล้วให้แยกตามวัน หรือ day_of_week จะมีจันทร์ถึงอาทิตย์ แล้วลองมาดูว่าวันไหนที่มียอดวิวสูงกว่าค่าเฉลี่ยรวม ก็คือใช้ avg(views) เพื่อหาค่าเฉลี่ยรวมทั้งหมดแล้วค่อยมาหาค่าเฉลี่ยรายวัน

ขั้นตอนแรกให้ดึงค่าเฉลี่ยรายวันออกมาก่อน และสร้าง column ค่าเฉลี่ย overall_avg ขึ้นมา แต่คำถามคือ ช่วงเวลาไหนของสัปดาห์ที่คนดูเข้ามาดูเยอะที่สุด ? นั้นหมายความว่าเราต้องเอาค่าเฉลี่ยของทั้ง 7 วันมาหาค่าเฉลี่ยเพื่อเทียบว่าทั้งสัปดาห์ว่า วันไหนที่มีค่าเฉลี่ยมากกว่าค่าเฉลี่ยทั้งสัปดาห์ (ก็คือหาเส้นค่าเฉลี่ยมาเปรียบเทียบแหละ 🍌)

    SELECT 
        day_of_week, 
        AVG(views) AS avg_views_per_day,
        AVG(AVG(views)) OVER () AS overall_avg
    FROM tb3_trending
    GROUP BY day_of_week

หมายเหตุ: คำสั่ง AVG(AVG(views)) OVER () คือการนำ Window Function มาครอบ Aggregate Function

ดังนั้นเวลทำงานจริงๆ เราจะต้อง run ให้จบในรอบเดียว ด้วยการ subquery อีกที (ทุกการดึง query จาก database มีค่าใช้จ่ายเสมอ ท่องไว้ๆ 🤯)

SELECT 
    day_of_week, 
    ROUND(avg_views_per_day, 2) AS avg_views_day,
   ROUND( overall_avg, 2) AS overall_avg,
    -- คำนวณดูว่าวันนั้นทำผลงานได้ดีกว่าค่าเฉลี่ยแค่ไหน --
    ROUND(((avg_views_per_day - overall_avg) / overall_avg) * 100, 2) AS pct_performance
FROM (
    SELECT 
        day_of_week, 
        AVG(views) AS avg_views_per_day,
        AVG(AVG(views)) OVER () AS overall_avg
    FROM tb3_trending
    GROUP BY day_of_week
)
ORDER BY avg_views_per_day DESC;

สังเกตว่า code ที่เค้าเขียนตะกี้ โดนโยนไปอยู่ตรง FROM แล้ว เปรียบเสมือนไปดึงมาจาก table มาอีกทีนึ่ง

แถมๆ เราสามารถใช้ WITH ในการมาจัดการข้อมูลพวกนี้ได้เหมือนกัน แล้วแต่ความถนัดของแต่ละคนเนอะ แต่ให้คง concept ความ general ไว้ให้มากที่สุด อ่านง่าย แก้ไขง่าย และทำงานถูกต้อง เฉียบบบบบ 🔥🚀

WITH DailyStats AS (
    SELECT 
        day_of_week, 
        AVG(views) AS avg_views_per_day,
        -- คำนวณค่าเฉลี่ยรวมจากค่าเฉลี่ยของแต่ละวันไปพร้อมกันเลย
        AVG(AVG(views)) OVER () AS overall_avg
    FROM tb3_trending
    GROUP BY day_of_week
)
SELECT 
    day_of_week,
    ROUND(avg_views_per_day, 2) AS avg_views_day,
    ROUND(overall_avg, 2) AS overall_avg,
    ROUND(((avg_views_per_day - overall_avg) / overall_avg) * 100, 2) AS pct_performance
FROM DailyStats;

พอเราเอามา plot graph จะเห็นว่า sunday มีคนดูเข้ามาดูเยอะที่สุด เย๊

จบไปอีกข้อ รู้สึกสมองเริ่มบวมขึ้มนิดหน่อย แต่ได้ความเข้าใจมากขั้น run หลายรอบอยู่เหมือนกัน แฮะๆ อ่าน document เยอะๆ สงสัยก็ถาม AI ได้เลย แหล่งความรู้ที่มหาศาลมากกกกกก

🔥 AI ก็ทำได้ ! แต่เราต้องทำได้ ก่อนจะใช้ AI 🤖🌸

5. การจำแนกประเภทวิดีโอ (Data Segmentation)

สถานการณ์จริง: ทีมคอนเทนต์อยากแบ่งวิดีโอเป็นกลุ่มๆ เพื่อทำกลยุทธ์ต่างกัน โดยสร้างคอลัมน์ใหม่ชื่อ video_class โดยกำหนดเงื่อนไข:

• ถ้า views > 5,000,000 ให้เป็น Viral
• ถ้า views > 1,000,000 และ <= 5,000,000 ให้เป็น Popular
• ถ้าต่ำกว่านั้นให้เป็น Standard

หลังจากที่เรารู้แล้วว่าเงื่อนไขที่ทีมคอนเท้นต์อยากได้มีอะไรบ้าง ก็เตรียมใช้ control flow เลย หรือใน SQL ใช้ CASE WHEN condition 1 then return ตามด้วย condition 2, 3, 4 ect ถ้าไม่ตรงเลยจะตามด้วย ELSE false แล้ว END ถือว่าจบ คล้ายๆ if-else เลยละ

SELECT 
    title,
    views,
    CASE 
        WHEN views > 5000000 THEN 'Viral'
        WHEN views > 1000000 THEN 'Popular'
        ELSE 'Standard'
    END AS video_class
FROM tb3_trending
ORDER BY views DESC;

เลือดตาแทบกระเด็น 🥹 แต่คุ้มค่ามาก ทำให้รู้ว่าเรื่องบางเรื่อง ต่อให้ ณ เวลานั้น เราเข้าใจแล้ว แต่ถ้าเราไม่ได้ใช้บ่อยๆ หรือไม่ได้ใช้เลยก็ลืมได้ แต่ไม่ต้องอาย อย่ากลัวที่จะหาคำตอบ แต่ก่อนเราจะหาคำตอบที่ถูกต้อง เราต้องพยายามด้วยตัวเองก่อน 🔑

กุญแจสำคัญในการเรียนรู้ คือการที่เราได้เรียนรู้จากสิ่งที่ผิดพลาด จะทำให้เราแข่งแกร่งขึ้นได้แน่นอน แต่อย่ายอมแพ้เด็ดขาด สู้ๆ ไฟมา

วันนี้ขอบคุณทุกคนมากๆ ที่เข้ามาอ่าน มาเรียนรู้ด้วยกัน ใครที่กำลังหางาน freelace หรือ OPB อยู่ Ruby’s Duckie เป็นกำลังใจให้ทั้งตัวเองและเพื่อนๆ พัฒนาตัวเองต่อไป เพื่อเจอกันเป้าหมายใหม่ ที่คู่ควรกับเรา เย๊ ลุยยย ขอตัวไปนอนก่อน เดียวมาฝึกทำโจทย์อื่นๆ แล้วเจอกันนน

แถมมมมมม มาแถม !! 555+ ทุกคนลองไป run คำสั่งนี้ดูๆ ว่าแต่ละ segment มีจำนวนเท่าไร ใช้ Subquery + Case When เลยยยย วุ้ว

SELECT 
    video_class, 
    COUNT(*) AS total_videos
FROM (
    SELECT 
        CASE 
            WHEN views > 5000000 THEN 'Viral'
            WHEN views > 1000000 THEN 'Popular'
            ELSE 'Standard'
        END AS video_class
    FROM tb3_trending
) AS sub
GROUP BY video_class
ORDER BY total_videos DESC;

บายยยยยย 🔥