Orcale SQL 优化 ：查找平均薪资最高的部门

Orcale SQL 优化 查找平均薪资最高的部门

前言
Orcale 11g Scott 用户下 emp表， 查找平均薪资最高的部门，SQL 优化方案

在本文中，我们将探讨如何使用 SQL 查询来找出平均薪资最高的部门。我们有三种实现方式，接下来会分析每种方式的优缺点以及性能表现。

1. 使用 FOR 循环查询

DECLARE
    v_dept_no NUMBER; -- Highest average salary department number
    v_dept_avg_sal NUMBER := -1; -- Initialize department average salary to -1
BEGIN
 
    -- 查询所有部门及其平均薪资，按薪资排序
    FOR dept_info IN (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) LOOP
        -- 如果当前部门的薪资高于当前最高的平均薪资
        IF dept_info.avg_sal > v_dept_avg_sal THEN
            -- 更新最高薪资的部门信息
            v_dept_no := dept_info.deptno;
            v_dept_avg_sal := dept_info.avg_sal;
        END IF;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Highest average salary department: ' || v_dept_no || ', Salary: ' || ROUND(v_dept_avg_sal, 2));
END;

2. 使用 ROWNUM 查询

DECLARE
    v_dept_no NUMBER;
    v_dept_avg_sal NUMBER;
BEGIN
  
    -- 使用 ROWNUM 查询最优部门
    SELECT deptno, avg_sal
    INTO v_dept_no, v_dept_avg_sal
    FROM (
        SELECT deptno, ROUND(AVG(sal), 2) AS avg_sal
        FROM emp
        GROUP BY deptno
        ORDER BY avg_sal DESC
    ) a
    WHERE rownum = 1;

    
    DBMS_OUTPUT.PUT_LINE('Highest average salary department: ' || v_dept_no || ', Salary: ' || ROUND(v_dept_avg_sal, 2));
END;

3. 使用窗口函数 ROW_NUMBER()

DECLARE
    v_dept_no NUMBER;
    v_dept_avg_sal NUMBER;
BEGIN
 
    -- 使用窗口函数 ROW_NUMBER() 查询最优部门
    SELECT deptno, avg_sal
    INTO v_dept_no, v_dept_avg_sal
    FROM (
        SELECT deptno,
               ROUND(AVG(sal), 2) AS avg_sal,
               ROW_NUMBER() OVER (ORDER BY AVG(sal) DESC) AS rn
        FROM emp
        GROUP BY deptno
    )
    WHERE rn = 1;

    
    DBMS_OUTPUT.PUT_LINE('Highest average salary department: ' || v_dept_no || ', Salary: ' || ROUND(v_dept_avg_sal, 2));
END;

输入结果 都是

总结：哪种方式最优化？

1. 性能最优：第三种写法（窗口函数 ROW_NUMBER()）
   第三种写法通常是最优的选择，特别是在大数据量的情况下。ROW_NUMBER() 窗口函数通过一次扫描来计算所有部门的薪资，并为每个部门分配一个序号，然后只选择排名第一的部门。这个方法避免了额外的排序和循环操作，具有很好的性能表现。

2. 可读性和简洁性：第二种写法（ROWNUM）
   第二种写法使用了 ROWNUM，该方法相对简洁，但其缺点是缺乏灵活性。ROWNUM 在某些情况下可能会引发排序问题，尤其是在复杂查询中，可能需要额外的排序步骤。此外，它的性能不如窗口函数，尤其是在数据量较大的时候。

3. 不推荐的方式：第一种写法（FOR 循环）
   第一种写法使用了 FOR 循环，这在 SQL 查询中并不常见，且效率较低。它需要逐行检查每个部门的平均薪资，且每次都要与当前最高薪资进行比较。由于这种方法是基于数据库外部的循环实现，它违背了 SQL 的批量处理思想，不仅可读性差，而且效率较低。

结论
综合来看，第三种写法（窗口函数 ROW_NUMBER()） 是性能最优且可扩展的方式，尤其适用于大数据量的查询。第二种写法（ROWNUM）虽然简单，但灵活性较差，适合小规模数据。而第一种写法（FOR 循环）则是最不推荐的方式，因为它违背了 SQL 批量处理的理念，效率较低。