sql - 工资高于部门平均水平的员工 - 优化

Question

我们的数据库中只有一个名为 EMPLOYEESALARY 的表，其中包含以下 3 列:

Employee_ID, Employee_Salary, Department_ID

现在我必须选择薪水高于其部门平均水平的每位员工。我该怎么做？

我知道这是一个重复的问题，但我发现的最佳解决方案是:

SELECT * from employee join (SELECT AVG(employee_salary) as sal, department_ID 
FROM employee GROUP BY Department_ID) as t1 
ON employee.department_ID = t1.department_ID 
where employee.employee_salary > t1.sal

我们可以进一步优化它并且不使用子查询吗？

---

引用:

SELECT every employee that has a higher salary than the AVERAGE of his department

Employees with higher salary than their department average?

---

在这里找到Schema，进行测试: SQL Fiddle

Answer 1

Can we do it without a subquery?

不是我能想到的。如果条件是 >= 那么下面的代码会起作用

SELECT TOP 1 WITH TIES *
FROM   employee
ORDER  BY CASE
            WHEN employee_salary >= AVG(employee_salary)
                                     OVER (
                                       PARTITION BY Department_ID) THEN 0
            ELSE 1
          END 

但是这不是优化，如果没有员工的薪水高于平均水平(即所有员工在一个部门有相同的薪水)

Can we optimize it further?

您可以使用

稍微缩短语法

WITH T AS
(
 SELECT *,
       AVG(employee_salary) OVER (PARTITION BY Department_ID)  AS sal
FROM   employee
)
SELECT *
FROM T
WHERE  employee_salary > sal

但它仍然需要做很多相同的工作。

假设基表上已经存在合适的索引，那么在 SELECT 时避免更多工作的唯一方法是预先计算分组的 SUM 和COUNT_BIG 在按 Department_ID 分组的索引 View 中(以允许廉价地导出平均值)。