ฟังก์ชันหน้าต่าง (Window functions): ROW_NUMBER, RANK, SUM OVER

คำนวณอันดับ, ยอดสะสม และการเปรียบเทียบภายในกลุ่มโดยไม่ยุบแถว ด้วย OVER และ PARTITION BY

10 นาที · 3 คำถาม

GROUP BY ยุบแถวต่าง ๆ ให้เป็นกลุ่ม ส่วน ฟังก์ชันหน้าต่าง (window function) คำนวณค่าการรวมกลุ่มหรืออันดับ โดยยังคงเก็บทุกแถวไว้ มันคือเครื่องมือสำหรับหา top N ต่อกลุ่ม, ยอดสะสม และการเปรียบเทียบกับค่าเฉลี่ยของทีมคุณเอง มีให้ใช้ใน SQLite ตั้งแต่เวอร์ชัน 3.25 (2018) จงโหลดชุดข้อมูล

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);

ฟังก์ชันจัดอันดับสามตัว มีพฤติกรรมสามแบบเมื่อเจอ ค่าเสมอกัน (ties) โดย ROW_NUMBER ให้หมายเลขโดยไม่ซ้ำกัน (ตัดสินค่าเสมอแบบสุ่ม), RANK ให้อันดับเดียวกันแก่ค่าที่เสมอกันแล้ว ข้าม อันดับ (1, 2, 2, 4), ส่วน DENSE_RANK ไม่ข้าม (1, 2, 2, 3) คำสั่ง WINDOW ช่วยหลีกเลี่ยงการเขียนนิยามหน้าต่างซ้ำ (รองรับโดย SQLite และ PostgreSQL แต่ไม่รองรับโดย SQL Server)

SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);

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

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;

ฟังก์ชันการรวมกลุ่มแบบคลาสสิกจะกลายเป็นหน้าต่างด้วย OVER โดย SUM(salary) OVER (ORDER BY ...) จะสร้าง ยอดสะสม (running total) ทีละแถว จงระบุกรอบ (frame) ด้วย ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW เพื่อให้ได้ยอดสะสมแบบทีละแถวอย่างเคร่งครัด

SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_total
FROM employees;

ทดสอบความรู้

ตรวจสอบว่าคุณจำประเด็นสำคัญของบทเรียนนี้ได้ครบถ้วน

1. จากเงินเดือน 5200, 4800, 4800, 4100 ฟังก์ชัน RANK() ให้อันดับใดแก่ 4100?
   • 3
   • 4
   • 2
2. ทำไมคุณจึงต้องใช้ CTE เพื่อกรองด้วย ROW_NUMBER()?
   • OVER ถูกประเมินหลัง WHERE จึงห้ามใช้ภายใน WHERE
   • CTE มีประสิทธิภาพมากกว่าเสมอ
   • ROW_NUMBER ทำงานได้ภายใน WITH เท่านั้น
3. PARTITION BY dept ทำอะไรในคำสั่ง OVER?
   • มันเรียงลำดับผลลัพธ์สุดท้ายตามแผนก
   • มันเริ่มการคำนวณหน้าต่างใหม่สำหรับแต่ละแผนก
   • มันผสานแถวให้เหลือแถวเดียวต่อแผนก