PostgreSQL:有效地获得序数排名(行索引?) [英] PostgreSQL: getting ordinal rank (row index? ) efficiently

问题描述

你有一张这样的桌子:

id dollars dollars_rank points points_rank
1  20      1            35     1
2  18      2            30     3
3  10      3            33     2

我想要一个更新表排名列(dollars_rank 和 points_rank)的查询来设置给定 ID 的排名，这只是该 ID 的行索引按相关列按降序排序.在 PostgreSQL 中如何最好地做到这一点?

I want a query that updates the table's rank columns (dollars_rank and points_rank) to set the rank for the given ID, which is just the row's index for that ID sorted by the relevant column in a descending order. How best to do this in PostgreSQL?

推荐答案

@OMG_Ponies 已经指出:窗口函数 dense_rank() 是你需要的 - 或者 rank().UPDATE 可能如下所示:

@OMG_Ponies already pointed it out: The window function dense_rank() is what you need - or maybe rank(). The UPDATE could look like this:

测试用例:

CREATE TEMP TABLE tbl (
   id int
 , dollars int
 , dollars_rank int
 , points int
 , points_rank int
 );
INSERT INTO tbl VALUES
 (1, 20, 1, 35, 1)
,(2, 18, 2, 30, 3)
,(3, 10, 3, 33, 2)
,(4, 10, 3, 33, 2);  -- I put a dupe row in to demonstrate ties.

更新语句:

UPDATE tbl
SET    dollars_rank = r.d_rnk
     , points_rank  = r.p_rnk
FROM (
    SELECT id
         , dense_rank() OVER (ORDER BY dollars DESC) AS d_rnk
         , dense_rank() OVER (ORDER BY points DESC)  AS p_rnk
    FROM   tbl
    ) r
WHERE tbl.id = r.id

您需要 PostgreSQL 8.4 或更高版本才能使用窗口函数.

You need PostgreSQL 8.4 or later for window functions.

这篇关于PostgreSQL:有效地获得序数排名(行索引?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！