SQL Interview Prep · บทเรียน

SUM และ AVG กับ NULL

เหตุใด AVG จึงไม่สนใจ NULL และสิ่งนี้เปลี่ยนคำตอบที่ผู้สัมภาษณ์คาดหวังอย่างไร

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

กับดักที่ซ่อนอยู่ใน AVG

นี่คือคำถามคลาสสิกในการสัมภาษณ์งานที่ทำให้ผู้สมัครซึ่งไม่รอบคอบพลาดได้: "คุณมีคอลัมน์เงินเดือนที่มีค่า NULL อยู่บางส่วน AVG(salary) จะคำนวณอะไร และผลลัพธ์นั้นตรงกับสิ่งที่ธุรกิจต้องการหรือไม่?"

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

มาดูพฤติกรรมนี้ให้ชัดเจนจนไม่อาจเข้าใจผิดกัน

ข้อมูลตัวอย่าง

ใช้ตาราง employees ที่มีคอลัมน์ bonus ซึ่งอาจมีค่า NULL ตลอดบทเรียนนี้:

อลิซ, โบนัส 100
บ็อบ, โบนัส 200
แครอล, โบนัส NULL
แดน, โบนัส 300

มีทั้งหมดสี่แถว เป็นโบนัสที่ไม่ใช่ NULL สามค่า และ NULL หนึ่งค่า เราจะเรียกใช้ SUM และ AVG กับข้อมูลนี้ และดูว่า NULL ถูกจัดการอย่างไร

SUM ไม่สนใจค่า NULL

SUM(bonus) จะบวกเฉพาะค่าที่ไม่ใช่ NULL: 100 + 200 + 300 = 600 แถวที่เป็น NULL ไม่ได้มีส่วนร่วมใด ๆ โดยถูกข้ามไป ไม่ใช่การถือว่าเป็นศูนย์ในแง่การคำนวณที่ทำให้จำนวนเปลี่ยนไป

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

SELECT SUM(bonus) AS total_bonus
FROM employees;
-- returns 600

AVG ไม่สนใจค่า NULL เช่นกัน

AVG(bonus) คือส่วนสำคัญ โดยจะคำนวณเป็นผลรวมของค่าที่ไม่ใช่ NULL หารด้วยจำนวนค่าที่ไม่ใช่ NULL: 600 / 3 = 200

ตัวส่วนคือ 3 ไม่ใช่ 4 แถวที่เป็น NULL จะถูกตัดออกทั้งจากตัวเศษและตัวหาร นี่คือเหตุผลที่ AVG อาจทำให้หลายคนประหลาดใจ เพราะค่าเฉลี่ยคำนวณจากค่าที่มีอยู่ ไม่ใช่จากทุกแถว

SELECT AVG(bonus) AS avg_bonus
FROM employees;
-- 600 / 3 = 200, NOT 600 / 4 = 150

เหตุใดตัวส่วนจึงสำคัญ

สมมติว่าในทางธุรกิจ NULL ของโบนัสหมายถึง "ไม่ได้รับโบนัส" = 0 ดังนั้นค่าเฉลี่ยที่แท้จริงควรเป็น 600 / 4 = 150 แต่ AVG(bonus) รายงานค่า200

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

บังคับให้ NULL เป็นศูนย์ด้วย COALESCE

หากต้องการหาค่าเฉลี่ยจากทุกแถวโดยถือว่า NULL เป็น 0 ให้ครอบคอลัมน์ด้วย COALESCE(bonus, 0) ตอนนี้ทุกแถวมีค่าตัวเลข ดังนั้นตัวส่วนจึงกลายเป็น 4

ผลลัพธ์คือ 600 / 4 = 150 บทเรียนคือ AVG(col) และ AVG(COALESCE(col, 0)) ตอบคำถามทางธุรกิจคนละแบบ จึงควรเลือกใช้โดยตั้งใจ

SELECT AVG(COALESCE(bonus, 0)) AS avg_over_all
FROM employees;
-- 600 / 4 = 150

AVG = SUM / COUNT อย่างระมัดระวัง

ความสัมพันธ์ที่มีประโยชน์คือ AVG(col) เท่ากับ SUM(col) / COUNT(col) โปรดสังเกตว่าเป็น COUNT(col) ไม่ใช่ COUNT(*) เพราะทั้ง AVG และ COUNT รูปแบบนี้ไม่รวมค่า NULL

หากเขียน SUM(col) / COUNT(*) ผิด คุณจะได้ค่าเฉลี่ยจากทุกแถว (ในที่นี้คือ 150) ซึ่งแตกต่างจาก AVG (200) ผู้สัมภาษณ์บางครั้งจะขอให้คุณสร้าง AVG ขึ้นมาเอง เพื่อดูว่าคุณเลือก COUNT ได้ถูกต้องหรือไม่

SELECT
  AVG(bonus)                       AS builtin_avg,   -- 200
  SUM(bonus) * 1.0 / COUNT(bonus)  AS manual_avg,    -- 200
  SUM(bonus) * 1.0 / COUNT(*)      AS over_all_rows  -- 150
FROM employees;

กับดักของการหารจำนวนเต็ม

ข้อผิดพลาดเล็กน้อยที่อาจเกิดขึ้นเมื่อคำนวณค่าเฉลี่ยเองคือ ในฐานข้อมูลหลายระบบ การหารจำนวนเต็มสองจำนวนจะทำเป็นการหารจำนวนเต็ม ซึ่งตัดส่วนทศนิยมทิ้ง 7 / 2 อาจให้ผลเป็น 3 ไม่ใช่ 3.5

โดยปกติ AVG จะคืนค่าเป็นทศนิยม แต่หากคุณสร้างค่านี้ขึ้นใหม่ด้วย SUM / COUNT จากคอลัมน์จำนวนเต็ม คุณอาจสูญเสียความแม่นยำ ให้คูณด้วย 1.0 หรือแปลงเป็นชนิดข้อมูลทศนิยมก่อน

SELECT
  SUM(bonus) / COUNT(bonus)        AS maybe_truncated,
  SUM(bonus) * 1.0 / COUNT(bonus)  AS precise
FROM employees;

เมื่อทุกค่าเป็น NULL

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

SUM จะคืนค่าNULL (ไม่ใช่ 0) เมื่อไม่มีอินพุตที่ไม่ใช่ NULL
AVG จะคืนค่าNULL เช่นกัน เนื่องจากการหารด้วยจำนวนศูนย์ไม่มีนิยาม
ในทางตรงกันข้าม COUNT จะคืนค่า0

ครอบผลลัพธ์ด้วย COALESCE(SUM(col), 0) หากต้องการค่าเริ่มต้นที่เป็นตัวเลข

SELECT COALESCE(SUM(bonus), 0) AS safe_total
FROM employees
WHERE 1 = 0;  -- no rows: returns 0, not NULL

ค่าเฉลี่ยแยกตามกลุ่ม

กฎเกี่ยวกับ NULL แบบเดียวกันนี้ใช้ภายใน GROUP BY ด้วย AVG ของแต่ละกลุ่มจะหารด้วยจำนวนค่าที่ไม่ใช่ NULL ของกลุ่มนั้น กลุ่มที่โบนัสเป็น NULL ทั้งหมดจะให้ผล AVG = NULL สำหรับกลุ่มนั้น

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

SELECT department, AVG(bonus) AS avg_bonus
FROM employees
GROUP BY department;

วิธีเรียบเรียงคำตอบ

คำตอบในการสัมภาษณ์งานที่เรียบเรียงอย่างดีอาจเป็นดังนี้: "SUM และ AVG ต่างก็ไม่สนใจค่า NULL โดย AVG จะหารด้วยจำนวนค่าที่ไม่ใช่ NULL ดังนั้น NULL จึงทำให้ตัวส่วนเล็กลงในทางปฏิบัติ หาก NULL ควรถูกนับเป็นศูนย์ ฉันจะแปลงค่าด้วย COALESCE ก่อนรวมค่า มิฉะนั้นค่าเฉลี่ยจะแสดงเฉพาะแถวที่มีค่าเท่านั้น"

ประโยคเดียวนี้แสดงให้เห็นทั้งความถูกต้อง ความเข้าใจด้านธุรกิจ และวิธีแก้ไข

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

นำกฎนี้ไปใช้กับข้อมูลตัวอย่าง

สรุป

ประเด็นสำคัญเกี่ยวกับ SUM และ AVG ที่มีค่า NULL:

ทั้งสองแบบไม่สนใจค่า NULL โดยสิ้นเชิง
AVG(col) = SUM(col) / COUNT(col) — ตัวส่วนไม่รวมค่า NULL
ใช้ COALESCE(col, 0) เมื่อ NULL หมายถึงศูนย์และควรนำมานับรวม
อินพุตที่เป็น NULL ทั้งหมดหรือไม่มีแถวทำให้ SUM และ AVG คืนค่าNULL (COUNT คืนค่า 0)
ระวังการหารจำนวนเต็มเมื่อสร้าง AVG ขึ้นใหม่ด้วยตนเอง

ถัดไป: MIN, MAX และการรวมข้อมูลที่ไม่ใช่ตัวเลข

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

บทเรียน “SUM และ AVG กับ NULL” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “SUM และ AVG กับ NULL”

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

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

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

บทเรียน “SUM และ AVG กับ NULL” ใช้เวลานานแค่ไหน

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

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

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

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

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