如何在PostgreSQL查询中进行排名

Question

我正在尝试在表格内对一部分数据进行排名，但我认为我的方法有问题。我在PostgreSQL的文档中找不到关于rank()函数的详细信息，可能是我找错了地方。

无论如何，我想知道根据日期，在表格内针对一个集群中的id的序列排名。我的查询语句如下：

select cluster_id,feed_id,pub_date,rank 
from (select feed_id,pub_date,cluster_id,rank() 
    over (order by pub_date asc) from url_info) 
as bar where cluster_id = 9876 and feed_id = 1234;

我是依据以下StackOverflow帖子进行建模的：postgres rank 我认为自己做错了什么，原因是url_info中只有39行位于cluster_id 9876，并且这个查询运行了10分钟也没有返回结果。(实际上重新运行了相当长的时间，但没有返回结果，而在id 1234的cluster 9876中有一行) 我期望这将告诉我一些如"根据给定的条件，id 1234排名第5"之类的信息。它将根据我的查询约束返回一个相对排名，对吗？

顺便提一下，这是PostgreSQL 8.4。

- WildBill

---

你希望相对于什么来排名？所有记录（这就是你上面的查询所询问的，可能也是为什么它太慢的原因）？由谓词选择的一组记录？还是其他分组？如果不想在所有行上排名，over 子句应该指定一个 partition by。这里是窗口函数的教程。 - dbenhur

我想相对于pub_date进行排名。或许我可以更好地解释一下：url_info中有成千上万个URL。其中39个组成了集群9876。通过仅选择是9876成员的URL（URL只能是一个集群的成员），我希望按照pub_date的顺序排列组成集群9876的URL。我仍然要使用基于窗口函数的分区吗？我查看了您发送的URL，看起来这是在计算某个值后对项目进行排名的引用，而我在这里没有这样做... - WildBill

Answer 1

将 rank() 函数放在子查询中并且在 over 子句中没有指定 PARTITION BY 或者任何谓词，你的查询请求对 url_info 表按照 pub_date 排序并产生一个整个表的 rank。这可能是为什么它运行时间如此之长，因为 Pg 必须对整张 url_info 表按照 pub_date 进行排序，如果表非常大，这将需要一段时间。

看起来你想为 where 子句所选的记录集生成一个 rank，在这种情况下，你只需要消除子查询，rank 函数隐式地应用于与该谓词匹配的记录集。

select 
  cluster_id
 ,feed_id
 ,pub_date
 ,rank() over (order by pub_date asc) as rank
from url_info
where cluster_id = 9876 and feed_id = 1234;

如果您真正想要的是集群内的排名，而不考虑feed_id，则可以在子查询中进行过滤并排名：

select ranked.*
from (
  select 
    cluster_id
   ,feed_id
   ,pub_date
   ,rank() over (order by pub_date asc) as rank
  from url_info
  where cluster_id = 9876
) as ranked
where feed_id = 1234;

- dbenhur

---

“rank() over (order by pub_date asc) as rank” 是多余的，因为默认列名就是函数名。 - isapir

@isapir 也许是这样，但这并不是一个强有力的承诺；根据文档所述：“在更复杂的情况下，可能会使用函数或类型名称，或者系统可能会退回到生成的名称，例如?column?” - dbenhur

Answer 2

分享一下 PostgreSQL 的 DENSE_RANK() 函数的另一个示例。 查找前三名学生的样例查询。 参考自此博客：

创建一个包含示例数据的表：

CREATE TABLE tbl_Students
(
    StudID INT
    ,StudName CHARACTER VARYING
    ,TotalMark INT
);

INSERT INTO tbl_Students 
VALUES 
(1,'Anvesh',88),(2,'Neevan',78)
,(3,'Roy',90),(4,'Mahi',88)
,(5,'Maria',81),(6,'Jenny',90);

使用DENSE_RANK()函数，计算学生排名：

;WITH cteStud AS
(
    SELECT 
        StudName
        ,Totalmark
        ,DENSE_RANK() OVER (ORDER BY TotalMark DESC) AS StudRank
    FROM tbl_Students
)
SELECT 
    StudName
    ,Totalmark
    ,StudRank
FROM cteStud 
WHERE StudRank <= 3;

结果：

studname | totalmark | studrank
----------+-----------+----------
 Roy      |        90 |        1
 Jenny    |        90 |        1
 Anvesh   |        88 |        2
 Mahi     |        88 |        2
 Maria    |        81 |        3
(5 rows)

- Anvesh

---

值得一提的是，dense_rank() 函数生成的排名是没有间隔的（1,1,2,3,4,5,5,5,6,7,...），而 rank() 函数生成的排名是有间隔的（1,1,3,4,5,5,7,...）。 - lionbigcat