Postgresql密集排名从2开始，如果初始平局为1(Postgresql dense ranking to start at 2 if there is an initial tie at 1)

所以我有一个表和一个查询项目的成本排名，并不允许与位置1的关系，如果在位置1有一个平局，排名从2开始。

这是包含示例数据的模式

CREATE TABLE applications (id int, name char(10), cost int); INSERT INTO applications (id, name, cost) VALUES (1, 'nfhfjs', 10), (2, 'oopdld', 20), (3, 'Wedass', 14), (4, 'djskck', 22), (5, 'laookd', 25), (6, 'mfjjf', 25), (7, 'vfhgg', 28), (8, 'nvopq', 29), (9, 'nfhfj', 56), (10, 'voapp', 56);

这是查询

WITH start_tie AS ( SELECT DENSE_RANK() OVER(ORDER BY cost DESC) cost_rank, lead(cost,1) OVER (ORDER BY cost DESC) as next_app_cost FROM applications LIMIT 1 ) SELECT *, DENSE_RANK() OVER(ORDER BY cost DESC) cost_rank, (CASE start_tie.cost_rank WHEN start_tie.next_app_cost THEN cost_rank+1 ELSE cost_rank END) AS right_cost_rank FROM applications;

我的预期结果是

id name cost cost_rank 10 voapp 56 2 9 nfhfj 56 2 8 nvopq 29 3 7 vfhgg 28 4 6 mfjjf 25 5 5 laookd 25 5 4 djskck 22 6 2 oopdld 20 7 3 Wedass 14 8 1 nfhfjs 10 9

请修改查询以获得结果。

SQL FIDDLE

So i have a table and a query that ranks the cost of items and doesn't allows ties with position 1, if there is a tie at position 1 the ranking starts at 2.

Here is the schema with a sample data

Here is the query

my expected result is

id name cost cost_rank 10 voapp 56 2 9 nfhfj 56 2 8 nvopq 29 3 7 vfhgg 28 4 6 mfjjf 25 5 5 laookd 25 5 4 djskck 22 6 2 oopdld 20 7 3 Wedass 14 8 1 nfhfjs 10 9

Please modify the query to achieve the result.

SQL FIDDLE

最满意答案

您需要做的就是检查最高成本是否与第二高成本相同。如果是这种情况，请在所有排名值中加1：

with start_tie as ( select case when cost = lead(cost) over (order by cost desc) then 1 else 0 end as tie_offset from applications order by cost desc limit 1 ) select *, dense_rank() over (order by cost desc) + (select tie_offset from start_tie) cost_rank from applications;

示例： http ： //rextester.com/EKSLJK65530

如果关系数定义了用于“新”排名的偏移量，则可以使用以下方法计算偏移量：

with start_tie as ( select count(*) - 1 as tie_offset from applications a1 where cost = (select max(cost) from applications) ) select *, dense_rank() over(order by cost desc) + (select tie_offset from start_tie) cost_rank from applications;

All you need to do is to check if the highest cost is the same as the second-highest cost. And if that is the case, add 1 to all rank values:

Example: http://rextester.com/EKSLJK65530

If the number of ties defines the offset to be used for the "new" ranking, the offset could be calculated using this:

更多推荐