Window functions คืออะไร?

Question

Accepted Answer

**Window functions** ทำการคำนวณข้ามชุดของแถว ("window") ที่ **สัมพันธ์กับแถวปัจจุบัน** *โดยไม่ยุบรวมเป็นแถวผลลัพธ์เดียว* (ต่างจาก aggregates ที่ใช้ `GROUP BY`) ฟังก์ชันเหล่านี้เปิดทางให้ทำ analytics ที่ทรงพลัง — running totals, การจัดอันดับ, การเปรียบเทียบกับแถวอื่น — ในขณะที่ยังคงแต่ละแถวไว้ในผลลัพธ์

## ความแตกต่างที่สำคัญจาก GROUP BY

```text
GROUP BY      → collapses groups into ONE row each (you lose individual rows)
Window function → computes across related rows but KEEPS every row
→ You get per-row detail AND aggregate/ranking info together.
```

## Ranking functions

```sql
SELECT
  name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,     -- unique sequential rank
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,  -- rank within dept
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
```

Clause `OVER (...)` กำหนด window **`PARTITION BY`** แบ่งแถวออกเป็นกลุ่ม (เหมือน GROUP BY แต่ทำต่อแถว) และ **`ORDER BY`** เรียงลำดับภายในแต่ละ partition `ROW_NUMBER`/`RANK`/`DENSE_RANK` กำหนดการจัดอันดับ

## Aggregate window functions (running totals, aggregates ต่อกลุ่ม)

```sql
SELECT
  date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,            -- cumulative sum
  AVG(amount) OVER (PARTITION BY category) AS category_avg,      -- avg per category, kept per row
  amount - AVG(amount) OVER (PARTITION BY category) AS diff_from_avg
FROM transactions;
```

Aggregates ในรูปแบบ window functions คำนวณ running totals, moving averages หรือ aggregates ต่อ partition ในขณะที่ยังคงแต่ละแถวไว้ — ซึ่งทำไม่ได้ด้วย GROUP BY เพียงอย่างเดียว

## LAG/LEAD — เปรียบเทียบกับแถวอื่น

```sql
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,             -- the previous row's value
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth,        -- month-over-month change
  LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;
```

**`LAG`/`LEAD`** เข้าถึงค่าจากแถวก่อนหน้า/ถัดไป — เหมาะอย่างยิ่งสำหรับการเปรียบเทียบช่วงเวลาต่อช่วงเวลา (การเติบโตแบบเดือนต่อเดือน เป็นต้น)

## ทำไมจึงสำคัญ

Window functions เป็นฟีเจอร์ SQL ขั้นสูงที่ทรงพลังซึ่งจำเป็นต่อ **query เชิงวิเคราะห์** และการเข้าใจมันเป็นความรู้ระดับ senior ที่มีคุณค่าสำหรับการวิเคราะห์ข้อมูล, การทำรายงาน และการ query ที่ซับซ้อน

ความสามารถที่เป็นเอกลักษณ์ — การคำนวณข้ามแถวที่สัมพันธ์กัน **ในขณะที่ยังคงทุกแถวไว้** (ต่างจาก GROUP BY ที่ยุบแถว) — เปิดทางให้ทำ analytics ทั้งกลุ่มที่ยากหรือเป็นไปไม่ได้ในวิธีอื่น: **การจัดอันดับ** (`ROW_NUMBER`/`RANK`/`DENSE_RANK` — การหา top N ต่อกลุ่ม, การกำหนดอันดับ), **running totals และ moving averages** (cumulative sums, aggregates ต่อ partition ที่แสดงข้างแต่ละแถว) และ **การเปรียบเทียบแถวต่อแถว** (`LAG`/`LEAD` สำหรับการวิเคราะห์ช่วงเวลาต่อช่วงเวลา เช่น การเติบโตแบบเดือนต่อเดือน)

สิ่งเหล่านี้เป็นความต้องการเชิงวิเคราะห์ที่พบบ่อยและมีคุณค่า (leaderboards, cumulative metrics, การวิเคราะห์แนวโน้ม, การเปรียบเทียบแต่ละแถวกับค่าเฉลี่ยของกลุ่ม) ที่ window functions จัดการได้อย่างสง่างามใน query เดียว ในขณะที่ SQL ก่อนยุค window functions ต้องใช้ self-joins หรือ subqueries ที่ซับซ้อน

การเข้าใจ syntax `OVER (PARTITION BY ... ORDER BY ...)` (การกำหนด window), ranking functions, aggregate window functions (running totals, aggregates ต่อ partition) และ `LAG`/`LEAD` (การเข้าถึงแถวอื่น) ครอบคลุมชุดเครื่องมือเชิงวิเคราะห์ที่ทรงพลัง

เนื่องจากการวิเคราะห์ข้อมูลและการทำรายงานที่ซับซ้อนเป็นการใช้งานที่สำคัญของ SQL และเนื่องจาก window functions เป็นวิธีสมัยใหม่ที่ทรงพลังในการแสดงการจัดอันดับ, การคำนวณแบบ running และการเปรียบเทียบแถว (มีความสามารถและสง่างามกว่าทางเลือกอื่นอย่างมาก) การเชี่ยวชาญ window functions จึงเป็นความรู้ที่มีคุณค่าและนำไปใช้บ่อยสำหรับ SQL เชิงวิเคราะห์ที่แยกแยะนักพัฒนาที่สามารถเขียน query ข้อมูลที่ซับซ้อนได้ — เป็นเครื่องหมายของทักษะ SQL ขั้นสูงและเป็นหัวข้อที่พบบ่อยในบทบาทและการสัมภาษณ์ที่เน้นข้อมูล