SQL Interview Prep · บทเรียน

ค่าเฉลี่ยเคลื่อนที่บนวินโดว์เลื่อน

คำนวณค่าเฉลี่ยย้อนหลัง N งวดด้วย BETWEEN PRECEDING AND CURRENT ROW

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

คำถามเรื่องค่าเฉลี่ยเคลื่อนที่

นักวิเคราะห์มักถูกถามคำถามนี้: "คำนวณค่าเฉลี่ยเคลื่อนที่ของรายได้ในช่วง 7 วัน" ค่าเฉลี่ยเคลื่อนที่ หรือค่าเฉลี่ยแบบเลื่อน ช่วยลดความผันผวนของข้อมูลรายวันด้วยการหาค่าเฉลี่ยของแต่ละจุดร่วมกับจุดใกล้เคียงล่าสุด

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

รูปแบบหลัก

ค่าเฉลี่ยเคลื่อนที่คือ AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW) กรอบจะเลื่อนไปเรื่อย ๆ โดยในแต่ละแถวจะครอบคลุมแถวปัจจุบันและอีก n แถวก่อนหน้า

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

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

การนับขนาดกรอบให้ถูกต้อง

ขนาดกรอบเท่ากับ preceding + 1 โดย +1 คือแถวปัจจุบัน ดังนี้:

กรอบ 3 แถว: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
กรอบ 7 แถว: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
กรอบ 30 แถว: ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

กล่าวการคำนวณนี้ออกมาในการสัมภาษณ์ เพื่อให้คณะผู้สัมภาษณ์เห็นว่าคุณคำนวณอย่างรอบคอบ ไม่ได้เดา

กรอบแบบมีศูนย์กลางกับกรอบแบบย้อนหลัง

ตัวอย่างข้างต้นเป็นค่าเฉลี่ยแบบ ย้อนหลัง ซึ่งมองเฉพาะข้อมูลก่อนหน้า จึงไม่ใช้ข้อมูลอนาคตและเหมาะสำหรับแผงแสดงผลการพยากรณ์

ค่าเฉลี่ยแบบ มีศูนย์กลาง จะมองทั้งสองทิศทาง เช่น ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING สำหรับกรอบ 7 แถวแบบมีศูนย์กลาง กรอบแบบมีศูนย์กลางช่วยทำให้ข้อมูลเรียบอย่างสมมาตรมากกว่า แต่ไม่สามารถคำนวณสำหรับแถวล่าสุดในเวลาจริงได้ ควรระบุว่ากรณีใช้งานต้องการแบบใด

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7
FROM daily_sales;

ผลกระทบที่ขอบกรอบ

ในช่วงเริ่มต้นของข้อมูล กรอบเต็มรูปแบบยังมีข้อมูลไม่ครบ สำหรับแถวที่ 1 ของค่าเฉลี่ยเคลื่อนที่ย้อนหลัง 7 วัน จะมีข้อมูลให้ใช้เพียง 1 แถว ดังนั้น AVG จึงหาค่าเฉลี่ยจากค่านั้นเพียงค่าเดียว

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

การไม่แสดงกรอบที่มีข้อมูลไม่ครบ

หากฝ่ายธุรกิจต้องการให้เป็น NULL จนกว่าจะมีข้อมูลครบตามกรอบ ให้ใช้ COUNT(*) กับกรอบเดียวกัน แล้วทำให้กรอบที่สั้นกว่านั้นไม่แสดงผลด้วยนิพจน์ CASE

รายละเอียดนี้แสดงถึงความใส่ใจและความถูกต้องบริเวณขอบเขต ซึ่งผู้สมัครจำนวนมากมักมองข้าม

SELECT
  sale_date,
  CASE WHEN COUNT(*) OVER w = 7
       THEN AVG(amount) OVER w
  END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

การใช้กรอบซ้ำด้วย WINDOW

คำสั่งก่อนหน้านี้ใช้ส่วนคำสั่ง WINDOW ที่ตั้งชื่อไว้ เมื่อกรอบเดียวกันปรากฏหลายครั้ง การกำหนดชื่อครั้งเดียวด้วย WINDOW w AS (...) แล้วอ้างอิงด้วย OVER w จะช่วยให้คำสั่งไม่ซ้ำซ้อนและอ่านง่าย

PostgreSQL, MySQL 8 และเซิร์ฟเวอร์ SQL รองรับหน้าต่างที่ตั้งชื่อ การใช้หน้าต่างที่ตั้งชื่อในการสัมภาษณ์แสดงถึงความชำนาญที่มากกว่าการคัดลอกฟังก์ชันหน้าต่างมาใช้

SELECT
  sale_date,
  AVG(amount) OVER w AS avg_7d,
  SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

ข้อผิดพลาดระหว่างวันตามปฏิทินกับแถว

จุดพลาดที่แยบยลคือ หากตารางมีวันที่หายไป ROWS 6 PRECEDING จะครอบคลุม 7 แถวที่บันทึกไว้ล่าสุด ซึ่งอาจกินช่วงเวลามากกว่า 7 วันตามปฏิทิน

สำหรับค่าเฉลี่ย 7 วันที่อิงตามปฏิทินจริงและคำนึงถึงช่องว่าง ให้ใช้ RANGE พร้อมออฟเซ็ตช่วงเวลา หรือเชื่อมกับชุดวันที่ที่ครบถ้วนก่อน เพื่อให้ทุกวันมีแถว การระบุความแตกต่างนี้ก็คือแนวคิดสำคัญเรื่อง ROWS กับ RANGE จากบทเรียนก่อนหน้า

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS calendar_avg_7d
FROM daily_sales;

ค่าเฉลี่ยเคลื่อนที่แยกตามกลุ่ม

เช่นเดียวกับผลรวมสะสม ค่าเฉลี่ยเคลื่อนที่มักต้องเริ่มนับใหม่สำหรับแต่ละหน่วยข้อมูล ให้เพิ่ม PARTITION BY เพื่อให้สินค้าแต่ละรายการหรือร้านค้าแต่ละแห่งมีกรอบเคลื่อนที่ของตนเองและไม่ปะปนข้ามกลุ่ม

กรอบและลำดับจะทำงานแยกกันภายในแต่ละกลุ่ม ดังนั้นแถวช่วงแรกของทุกกลุ่มจะเริ่มช่วงเริ่มต้นของตัวเองได้อย่างถูกต้อง

SELECT
  product_id,
  sale_date,
  AVG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS product_ma_7d
FROM daily_sales;

การทำให้ข้อมูลเรียบในทางปฏิบัติ

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

คุณยังสามารถเปรียบเทียบค่าเฉลี่ยเคลื่อนที่ระยะสั้นกับระยะยาว เช่น 7 วันกับ 30 วัน เพื่อดูแรงส่ง ซึ่งเทียบเท่ากับการตัดกันของค่าเฉลี่ยเคลื่อนที่ใน SQL

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma_7,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;

รายการตรวจสอบสำหรับการสัมภาษณ์

เพื่อตอบคำถามเรื่องค่าเฉลี่ยเคลื่อนได้อย่างครบถ้วน ควรกล่าวถึง:

AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW)
ขนาดกรอบ = จำนวนแถวก่อนหน้า + 1
การเลือกกรอบแบบย้อนหลังกับแบบมีศูนย์กลาง
ช่วงเริ่มต้นที่กรอบยังมีข้อมูลไม่ครบและวิธีไม่แสดงช่วงดังกล่าว
ROWS กับ RANGE เมื่อมีวันที่หายไป
PARTITION BY เพื่อเริ่มนับใหม่สำหรับแต่ละหน่วยข้อมูล

ตรวจสอบอย่างรวดเร็ว

เลือกกรอบสำหรับค่าเฉลี่ยเคลื่อนที่ย้อนหลัง 7 วันจากแถวข้อมูลรายวัน

สรุป: ค่าเฉลี่ยเคลื่อนที่

ค่าเฉลี่ยเคลื่อนที่คือ AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW) โดยขนาดกรอบเท่ากับจำนวนแถวก่อนหน้าบวกหนึ่ง เลือกแบบย้อนหลังหรือแบบมีศูนย์กลางให้ตรงกับกรณีใช้งาน จัดการแถวช่วงเริ่มต้นด้วยตัวตรวจสอบ COUNT และเปลี่ยนไปใช้ช่วงเวลาใน RANGE เมื่อช่องว่างตามปฏิทินมีความสำคัญ

เริ่มนับใหม่สำหรับแต่ละหน่วยข้อมูลด้วย PARTITION BY และใช้กรอบซ้ำด้วย WINDOW ที่ตั้งชื่อ ต่อไปเราจะเปลี่ยนผลรวมสะสมให้เป็นเปอร์เซ็นต์ด้วยสัดส่วนสะสมของผลรวมทั้งหมด

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

บทเรียน “ค่าเฉลี่ยเคลื่อนที่บนวินโดว์เลื่อน” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “ค่าเฉลี่ยเคลื่อนที่บนวินโดว์เลื่อน”

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

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

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

บทเรียน “ค่าเฉลี่ยเคลื่อนที่บนวินโดว์เลื่อน” ใช้เวลานานแค่ไหน

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

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

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

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

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