SQL Interview Prep · บทเรียน

ฟังก์ชันรวมแยกตามกลุ่มโดยไม่มี GROUP BY

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

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

ปัญหารายละเอียดพร้อมค่ารวม

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

GROUP BY แบบปกติจะรวมแถวเข้าด้วยกัน จึงไม่สามารถเก็บรายละเอียดรายพนักงานไว้ได้ คุณต้องการทั้งแถวรายละเอียดและตัวเลขระดับกลุ่มพร้อมกัน

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

เหตุใด GROUP BY แบบปกติจึงใช้ไม่ได้ที่นี่

หากเขียน SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id คุณจะได้หนึ่งแถวต่อแผนก ทำให้ชื่อของแต่ละบุคคลหายไป

การเพิ่ม name ลงใน SELECT โดยไม่เพิ่มลงใน GROUP BY จะทำให้เกิดข้อผิดพลาดคลาสสิกว่า "คอลัมน์ต้องปรากฏใน GROUP BY"

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

ใช้คิวรีย่อยแบบสัมพันธ์แก้ปัญหา

ใส่ค่ารวมของกลุ่มไว้ในรายการ SELECT ในรูปคิวรีย่อยแบบสัมพันธ์ แถวพนักงานแต่ละแถวจะเรียกใช้ MAX ภายในโดยจำกัดขอบเขตไว้ที่แผนกของพนักงานคนนั้น

ความสัมพันธ์ e2.dept_id = e1.dept_id ทำให้ค่ารวมผูกกับกลุ่มที่ถูกต้อง ขณะที่คิวรีภายนอกยังคงคืนค่าหนึ่งแถวต่อพนักงานหนึ่งคน

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT MAX(e2.salary)
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;

เปรียบเทียบแต่ละแถวกับกลุ่มของตน

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

ในที่นี้ AVG แบบสัมพันธ์อยู่ใน WHERE ดังนั้นพนักงานแต่ละคนจะถูกตรวจสอบกับค่าเฉลี่ยของแผนกของตนเอง

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

คำนวณความแตกต่างจากกลุ่ม

คุณยังสามารถแสดงได้ว่าแต่ละแถวอยู่ห่างจากค่ารวมของกลุ่มมากเพียงใด การลบด้วยค่าเฉลี่ยแบบสัมพันธ์จะให้ส่วนต่างของแต่ละแถว

สังเกตว่าคิวรีย่อยแบบสัมพันธ์เดียวกันสามารถนำกลับมาใช้ในนิพจน์ SELECT หลายรายการได้ ระบบประมวลผลจะประเมินคิวรีย่อยนั้นแยกตามแถวทุกครั้งที่ปรากฏ

SELECT e1.name,
       e1.salary,
       e1.salary - (SELECT AVG(e2.salary)
                    FROM employees e2
                    WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;

หาผู้มีรายได้สูงสุดในแต่ละกลุ่ม

หากต้องการคืนเฉพาะผู้ที่ได้รับเงินเดือนสูงสุดในแต่ละแผนก ให้เปรียบเทียบเงินเดือนแต่ละรายการกับ MAX แบบสัมพันธ์ แล้วเก็บรายการที่ตรงกันไว้

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

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

ทางเลือก: ฟังก์ชันหน้าต่าง

SQL สมัยใหม่มีเครื่องมือที่สะอาดกว่า นั่นคือฟังก์ชันหน้าต่าง MAX(salary) OVER (PARTITION BY dept_id) จะคำนวณค่ารวมของกลุ่มโดยไม่รวมแถวเข้าด้วยกัน และไม่ต้องอ่านข้อมูลซ้ำด้วยคิวรีย่อยแบบสัมพันธ์

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

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;

ข้อแลกเปลี่ยนระหว่างแบบสัมพันธ์กับฟังก์ชันหน้าต่าง

ทั้งสองแนวทางคืนผลลัพธ์ที่มีรูปแบบเหมือนกัน แต่มีความแตกต่างดังนี้:

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

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

ตัวอย่างพร้อมวิธีทำ: คำสั่งซื้อที่สูงกว่าค่าเฉลี่ยของลูกค้า

นำรูปแบบนี้ไปใช้กับคำสั่งซื้อ โดยแสดงคำสั่งซื้อที่มียอดเงินสูงกว่าค่าเฉลี่ยของคำสั่งซื้อของลูกค้าที่สั่งรายการนั้น

AVG แบบสัมพันธ์กำหนดขอบเขตด้วย o2.customer_id = o1.customer_id ทำให้คำสั่งซื้อแต่ละรายการมีค่าพื้นฐานเฉพาะของลูกค้ารายนั้น

SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

ระวังกรณี NULL และกลุ่มว่าง

หากกลุ่มมีเพียงหนึ่งแถว ค่าเฉลี่ยของกลุ่มจะเท่ากับค่าของแถวนั้น ดังนั้น salary > avg จึงเป็น FALSE และแถวนั้นจะถูกตัดออก ควรกล่าวถึงกรณีขอบนี้ไว้ล่วงหน้า

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

จัดอันดับภายในกลุ่มด้วยการนับ

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

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

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT COUNT(*) + 1
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
          AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;

ตรวจสอบความเข้าใจอย่างรวดเร็ว

เลือกเหตุผลที่คิวรีย่อยแบบสัมพันธ์เหมาะกว่า GROUP BY แบบปกติสำหรับงานนี้

สรุป: ค่ารวมรายกลุ่มโดยไม่ใช้ GROUP BY

ประเด็นสำคัญ:

คิวรีย่อยแบบสัมพันธ์ใส่ค่ารวมระดับกลุ่มไว้ในทุกแถวรายละเอียดโดยไม่รวมแถวเหล่านั้นเข้าด้วยกัน
ใช้ใน SELECT เพื่อแสดงค่ารวม หรือใช้ใน WHERE เพื่อเปรียบเทียบแต่ละแถวกับกลุ่มของตนเอง
รูปแบบ = MAX(...) จะคืนแถวบนสุดที่มีค่าเท่ากันทั้งหมด
ฟังก์ชันหน้าต่างที่ใช้ PARTITION BY ทำงานเดียวกันได้ในการอ่านข้อมูลรอบเดียว และโดยทั่วไปปรับขนาดได้ดีกว่า

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

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

บทเรียน “ฟังก์ชันรวมแยกตามกลุ่มโดยไม่มี GROUP BY” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “ฟังก์ชันรวมแยกตามกลุ่มโดยไม่มี GROUP BY”

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

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

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

บทเรียน “ฟังก์ชันรวมแยกตามกลุ่มโดยไม่มี GROUP BY” ใช้เวลานานแค่ไหน

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

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

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

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

กายวิภาคของคิวรีย่อยที่สัมพันธ์กับคิวรีภายนอก
ฟังก์ชันรวมแยกตามกลุ่มโดยไม่มี GROUP BY
EXISTS และ NOT EXISTS ที่สัมพันธ์กับคิวรีภายนอก
เขียนคิวรีย่อยที่สัมพันธ์กับคิวรีภายนอกใหม่เป็นการเชื่อมตาราง

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