บทที่ 3 — Aggregation, Subquery และ Window Function

← บทที่ 2 | สารบัญ | บทที่ 4 →

หลังจบบท คุณจะ:

• ใช้ GROUP BY + HAVING ได้คล่อง
• เข้าใจ aggregate function (COUNT, SUM, AVG, MIN, MAX)
• ใช้ Subquery ใน SELECT / FROM / WHERE
• ใช้ CTE (WITH) แทน subquery ซ้อน ๆ
• ใช้ Window Function (ranking, running total, lag/lead)
• เขียน Recursive CTE (อ่าน รี-เคอร์-ซิฟ ซี-ที-อี) สำหรับ tree (= โครงสร้างต้นไม้ / ข้อมูลแบบลำดับชั้น)

---

Part 1: Aggregate Function

1. Functions พื้นฐาน

Aggregation (อ่าน แอก-เกร-เก-ชั่น = "การรวบยอด") = การสรุปหลายแถวให้เหลือค่าเดียว เช่น นับจำนวน, หาผลรวม, ค่าเฉลี่ย

aggregate function สรุปหลายแถวเป็นค่าเดียว — COUNT (นับ), SUM (รวม), AVG (เฉลี่ย), MIN/MAX, STRING_AGG (อ่าน สตริง-แอก = ต่อ string), STDDEV (อ่าน เอส-ที-ดี-เดฟ = standard deviation / ส่วนเบี่ยงเบนมาตรฐาน) จุดที่พลาดบ่อยคือพฤติกรรมกับ NULL: COUNT(*) นับทุกแถว แต่ COUNT(column)/SUM/AVG จะข้าม NULL — เข้าใจตรงนี้กันคำนวณผิด:

SELECT 
    COUNT(*) AS total_rows,
    COUNT(email) AS rows_with_email,         -- ข้ามแถวที่เป็น NULL
    COUNT(DISTINCT category) AS unique_cats,
    SUM(price) AS total_revenue,
    AVG(price) AS avg_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    STDDEV(price) AS price_stddev,           -- ส่วนเบี่ยงเบนมาตรฐาน (ข้ามได้ถ้าไม่เคยเรียนสถิติ)
    STRING_AGG(name, ', ') AS all_names      -- ต่อ string หลายแถวเข้าด้วยกัน
FROM products;

⚠️ STDDEV ใน PostgreSQL = alias ของ STDDEV_SAMP (sample stddev — หารด้วย n-1) ไม่ใช่ population stddev ถ้าจะใช้แบบ population ใช้ STDDEV_POP (หารด้วย n) — บาง DB (MySQL, SQL Server) STDDEV แปลเป็น population stddev ให้ระวังตอนพอร์ตข้าม DB

⚠️ NULL กับ Aggregate

• COUNT(*) — นับทุก row (รวม NULL)
• COUNT(column) — นับเฉพาะ NOT NULL
• SUM/AVG — ignore NULL ใน calculation

SELECT AVG(age) FROM users;
-- ถ้า 100 user, 60 มี age, 40 NULL
-- AVG จะคำนวณจาก 60 — ไม่ใช่ ÷ 100

---

2. GROUP BY — แบ่งกลุ่ม

GROUP BY แบ่งแถวเป็นกลุ่มตามค่าของ column แล้วคำนวณ aggregate ต่อกลุ่ม (เช่น จำนวน product ต่อ category) — กฎสำคัญที่มือใหม่ติดบ่อยคือ ทุก column ใน SELECT ที่ไม่ใช่ aggregate ต้องอยู่ใน GROUP BY (หรือเป็น window function) ไม่งั้น PostgreSQL error ทันที (MySQL ใน mode default ปล่อยผ่านแต่ผลลัพธ์ไม่แน่นอน — strict mode ใหม่จะ error เหมือน PG):

-- จำนวน product ต่อ category
SELECT category, COUNT(*) AS total
FROM products
GROUP BY category;

-- ผล:
-- category │ total
-- ─────────┼──────
-- BOOK     │ 50
-- TOY      │ 30
-- TECH     │ 20

กฎ — column ใน SELECT ต้อง:

1. อยู่ใน GROUP BY, หรือ
2. เป็น aggregate function

-- ❌ name ไม่ได้ group + ไม่ใช่ aggregate
SELECT category, name, COUNT(*) FROM products GROUP BY category;

-- ✅ 1. group ด้วย name
SELECT category, name, COUNT(*) FROM products GROUP BY category, name;

-- ✅ 2. ใช้ aggregate
SELECT category, MIN(name), COUNT(*) FROM products GROUP BY category;
-- ⚠️ MIN(name) คืน "ชื่อแรกตามอักษร" ในกลุ่ม (a < b < c ...) ไม่ใช่ "ชื่อตัวแทน"
--    เหมาะแค่ตอนต้องการแสดงค่า arbitrary หนึ่งค่า — ส่วนใหญ่ไม่ใช่สิ่งที่ user อยากเห็นจริง ๆ

---

3. Multiple GROUP BY

GROUP BY หลาย column สร้างกลุ่มย่อยตาม "ทุก combination" ของค่าเหล่านั้น — เช่น group ตาม user + เดือน ได้ยอดขายของแต่ละคนแยกรายเดือน เป็นพื้นฐานของรายงานแบบ pivot:

DATE_TRUNC('month', x) = ตัดค่า x (timestamp) ให้เหลือ "ต้นเดือน" เช่น 2026-05-18 14:32:00 → 2026-05-01 00:00:00 — ใช้สำหรับ group by เดือน ('day' = ต้นวัน, 'year' = ต้นปี ก็มี)

-- ยอดขายต่อ user ต่อเดือน
SELECT 
    user_id,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY user_id, DATE_TRUNC('month', created_at)
ORDER BY user_id, month;

---

4. HAVING — filter หลัง GROUP BY

จุดที่มือใหม่สับสนคือความต่างของ WHERE กับ HAVING — WHERE กรองแถว "ก่อน" group, HAVING กรองกลุ่ม "หลัง" group (ใช้กับ aggregate ได้ เช่น "เอาเฉพาะ user ที่ order > 5 ครั้ง") การเข้าใจลำดับ execution ทำให้เขียน query ซับซ้อนได้ถูก:

WHERE = filter ก่อน group
HAVING = filter หลัง group

-- User ที่ order > 5 ครั้ง
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

ลำดับ execution

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

-- เฉพาะ order ที่ paid แล้ว — group by user — เอาที่ revenue > 1000
SELECT user_id, SUM(total) AS revenue
FROM orders
WHERE status = 'PAID'                 -- filter row ก่อน group
GROUP BY user_id
HAVING SUM(total) > 1000;             -- filter group หลัง group

---

5. ROLLUP, CUBE, GROUPING SETS — รายงานหลายระดับ

🚩 โซนขั้นสูง — ข้ามได้รอบแรก ถ้าเพิ่งเริ่ม ROLLUP/CUBE/GROUPING SETS ใช้ตอนทำรายงาน/dashboard จริง ไม่ใช่ query ทั่วไป

รายงานจริงมักต้องการทั้งยอดย่อยและยอดรวมในตารางเดียว — ROLLUP เพิ่ม subtotal/grand total, CUBE เพิ่มทุก combination, GROUPING SETS เลือกเองว่าต้องการระดับไหน ใช้ทำ dashboard/รายงานโดยไม่ต้อง query หลายรอบ:

-- ยอดต่อ category + ยอดรวม
SELECT category, SUM(price) AS total
FROM products
GROUP BY ROLLUP(category);

-- ผล:
-- category │ total
-- ─────────┼──────
-- BOOK     │ 500
-- TOY      │ 300
-- TECH     │ 200
-- NULL     │ 1000   ← grand total

-- CUBE = ทุกคู่ combination (รวม "อย่างใดอย่างหนึ่งเป็น NULL" และ "ทั้งคู่เป็น NULL")
SELECT category, brand, SUM(price) AS total
FROM products
GROUP BY CUBE(category, brand);

-- ผลลัพธ์ (สมมุติ):
-- category │ brand │ total
-- ─────────┼───────┼──────
-- BOOK     │ X     │ 200    ← BOOK + brand X
-- BOOK     │ Y     │ 300    ← BOOK + brand Y
-- TOY      │ X     │ 100    ← TOY + brand X
-- BOOK     │ NULL  │ 500    ← total BOOK ทุก brand
-- TOY      │ NULL  │ 100    ← total TOY ทุก brand
-- NULL     │ X     │ 300    ← total brand X ทุก category
-- NULL     │ Y     │ 300    ← total brand Y ทุก category
-- NULL     │ NULL  │ 600    ← grand total ทั้งหมด

ใช้ใน reporting / dashboard

---

Part 2: Subquery

6. Subquery ใน WHERE

subquery คือ query ซ้อนใน query — ใน WHERE ใช้กรองด้วยผลของอีก query (เช่น "user ที่ order มากกว่าค่าเฉลี่ย") subquery ที่คืนค่าเดียวใช้เปรียบเทียบได้ ส่วนที่คืน list ใช้กับ IN/NOT IN ส่วนนี้แสดง pattern ที่ใช้บ่อย:

เริ่มจากง่ายสุดก่อน — subquery ที่คืน "list" ให้ IN

อ่าน subquery จาก ในออกนอก เสมอ — รัน query ในวงเล็บก่อน ได้ผลออกมา แล้ว query นอกค่อยใช้ผลนั้นต่อ ตัวอย่างพื้นฐานสุด:

-- subquery ข้างใน: ดึง user_id ทุกคนที่เคยมี order → ได้ list เช่น (1, 2, 5)
-- query นอก: เอา user ที่ id อยู่ใน list นั้น = "user ที่เคย order"
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

ไต่ขึ้นทีละชั้น — เติมเงื่อนไขจำนวน order

-- ขั้นถัดมา: อยากได้เฉพาะ user ที่ order "เกิน 5 ครั้ง"
-- → ใน subquery group ตาม user_id แล้วใช้ HAVING นับจำนวน
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders
    GROUP BY user_id
    HAVING COUNT(*) > 5
);

ชั้นสุดท้าย — เทียบกับ "ค่าเฉลี่ยจำนวน order ต่อคน"

ทีนี้ถ้าอยากเปลี่ยน 5 เป็น "ค่าเฉลี่ยจำนวน order ต่อ user" ก็ต้องคำนวณค่าเฉลี่ยนั้น ซึ่งเองก็ต้องซ้อนอีกชั้น → กลายเป็น subquery 3 ชั้น
วิธีอ่านที่ง่ายที่สุด: แตกเป็น 3 query เล็ก ๆ ก่อน แล้วค่อยประกอบกัน

Step 1 — นับ order ต่อ user:

SELECT user_id, COUNT(*) AS c
FROM orders
GROUP BY user_id;
-- ผล:
-- user_id │ c
-- ────────┼───
-- 1       │ 3
-- 2       │ 8
-- 3       │ 5

Step 2 — หาค่าเฉลี่ยของ c (เอาผล Step 1 มาเป็น "ตารางชั่วคราว" ชื่อ t):

SELECT AVG(c) FROM (
    SELECT COUNT(*) AS c FROM orders GROUP BY user_id
) AS t;
-- ผล:
-- avg
-- ─────
-- 5.33   ← เฉลี่ย order ต่อคน

Step 3 — เอา 5.33 ไปแทนที่ 5 ในตัวอย่างก่อนหน้า → กลายเป็น subquery 3 ชั้น:

SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders 
    GROUP BY user_id 
    HAVING COUNT(*) > (SELECT AVG(c) FROM (
        SELECT COUNT(*) c FROM orders GROUP BY user_id
    ) AS t)
);

อ่านจากในสุดออกมา (เหมือนแกะหัวหอม):

1. ในสุด SELECT COUNT(*) c FROM orders GROUP BY user_id — นับ order ต่อ user ได้ค่าหนึ่งค่าต่อคน เช่น 3, 8, 5, ... ตั้ง alias c (ย่อจาก count) เพื่อให้ชั้นนอกเรียกใช้ได้
2. ชั้นกลาง SELECT AVG(c) FROM (...) AS t — เอาผลชั้นในมาเป็น "ตารางชั่วคราว" ชื่อ t (table ชั่วคราวต้องมีชื่อเสมอ จึงตั้ง AS t) แล้วหาค่าเฉลี่ยของ c = 5.33
3. ชั้นนอก เลือก user ที่จำนวน order (COUNT(*)) มากกว่าค่าเฉลี่ยนั้น (มากกว่า 5.33)

💡 c และ t เป็นแค่ "ชื่อเล่น" (alias) ที่เราตั้งเอง จะตั้งชื่ออื่นก็ได้ เช่น cnt, tmp — มันมีไว้ให้ชั้นนอกอ้างถึง subquery ข้างใน
💡 ถ้าซ้อนแบบนี้แล้วงง — บทนี้มี CTE (WITH) ที่เขียนแบบเดียวกันแต่อ่านง่ายกว่ามาก (ดูข้อ 10)

Subquery ที่ return 1 ค่า

SELECT name, price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

IN / NOT IN

-- user ที่ "เคย" order
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- user ที่ "ไม่เคย" order
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

⚠️ NOT IN + NULL จะ break — ใช้ NOT EXISTS แทน

---

7. EXISTS / NOT EXISTS

-- user ที่ "เคย" order
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- user ที่ "ไม่เคย" order
SELECT * FROM users u 
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

💡 ทำไม SELECT 1? EXISTS สนใจแค่ว่า "มีแถวหรือไม่" ไม่สนค่า → ใส่อะไรก็ได้ใน SELECT ของ subquery (SELECT 1, SELECT *, SELECT column ผลเหมือนกันหมด) นิยมเขียน SELECT 1 เป็น convention เพื่อสื่อว่า "ค่าไม่สำคัญ"

EXISTS vs IN (เวอร์ชันสมัยใหม่):

• NULL safety: NOT IN พังเมื่อ subquery มี NULL (return ผลผิด); NOT EXISTS ไม่พัง — เลือกใช้ NOT EXISTS เป็น default ปลอดภัยกว่า
• Performance: PG 12+ optimizer มัก rewrite IN/EXISTS เป็น semi-join เหมือนกัน → performance ใกล้เคียงในส่วนใหญ่ (คำพูด "EXISTS เร็วกว่าเสมอ" เป็น myth เก่า)
• อ่านง่าย: IN อ่านง่ายกว่าเมื่อ list เล็ก/static (WHERE id IN (1, 2, 3)); EXISTS ดีกว่าเมื่อ subquery มี correlation กับ outer query

---

8. Subquery ใน FROM (Derived Table)

subquery ใช้ใน FROM ได้ด้วย — ผลลัพธ์ของ query กลายเป็น "ตารางชั่วคราว" ให้ query ภายนอกใช้ต่อ เหมาะกับการ aggregate 2 ชั้น (เช่น คำนวณ revenue ต่อ user ก่อน แล้วเฉลี่ยต่อ category):

-- average revenue per category
-- ใช้ schema จากบทที่ 2: orders → order_items → products (order_items เป็น junction)
SELECT category, AVG(revenue) AS avg_revenue
FROM (
    SELECT p.category, o.user_id, SUM(oi.quantity * oi.price_at_purchase) AS revenue
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    GROUP BY p.category, o.user_id
) AS user_category_revenue
GROUP BY category;

ใช้เมื่อต้อง aggregate 2 ชั้น

---

9. Correlated Subquery — อ้างถึง outer query

Correlated (อ่าน คอ-เร-เลต-เท็ด = "เชื่อมโยง/อ้างอิงกัน") = subquery ที่ "อ้างถึง" ค่าจาก query ภายนอก

correlated subquery ต่างจาก subquery ทั่วไปตรงที่มัน "อ้างถึงค่าจาก outer query" จึงต้อง execute ใหม่ทุกแถว (เช่น "product ที่แพงกว่าค่าเฉลี่ยใน category ของตัวเอง") — ทรงพลังแต่ช้า ส่วนใหญ่ window function ทำได้เร็วกว่า (ดูด้านล่าง):

-- product ที่แพงกว่า average ใน category ของตัวเอง
SELECT name, category, price
FROM products p1
WHERE price > (
    SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category
);

⚠️ ช้า — execute subquery ทุก row → ใช้ window function แทน (ด้านล่าง)

---

Part 3: CTE — Common Table Expression

10. WITH clause — readable + reusable

WITH user_revenue AS (
    SELECT user_id, SUM(total) AS revenue
    FROM orders
    WHERE status = 'PAID'
    GROUP BY user_id
)
SELECT u.name, ur.revenue
FROM users u
JOIN user_revenue ur ON u.id = ur.user_id
WHERE ur.revenue > 1000;

ข้อดี:

• อ่านง่าย (top-down แทน nested subquery)
• Reuse ได้ใน query เดียวกัน
• Optimizer มัก inline → optimize ได้ดี

⚠️ CTE materialization (PG 12+) เปลี่ยนพฤติกรรม สำคัญสำหรับ tuning:

• PG ≤ 11: CTE ถูก materialize เสมอ (เก็บผลลัพธ์ใน temp ก่อนใช้) → เป็น "optimization fence" ตัด optimizer ออก
• PG 12+: CTE ถูก inline เป็น default (เหมือน subquery) → optimizer ทำงานได้เต็มที่ ถ้าอยาก force ให้ materialize เหมือนเดิม ใส่ WITH foo AS MATERIALIZED (...) หรือ AS NOT MATERIALIZED เพื่อบังคับ inline

Multiple CTE

WITH 
paid_orders AS (
    SELECT * FROM orders WHERE status = 'PAID'
),
user_revenue AS (
    SELECT user_id, SUM(total) AS revenue FROM paid_orders GROUP BY user_id
),
top_users AS (
    SELECT user_id FROM user_revenue WHERE revenue > 1000
)
SELECT u.name 
FROM users u 
JOIN top_users tu ON u.id = tu.user_id;

---

11. Recursive CTE — สำหรับ Tree/Hierarchy

🚩 โซนขั้นสูง — ข้ามได้ ถ้าเพิ่งเริ่ม recursive CTE เป็นเรื่องที่ค่อยกลับมาอ่านตอนเจอข้อมูลแบบลำดับชั้น (org chart, comment thread) จริง ๆ ได้ — ไม่ได้ใช้ใน query ทั่วไป

ข้อมูลแบบลำดับชั้น (org chart, comment thread, category tree) มีความลึกไม่แน่นอน — SQL ปกติ query ไม่ได้ recursive CTE แก้ด้วยการให้ query "เรียกตัวเอง" ไล่จาก root ลงไปทีละชั้นจนสุด (base case + recursive case) เป็นวิธีมาตรฐานในการ traverse tree ใน SQL:

💡 อ่านเอาภาพรวมพอ — recursive CTE คือ query ที่ "เรียกตัวเอง" ทำซ้ำหลายรอบ ใช้ traverse tree:

1. Base case — เริ่มจากแถวระดับบนสุด (เช่น CEO ที่ไม่มี manager)
2. Recursive case — JOIN ผลรอบก่อนกับ table เดิมเพื่อหา "ลูก" → ทำซ้ำจนไม่มีลูกเหลือ
3. UNION ALL ต่อผลทั้งสองชุดเข้าด้วยกัน
4. || คือ operator ต่อ string ใน PostgreSQL (เช่น 'a' || 'b' = 'ab') — ไม่ใช่ "or" เหมือนภาษาอื่น (ดูข้อควรระวังในบทที่ 1)

-- Employee hierarchy
WITH RECURSIVE employee_tree AS (
    -- Base case: top-level employees (CEO ที่ manager_id เป็น NULL)
    SELECT id, name, manager_id, 1 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: หา children ของแถวที่อยู่ใน employee_tree แล้ว
    -- et.path || ' > ' || e.name = ต่อ string เช่น 'Alice' || ' > ' || 'Bob' = 'Alice > Bob'
    SELECT e.id, e.name, e.manager_id, et.level + 1, et.path || ' > ' || e.name
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree ORDER BY path;
-- หมายเหตุ: ORDER BY path เรียงตาม "ตัวอักษร" (lexicographic) → Bob มาก่อน Carol
-- ถ้าชื่อเปลี่ยน ลำดับจะเปลี่ยน (depends on names)

-- ผล:
-- id │ name        │ level │ path
-- ───┼─────────────┼───────┼────────────────────────
-- 1  │ Alice (CEO) │ 1     │ Alice (CEO)
-- 2  │ Bob         │ 2     │ Alice (CEO) > Bob
-- 4  │ Dan         │ 3     │ Alice (CEO) > Bob > Dan
-- 5  │ Emma        │ 3     │ Alice (CEO) > Bob > Emma
-- 3  │ Carol       │ 2     │ Alice (CEO) > Carol

ใช้กับ:

• Comment thread (reply ของ reply)
• Category tree
• Folder structure
• Org chart

---

Part 4: Window Function — ⭐ เครื่องมือพลิกเกม (Game Changer)

🚩 โซนขั้นสูง — ข้ามได้รอบแรก Window function ทรงพลังมากสำหรับงาน analytics/รายงาน แต่ไม่จำเป็นต้องเก่งตั้งแต่แรก ถ้าเพิ่งหัด SQL อ่านผ่าน ๆ ให้รู้ว่า "มีของแบบนี้อยู่" พอ แล้วค่อยกลับมาเจาะตอนต้องทำ ranking / running total / เทียบเดือนต่อเดือนจริง ๆ

12. ทำไม Window Function

ปัญหาของ GROUP BY คือมัน "ยุบ" แถวหายไป — เห็นแค่ค่าสรุป ไม่เห็นแถวเดิม window function แก้ตรงนี้: คำนวณ aggregate (เฉลี่ย, ranking, running total) "โดยไม่ยุบแถว" ทำให้เห็นทั้งข้อมูลรายแถวและค่าสรุปพร้อมกัน เป็นเครื่องมือที่เปลี่ยนวิธีเขียน analytics query ไปเลย:

ปัญหา: เห็น row + aggregate พร้อมกัน

-- ❌ aggregate ใน GROUP BY → row หายไป
SELECT category, AVG(price) FROM products GROUP BY category;
-- ได้: BOOK | 15, TOY | 25 — แต่ไม่เห็น product แต่ละตัว

-- ✅ Window function → เห็น product + average
SELECT 
    name, category, price,
    AVG(price) OVER (PARTITION BY category) AS avg_in_category,
    price - AVG(price) OVER (PARTITION BY category) AS diff
FROM products;

-- ผล:
-- name    │ category │ price │ avg_in_category │ diff
-- ────────┼──────────┼───────┼─────────────────┼──────
-- Book A  │ BOOK     │ 10    │ 15              │ -5
-- Book B  │ BOOK     │ 20    │ 15              │ +5
-- Toy A   │ TOY      │ 25    │ 25              │ 0

Window Function = "aggregate without collapse rows" (= สรุปค่าโดยไม่ยุบแถวหาย — เห็นทั้งรายแถวและค่าสรุปพร้อมกัน)

---

13. Syntax

window function ทุกตัวใช้โครงสร้าง OVER(...) เหมือนกัน — PARTITION BY แบ่งกลุ่ม (เหมือน GROUP BY แต่ไม่ยุบแถว), ORDER BY กำหนดลำดับใน partition, และ frame (ROWS BETWEEN) กำหนดช่วงที่คำนวณ จำโครงนี้ได้แล้วใช้ได้ทุก window function:

function() OVER (
    PARTITION BY column      -- แบ่งกลุ่ม (เหมือน GROUP BY แต่ไม่ยุบแถว)
    ORDER BY column          -- ลำดับใน partition
    ROWS BETWEEN ...         -- frame: ช่วงแถวที่นำมาคำนวณ
)

ตัวอย่าง PARTITION BY ทำอะไรกับแถว — ก่อน vs หลัง

ลองดูตาราง products สมมุติ:

products (input):
name    │ category │ price
────────┼──────────┼──────
Book A  │ BOOK     │ 10
Book B  │ BOOK     │ 20
Toy A   │ TOY      │ 25
Toy B   │ TOY      │ 35

SELECT name, category, price,
       AVG(price) OVER (PARTITION BY category) AS avg_in_cat
FROM products;

ผลลัพธ์ (PARTITION BY category):
name    │ category │ price │ avg_in_cat
────────┼──────────┼───────┼────────────
Book A  │ BOOK     │ 10    │ 15           ← เฉลี่ยใน BOOK = (10+20)/2
Book B  │ BOOK     │ 20    │ 15
Toy A   │ TOY      │ 25    │ 30           ← เฉลี่ยใน TOY = (25+35)/2
Toy B   │ TOY      │ 35    │ 30

→ แต่ละแถวยังอยู่ครบ + เพิ่มค่าเฉลี่ย "ของกลุ่มตัวเอง" มาด้วย

Frame: ROWS vs RANGE vs GROUPS (⚠️ จุดที่พลาดบ่อย)

เมื่อใส่ ORDER BY ใน OVER แต่ไม่ระบุ frame ชัดเจน PostgreSQL ใช้ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW เป็น default

Frame mode	ความหมาย
ROWS BETWEEN ... AND ...	นับเป็น "จำนวนแถว" — แม่นตรงตัว แถว = แถว
RANGE BETWEEN ... AND ... (default)	นับเป็น "ช่วงค่า" — แถวที่ ORDER BY value เท่ากัน (peers) จะถูกรวมหมด
GROUPS BETWEEN ... AND ... (PG 11+)	นับเป็น "จำนวนกลุ่ม peers"

⚠️ กับดัก default RANGE: ถ้ามีแถวที่ค่า ORDER BY ซ้ำกัน (เช่น 2 orders timestamp เดียวกัน) frame จะกินทุก peer พร้อมกัน → running total อาจ "กระโดด" ไม่ค่อย ๆ ขึ้น ถ้าต้องการแถวต่อแถวจริง ๆ ใส่ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ชัดเจน

---

14. Ranking — ROW_NUMBER, RANK, DENSE_RANK

ranking function จัดอันดับแถวภายในแต่ละ partition — ต่างกันตรงการจัดการค่าเสมอ: ROW_NUMBER (ไม่ซ้ำเลย), RANK (เสมอได้อันดับเดียวกันแล้วข้าม), DENSE_RANK (เสมอแล้วไม่ข้าม) ใช้บ่อยกับโจทย์ "top N ต่อกลุ่ม":

SELECT 
    name, category, price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
    RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

-- ผล (ในแต่ละ category):
-- name    │ price │ row_num │ rank │ dense_rank
-- ────────┼───────┼─────────┼──────┼────────────
-- Book A  │ 30    │ 1       │ 1    │ 1
-- Book B  │ 20    │ 2       │ 2    │ 2
-- Book C  │ 20    │ 3       │ 2    │ 2    ← rank ซ้ำ
-- Book D  │ 15    │ 4       │ 4    │ 3    ← RANK ข้าม, DENSE_RANK ต่อเนื่อง

Top N per Group

-- 3 product ที่แพงสุดของแต่ละ category
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

---

15. Running Total + Moving Average

ด้วย ORDER BY + frame ใน window เราคำนวณค่าสะสมได้ — running total (ยอดสะสมเรื่อย ๆ) และ moving average (เฉลี่ยเคลื่อนที่ N วันล่าสุด) เป็น query ที่ analytics/dashboard ต้องใช้ตลอด:

-- ยอดสะสมต่อ user
-- ⚠️ ไม่ระบุ frame → default = RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- ถ้ามี 2 orders ที่ created_at เท่ากัน peers จะถูกรวมพร้อมกัน (running total กระโดด)
-- ป้องกัน: ใส่ ROWS แทน RANGE
SELECT 
    user_id, created_at, total,
    SUM(total) OVER (
        PARTITION BY user_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- per-row strict
    ) AS running_total
FROM orders;

-- moving average 7 days
SELECT 
    date, sales,
    AVG(sales) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma7
FROM daily_sales;

---

16. LAG / LEAD — เปรียบเทียบกับ row อื่น

LAG/LEAD ดึงค่าจากแถว "ก่อนหน้า" หรือ "ถัดไป" มาเทียบกับแถวปัจจุบัน — เหมาะกับการหา delta/trend (เช่น ยอดเดือนนี้ต่างจากเดือนก่อนเท่าไร) โดยไม่ต้อง self-join ที่ยุ่งและช้ากว่า:

-- เปรียบเทียบกับ order ก่อนหน้า
SELECT 
    user_id, created_at, total,
    LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total,
    total - LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS diff
FROM orders;

-- ผล:
-- user_id │ created_at │ total │ prev_total │ diff
-- ────────┼────────────┼───────┼────────────┼──────
-- 1       │ 2026-01-01 │ 100   │ NULL       │ NULL
-- 1       │ 2026-01-15 │ 150   │ 100        │ +50
-- 1       │ 2026-02-01 │ 120   │ 150        │ -30

• LAG(col) — row ก่อน
• LEAD(col) — row หลัง
• LAG(col, 2) — ก่อน 2 row

ใช้ทำ: trend analysis, delta, gap

---

17. NTILE — แบ่ง quartile/percentile

NTILE (อ่าน เอ็น-ไทล์) = แบ่งแถวเป็น N กลุ่มเท่า ๆ กัน (N-tile = N กอง)

NTILE แบ่งแถวเป็น N กลุ่มเท่า ๆ กันตามลำดับ — เช่น NTILE(4) แบ่ง user เป็น 4 quartile ตาม revenue เหมาะกับ segmentation/cohort analysis (กลุ่ม top 25%, bottom 25% ฯลฯ):

-- แบ่ง user เป็น 4 กลุ่มตาม revenue
SELECT 
    user_id, revenue,
    NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
FROM user_revenue;

-- quartile 1 = top 25% (rich)
-- quartile 4 = bottom 25%

ใช้ทำ: cohort analysis, segmentation

---

18. FIRST_VALUE / LAST_VALUE

FIRST_VALUE/LAST_VALUE ดึงค่าแรกหรือค่าสุดท้ายใน partition — เช่น ราคาแรกกับราคาล่าสุดของสินค้า ⚠️ จุดที่พลาดบ่อยคือ LAST_VALUE ต้องระบุ frame UNBOUNDED FOLLOWING ไม่งั้นได้แค่ค่าถึงแถวปัจจุบัน:

💡 UNBOUNDED PRECEDING = ตั้งแต่แถวแรกของ partition; UNBOUNDED FOLLOWING = ถึงแถวสุดท้าย → frame กินทั้ง partition
💡 Default frame เมื่อมี ORDER BY คือ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — เป็นเหตุที่ LAST_VALUE ไม่ระบุ frame จะได้ "ค่าของแถวปัจจุบัน" (ไม่ใช่ค่าสุดท้ายของ partition) ต้องเขียน UNBOUNDED FOLLOWING ชัดเจน

-- ราคาแรก + ล่าสุดของแต่ละ product
-- ใช้ pattern subquery + DISTINCT ON (PG-specific) อ่านง่ายและไม่ต้องพึ่ง DISTINCT + window
SELECT DISTINCT ON (product_id)
    product_id,
    FIRST_VALUE(price) OVER w AS first_price,
    LAST_VALUE(price)  OVER w AS last_price
FROM price_history
WINDOW w AS (
    PARTITION BY product_id 
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

-- แบบเดิมที่ใช้ DISTINCT (อาจ duplicate ถ้า frame ของ first/last ต่างกัน) — ไม่แนะนำ:
-- SELECT DISTINCT product_id,
--     FIRST_VALUE(price) OVER (PARTITION BY product_id ORDER BY date) AS first_price,
--     LAST_VALUE(price) OVER (
--         PARTITION BY product_id ORDER BY date 
--         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
--     ) AS last_price
-- FROM price_history;

---

19. ตัวอย่างเต็ม — Sales Analytics

ปิดท้ายด้วยการรวมทุกอย่างในบท (CTE + GROUP BY + window function) เป็น analytics query จริง — รายงานยอดขายรายเดือนต่อ user พร้อม ranking และ growth ตัวอย่างนี้แสดงว่าเครื่องมือทั้งหมดประกอบกันตอบคำถามธุรกิจซับซ้อนได้ใน query เดียว:

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', o.created_at) AS month,
        u.id AS user_id,
        u.name,
        SUM(o.total) AS revenue
    FROM orders o
    JOIN users u ON o.user_id = u.id
    WHERE o.status = 'PAID'
    GROUP BY DATE_TRUNC('month', o.created_at), u.id, u.name   -- explicit ดีกว่า GROUP BY 1, 2, 3
)
SELECT 
    month,
    name,
    revenue,
    
    -- รายงานในแต่ละเดือน
    RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS rank_in_month,
    
    -- เปรียบเทียบเดือนก่อน
    LAG(revenue) OVER (PARTITION BY user_id ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (PARTITION BY user_id ORDER BY month) AS mom_change,
    
    -- ยอดสะสม
    SUM(revenue) OVER (PARTITION BY user_id ORDER BY month) AS cumulative,
    
    -- % ของเดือน
    revenue * 100.0 / SUM(revenue) OVER (PARTITION BY month) AS pct_of_month
FROM monthly_sales
ORDER BY month, rank_in_month;

---

20. ⚠️ Common Pitfalls

Pitfall	แก้
Column ใน SELECT ไม่อยู่ใน GROUP BY	ใส่ใน GROUP BY หรือ aggregate
WHERE กับ aggregate	ใช้ HAVING
COUNT(*) กับ JOIN multiply	COUNT(DISTINCT id)
NOT IN กับ NULL	ใช้ NOT EXISTS
Subquery ซ้อนเยอะ → อ่านไม่ออก	ใช้ CTE
Top N per group ด้วย LIMIT	ใช้ ROW_NUMBER()
AVG(price) ที่ category	ใช้ AVG OVER (PARTITION BY category) แทน correlated subquery

---

21. Checkpoint

🛠️ Checkpoint 3.1 — Sales Report
schema: sales(id, product_id, user_id, amount, sold_at)
เขียน query:

1. ยอดขายต่อเดือนต่อ product
2. Top 3 products ของแต่ละเดือน
3. Month-over-month growth ต่อ product
4. Running total ของแต่ละ product

🛠️ Checkpoint 3.2 — Recursive Tree
สร้าง schema categories(id, name, parent_id) (hierarchical)

• Insert tree 3 ระดับ
• Query: list categories พร้อม path (e.g., "Electronics > Phone > iPhone")
• Query: count products ต่อ category รวม sub-category

🛠️ Checkpoint 3.3 — Cohort Analysis
จาก users(id, signup_at) + orders(user_id, created_at, total):

• Cohort by signup month
• Retention: % ของ cohort ที่ order ในเดือนที่ 1, 2, 3 หลัง signup

---

22. สรุปบท

✅ Aggregate: COUNT, SUM, AVG, MIN, MAX, STRING_AGG
✅ GROUP BY + HAVING (filter หลัง group)
✅ ลำดับ execution: FROM → WHERE → GROUP → HAVING → SELECT → ORDER → LIMIT
✅ Subquery ใน WHERE / FROM / SELECT
✅ EXISTS > IN/NOT IN (กับ NULL safety + performance)
✅ CTE (WITH) > nested subquery — อ่านง่ายกว่า
✅ Recursive CTE สำหรับ tree/hierarchy
✅ Window Function = aggregate without collapse — game changer
✅ ROW_NUMBER / RANK / DENSE_RANK / LAG / LEAD / NTILE — top N per group, trend, cohort

---

← บทที่ 2 | บทที่ 4 → Indexing + Performance