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

我应该如何在PostgreSQL中处理“从y中排名x”的数据?

ranking postgresql sql

6

Carl Meyer · 技术社区 · 16 年前

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

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

编辑显然,我表达得不够清晰。我知道OFFSET/LIMIT,但我看不出它如何帮助解决这个问题。我并没有试图选择第X个排名项目,而是试图选择一个任意项目(比如通过其PK),然后能够向用户显示类似“在312个项目中排名第43位”的内容

4 回复 | 直到 16 年前

1

7

Magnus Hagander 16 年前

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

当你在某个地方有相等的值时,它们就会不同。如果你需要的话,还有dense_rank()。

当然,这需要PostgreSQL 8.4。

2

5

Quassnoi 16 年前

不就是这样吗:

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

3

Community Mohan Dere 8 年前

ROW_NUMBER PostgreSQL中的功能是通过以下方式实现的 LIMIT n OFFSET skip .

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

编辑:既然你要求 ROW_NUMBER() 而不是简单的排名: row_number() this workaround 可能会有所帮助。

4

1

bobflux 16 年前

之前的回复解决了“选择所有行并获得其排名”的问题,这不是你想要的。..

只要做:

从表中选择计数(*)WHERE分数>1.

选择a。 ,(选择计数( )表b中的WHERE分数>b.score)作为排名表作为WHERE pk=。..

但是,如果您选择排名最后的行,是的,您需要计算排名在它之前的所有行,因此您需要扫描整个表,这将非常缓慢。

谁在乎失败者?

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

你制作一个有多少行得分为X的直方图,这是一个有100行的简单小表。在主表中添加触发器以更新直方图。

现在,如果你想对得分为X的失败者进行排名,他的排名是sum(histo),其中histo_core>十、

由于你的分数可能不在0到100之间,而是(比如)在0到1000000000之间,你需要稍微调整一下,比如放大你的直方图。所以你最多只需要100个箱子,或者使用一些对数直方图分布函数。

解释从表中选择*,其中分数>1.

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