代码之家 › 专栏 › 技术社区 › Carl Meyer

我应该如何处理PostgreSQL中的“Y中排名X”数据?

ranking postgresql sql

Carl Meyer · 技术社区 · 15 年前

我有一个表,我想能够提供“排名X出Y”的数据。特别是,我希望能够以一种相对有效的方式(即,不选择表中的每一行)呈现单个行的数据。排名本身很简单,它是在表中的单列上直接排序的。

Postgres似乎在这方面提出了一些独特的挑战;afaict它没有秩、行数或等效函数(至少在8.3中,这是我目前一直坚持的)。邮件列表存档中的规范答案似乎是创建一个临时序列并从中进行选择:

test=> create temporary sequence tmp_seq;
CREATE SEQUENCE
test=*> select nextval('tmp_seq') as row_number, col1, col2 from foo;

当我只想从表中选择一行(而我想按pk而不是按等级选择)时,这个解决方案似乎仍然没有帮助。

我可以将排名非规范化并存储在一个单独的列中,这使得显示数据变得简单,但只需重新定位我的问题。更新不支持ORDER BY,所以我不确定如何构造更新查询来设置列组(除了选择每一行并为每一行运行单独的更新之外,似乎每次列组需要更新时都会触发太多的DB活动)。

我是否遗漏了一些明显的东西?正确的方法是什么?

编辑 :显然我不够清楚。我知道偏移/限制,但我不知道它如何帮助解决这个问题。我不是要选择第x个排名的项目,而是要选择一个任意的项目(比如说,根据pk),然后能够向用户显示“312个排名中的43个”。

4 回复 | 直到 14 年前

Magnus Hagander 15 年前

如果你想要军衔,就做些类似的事

SELECT id,num,rank FROM (
  SELECT id,num,rank() OVER (ORDER BY num) FROM foo
) AS bar WHERE id=4

或者如果您真的想要行号,请使用

SELECT id,num,row_number FROM (
  SELECT id,num,row_number() OVER (ORDER BY num) FROM foo
) AS bar WHERE id=4

当你在某个地方有相同的值时,它们会有所不同。如果需要的话,还有密集的_rank()。

当然,这需要PostgreSQL 8.4。

Quassnoi 15 年前

不是只有这样吗:

SELECT  *
FROM    mytable
ORDER BY
        col1
OFFSET X LIMIT 1

或者我错过了什么?

更新:

如果要显示排名,请使用以下命令:

SELECT  mi.*, values[1] AS rank, values[2] AS total
FROM    (
        SELECT  (
                SELECT  ARRAY[SUM(((mi.col1, mi.ctid) < (mo.col1, mo.ctid))::INTEGER), COUNT(*)]
                FROM    mytable mi
                ) AS values
        FROM    mytable mo
        WHERE   mo.id = @myid
        ) q

Community CDub 7 年前

ROW_NUMBER PostgreSQL中的功能是通过 LIMIT n OFFSET skip .

查找概述 here .
关于排名的陷阱 this SO question .

编辑:既然你要求 ROW_NUMBER() 而不是简单的排名: row_number() 在8.4版中介绍了PostgreSQL。所以你可以考虑更新。否则 this workaround 可能会有所帮助。

bobflux 15 年前

以前的回答处理的问题是“选择所有行并获得它们的排名”,这不是您想要的…

你吵架了
你想知道它的等级

只做:

从表中选择count(*),其中score>$1

其中$1是您刚刚选择的行的分数(我想您希望显示它,以便选择它…)。

或做:

选择A ,(选择计数( )从表B中得分>b.score)从表中排名为a,其中pk=…

但是,如果选择排在最后的一行,则需要对排在它前面的所有行进行计数,因此需要扫描整个表,而且速度非常慢。

解决方案:

选择Count(*)From(从分数限制为30的表中选择1)

你会得到30个最佳分数的精确排名,而且会很快。谁在乎失败者?

好吧,如果你真的关心失败者,你需要做一个柱状图:

假设得分可以从0到100,您有100万失败者得分为80分,10名优胜者得分为80分。

你做了一个有多少行X的柱状图,这是一个简单的100行小表。向主表添加触发器以更新柱状图。

如果你想给一个得分为x的失败者排名,他的排名是sum(his to),其中his to得分为x。

因为你的分数可能不在0到100之间,但(比如)在0到1000000000之间,你需要稍微修改一下,比如放大你的柱状图箱。所以您最多只需要100个箱子,或者使用一些日志柱状图分布函数。

正如Postgres在分析表时所做的那样,如果您在得分上将统计目标设置为100或1000,则分析,然后运行:

解释从分数为1美元的表中选择*。

你会得到一个不错的行数估计。

谁需要确切的答案?