我可以使用窗口函数在 SQL 查询中分组吗? [英] Can I group by in SQL query with window function?
问题描述
我需要他们部门中薪水最低的员工我是用反连接做的.
I need to get employees with smallest salary in their departments I did it using anti join.
select emp.employee_id,emp.last_name,emp.salary,emp.department_id
from employees emp
left join employees sml
on sml.department_id = emp.department_id and sml.salary < emp.salary
where sml.employee_id is null and emp.department_id is not null
但有人告诉我可以使用窗口函数使用一个选择来做到这一点.但是我不能按部门 ID 对其进行分组并同时使用它.这是错误还是我愚蠢?
But I've been told that it's possible to do it using window function using one select. However I can't group it by department_id and use it at the same time. Is that a bug or me being stupid?
SELECT department_id,
min(salary) OVER (partition by department_id) as minsalary
FROM employees;
GROUP BY department_id
SQL Developer 说 00979. 00000 - 不是 GROUP BY 表达式"
SQL Developer says 00979. 00000 - "not a GROUP BY expression"
推荐答案
如果您在没有 group by 的情况下运行第二个查询 - 您可能已经尝试过，从您发布的内容中的额外分号- 您会看到每个员工都有一行，每个员工都显示其部门的最低工资.这个最小值是解析 min()，因为它有一个 window 子句.PARTITION BY 相当于 GROUP BY，但没有对整个结果集进行聚合.
If you run your second query without the group by - which you may have already tried, from the extra semicolon in what you posted - you'll see that you get one row for every employee, each showing the minimum salary in their department. That minimum is the analytic min() because it has a window clause. The PARTITION BY is the equivalent of a GROUP BY, but without the aggregation over the whole result set.
获得相同结果(几乎)的最简单方法是改用 RANK() 分析函数，它根据您提供的分区和顺序对值进行排名，同时允许平局:
The simplest way to get the same result (almost) is to use the RANK() analytic function instead, which ranks the values based on the partition and order you supply, while allowing for ties:
SELECT employee_id, last_name, salary, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary) AS rnk
FROM employees
ORDER BY department_id, rnk;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID RNK
----------- ------------------------- ---------- ------------- ----------
200 Whalen 4400 10 1
202 Fay 6000 20 1
201 Hartstein 13000 20 2
119 Colmenares 2500 30 1
118 Himuro 2600 30 2
117 Tobias 2800 30 3
116 Baida 2900 30 4
115 Khoo 3100 30 5
114 Raphaely 11000 30 6
...
102 De Haan 17000 90 1
101 Kochhar 17000 90 1
100 King 24000 90 3
...
对于部门 20 和 30，您可以看到排名 1 的行是最低工资.对于部门 90，有两名员工排名 1，因为他们的工资最低.
For departments 20 and 30 you can see the row ranked 1 is the lowest salary. For department 90 there are two employees ranked 1, because they have the same lowest salary.
您可以将其用作内联视图并仅选择排名第 1 的行:
You can use that as an inline view and select just those rows ranked number 1:
SELECT employee_id, last_name, salary, department_id
FROM (
SELECT employee_id, last_name, salary, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary) AS rnk
FROM employees
)
WHERE rnk = 1
ORDER BY department_id;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen 4400 10
202 Fay 6000 20
119 Colmenares 2500 30
203 Mavris 6500 40
132 Olson 2100 50
107 Lorentz 4200 60
204 Baer 10000 70
173 Kumar 6100 80
101 Kochhar 17000 90
102 De Haan 17000 90
113 Popp 6900 100
206 Gietz 8300 110
178 Grant 7000
13 rows selected.
如果您不必担心领带问题，还有一个更简单的选择，但在这里不合适.
If you didn't have to worry about ties there is an even simpler alternative, but it ins't appropriate here.
请注意，这比原始查询多出一行.您正在通过 sml.department_id = emp.department_id 加入.如果部门 ID 为 null，就像员工 178 的情况一样，则该连接将失败，因为您无法使用相等测试将 null 与 null 进行比较.由于此解决方案没有联接，因此不适用，并且您会在结果中看到该员工.
Notice that this gives you one more row than your original query. You are joining on sml.department_id = emp.department_id. If the department ID is null, as it is for employee 178, that join fails because you can't compare null to null with equality tests. Because this solution doesn't have a join, that doesn't apply, and you see that employee in the results.
这篇关于我可以使用窗口函数在 SQL 查询中分组吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
