SQL Interview Prep · บทเรียน

ผลรวมสะสมด้วยเฟรมวินโดว์

สร้างผลรวมสะสมโดยใช้ SUM OVER กับเฟรมที่จัดเรียงแล้ว

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

คำถามเรื่องยอดสะสม

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

ก่อนจะมีฟังก์ชันหน้าต่าง ผู้สมัครจะแก้โจทย์นี้ด้วยการเชื่อมตารางกับตัวเองที่ทำงานช้า หรือคิวรีย่อยแบบสัมพันธ์ คำตอบสมัยใหม่ที่คาดหวังคือ SUM(...) OVER (ORDER BY ...) การรู้จักรูปแบบที่ใช้กรอบหน้าต่างแสดงว่าคุณเข้าใจ SQL ที่เขียนขึ้นหลังประมาณปี 2012

โครงสร้างของผลรวมในหน้าต่างที่เรียงลำดับ

ยอดสะสมก็คือฟังก์ชันรวมที่เปลี่ยนให้เป็นฟังก์ชันหน้าต่าง คุณยังคงใช้ SUM(amount) แต่เพิ่มส่วนคำสั่ง OVER ที่มี ORDER BY

ORDER BY ภายใน OVER คือสิ่งที่ทำให้ผลลัพธ์เป็นยอดสะสม โดยบอกให้ SQL สะสมแถวตามลำดับนั้น หากไม่มี ORDER BY SUM จะรวมทั้งพาร์ทิชันให้กับทุกแถว แทนที่จะค่อย ๆ เพิ่มขึ้น

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

เหตุใด ORDER BY จึงกำหนดกรอบ

นี่คือรายละเอียดที่ผู้สัมภาษณ์ชอบถามเจาะลึก: เมื่อเพิ่ม ORDER BY ให้กับฟังก์ชันรวมในหน้าต่าง SQL จะใช้ กรอบเริ่มต้น เป็น RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

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

การระบุกรอบอย่างชัดเจน

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

การเขียน ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW เป็นรูปแบบที่ระบุชัดเจนและปลอดภัยที่สุดสำหรับยอดสะสม เพราะนับแถวจริง จึงหลีกเลี่ยงผลลัพธ์ที่ไม่คาดคิดจากการจัดกลุ่มตามค่าของ RANGE (ซึ่งจะกล่าวถึงในบทถัดไป)

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

ตัวอย่างการทำงาน: ยอดขายรายวัน

ลองนึกภาพยอดขายสี่วัน: วันจันทร์ 100 วันอังคาร 50 วันพุธ 200 วันพฤหัสบดี 75 ยอดสะสมจะเพิ่มจากซ้ายไปขวา

วันจันทร์: 100
วันอังคาร: 100 + 50 = 150
วันพุธ: 150 + 200 = 350
วันพฤหัสบดี: 350 + 75 = 425

แถวสุดท้ายจะเท่ากับยอดรวมทั้งหมดเสมอ นี่คือการตรวจสอบความสมเหตุสมผลอย่างรวดเร็วที่คุณสามารถกล่าวถึงในการสัมภาษณ์ได้: ค่ายอดสะสมสุดท้ายต้องตรงกับ SUM(amount) ของข้อมูลทั้งชุด

รีเซ็ตแยกตามกลุ่มด้วย PARTITION BY

โจทย์ในโลกจริงมักต้องการยอดสะสมต่อลูกค้าหรือต่อภูมิภาค ไม่ใช่ยอดรวมเดียวทั้งชุด ให้เพิ่ม PARTITION BY แล้วการสะสมจะเริ่มต้นใหม่ที่ต้นพาร์ทิชันแต่ละส่วน

แบบจำลองทางความคิดคือ PARTITION BY แบ่งแถวออกเป็นกลุ่มย่อยอิสระ และ ORDER BY รวมถึงกรอบจะทำงานแยกกันภายในแต่ละกลุ่ม

SELECT
  customer_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
  ) AS customer_running_total
FROM sales;

กับดักตัวตัดสินกรณีเสมอ

หากสองแถวมีค่าของ ORDER BY เท่ากัน (เช่น มียอดขายสองรายการในวันเดียวกัน) กรอบเริ่มต้นแบบ RANGE จะถือว่าแถวเหล่านั้นอยู่ในกลุ่มเดียวกัน และให้ยอดสะสมเท่ากันโดยรวมจำนวนเงินทั้งสองรายการเข้าไปด้วย

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

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

ยอดสะสมของจำนวน

ตรรกะของการสะสมไม่ได้จำกัดอยู่แค่ SUM ฟังก์ชันรวมใด ๆ ก็ใช้เป็นฟังก์ชันหน้าต่างได้ ดังนั้นคุณจึงสร้างจำนวนสะสม ค่าเฉลี่ยสะสม หรือค่าสูงสุดสะสมได้

จำนวนคำสั่งซื้อสะสมเป็นตัวชี้วัดบนแดชบอร์ดที่พบบ่อย: ณ แต่ละวัน เรารับคำสั่งซื้อมาแล้วกี่รายการ

SELECT
  order_date,
  COUNT(*) OVER (
    ORDER BY order_date
  ) AS orders_to_date
FROM orders;

วิธีแบบเดิม: คิวรีย่อยแบบสัมพันธ์

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

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

SELECT
  s.sale_date,
  s.amount,
  (SELECT SUM(s2.amount)
   FROM sales s2
   WHERE s2.sale_date <= s.sale_date) AS running_total
FROM sales s
ORDER BY s.sale_date;

การกรองกับผลลัพธ์ของหน้าต่าง

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

วิธีแก้คือคำนวณยอดสะสมใน CTE หรือคิวรีย่อยก่อน จากนั้นจึงกรองในคิวรีชั้นนอก นี่คือกฎการห่อหุ้มแบบเดียวกับที่ใช้กับฟังก์ชันหน้าต่างทุกชนิด

WITH t AS (
  SELECT
    sale_date,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales
)
SELECT *
FROM t
WHERE running_total >= 1000;

ประเด็นที่ควรพูดในการสัมภาษณ์

เมื่ออธิบายคำตอบเรื่องยอดสะสม ให้พูดถึงประเด็นต่อไปนี้เพื่อให้ได้คะแนนเต็ม:

SUM OVER (ORDER BY ...) คือรูปแบบสะสม
การเพิ่ม ORDER BY จะสร้างกรอบเริ่มต้นตั้งแต่ UNBOUNDED PRECEDING ถึง CURRENT ROW
ใช้ PARTITION BY เพื่อรีเซ็ตการสะสมแยกตามกลุ่ม
เพิ่มตัวตัดสินที่ไม่ซ้ำและใช้กรอบแบบ ROWS เพื่อหลีกเลี่ยงกับดักค่าซ้ำ
ห่อหุ้มคำสั่งไว้ใน CTE เพื่อกรองจากผลลัพธ์

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

ทดสอบความเข้าใจของคุณเกี่ยวกับกรอบเริ่มต้น

ทบทวน: ผลรวมสะสม

ยอดสะสมคือฟังก์ชันรวมในหน้าต่างที่เรียงลำดับ SUM(amount) OVER (ORDER BY sale_date) จะสะสมแถวตั้งแต่ต้นพาร์ทิชันจนถึงแถวปัจจุบัน โดยอาศัยกรอบโดยนัยตั้งแต่ UNBOUNDED PRECEDING ถึง CURRENT ROW

รีเซ็ตยอดสะสมแยกตามกลุ่มด้วย PARTITION BY เพิ่มตัวตัดสินพร้อมกรอบแบบ ROWS เพื่อจัดการค่าการเรียงซ้ำ และห่อหุ้มไว้ใน CTE เมื่อใดก็ตามที่ต้องกรองจากค่าสะสม ต่อไปเราจะวิเคราะห์ความแตกต่างระหว่าง ROWS กับ RANGE ที่บทนี้ได้เกริ่นไว้

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

บทเรียน “ผลรวมสะสมด้วยเฟรมวินโดว์” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “ผลรวมสะสมด้วยเฟรมวินโดว์”

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

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

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

บทเรียน “ผลรวมสะสมด้วยเฟรมวินโดว์” ใช้เวลานานแค่ไหน

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

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

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

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

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