SQL Interview Prep · บทเรียน

OVER, PARTITION BY และ ORDER BY

โครงสร้างของข้อกำหนดวินโดว์ และวิธีที่พาร์ทิชันเริ่มการคำนวณใหม่

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

เหตุผลที่ผู้สัมภาษณ์เลือกใช้ฟังก์ชันหน้าต่าง

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

GROUP BY คืนหนึ่งแถวต่อหนึ่งกลุ่ม
ฟังก์ชันหน้าต่าง คืนทุกแถวที่เป็นข้อมูลเข้า พร้อมคอลัมน์ที่คำนวณเพิ่ม

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

องค์ประกอบของอนุประโยค OVER

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

PARTITION BY — แบ่งแถวออกเป็นกลุ่ม โดยฟังก์ชันจะเริ่มนับใหม่ในแต่ละกลุ่ม
ORDER BY — จัดลำดับแถวภายในแต่ละกลุ่ม (จำเป็นสำหรับการจัดอันดับและยอดสะสม)
กรอบ — จำกัดแถวที่นำมาใช้คำนวณ (ROWS/RANGE)

OVER () ที่ว่างเปล่าจะถือว่าชุดผลลัพธ์ทั้งหมดเป็นกลุ่มเดียว

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

ฟังก์ชันหน้าต่างกับการรวมค่า: ฟังก์ชันเดียวกัน ผลลัพธ์ต่างกัน

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

AVG(salary) ร่วมกับ GROUP BY department จะคืนหนึ่งแถวต่อหนึ่งแผนก
AVG(salary) OVER (PARTITION BY department) จะคืนพนักงานทุกคน โดยกำกับค่าเฉลี่ยของแผนกไว้กับแต่ละคน

เคล็ดลับในการสัมภาษณ์: เน้นว่ารูปแบบฟังก์ชันหน้าต่างไม่จำเป็นต้องใช้ GROUP BY และไม่ลบแถวรายละเอียดที่ซ้ำกันออก

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY: เริ่มการคำนวณใหม่

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

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

สามารถแบ่งพาร์ทิชันด้วยคอลัมน์เดียวหรือหลายคอลัมน์ได้
การไม่มี PARTITION BY หมายถึงมีพาร์ทิชันขนาดใหญ่เพียงหนึ่งพาร์ทิชัน ซึ่งก็คือชุดข้อมูลทั้งหมด

SELECT
  department,
  name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

ORDER BY ภายใน OVER

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

ฟังก์ชันจัดอันดับ (ROW_NUMBER, RANK) จำเป็นต้องมีสิ่งนี้ เพราะต้องมีลำดับที่จะใช้จัดอันดับ
ฟังก์ชันรวมทั่วไปที่ทำงานบนพาร์ทิชันไม่จำเป็นต้องใช้สิ่งนี้ เว้นแต่ต้องการคำนวณแบบสะสม

ข้อผิดพลาดที่พบได้บ่อยในการสัมภาษณ์คือการสับสนระหว่าง ORDER BY ของหน้าต่างกับลำดับการแสดงผลลัพธ์

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

การรวม PARTITION BY และ ORDER BY

หน้าต่างสำหรับจัดอันดับแบบคลาสสิกจะใช้ทั้งสองอย่างร่วมกัน โดย PARTITION BY จะแบ่งกลุ่ม จากนั้น ORDER BY จะจัดลำดับภายในแต่ละกลุ่ม

ให้อ่านข้อกำหนดด้านล่างว่า: "ภายในแต่ละแผนก ให้เรียงพนักงานตามเงินเดือนจากมากไปน้อย แล้วกำหนดหมายเลขให้พวกเขา" พนักงานที่มีเงินเดือนสูงสุดในแต่ละแผนกจะได้หมายเลขแถวเป็น 1

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

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

ORDER BY เปลี่ยนพฤติกรรมของฟังก์ชันรวม

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

SUM(x) OVER (PARTITION BY g) → ยอดรวมของกลุ่มเดียวกันในทุกแถว
SUM(x) OVER (PARTITION BY g ORDER BY d) → ยอดรวมสะสมจนถึงแถวปัจจุบัน

การเข้าใจว่า ORDER BY เพิ่มกรอบโดยนัยแยกผู้สมัครระดับกลางออกจากผู้สมัครระดับต้นได้

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

ฟังก์ชันหน้าต่างใช้ได้ที่ใดบ้าง

ฟังก์ชันหน้าต่างสามารถปรากฏได้เฉพาะในรายการ SELECT และส่วนคำสั่ง ORDER BY เท่านั้น โดยไม่อนุญาตให้ใช้ใน WHERE, GROUP BY หรือ HAVING

เหตุผลเกี่ยวข้องกับลำดับการทำงานเชิงตรรกะ โดยฟังก์ชันหน้าต่างจะถูกประเมิน หลังจาก WHERE, GROUP BY และ HAVING ทำงานเสร็จแล้ว แถวต่าง ๆ ถูกเลือกไว้ก่อนที่ฟังก์ชันหน้าต่างจะเห็นแถวเหล่านั้น

นี่คือสาเหตุที่การกรองตามอันดับต้องใช้ซับคิวรีหรือ CTE ซึ่งจะอธิบายอย่างครบถ้วนในบทเรียนถัดไป

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

ฟังก์ชันหน้าต่างหลายตัวในคิวรีเดียว

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

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

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w  AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

ตัวอย่างการทำงาน: เงินเดือนเทียบกับค่าเฉลี่ยของแผนก

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

โปรดสังเกตว่าไม่มี GROUP BY และแถวของพนักงานทุกคนยังคงอยู่ ค่า dept_avg จะแสดงซ้ำสำหรับทุกคนในแผนกเดียวกัน ซึ่งเป็นสิ่งที่ทำให้สามารถเปรียบเทียบกันทีละแถวได้

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

ข้อผิดพลาดที่ผู้สัมภาษณ์มักเฝ้าดู

หลีกเลี่ยงจุดที่มักพลาดเหล่านี้เมื่อกล่าวถึงฟังก์ชันหน้าต่าง:

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

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

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

ทบทวน: ข้อกำหนดหน้าต่าง

ขณะนี้คุณเข้าใจโครงสร้างของ OVER (...) แล้ว:

ฟังก์ชันหน้าต่างเก็บทุกแถวไว้ขณะคำนวณข้ามแถวที่เกี่ยวข้อง
PARTITION BY แบ่งกลุ่มและเริ่มการคำนวณใหม่ แต่ไม่เคยลบแถว
ORDER BY จัดลำดับแถวภายในพาร์ทิชัน ฟังก์ชันจัดอันดับจำเป็นต้องใช้สิ่งนี้ และสิ่งนี้จะเปลี่ยนฟังก์ชันรวมให้เป็นการคำนวณแบบสะสม
ฟังก์ชันหน้าต่างใช้ได้ตามกฎเฉพาะใน SELECT และ ORDER BY เท่านั้น ห้ามใช้ใน WHERE/HAVING

ถัดไป คุณจะกำหนดหมายเลขลำดับที่แน่นอนด้วย ROW_NUMBER

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

บทเรียน “OVER, PARTITION BY และ ORDER BY” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “OVER, PARTITION BY และ ORDER BY”

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

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

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

บทเรียน “OVER, PARTITION BY และ ORDER BY” ใช้เวลานานแค่ไหน

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

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

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

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

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