SQL Deep Dive：掌握窗口功能，常見表表達式（CTE）和存儲過程

 SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_department_salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

 WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, eh.level 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

 CREATE PROCEDURE get_employee_details(IN emp_id INT)
BEGIN
    SELECT 
        e.employee_id,
        e.first_name,
        e.last_name,
        d.department_name
    FROM 
        employees e
    JOIN 
        departments d ON e.department_id = d.department_id
    WHERE 
        e.employee_id = emp_id;
END;

 SELECT 
    product_id,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM 
    sales;

 WITH top_sellers AS (
    SELECT product_id, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY product_id
    ORDER BY total_sales DESC
    LIMIT 10
)
SELECT * FROM top_sellers;

 CALL get_employee_details(1);

 SELECT 
    product_id,
    sale_date,
    sale_amount,
    LAG(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS previous_sale,
    LEAD(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS next_sale
FROM 
    sales;

 WITH RECURSIVE category_hierarchy AS (
    SELECT category_id, parent_category_id, 0 AS level
    FROM categories
    WHERE parent_category_id IS NULL
    UNION ALL
    SELECT c.category_id, c.parent_category_id, ch.level 1
    FROM categories c
    JOIN category_hierarchy ch ON c.parent_category_id = ch.category_id
),
product_categories AS (
    SELECT p.product_id, ch.category_id, ch.level
    FROM products p
    JOIN category_hierarchy ch ON p.category_id = ch.category_id
)
SELECT * FROM product_categories;

 CREATE PROCEDURE update_employee_salary(IN emp_id INT, IN new_salary DECIMAL(10, 2))
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    UPDATE employees
    SET salary = new_salary
    WHERE employee_id = emp_id;
    COMMIT;
END;

 -- 錯誤示例SELECT 
    employee_id,
    salary,
    AVG(salary) -- 缺少OVER 子句FROM 
    employees;

 -- 錯誤示例WITH top_sellers AS (
    SELECT product_id -- 缺少total_sales 列FROM sales
    GROUP BY product_id
    ORDER BY total_sales DESC
    LIMIT 10
)
SELECT * FROM top_sellers;

 -- 錯誤示例CREATE PROCEDURE update_employee_salary(IN emp_id INT, IN new_salary DECIMAL(10, 2))
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE employee_id = emp_id;
END;

 -- 優化示例SELECT 
    product_id,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM 
    sales;

 -- 優化示例WITH sales_summary AS (
    SELECT product_id, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT * FROM sales_summary;

 -- 優化示例CREATE PROCEDURE update_employee_salaries()
BEGIN
    UPDATE employees
    SET salary = salary * 1.1;
END;

 -- 最佳實踐示例SELECT 
    e.employee_id AS emp_id, -- 使用有意義的別名e.first_name, -- 註釋代碼e.last_name,
    d.department_name -- 保持代碼的可讀性和可維護性FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id;