SQL Interview Prep · บทเรียน

FIRST_VALUE, LAST_VALUE และขอบเขตเฟรม

ดึงค่าที่ขอบเขต และทำความเข้าใจกับข้อผิดพลาดที่พบบ่อยของเฟรม LAST_VALUE

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

การดึงค่าที่ขอบเขต

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

ฟังก์ชันที่ใช้คือ FIRST_VALUE และ LAST_VALUE ฟังก์ชันเหล่านี้ดูเรียบง่าย แต่ LAST_VALUE ซ่อนจุดพลาดสำคัญเกี่ยวกับเฟรมของฟังก์ชันหน้าต่างใน SQL ไว้ บทเรียนนี้จะช่วยให้คุณใช้ทั้งสองฟังก์ชันได้อย่างถูกต้อง

พื้นฐานของ FIRST_VALUE

FIRST_VALUE(col) คืนค่าของ col จากแถวแรกของหน้าต่าง และแสดงค่านั้นกับทุกแถว เมื่อเรียงตามวันที่ แต่ละแถวจะได้รับค่าที่เก่าที่สุดในกลุ่มข้อมูลของตน

เนื่องจากเฟรมเริ่มต้นเริ่มจากแถวแรกของกลุ่มข้อมูล FIRST_VALUE จึงมักทำงานตรงตามที่คาดไว้

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS first_login
FROM logins;

กรอบหน้าต่างเริ่มต้น

ประเด็นสำคัญคือ เมื่อเพิ่ม ORDER BY ให้กับหน้าต่าง กรอบเริ่มต้นคือ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

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

กับดักของ LAST_VALUE

เมื่อใช้ LAST_VALUE โดยมีเพียง ORDER BY ผู้สมัครส่วนใหญ่มักคาดหวังค่าท้ายสุดของพาร์ทิชัน แต่เนื่องจากกรอบสิ้นสุดที่แถวปัจจุบัน "ค่าท้ายสุดในกรอบ" จึงเป็นค่าของแถวปัจจุบันเอง

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

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS wrong_last_login
FROM logins;

แก้ไข LAST_VALUE ด้วยกรอบแบบเต็ม

วิธีแก้คือขยายกรอบให้ครอบคลุมทั้งพาร์ทิชัน: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

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

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS last_login
FROM logins;

ทางเลือกที่ง่ายกว่า

วิศวกรจำนวนมากหลีกเลี่ยงการจัดการกรอบไปเลย โดยหากต้องการค่าท้ายสุด ให้ใช้ FIRST_VALUE ร่วมกับลำดับการเรียงที่กลับทิศ

FIRST_VALUE(login_date) OVER (... ORDER BY login_date DESC) จะคืนวันที่ล่าสุดโดยไม่ต้องระบุส่วนกำหนดกรอบ เป็นเทคนิคที่เรียบง่ายและจำได้ง่าย เหมาะสำหรับกล่าวถึงในการสัมภาษณ์

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date DESC
  ) AS last_login
FROM logins;

ROWS กับ RANGE ในกรอบ

กรอบมีอยู่สองรูปแบบ ROWS นับแถวจริง ส่วน RANGE จัดกลุ่มตามค่าของ ORDER BY ที่เท่ากัน (แถวที่มีค่าเท่ากัน)

กรอบเริ่มต้นใช้ RANGE นี่จึงเป็นเหตุผลที่ค่าของ ORDER BY ที่เสมอกันใช้ขอบเขตกรอบเดียวกัน สำหรับการแก้ไข LAST_VALUE ควรเลือกใช้ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ที่ระบุไว้อย่างชัดเจน เพื่อหลีกเลี่ยงผลลัพธ์ที่ไม่คาดคิดเมื่อมีค่าเสมอกัน

NTH_VALUE สำหรับตำแหน่งใดก็ได้

นอกเหนือจากค่าแรกและค่าสุดท้ายแล้ว NTH_VALUE(col, n) ยังดึงค่าที่ตำแหน่ง n ภายในกรอบได้ เช่น ราคาที่สูงเป็นอันดับสอง

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

SELECT
  product_id,
  price,
  NTH_VALUE(price, 2) OVER (
    PARTITION BY product_id
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS second_highest_price
FROM prices;

ตัวอย่างการทำงาน: ค่าแรกและค่าสุดท้ายพร้อมกัน

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

ตอนนี้ทุกแถวจะมีค่าของธุรกรรมแรกและสุดท้ายจากทั้งพาร์ทิชัน พร้อมสำหรับการคำนวณส่วนต่างหรือขั้นตอนการติดป้ายกำกับ

SELECT
  customer_id,
  txn_date,
  amount,
  FIRST_VALUE(amount) OVER w AS first_amt,
  LAST_VALUE(amount) OVER w AS last_amt
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

หน้าต่างที่มีชื่อช่วยให้ใช้ DRY

สังเกตว่าคิวรีก่อนหน้าใช้ส่วนคำสั่ง WINDOW w AS (...) และอ้างถึง OVER w สองครั้ง การกำหนดหน้าต่างเพียงครั้งเดียวช่วยหลีกเลี่ยงการเขียนข้อกำหนดกรอบยาว ๆ ซ้ำ และป้องกันไม่ให้ฟังก์ชันทั้งสองมีรายละเอียดไม่สอดคล้องกัน

ฐานข้อมูลหลักส่วนใหญ่รองรับหน้าต่างที่มีชื่อ การใช้หน้าต่างลักษณะนี้เป็นรายละเอียดเล็ก ๆ ที่เรียบร้อย ซึ่งผู้สัมภาษณ์ชื่นชมเมื่อมีหลายคอลัมน์ใช้หน้าต่างเดียวกัน

ตัวอย่างการทำงาน: ส่วนต่างจากค่าแรกถึงค่าสุดท้าย

คำถามต่อยอดที่พบบ่อยคือ การเปลี่ยนแปลงจากธุรกรรมแรกถึงธุรกรรมสุดท้ายของลูกค้า เมื่อมีค่าขอบเขตทั้งสองอยู่ในทุกแถวแล้ว ให้ลบค่าทั้งสอง จากนั้นกำจัดรายการซ้ำให้เหลือหนึ่งแถวต่อลูกค้าหากจำเป็น

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

SELECT DISTINCT
  customer_id,
  LAST_VALUE(amount) OVER w - FIRST_VALUE(amount) OVER w AS first_to_last_delta
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

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

กับดักคลาสสิกของ LAST_VALUE

ทบทวน

ฟังก์ชันค่าขอบเขตขึ้นอยู่กับกรอบ:

FIRST_VALUE ทำงานได้ภายใต้กรอบเริ่มต้น แต่ LAST_VALUE ทำไม่ได้
กรอบเริ่มต้นสิ้นสุดที่แถวปัจจุบัน ดังนั้นให้แก้ไข LAST_VALUE ด้วย ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING หรือกลับลำดับการเรียงแล้วใช้ FIRST_VALUE
NTH_VALUE(col, n) ดึงค่าจากตำแหน่งใดก็ได้ ส่วนหน้าต่างที่มีชื่อช่วยให้ข้อกำหนดสำหรับหลายคอลัมน์ใช้ DRY

เท่านี้ชุดเครื่องมือของ LAG, LEAD, NTILE และฟังก์ชันค่าขอบเขตก็ครบถ้วน

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

บทเรียน “FIRST_VALUE, LAST_VALUE และขอบเขตเฟรม” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “FIRST_VALUE, LAST_VALUE และขอบเขตเฟรม”

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

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

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

บทเรียน “FIRST_VALUE, LAST_VALUE และขอบเขตเฟรม” ใช้เวลานานแค่ไหน

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

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

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

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

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