SQL Interview Prep · บทเรียน

LAG และ LEAD สำหรับแถวที่อยู่ติดกัน

เข้าถึงค่าจากแถวก่อนหน้าและถัดไปโดยไม่ต้องเชื่อมตารางเข้ากับตัวเอง

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

คำถามที่ผู้สัมภาษณ์มักถาม

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

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

LAG และ LEAD ทำอะไร

LAG(col) ส่งกลับค่าของ col จากแถวก่อนหน้า ส่วน LEAD(col) ส่งกลับค่าจากแถวถัดไป คำว่า "ก่อนหน้า" และ "ถัดไป" ถูกกำหนดทั้งหมดโดย ORDER BY ภายในส่วนคำสั่ง OVER

LAG มองย้อนกลับ
LEAD มองไปข้างหน้า

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

ไวยากรณ์พื้นฐานของ LAG

นี่คือรูปแบบมาตรฐาน เรามีตาราง sales ที่มี month และ revenue และต้องการให้แต่ละแถวแสดงรายได้ของเดือนก่อนหน้าด้วย

OVER (ORDER BY month) บอกระบบประมวลผลว่าจะกำหนดคำว่า "ก่อนหน้า" อย่างไร แถวแรกไม่มีแถวก่อนหน้า ดังนั้น prev_revenue จึงเป็น NULL ในแถวนั้น

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

การอ่านผลลัพธ์

สำหรับข้อมูล 2024-01 = 100, 2024-02 = 130, 2024-03 = 120 คิวรีจะส่งกลับผลดังนี้:

ม.ค.: รายได้ 100, รายได้ก่อนหน้า NULL
ก.พ.: รายได้ 130, รายได้ก่อนหน้า 100
มี.ค.: รายได้ 120, รายได้ก่อนหน้า 130

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

LEAD มองไปข้างหน้า

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

แถวสุดท้ายในชุดข้อมูลที่เรียงลำดับแล้วไม่มีแถวถัดไป ดังนั้นผลลัพธ์จาก LEAD ของแถวนั้นจึงเป็น NULL

SELECT
  month,
  revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales
ORDER BY month;

อาร์กิวเมนต์ระยะเลื่อน

ทั้งสองฟังก์ชันรับอาร์กิวเมนต์ตัวที่สองที่ไม่จำเป็น ซึ่งระบุว่าจะข้ามไปกี่แถว LAG(col, 2) ย้อนกลับไปสองแถว ส่วน LEAD(col, 3) ข้ามไปข้างหน้าสามแถว

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

SELECT
  month,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY month) AS revenue_2_months_ago
FROM sales
ORDER BY month;

อาร์กิวเมนต์ค่าเริ่มต้น

อาร์กิวเมนต์ตัวที่สามจะระบุค่าทดแทนเมื่อไม่มีแถวข้างเคียง แทนที่จะได้ NULL รูปแบบการเรียกใช้คือ LAG(col, offset, default)

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

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

PARTITION BY เริ่มหน้าต่างใหม่

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

นั่นหมายความว่าแถวแรกของแต่ละพาร์ติชันจะได้รับค่า NULL จาก LAG และจะไม่มีค่ารั่วข้ามขอบเขตไปยังข้อมูลของลูกค้ารายอื่น

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount
FROM orders;

ตัวอย่างแบบลงมือทำ: จำนวนวันระหว่างคำสั่งซื้อ

งานที่พบบ่อยคือการวัดช่วงห่างระหว่างคำสั่งซื้อที่ลูกค้าสั่งต่อเนื่องกัน ดึงวันที่ของคำสั่งซื้อก่อนหน้าด้วย LAG แล้วจึงนำมาลบกัน

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

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS days_since_prev
FROM orders;

ทำไมไม่ใช้การเชื่อมตารางกับตัวเอง

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

LAG/LEAD สื่อเจตนาได้ในบรรทัดเดียว
ระบบจะคำนวณทั้งสองฟังก์ชันในการประมวลผลตามลำดับเพียงรอบเดียว
ค่าที่เท่ากันจะถูกจัดการอย่างแน่นอนตาม ORDER BY ของคุณ

การพูดว่า “ฉันจะใช้ LAG แทนการเชื่อมตารางกับตัวเอง” แสดงว่าคุณมีความคล่องแคล่ว

จุดพลาดที่พบบ่อย: ไม่มี ORDER BY

หากไม่มี ORDER BY ในส่วนคำสั่ง OVER “แถวก่อนหน้า” จะไม่ถูกกำหนดไว้ ระบบฐานข้อมูลบางระบบอาจปฏิเสธคำสั่ง ขณะที่บางระบบคืนผลลัพธ์ที่คาดเดาไม่ได้ ดังนั้นควรจัดลำดับหน้าต่างเสมอ

โปรดจำไว้ว่าการจัดลำดับภายใน OVER เป็นอิสระจาก ORDER BY ภายนอกของคำสั่งสอบถาม การจัดลำดับหน้าต่างจะกำหนดว่าแถวใดเป็นแถวข้างเคียง ส่วนส่วนคำสั่งภายนอกมีหน้าที่กำหนดลำดับการแสดงผลเท่านั้น

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

ทดสอบความเข้าใจเกี่ยวกับฟังก์ชันหน้าต่างแบบเลื่อนตำแหน่ง

สรุปทบทวน

ตอนนี้คุณรู้จักฟังก์ชันหน้าต่างแบบเลื่อนตำแหน่งแล้ว:

LAG(col) อ่านแถวก่อนหน้า ส่วน LEAD(col) อ่านแถวถัดไป โดยกำหนดตาม ORDER BY ของหน้าต่าง
อาร์กิวเมนต์เสริม: LAG(col, offset, default)
PARTITION BY รีเซ็ตการเลื่อนดูแถวในแต่ละกลุ่ม ดังนั้นแถวขอบเขตจึงมีค่าเป็น NULL
ฟังก์ชันเหล่านี้ใช้แทนการเชื่อมตารางกับตัวเองที่ยุ่งยากเมื่อต้องเปรียบเทียบแถวที่อยู่ติดกัน

ต่อไปเราจะนำแนวคิดนี้ไปใช้กับคำถามยอดฮิตที่นักวิเคราะห์ต้องเจอ: การเปลี่ยนแปลงเทียบช่วงก่อนหน้า

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

บทเรียน “LAG และ LEAD สำหรับแถวที่อยู่ติดกัน” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “LAG และ LEAD สำหรับแถวที่อยู่ติดกัน”

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

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

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

บทเรียน “LAG และ LEAD สำหรับแถวที่อยู่ติดกัน” ใช้เวลานานแค่ไหน

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

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

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

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

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