如何在postgres中为每个division_id求最高N值

Question

我有一个带有id, name, salary, division_id列的表id, name, salary, division_id，我需要显示每个部门中工资最高的3位员工。下面是表工作者的示例数据：

insert into worker values (1, 'Joe', 70000, 1);
insert into worker values (2, 'Henry', 80000, 2);
insert into worker values (3, 'Sam', 60000, 2);
insert into worker values (4, 'Max', 90000, 1);
insert into worker values (5, 'Janet', 69000, 1);
insert into worker values (6, 'Randy', 85000, 1);
insert into worker values (7, 'Jordan', 70000, 4);
insert into worker values (8, 'Adam', 69000, 3);
insert into worker values (9, 'David', 76000, 4);
insert into worker values (10, 'Moses', 68000, 4);
insert into worker values (11, 'Solomon', 55000, 4);
insert into worker values (12, 'Cloe', 38000, 3);
insert into worker values (13, 'Sarah', 88000, 3);
insert into worker values (14, 'Deb', 92000, 3);
insert into worker values (15, 'Lea', 98000, 4);
commit;

​

​

对于上面的示例数据，我的查询应该返回以下行：

​

注意:第2部分只有2行，因为它在示例数据中只有2行。

查询应该在Oracle或PostgreSQL中。

我知道我可以限制使用甲骨文的ROWNUM或Postgres LIMIT关键字的行数，但这对我没有帮助，因为我需要为每个division_id检索3行或更少的行。

我的查询应该尽可能高效，因为workers中的行数可能很大(我不知道确切的行数)。

在我的解决方案中，我使用一个游标和FOR my_rec IN my_cursor来扫描结果集，并为每个division_id只打印前3行或更少行。这个解决方案给了我O(N)的复杂性，我希望找到一个更好的解决方案，也许只需使用一个查询就可以不使用CURSOR。

DECLARE
   CREATE CURSOR my_cursor IS
      SELECT division_id, name AS worker, salary
         FROM worker
         ORDER BY division_id, salary desc;
   division_id NUMBER;
   row_count_per_id NUMBER;
BEGIN
   FOR my_rec IN my_cursor LOOP
      -- If first iteration then initialize variables
      IF (row_num = 0) THEN
         division_id := myRec.division_id;
         row_count_per_id := 1;
      END IF;

      -- row_num can't be 0 at this point
      IF (division_id = myRec.division_id) THEN
         IF (row_count_per_id < 3) THEN
            -- Print first record of the new division_id
            DBMS_OUTPUT.PUT_LINE('division_id = ' || myRec.division_id || 
                                 ', Worker = ' myRec.worker ||
                                 ', salary = ' myRec.salary;
            row_count_per_id := row_count_per_id + 1;
         END IF;
      ELSE
         -- division_id has changed from the previous row
         -- Add the first row of new division_id 
         division_id = myRec.division_id;
         row_count_per_id := 1;
         DBMS_OUTPUT.PUT_LINE('division_id = ' || myRec.division_id || 
                              ', Worker = ' myRec.worker ||
                              ', salary = ' myRec.salary;
      END IF;
   END LOOP;
END;

Answer 1

只需在row_number中使用查询

WITH c AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY division_id ORDER BY Salary DESC) AS rn
   FROM worker
)
SELECT *
  FROM c
WHERE rn <= 3

根据您是否希望同等级别的薪资获得相同的级别并按顺序或其他级别，您可以使用RANK或DENSE_RANK来代替ROW_NUMBER。