Window Functions & Recursive CTEs (WITH RECURSIVE vs Oracle/MSSQL implementations)

ในโลกของการวิเคราะห์ข้อมูลขนาดใหญ่ (Big Data) และการจัดการฐานข้อมูลที่ซับซ้อน การดึงข้อมูลออกมาให้ได้เพียงแค่แถวและคอลัมน์ตามปกติของ JOIN นั้นไม่เพียงพออีกต่อไป นักพัฒนาจำเป็นต้องมีเครื่องมือที่สามารถมองเห็นความสัมพันธ์เชิงบริบท (Contextual Relationships) ระหว่างชุดข้อมูลจำนวนมากได้อย่างแม่นยำ ความท้าทายเหล่านี้ผลักดันให้นักพัฒนามองลึกเข้าไปในฟังก์ชันขั้นสูงของภาษา SQL เพื่อให้ได้มาซึ่งมุมมองข้อมูลที่สมบูรณ์และมีมิติมากขึ้น

Window Functions: การคำนวณแบบมีบริบท (Contextual Calculation)

Window Functions คือหัวใจสำคัญของการวิเคราะห์ข้อมูลระดับสูง ฟังก์ชันเหล่านี้ช่วยให้เราสามารถคำนวณค่าทางสถิติ (เช่น ค่าเฉลี่ย, ค่าสะสม, ลำดับที่) โดยอ้างอิงจากชุดแถวที่เกี่ยวข้อง (Window) โดยไม่จำเป็นต้องยุบรวมข้อมูล (Aggregate) เหมือนกับการใช้ GROUP BY สิ่งนี้ทำให้เราได้ผลลัพธ์ระดับแถว (Row-level result) ที่ยังคงรักษาข้อมูลต้นฉบับไว้ แต่เพิ่มมิติการคำนวณเข้าไปด้วย

ตัวอย่างการใช้งานที่พบบ่อยคือการหาค่าเฉลี่ยของยอดขายในกลุ่มสินค้าเดียวกัน แต่ยังคงแสดงยอดขายของสินค้าแต่ละรายการไว้ในแถวเดิม

Recursive CTEs: การจัดการข้อมูลแบบลำดับชั้น (Hierarchical Data)

สำหรับโครงสร้างข้อมูลที่มีความสัมพันธ์แบบลำดับชั้น (เช่น โครงสร้างองค์กร, รายการส่วนประกอบสินค้า) การใช้ JOIN แบบปกติจะทำได้ยากและซับซ้อน Recursive Common Table Expressions (CTEs) หรือ `WITH RECURSIVE` คือกลไกที่ทรงพลังที่สุดในการจัดการปัญหานี้ CTE จะทำงานโดยการเรียกซ้ำ (Recursion) ระหว่าง Anchor Member (จุดเริ่มต้น) และ Recursive Member (การเรียกซ้ำ) จนกว่าจะถึง Termination Condition (เงื่อนไขการหยุด)

ตัวอย่างการใช้งาน PostgreSQL (PostgreSQL Syntax)

เราจะสาธิตการใช้ทั้งสองฟังก์ชันด้วยไวยากรณ์มาตรฐานของ PostgreSQL ซึ่งเป็นระบบที่รองรับฟีเจอร์เหล่านี้ได้อย่างเต็มที่

1. Window Function Example (Ranking)

ตัวอย่างการหาอันดับพนักงานตามเงินเดือนในแต่ละแผนก:

SQL

SELECT
    employee_name, 
    department, 
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
    employees;

2. Recursive CTE Example (Hierarchy Traversal)

ตัวอย่างการไล่ลำดับโครงสร้างองค์กร (Department Hierarchy):

SQL

WITH RECURSIVE department_hierarchy AS (
    -- Anchor Member: จุดเริ่มต้น
    SELECT department_id, department_name, 0 AS level
    FROM departments
    WHERE department_id = 1

    UNION ALL

    -- Recursive Member: การเรียกซ้ำ
    SELECT d.department_id, d.department_name, h.level + 1
    FROM departments d
    JOIN department_hierarchy h ON d.parent_id = h.department_id
) 
SELECT * FROM department_hierarchy;

บทสรุปสำหรับสถาปนิกข้อมูล

การทำความเข้าใจ Window Functions และ Recursive CTEs ไม่ใช่แค่การรู้ไวยากรณ์ SQL แต่คือการเปลี่ยนมุมมองในการคิดเชิงโครงสร้างข้อมูล (Data Modeling Thinking) สำหรับ Senior Architect การใช้เครื่องมือเหล่านี้อย่างเชี่ยวชาญจะช่วยให้คุณสามารถเขียนโค้ดที่ไม่ได้เพียงแค่ดึงข้อมูล แต่ยังสามารถวิเคราะห์ความสัมพันธ์เชิงลึก (Deep Contextual Analysis) และจัดการกับความซับซ้อนของข้อมูลแบบลำดับชั้นได้อย่างมีประสิทธิภาพสูงสุด การฝึกฝนการใช้งานบน PostgreSQL จะช่วยให้คุณเป็นผู้เชี่ยวชาญที่สามารถแก้ไขปัญหาข้อมูลที่ซับซ้อนที่สุดได้