SQL Interview Prep · บทเรียน

การเปลี่ยนแปลงระหว่างช่วงเวลา

คำนวณการเติบโตจากเดือนหนึ่งเทียบกับเดือนก่อนหน้าและการเปลี่ยนแปลงรายวันด้วย LAG

การเปลี่ยนแปลงระหว่างช่วงเวลา เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คำถามที่นักวิเคราะห์ต้องเจอแน่นอน

หากคุณสัมภาษณ์งานตำแหน่งนักวิเคราะห์ข้อมูล คาดว่าจะเจอคำถามว่า “คำนวณการเติบโตเทียบเดือนก่อน” หรือ “การเปลี่ยนแปลงเทียบวันก่อนหน้าเป็นเท่าไร” คำถามลักษณะนี้แทบหลีกเลี่ยงไม่ได้

องค์ประกอบพื้นฐานคือ LAG: ดึงค่าของช่วงเวลาก่อนหน้า แล้วคำนวณผลต่างแบบสัมบูรณ์หรือการเปลี่ยนแปลงเป็นเปอร์เซ็นต์ บทเรียนนี้จะสอนรูปแบบการใช้ LAG เพื่อคำนวณการเปลี่ยนแปลงเทียบช่วงก่อนหน้าที่ผู้สัมภาษณ์ใช้ประเมินคุณ

การเปลี่ยนแปลงแบบสัมบูรณ์ด้วย LAG

รูปแบบที่ง่ายที่สุดคือผลต่างโดยตรงระหว่างช่วงเวลานี้กับช่วงก่อนหน้า ดึงค่าก่อนหน้าด้วย LAG แล้วลบกันภายในคำสั่งเลย

แถวแรกตามลำดับไม่มีแถวก่อนหน้า ดังนั้นการเปลี่ยนแปลงของแถวนี้จึงเป็น NULL ซึ่งถูกต้องและไม่ใช่ข้อผิดพลาด เพราะไม่มีช่วงเวลาก่อนหน้าให้เปรียบเทียบจริง ๆ

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_sales
ORDER BY month;

การเปลี่ยนแปลงเป็นเปอร์เซ็นต์

โดยทั่วไปผู้สัมภาษณ์ต้องการให้แสดงการเติบโตเป็นเปอร์เซ็นต์ สูตรคือ (ค่าปัจจุบัน - ค่าก่อนหน้า) / ค่าก่อนหน้า แล้วคูณด้วย 100 ให้เขียนสูตรนี้โดยตรงด้วย LAG

โปรดระวังลำดับการคำนวณและวงเล็บ วงเล็บที่วางผิดตำแหน่งเป็นข้อผิดพลาดที่พบได้บ่อยเมื่ออยู่ภายใต้ความกดดัน

SELECT
  month,
  revenue,
  100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) AS pct_growth
FROM monthly_sales
ORDER BY month;

หลีกเลี่ยงการหารจำนวนเต็ม

ข้อผิดพลาดที่ผู้สมัครจำนวนมากพลาดคือ หาก revenue เป็นจำนวนเต็ม นิพจน์ (110 - 100) / 100 จะให้ผลเป็น 0 ในฐานข้อมูลที่ใช้การหารจำนวนเต็ม

ให้คูณด้วย 100.0 ซึ่งเป็นค่าคงที่ทศนิยม หรือใช้ CAST แปลงเป็นชนิดข้อมูลทศนิยมก่อน เพื่อบังคับให้คำนวณแบบทศนิยม การพูดถึงเรื่องนี้ล่วงหน้าแสดงว่าคุณใส่ใจในรายละเอียด

SELECT
  month,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / LAG(revenue) OVER (ORDER BY month), 2
  ) AS pct_growth
FROM monthly_sales;

สะอาดขึ้น: คำนวณครั้งเดียวใน CTE

การเรียกใช้ LAG สองครั้ง ทั้งสำหรับตัวเศษและตัวส่วน เป็นการเขียนซ้ำและพิมพ์ผิดได้ง่าย วิธีปรับโครงสร้างที่ใช้กันทั่วไปคือคำนวณค่าก่อนหน้าครั้งเดียวใน CTE แล้วจึงคำนวณทางคณิตศาสตร์ในคำสั่งสอบถามภายนอก

วิธีนี้อ่านเข้าใจได้ดีกว่าในการสัมภาษณ์โดยเขียนบนกระดาน และหลีกเลี่ยงไม่ให้การเรียกใช้ LAG ทั้งสองครั้งให้ผลแตกต่างกัน

WITH t AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev
  FROM monthly_sales
)
SELECT
  month,
  revenue,
  ROUND(100.0 * (revenue - prev) / prev, 2) AS pct_growth
FROM t;

ป้องกันการหารด้วยศูนย์

หากค่าของช่วงเวลาก่อนหน้าอาจเป็น 0 สูตรเปอร์เซ็นต์จะหารด้วยศูนย์และทำให้เกิดข้อผิดพลาด ใช้ NULLIF(prev, 0) เพื่อให้ตัวส่วนกลายเป็น NULL และผลลัพธ์เป็น NULL แทนที่จะเกิดข้อผิดพลาด

การหารแบบป้องกันข้อผิดพลาดเป็นรายละเอียดเล็ก ๆ ที่ผู้สัมภาษณ์สังเกตเห็นในผู้สมัครที่คำนึงถึงระบบใช้งานจริง

SELECT
  month,
  revenue,
  100.0 * (revenue - prev) / NULLIF(prev, 0) AS pct_growth
FROM (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev
  FROM monthly_sales
) s;

การเปลี่ยนแปลงเทียบช่วงก่อนหน้าแยกตามกลุ่ม

คำถามในสถานการณ์จริงส่วนใหญ่มักกำหนดขอบเขต เช่น การเติบโตเทียบเดือนก่อน แยกตามผลิตภัณฑ์ หรือ แยกตามภูมิภาค ให้เพิ่ม PARTITION BY เพื่อให้เปรียบเทียบลำดับข้อมูลของแต่ละกลุ่มกับข้อมูลในกลุ่มเดียวกันเท่านั้น

เดือนแรกสุดของแต่ละกลุ่มจะถูกรีเซ็ตเป็น NULL และจะไม่มีการเปรียบเทียบข้ามขอบเขตของผลิตภัณฑ์

SELECT
  product_id,
  month,
  revenue,
  revenue - LAG(revenue) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS mom_change
FROM product_sales;

การเปรียบเทียบรายปีกับระยะเลื่อน

สำหรับการเปรียบเทียบรายปีจากข้อมูลรายเดือนที่แต่ละแถวห่างกันหนึ่งเดือน คุณต้องเปรียบเทียบกับค่าที่อยู่ ย้อนหลังไป 12 แถว อาร์กิวเมนต์ระยะเลื่อนทำสิ่งนี้ได้โดยตรง: LAG(revenue, 12)

วิธีนี้สมมติว่ามีหนึ่งแถวต่อเดือนและไม่มีเดือนใดหายไป หากอาจมีเดือนที่ขาดหาย คุณควรเชื่อมตามวันที่ที่ระบุไว้อย่างชัดเจนแทน ซึ่งเป็นรายละเอียดเล็กน้อยที่ควรพูดให้ผู้สัมภาษณ์ฟัง

SELECT
  month,
  revenue,
  revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_change
FROM monthly_sales
ORDER BY month;

รวมข้อมูลล่วงหน้าก่อนเปรียบเทียบ

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

การใช้ LAG กับแถวที่ยังไม่ได้รวมจะเปรียบเทียบธุรกรรมแต่ละรายการ ไม่ใช่ยอดรวมรายเดือน ซึ่งเป็นข้อผิดพลาดทางตรรกะที่พบได้บ่อย

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS mth,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT mth, revenue,
       revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
FROM monthly
ORDER BY mth;

การตีความค่า NULL แรก

บางครั้งผู้สัมภาษณ์จะถามว่า “ทำไมการเติบโตของแถวแรกจึงเป็น NULL และยอมรับได้หรือไม่” คำตอบที่ถูกต้องคือไม่มีช่วงเวลาก่อนหน้า ดังนั้นการเปลี่ยนแปลงจึงไม่ได้กำหนดไว้ และ NULL แสดงความหมายนี้ได้อย่างถูกต้อง

หากธุรกิจต้องการให้เป็น 0 แทน คุณอาจใช้ LAG(revenue, 1, 0) หรือ COALESCE แต่ควรใช้ก็ต่อเมื่อสอดคล้องกับความหมายที่ต้องการ

ระบุทิศทางการเติบโต

บางครั้งผู้สัมภาษณ์จะต่อยอดคำถามว่า “ให้ระบุด้วยว่าแต่ละเดือนเติบโต ลดลง หรือคงที่” ให้ครอบการเปรียบเทียบ LAG ไว้ในนิพจน์ CASE

การเปรียบเทียบกับค่าก่อนหน้าจะทำให้ได้คอลัมน์แนวโน้มที่อ่านเข้าใจได้สำหรับมนุษย์ เพิ่มเติมจากผลต่างเชิงตัวเลข และสามารถแสดง NULL หรือป้ายกำกับเริ่มต้นสำหรับช่วงเวลาแรกที่ยังไม่มีค่าให้เปรียบเทียบได้อย่างเหมาะสม

SELECT
  month,
  revenue,
  CASE
    WHEN revenue > LAG(revenue) OVER (ORDER BY month) THEN 'up'
    WHEN revenue < LAG(revenue) OVER (ORDER BY month) THEN 'down'
    ELSE 'flat'
  END AS trend
FROM monthly_sales
ORDER BY month;

ตรวจสอบความเข้าใจ

ค้นหาข้อผิดพลาดที่พบบ่อยในคำสั่งสอบถามการเปลี่ยนแปลงเป็นเปอร์เซ็นต์

สรุปทบทวน

การเปลี่ยนแปลงเทียบช่วงก่อนหน้าคือการใช้ LAG ร่วมกับการคำนวณทางคณิตศาสตร์:

การเปลี่ยนแปลงแบบสัมบูรณ์: value - LAG(value) OVER (ORDER BY period)
การเปลี่ยนแปลงเป็นเปอร์เซ็นต์: 100.0 * (value - prev) / NULLIF(prev, 0)
บังคับให้คำนวณแบบทศนิยม ป้องกันการหารด้วยศูนย์ และรวมข้อมูลล่วงหน้าให้เหลือหนึ่งแถวต่อช่วงเวลา
กำหนดขอบเขตด้วย PARTITION BY และใช้ระยะเลื่อนสำหรับการเปรียบเทียบรายปี

ต่อไป: การแบ่งแถวเป็นกลุ่มและระดับด้วย NTILE

คำถามที่พบบ่อย

บทเรียน “การเปลี่ยนแปลงระหว่างช่วงเวลา” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “การเปลี่ยนแปลงระหว่างช่วงเวลา” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “การเปลี่ยนแปลงระหว่างช่วงเวลา”

คำนวณการเติบโตจากเดือนหนึ่งเทียบกับเดือนก่อนหน้าและการเปลี่ยนแปลงรายวันด้วย LAG คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน

บทเรียน “การเปลี่ยนแปลงระหว่างช่วงเวลา” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม

ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

← กลับไปที่ SQL Interview Prep