MySQL สร้าง Function หาค่าเฉลี่ย (AVG) ของคอลัมน์ตามเงื่อนไข

CREATE FUNCTION คืนค่า Average แบบ Conditional

MySQL User-Defined Function (UDF) สามารถ Return ค่า Scalar ได้ ใช้ DETERMINISTIC เพื่อบอก Optimizer ว่า Same Input = Same Output

ตัวอย่าง: หาค่าเฉลี่ย Marks ของ John

CREATE TABLE DemoTable638 (Name VARCHAR(100), Marks INT);

INSERT INTO DemoTable638 VALUES
('John', 67), ('John', 90), ('David', 99), ('John', 60);

สร้าง Function

SET GLOBAL log_bin_trust_function_creators = 1;  -- ต้องการสำหรับ Binary Logging

DELIMITER //
CREATE FUNCTION getAverageDemo()
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE avg_val INT;
    SELECT AVG(Marks) INTO avg_val 
    FROM DemoTable638 
    WHERE Name = 'John';
    RETURN avg_val;
END //
DELIMITER ;

เรียกใช้ Function

SELECT getAverageDemo();

ผลลัพธ์: 72 ( (67+90+60)/3 = 72.33 → INT = 72 )

Function Parameters (ยืดหยุ่นกว่า)

CREATE FUNCTION getAverageByName(p_name VARCHAR(100))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE avg_val DECIMAL(10,2);
    SELECT AVG(Marks) INTO avg_val 
    FROM DemoTable638 
    WHERE Name = p_name;
    RETURN COALESCE(avg_val, 0);
END;

การใช้งาน:

SELECT getAverageByName('John');   -- 72.33
SELECT getAverageByName('David');  -- 99.00
SELECT getAverageByName('Unknown'); -- 0.00

⚠️ ข้อจำกัดและ Best Practices

1. log_bin_trust_function_creators = 1 - จำเป็นถ้า Binary Logging เปิดอยู่
2. DETERMINISTIC / NO SQL / READS SQL DATA - ต้องระบุอย่างน้อย 1 ตัว
3. ไม่ควรใช้ใน WHERE/JOIN บนตารางใหญ่ - เรียกต่อ Row ช้ามาก
4. ไม่สามารถใช้ Dynamic SQL (PREPARE/EXECUTE) ใน Function
5. Transaction Control ไม่ได้ (ไม่มี COMMIT/ROLLBACK)

Performance: Function vs Inline Query

-- Function: เรียกต่อ Row (ช้า)
SELECT getAverageByName(name) FROM users;

-- Inline: Single Scan (เร็ว)
SELECT name, AVG(marks) OVER (PARTITION BY name) FROM scores;

-- หรือ Group By
SELECT name, AVG(marks) FROM scores GROUP BY name;

MySQL 8.0: Window Functions แทน Function

SELECT 
    Name,
    Marks,
    AVG(Marks) OVER (PARTITION BY Name) AS avg_marks
FROM DemoTable638;