我应该如何处理“从y中排序x”？ PostgreSQL中的数据？ [英] How should I handle "ranked x out of y" data in PostgreSQL?

问题描述

我有一个表，希望能够显示从Y到X的排名数据。特别是，我希望能够以一种相对有效的方式（即不选择表格中的每一行）来显示单个行的数据。排名本身很简单，在表格的单个列上是直接的ORDER BY。

I have a table that I would like to be able to present "ranked X out of Y" data for. In particular, I'd like to be able to present that data for an individual row in a relatively efficient way (i.e. without selecting every row in the table). The ranking itself is quite simple, it's a straight ORDER BY on a single column in the table.

Postgres在这方面似乎提出了一些独特的挑战； AFAICT没有RANK或ROW_NUMBER或同等功能（至少在8.3中，我暂时坚持使用）。邮件列表档案中的规范答案似乎是创建一个临时序列并从中选择：

Postgres seems to present some unique challenges in this regard; AFAICT it doesn't have a RANK or ROW_NUMBER or equivalent function (at least in 8.3, which I'm stuck on for the moment). The canonical answer in the mailing list archives seems to be to create a temporary sequence and select from it:

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

当我只想从列表中选择一行时，似乎此解决方案仍然无济于事表（我想按PK而不是按等级选择它）。

It seems like this solution still won't help when I want to select just a single row from the table (and I want to select it by PK, not by rank).

我可以对序列进行非规范化并将其存储在单独的列中，这使得呈现数据变得微不足道，但只是重新定位了我的问题。 UPDATE不支持ORDER BY，因此我不确定如何构造UPDATE查询来设置等级（缺少选择每一行并为每一行运行单独的UPDATE的方法，这似乎使DB活动过多

I could denormalize and store the rank in a separate column, which makes presenting the data trivial, but just relocates my problem. UPDATE doesn't support ORDER BY, so I'm not sure how I'd construct an UPDATE query to set the ranks (short of selecting every row and running a separate UPDATE for each row, which seems like way too much DB activity to trigger every time the ranks need updating).

我错过了明显的东西吗？正确的方法是什么？

Am I missing something obvious? What's the Right Way to do this?

编辑：显然我还不够清楚。我知道OFFSET / LIMIT，但看不到它如何帮助解决此问题。我不是要选择排名第X的项目，而是要选择一个任意项目（例如，按其PK），然后向用户显示类似 312中排名第43的内容。 / p>

EDIT: Apparently I wasn't clear enough. I'm aware of OFFSET/LIMIT, but I don't see how it helps solve this problem. I'm not trying to select the Xth-ranked item, I'm trying to select an arbitrary item (by its PK, say), and then be able to display to the user something like "ranked 43rd out of 312."

推荐答案

如果您想要排名，请执行以下操作

If you want the rank, do something like

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

或者如果您确实想要行号，请使用

Or if you actually want the row number, use

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

当您在某处具有相等的值时，它们将有所不同。

They'll differ when you have equal values somewhere. There is also dense_rank() if you need that.

当然，这需要PostgreSQL 8.4。

This requires PostgreSQL 8.4, of course.

这篇关于我应该如何处理“从y中排序x”？ PostgreSQL中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！