วัน: 17 กันยายน 2018

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

หากคุณเคยใช้ MySQL และคุ้นเคยกับการใช้ตัวแปร (Variables) เพื่อลดความซ้ำซ้อนของโค้ด ในระบบ PostgreSQL มีเครื่องมือที่ทรงพลังกว่าและเหมาะสมกว่าในการจัดการสถานการณ์นี้ นั่นคือ Common Table Expressions (CTEs) หรือการใช้คำสั่ง WITH

💡 ทำไมต้องใช้ CTE ใน PostgreSQL?

ในทางเทคนิคแล้ว การคำนวณค่าใดค่าหนึ่งใน SQL และนำค่าที่ได้ไปใช้ในส่วนอื่นของ Query (เช่น การคำนวณคะแนนรวม แล้วนำคะแนนรวมนั้นไปเปรียบเทียบเงื่อนไขเกรด) หากเราเขียนโค้ดแบบเดิม เราจะต้องเขียนสูตรคำนวณคะแนนรวมซ้ำในทุกๆ ส่วนของ SELECT และ CASE WHEN ซึ่งไม่เพียงแต่ทำให้โค้ดดูยุ่งเหยิง แต่ยังเสี่ยงต่อข้อผิดพลาดในการบำรุงรักษาด้วย

CTE (Common Table Expression) คือการสร้างชุดผลลัพธ์ชั่วคราว (Temporary Result Set) ที่มีขอบเขตจำกัดภายใน Query นั้นๆ ทำให้เราสามารถคำนวณค่าที่ซับซ้อนเพียงครั้งเดียว และเรียกใช้ค่าที่คำนวณได้นั้นซ้ำๆ ในส่วนที่เหลือของ Query ได้อย่างสะอาดตาและมีประสิทธิภาพสูง

🛠️ ตัวอย่างการใช้ CTE คำนวณคะแนนรวม (Grading System)

สมมุติว่าเรามีตารางเก็บคะแนนนักเรียน (schoolreport) และเราต้องการคำนวณคะแนนรวม (Total Score) จากคะแนนเก็บ (50%), กลางภาค (20%), และปลายภาค (30%) จากนั้นใช้คะแนนรวมนี้ในการตัดเกรด (Grade) การใช้ CTE จะช่วยให้เราแยกขั้นตอนการคำนวณออกจากขั้นตอนการตัดสินใจได้ชัดเจน

เราจะใช้โครงสร้าง WITH เพื่อสร้างชุดข้อมูลชั่วคราวที่คำนวณคะแนนรวมไว้ก่อน:

SQL

-- 1. สร้าง CTE เพื่อคำนวณคะแนนรวมเพียงครั้งเดียว (Total Score) ภายในชุดข้อมูลชั่วคราวที่ชื่อว่า 'calculated_scores' </code>
WITH calculated_scores AS (
    SELECT
        studen_id,
        course_id,
        accumulatedScore,
        midtermScore,
        finalScore,
        -- สูตรคำนวณคะแนนรวม: (A/100)*50 + (M/100)*20 + (F/100)*30
        ((accumulatedScore * 50.0 / 100.0) + 
         (midtermScore * 20.0 / 100.0) + 
         (finalScore * 30.0 / 100.0)) AS total_score
    FROM schoolreport
) 
-- 2. SELECT จาก CTE ที่สร้างไว้ เพื่อนำ total_score ไปใช้ในการตัดสินใจตัดเกรด </code>
SELECT
    studen_id,
    course_id,
    accumulatedScore,
    midtermScore,
    finalScore,
    total_score,
    CASE
        WHEN total_score < 50 THEN 'F'
        WHEN total_score <= 60 THEN 'D'
        WHEN total_score <= 70 THEN 'C'
        ELSE 'A'
    END AS grade
FROM calculated_scores
ORDER BY studen_id, course_id;

✨ ข้อดีที่ได้จาก CTE

• ความสะอาดของโค้ด (Readability): โค้ดถูกแบ่งเป็นขั้นตอนที่ชัดเจน (Step 1: คำนวณ, Step 2: ตัดเกรด) ทำให้ง่ายต่อการทำความเข้าใจและแก้ไข
• ประสิทธิภาพ (Efficiency): ระบบฐานข้อมูลสามารถจัดการการคำนวณค่าที่ซับซ้อนใน CTE ได้อย่างมีประสิทธิภาพ ทำให้ไม่ต้องคำนวณสูตรเดิมซ้ำๆ ในทุกเงื่อนไข
• ความยืดหยุ่น: สามารถใช้ CTE ซ้อนกัน (Nested CTEs) เพื่อจัดการกับปัญหาการคำนวณที่ซับซ้อนยิ่งขึ้นได้

✅ บทสรุป

การใช้ Common Table Expressions (CTE) ใน PostgreSQL เป็นแนวทางปฏิบัติที่ดีที่สุด (Best Practice) สำหรับการจัดการกับ Query ที่ต้องมีการคำนวณค่าหลายขั้นตอน หรือการนำค่าที่คำนวณได้ไปใช้ในการเปรียบเทียบเงื่อนไขต่างๆ การเรียนรู้การใช้ WITH จะช่วยยกระดับทักษะการเขียน SQL ของคุณให้เป็นระดับ Senior Architect ที่สามารถออกแบบ Query ที่ไม่เพียงแค่ทำงานได้ แต่ยังทำงานได้อย่างมีโครงสร้างและอ่านง่ายด้วย