postgres window function with rank

Question

I have some records which show users salary and department. I'd like to know ranking and its diff.

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

I'd like to know the difference of each ranks salary

  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 |

teach me the query which returns above.

Gordon Linoff · Accepted Answer · 2016-12-22 11:52:07Z

2

Just use 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;

EDIT:

I noticed that your data has duplicates -- hence the rank(). This is a bit more troublesome, because Postgres does not support full the range keyword. Here is one method that does not use a 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;

edited Dec 22, 2016 at 11:52

answered Dec 22, 2016 at 11:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ema.jar · Accepted Answer · 2016-12-23 09:48:23Z

0

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 ) AS diff 
FROM empsalary ORDER BY depname, salary DESC;

edited Dec 23, 2016 at 9:48

Ema.jar

2,4341 gold badge35 silver badges44 bronze badges

answered Dec 22, 2016 at 16:27

Claudio

11 bronze badge

4 Comments

Claudio Over a year ago

hi try to answer to mo12mo34 question ;-)

hardillb Over a year ago

Please add some explanation to this so people can learn why it works

Claudio Over a year ago

salary-lag(salary) gives difference on salary by depname

lmo Over a year ago

While this may answer the question, answers that only contain code without a description as to how the code work can be flagged as low quality. Consider adding a bit of text explaining the code that may help the OP and future visitors to this post. Also, that way, you are more likely to earn points on the question.

Collectives™ on Stack Overflow

postgres window function with rank

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related