Window function

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

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

OVER clause

คุณเปลี่ยนฟังก์ชันธรรมดาให้กลายเป็น window function โดยการเพิ่ม OVER clause ภายในนั้น PARTITION BY แบ่ง row ออกเป็นกลุ่ม (เหมือน GROUP BY แต่ไม่ยุบ row ทิ้ง) และ ORDER BY กำหนดลำดับภายในแต่ละ partition ที่นี่เราใส่หมายเลขให้ยอดขายภายในแต่ละภูมิภาค โดยเรียงจากมากสุดก่อน

SELECT region, copies,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY copies DESC) AS seq
FROM sales
ORDER BY region, seq;

const res = await pool.query(`
  SELECT region, copies,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY copies DESC) AS seq
  FROM sales
  ORDER BY region, seq
`);

cur.execute("""
    SELECT region, copies,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY copies DESC) AS seq
    FROM sales
    ORDER BY region, seq
""")
rows = cur.fetchall()

rows, err := conn.Query(ctx, `
  SELECT region, copies,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY copies DESC) AS seq
  FROM sales
  ORDER BY region, seq`)

let rows = sqlx::query(
    "SELECT region, copies,
            ROW_NUMBER() OVER (PARTITION BY region ORDER BY copies DESC) AS seq
     FROM sales
     ORDER BY region, seq",
)
.fetch_all(&pool)
.await?;

 region | copies | seq
--------+--------+-----
 East   |    900 |   1
 East   |    200 |   2
 West   |    400 |   1
 West   |    350 |   2

การใส่หมายเลขเริ่มต้นใหม่ที่ 1 ภายในแต่ละภูมิภาคเพราะ PARTITION BY region ทุก row ของยอดขายยังคงอยู่ครบ — ไม่มีอะไรถูกยุบ ตรงกันข้ามกับ GROUP BY ที่จะเหลือเพียง row เดียวต่อหนึ่งภูมิภาค

ROW_NUMBER, RANK, และ DENSE_RANK

ฟังก์ชันจัดอันดับสามตัวนี้ล้วนเรียงลำดับ row ภายใน partition แต่ต่างกันที่วิธีจัดการกับค่าที่เสมอกัน ROW_NUMBER ให้หมายเลขที่ไม่ซ้ำกันเสมอ RANK ให้ row ที่เสมอกันได้หมายเลขเดียวกันแล้วกระโดดข้ามไปข้างหน้า DENSE_RANK ให้ row ที่เสมอกันได้หมายเลขเดียวกันแต่ไม่กระโดดข้าม

SELECT copies,
       ROW_NUMBER()  OVER (ORDER BY copies DESC) AS row_num,
       RANK()        OVER (ORDER BY copies DESC) AS rnk,
       DENSE_RANK()  OVER (ORDER BY copies DESC) AS dense
FROM sales
ORDER BY copies DESC;

const res = await pool.query(`
  SELECT copies,
         ROW_NUMBER() OVER (ORDER BY copies DESC) AS row_num,
         RANK()       OVER (ORDER BY copies DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY copies DESC) AS dense
  FROM sales
  ORDER BY copies DESC
`);

cur.execute("""
    SELECT copies,
           ROW_NUMBER() OVER (ORDER BY copies DESC) AS row_num,
           RANK()       OVER (ORDER BY copies DESC) AS rnk,
           DENSE_RANK() OVER (ORDER BY copies DESC) AS dense
    FROM sales
    ORDER BY copies DESC
""")
rows = cur.fetchall()

rows, err := conn.Query(ctx, `
  SELECT copies,
         ROW_NUMBER() OVER (ORDER BY copies DESC) AS row_num,
         RANK()       OVER (ORDER BY copies DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY copies DESC) AS dense
  FROM sales
  ORDER BY copies DESC`)

let rows = sqlx::query(
    "SELECT copies,
            ROW_NUMBER() OVER (ORDER BY copies DESC) AS row_num,
            RANK()       OVER (ORDER BY copies DESC) AS rnk,
            DENSE_RANK() OVER (ORDER BY copies DESC) AS dense
     FROM sales
     ORDER BY copies DESC",
)
.fetch_all(&pool)
.await?;

 copies | row_num | rnk | dense
--------+---------+-----+-------
    900 |       1 |   1 |     1
    400 |       2 |   2 |     2
    400 |       3 |   2 |     2
    200 |       4 |   4 |     3
    150 |       5 |   5 |     4
    50  |       6 |   6 |     5

ดูสอง row ของ 400 สิ ROW_NUMBER ให้เป็น 2 กับ 3, RANK เรียกทั้งคู่ว่า 2 แล้วกระโดดไป 4, ส่วน DENSE_RANK เรียกทั้งคู่ว่า 2 แล้วไปต่อที่ 3 เลือกตัวที่พฤติกรรมต่อค่าเสมอกันตรงกับที่คุณต้องการ

ยอดสะสมด้วย SUM() OVER

เพิ่ม ORDER BY ภายใน OVER ให้กับ aggregate แล้วจะกลายเป็นการคำนวณแบบสะสม คือแต่ละ row จะเห็นทั้งตัวเองบวกกับทุก row ที่อยู่ก่อนหน้าตามลำดับ นี่คือวิธีมาตรฐานในการผลิตยอดสะสม

SELECT region, copies,
       SUM(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS running
FROM sales
ORDER BY region, copies DESC;

const res = await pool.query(`
  SELECT region, copies,
         SUM(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS running
  FROM sales
  ORDER BY region, copies DESC
`);

cur.execute("""
    SELECT region, copies,
           SUM(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS running
    FROM sales
    ORDER BY region, copies DESC
""")
rows = cur.fetchall()

rows, err := conn.Query(ctx, `
  SELECT region, copies,
         SUM(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS running
  FROM sales
  ORDER BY region, copies DESC`)

let rows = sqlx::query(
    "SELECT region, copies,
            SUM(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS running
     FROM sales
     ORDER BY region, copies DESC",
)
.fetch_all(&pool)
.await?;

 region | copies | running
--------+--------+---------
 East   |    900 |     900
 East   |    200 |    1100
 West   |    400 |     400
 West   |    350 |     750

column running สะสมลงไปในแต่ละภูมิภาคและรีเซ็ตที่ขอบเขตของ partition หากไม่มี ORDER BY ภายใน OVER คำสั่ง SUM(copies) OVER (PARTITION BY region) จะกลับไปแสดงยอดรวมทั้งหมดของภูมิภาคซ้ำในทุก row แทน — เทคนิคที่มีประโยชน์สำหรับการแสดงยอดขายแต่ละรายการเคียงข้างยอดรวมของภูมิภาค

window เก็บ row ไว้ ส่วน GROUP BY ยุบทิ้ง

ภาพด้านล่างเปรียบเทียบทั้งสองอย่าง GROUP BY region จะคืนสอง row หนึ่ง row ต่อหนึ่งภูมิภาค ส่วน window function แบ่ง partition จาก row ชุดเดียวกันแต่คืนกลับมาครบทุก row โดยแต่ละ row พกค่าประจำ partition ติดมาด้วย

flowchart TD
  R[All sales rows] --> P1[Partition East]
  R --> P2[Partition West]
  P1 --> E1[East row keeps itself plus its window value]
  P1 --> E2[East row keeps itself plus its window value]
  P2 --> W1[West row keeps itself plus its window value]
  P2 --> W2[West row keeps itself plus its window value]

window แบ่ง partition row แต่เก็บไว้ทุก row

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

window function ไม่เคยลดจำนวน row ถ้าคุณต้องการหนึ่ง row ต่อหนึ่งกลุ่ม ให้ใช้ GROUP BY ถ้าคุณต้องการทุก row พร้อมตัวเลขต่อกลุ่ม ให้ใช้ window
PARTITION BY ใส่หรือไม่ก็ได้ ถ้าไม่ใส่ ผลลัพธ์ทั้งหมดจะนับเป็น partition เดียว การจัดอันดับจึงรันข้ามทุก row
ORDER BY ภายใน OVER ควบคุม window frame และเป็นอิสระจาก ORDER BY สุดท้ายของ query เพิ่มตัวด้านนอกเข้าไปด้วยถ้าคุณสนใจลำดับที่ row ถูกคืนกลับมา
คุณใช้ window function ใน WHERE clause ไม่ได้ เพราะ window ถูกคำนวณหลัง WHERE ให้ห่อ query ไว้ใน CTE หรือ subquery แล้วกรองบน column ที่คำนวณแล้วจากข้างนอก
เลือก ROW_NUMBER สำหรับลำดับที่เข้มงวด, RANK เมื่อค่าเสมอกันควรใช้อันดับร่วมกันและทิ้งช่องว่างไว้, และ DENSE_RANK เมื่อค่าเสมอกันใช้อันดับร่วมกันโดยไม่มีช่องว่าง

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

ตัวเลือก	Benefit	Cost
Window function (`ROW_NUMBER`, `RANK`, running total)	คำนวณในหนึ่ง query ไม่ต้อง self-join ให้ยุ่งยาก	ต้องเข้าใจ `PARTITION BY`/`ORDER BY` ให้ถูก ไม่งั้นผลลัพธ์ผิดแบบเงียบ ๆ
Self-join หรือ subquery แทน window function	ทำได้ในฐานข้อมูลที่ไม่รองรับ window function	เขียนยาวกว่า มักช้ากว่า และอ่านยากกว่า
คำนวณอันดับ/ยอดสะสมในโค้ด application	ปรับ logic เองได้อิสระ	ต้องดึงทุก row มาที่ backend ก่อน ไม่ scale กับผลลัพธ์ก้อนใหญ่

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

ลืมใส่ PARTITION BY — ถ้าละไว้ ผลลัพธ์ทั้งหมดจะกลายเป็น partition เดียว อันดับหรือยอดสะสมจะรันข้ามทั้งผลลัพธ์แทนที่จะรันแยกต่อกลุ่มตามที่ตั้งใจ
สับสนระหว่าง RANK() กับ DENSE_RANK() และ ROW_NUMBER() — RANK() ทิ้งช่องว่างของอันดับไว้หลังค่าที่เสมอกัน DENSE_RANK() ไม่ทิ้งช่องว่าง ส่วน ROW_NUMBER() ให้เลขไม่ซ้ำกันเสมอแม้ค่าจะเท่ากัน เลือกให้ตรงกับพฤติกรรมที่ต้องการ
ไม่เข้าใจลำดับการประมวลผลของ query — window function ทำงาน หลัง GROUP BY/aggregate ในลำดับตรรกะของ query ดังนั้นจึงใช้ window function ใน WHERE โดยตรงไม่ได้ ต้องห่อด้วย subquery หรือ CTE ก่อนแล้วค่อยกรองชั้นนอก

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

Discord — activity feed และการจัดอันดับผู้ใช้ที่ active ที่สุดในแต่ละ channel ใช้ ROW_NUMBER()/RANK() แบบ PARTITION BY channel โดยตรงใน SQL แทนที่จะดึงทุกข้อความมาจัดอันดับในโค้ด

GitLab — leaderboard และรายงานสรุป contribution ต่อผู้ใช้ในแต่ละ project คำนวณอันดับด้วย window function ในฐานข้อมูล ทำให้ query เดียวได้ทั้งรายละเอียดและอันดับโดยไม่ต้อง self-join