Aggregate และ GROUP BY

จนถึงตอนนี้ทุก query คืน row ที่มีอยู่แล้ว aggregate function ต่างออกไป คืออ่านหลาย row แล้วผลิตค่าสรุปออกมาค่าเดียว มีหนังสือกี่เล่ม? ยอดขายรวมทั้งหมดกี่เล่ม? ปีที่ตีพิมพ์โดยเฉลี่ยคือปีไหน? แต่ละข้อนั้นคือตัวเลขหนึ่งตัวที่ถูกบีบออกมาจากทั้ง column เพิ่ม GROUP BY เข้าไปแล้วคุณจะได้ตัวเลขสรุปหนึ่งตัวต่อหนึ่งกลุ่ม แทนที่จะได้หนึ่งตัวสำหรับทั้ง table

เรายังคงใช้ table authors, books, และ sales ต่อไป

Aggregate function ทั่วทั้ง table

เมื่อไม่มี GROUP BY aggregate function จะพับผลลัพธ์ทั้งหมดลงเหลือเพียง row เดียว ตัวที่ใช้บ่อยที่สุดคือ count, sum, avg, min, และ max

psql

SELECT
  count(*)    AS sale_rows,
  sum(copies) AS total_copies,
  avg(copies) AS avg_per_sale,
  max(copies) AS biggest_sale
FROM sales;

Node.js

const res = await pool.query(`
  SELECT count(*) AS sale_rows, sum(copies) AS total_copies,
         avg(copies) AS avg_per_sale, max(copies) AS biggest_sale
  FROM sales
`);
console.log(res.rows[0]);

Python

cur.execute("""
    SELECT count(*) AS sale_rows, sum(copies) AS total_copies,
           avg(copies) AS avg_per_sale, max(copies) AS biggest_sale
    FROM sales
""")
summary = cur.fetchone()

Go

row := conn.QueryRow(ctx, `
  SELECT count(*) AS sale_rows, sum(copies) AS total_copies,
         avg(copies) AS avg_per_sale, max(copies) AS biggest_sale
  FROM sales`)

Rust

let row = sqlx::query(
    "SELECT count(*) AS sale_rows, sum(copies) AS total_copies,
            avg(copies) AS avg_per_sale, max(copies) AS biggest_sale
     FROM sales",
)
.fetch_one(&pool)
.await?;

 sale_rows | total_copies |     avg_per_sale     | biggest_sale
-----------+--------------+----------------------+--------------
         6 |         1850 | 308.3333333333333333 |          900
(1 row)

สังเกตว่า avg คืนค่าตัวเลขความละเอียดสูง ห่อด้วย round(avg(copies), 2) เมื่อคุณต้องการตัวเลขทศนิยมสองตำแหน่งที่ดูเรียบร้อย

GROUP BY: หนึ่งสรุปต่อหนึ่งกลุ่ม

GROUP BY แยก row ออกเป็นถังที่ใช้ค่าเดียวกันใน column ที่ระบุไว้ แล้วรัน aggregate ครั้งเดียวต่อหนึ่งถัง ที่นี่เรารวมจำนวนเล่มที่ขายได้ต่อภูมิภาค

psql

SELECT region, sum(copies) AS copies_in_region
FROM sales
GROUP BY region
ORDER BY copies_in_region DESC;

Node.js

const res = await pool.query(`
  SELECT region, sum(copies) AS copies_in_region
  FROM sales
  GROUP BY region
  ORDER BY copies_in_region DESC
`);

Python

cur.execute("""
    SELECT region, sum(copies) AS copies_in_region
    FROM sales
    GROUP BY region
    ORDER BY copies_in_region DESC
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT region, sum(copies) AS copies_in_region
  FROM sales
  GROUP BY region
  ORDER BY copies_in_region DESC`)

Rust

let rows = sqlx::query(
    "SELECT region, sum(copies) AS copies_in_region
     FROM sales
     GROUP BY region
     ORDER BY copies_in_region DESC",
)
.fetch_all(&pool)
.await?;

 region | copies_in_region
--------+------------------
 East   |             1100
 West   |              750
(2 rows)

แต่ละภูมิภาคกลายเป็นผลลัพธ์หนึ่ง row column region อยู่ใน SELECT ได้เพราะเป็นตัวที่เราใช้จัดกลุ่ม ทุกอย่างที่เหลือต้องอยู่ภายใน aggregate

กรองกลุ่มด้วย HAVING

WHERE กรอง row ทีละ row ก่อน เข้าสู่การจัดกลุ่ม ในการกรองบนผลลัพธ์ของ aggregate คุณต้องใช้ HAVING ซึ่งทำงาน หลัง การจัดกลุ่ม ที่นี่เราเก็บเฉพาะภูมิภาคที่ขายได้มากกว่าแปดร้อยเล่ม

psql

SELECT region, sum(copies) AS copies_in_region
FROM sales
GROUP BY region
HAVING sum(copies) > 800
ORDER BY copies_in_region DESC;

Node.js

const res = await pool.query(`
  SELECT region, sum(copies) AS copies_in_region
  FROM sales
  GROUP BY region
  HAVING sum(copies) > 800
  ORDER BY copies_in_region DESC
`);

Python

cur.execute("""
    SELECT region, sum(copies) AS copies_in_region
    FROM sales
    GROUP BY region
    HAVING sum(copies) > 800
    ORDER BY copies_in_region DESC
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT region, sum(copies) AS copies_in_region
  FROM sales
  GROUP BY region
  HAVING sum(copies) > 800
  ORDER BY copies_in_region DESC`)

Rust

let rows = sqlx::query(
    "SELECT region, sum(copies) AS copies_in_region
     FROM sales
     GROUP BY region
     HAVING sum(copies) > 800
     ORDER BY copies_in_region DESC",
)
.fetch_all(&pool)
.await?;

 region | copies_in_region
--------+------------------
 East   |             1100
(1 row)

ใช้ WHERE ทิ้ง row ที่คุณไม่อยากให้ถูกนับเลย และใช้ HAVING ทิ้งทั้งกลุ่มโดยอิงจากยอดรวมของกลุ่ม ทั้งสองมักปรากฏใน query เดียวกัน

count(*) เทียบกับ count(column)

count(*) นับ row ส่วน count(column) นับ row ที่ column นั้นไม่เป็น NULL ความต่างนี้สำคัญทุกครั้งที่ column หนึ่งอาจขาดหายไป การ join authors เข้ากับ books ด้วย LEFT JOIN แสดงให้เห็นชัดเจน

psql

SELECT a.name,
       count(*)      AS row_count,
       count(b.id)   AS book_count
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.id
GROUP BY a.name
ORDER BY a.name;

Node.js

const res = await pool.query(`
  SELECT a.name, count(*) AS row_count, count(b.id) AS book_count
  FROM authors AS a
  LEFT JOIN books AS b ON b.author_id = a.id
  GROUP BY a.name
  ORDER BY a.name
`);

Python

cur.execute("""
    SELECT a.name, count(*) AS row_count, count(b.id) AS book_count
    FROM authors AS a
    LEFT JOIN books AS b ON b.author_id = a.id
    GROUP BY a.name
    ORDER BY a.name
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT a.name, count(*) AS row_count, count(b.id) AS book_count
  FROM authors AS a
  LEFT JOIN books AS b ON b.author_id = a.id
  GROUP BY a.name
  ORDER BY a.name`)

Rust

let rows = sqlx::query(
    "SELECT a.name, count(*) AS row_count, count(b.id) AS book_count
     FROM authors AS a
     LEFT JOIN books AS b ON b.author_id = a.id
     GROUP BY a.name
     ORDER BY a.name",
)
.fetch_all(&pool)
.await?;

    name     | row_count | book_count
-------------+-----------+------------
 Mara Linde  |         2 |          2
 Owen Pryce  |         1 |          1
 Sela Vance  |         1 |          0
(3 rows)

Sela Vance แสดง row_count เป็นหนึ่งเพราะ LEFT JOIN ยังคงผลิต row ให้เธอ แต่ book_count เป็นศูนย์เพราะ b.id เป็น NULL ตรงนั้น และ count(b.id) ข้ามค่า NULL ไป

เคล็ดลับและข้อควรระวัง

• ทุก column ในรายการ SELECT ที่ไม่ได้ห่อด้วย aggregate ต้องปรากฏใน GROUP BY มิฉะนั้น PostgreSQL จะบอกไม่ได้ว่าจะแสดงค่าของ row ไหน และจะโยน error ออกมา
• หยิบ count(*) มาใช้เพื่อนับ row และ count(col) เพื่อนับค่าที่ไม่เป็น NULL ใช้ count(DISTINCT col) เพื่อนับค่าที่ไม่ซ้ำกันและไม่เป็น NULL
• WHERE กรอง row ก่อนจัดกลุ่ม ส่วน HAVING กรองกลุ่มหลังจากนั้น คุณใส่ aggregate ใน WHERE ไม่ได้
• aggregate มองข้าม NULL สำหรับ sum, avg, min, และ max ด้วยเช่นกัน ดังนั้นค่าเฉลี่ยจึงคิดเฉพาะจากค่าที่ไม่เป็น NULL เท่านั้น

🐘 GROUP BY ยุบ row และนั่นคือจุดประสงค์

หลังจาก GROUP BY คุณจะไม่มี row ต้นฉบับอีกต่อไป — คุณมีหนึ่ง row ต่อหนึ่งกลุ่ม ถ้าคุณพบว่าตัวเองอยากได้ทั้งยอดรวมของกลุ่มและ row แต่ละ row ในผลลัพธ์เดียวกัน นั่นคือสัญญาณให้หันไปหา window function ซึ่งครอบคลุมในบทเรียนสุดท้ายของโมดูลนี้

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
Aggregate ด้วย GROUP BY ใน PostgreSQL	ทำงานใกล้ข้อมูล ใช้ index/parallel worker ได้ ส่งข้อมูลผ่าน network น้อย	ต้องเขียน SQL ให้ถูกต้อง เช่นเข้าใจกฎของ GROUP BY/HAVING
ดึงทุก row มาสรุปในโค้ด application	เขียนโค้ดง่าย ปรับ logic ได้อิสระ	โอนข้อมูลเปลืองแบนด์วิดท์ ไม่ scale เมื่อ table โต

ข้อผิดพลาดที่พบบ่อย

• เลือก column ที่ไม่ใช่ aggregate และไม่อยู่ใน GROUP BY — PostgreSQL เข้มงวดกว่าฐานข้อมูลบางตัว จะปฏิเสธและโยน error ทันที ไม่เดาค่าให้เหมือนบางระบบ
• GROUP BY บน expression ที่ไม่มี index บน table ขนาดใหญ่ — ทำให้ PostgreSQL ต้อง scan ทั้ง table ทุกครั้ง ถ้า query แบบนี้รันบ่อย ควรพิจารณาสร้าง expression index
• สับสนระหว่าง WHERE กับ HAVING — WHERE กรอง row ก่อนจัดกลุ่ม ส่วน HAVING กรองกลุ่มหลัง aggregate เอา aggregate function ใส่ใน WHERE ไม่ได้เด็ดขาด

💡 ตัวอย่างจากของจริง

E-commerce dashboard — รายงานยอดขายต่อวันหรือต่อสินค้าใช้ GROUP BY สรุปยอดตรงใน PostgreSQL แทนที่จะดึง row ระดับ transaction ทุก row มาบวกเองใน backend

SaaS usage reporting — รายงาน usage ต่อ tenant (เช่นจำนวน API call ต่อเดือน) คำนวณด้วย GROUP BY tenant_id ในฐานข้อมูลโดยตรง ทำให้ scale ได้แม้ tenant และ row จะเพิ่มขึ้นเรื่อย ๆ

ความต่างระหว่าง count(*) กับ count(some_column) คืออะไร?

เหมือนกันทุกประการ count(*) นับทุก row ส่วน count(some_column) นับเฉพาะ row ที่ column นั้นไม่เป็น NULL count(*) เร็วกว่าแต่แม่นยำน้อยกว่า count(some_column) นับค่าที่ไม่ซ้ำกันโดยอัตโนมัติ

clause ใดกรองกลุ่มโดยอิงจากผลลัพธ์ของ aggregate?

WHERE HAVING GROUP BY ORDER BY

column ที่ไม่ผ่าน aggregate ปรากฏใน SELECT ของคุณแต่ไม่อยู่ใน GROUP BY จะเกิดอะไรขึ้น?

PostgreSQL เดาค่าให้ PostgreSQL โยน error ออกมา column นั้นถูกทิ้งไปอย่างเงียบ ๆ ถูกนับเหมือนเป็น count(*)

Submit

Mark as complete