我可以在 SQL 查询中使用窗口函数进行分组吗？

Question

我需要让 他们部门中薪水最低的员工 我使用反加入来做到这一点。

      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

但是有人告诉我，可以 使用一个 select 使用窗口功能来做到这一点。但是我不能按 department_id 对它进行分组并同时使用它。这是一个错误还是我很愚蠢？

      SELECT  department_id,
     min(salary) OVER (partition by department_id)  as minsalary
     FROM employees;
     GROUP BY department_id

SQL Developer 说 00979. 00000 - “不是 GROUP BY 表达式”

原文由 E.Saraf 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

如果您在没有 group by 的情况下运行第二个查询 - 您可能已经尝试过，从您发布的额外分号中 - 您会看到每个员工都有一行，每个员工都显示最低工资在他们的部门。该最小值是分析 min() 因为它有一个窗口子句。 PARTITION BY 相当于 GROUP BY ，但没有对整个结果集进行聚合。

获得相同结果（几乎）的最简单方法是使用 RANK() 分析函数，它根据您提供的分区和顺序对值进行排名，同时允许平局：

 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，因为他们的最低工资相同。

您可以将其用作内联视图并仅选择排名第一的那些行：

 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.

如果您不必担心关系，还有一个更简单的选择，但在这里不合适。

请注意，这为您提供了比原始查询多一行的信息。您正在加入 on sml.department_id = emp.department_id 。如果部门 ID 为空（如员工 178 的情况），则该联接将失败，因为您无法使用相等测试将空值与空值进行比较。因为此解决方案没有联接，所以不适用，您会在结果中看到该员工。

原文由 Alex Poole 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

首先要记住的是窗口函数（如 OVER() 子句）对查询结果起作用。也就是说：服务器首先执行查询，然后才应用您定义的窗口函数（当然，这是对实际发生的事情的过度简化，但足以说明我的观点）。

这意味着您实际上可以在同一个查询中使用窗口函数和 group by 子句，但是您需要封装 group by 聚合与 windowed function 聚合，如下所示：

 SELECT department_id,
       min(min(salary)) OVER (partition by department_id) as minsalary
FROM employees
GROUP BY department_id;

但是，我同意这不是使用窗口函数的好地方。 Matt’s proposition - which I upvoted, full disclosure - is best here ( ROW_NUMBER() in CTE or subquery , then selecting only the desired rows in main SELECT )。

原文由 AcePL 发布，翻译遵循 CC BY-SA 4.0 许可协议