sql - 具有等级的 postgres 窗口函数

Question

我有一些显示用户工资和部门的记录。我想知道排名及其差异。

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2

我想知道各个职级的薪资差异

  depname  | empno | salary | rank | diff
-----------+-------+--------+------+------
 develop   |     8 |   6000 |    1 | 800
 develop   |    10 |   5200 |    2 | 700
 develop   |    11 |   5200 |    2 | 700
 develop   |     9 |   4500 |    4 | 300
 develop   |     7 |   4200 |    5 | 
 personnel |     2 |   3900 |    1 | 400
 personnel |     5 |   3500 |    2 | 
 sales     |     1 |   5000 |    1 | 200
 sales     |     4 |   4800 |    2 | 
 sales     |     3 |   4800 |    2 | 

教我上面返回的查询。

Answer 1

只需使用lag():

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC) as rnk,
       (salary - lag(salary) over (partition by depname order by salary desc)) as diff
FROM empsalary;

编辑:

我注意到您的数据有重复项——因此 rank()。这有点麻烦，因为 Postgres 不支持完整的 range 关键字。这是一种不使用 JOIN 的方法:

SELECT depname, empno, salary, rnk,
       (salary - MIN(prev_salary) OVER (PARTITION BY depname, rnk)) as diff
FROM (SELECT depname, empno, salary,
             rank() OVER (PARTITION BY depname ORDER BY salary DESC) as rnk,
             lag(salary) over (partition by depname order by salary desc) as prev_salary
      FROM empsalary
     ) e;