Как оценивать в postgres-запросе

Я пытаюсь ранжировать подмножество данных в таблице, но я думаю, что я делаю что-то неправильно. Я не могу найти много информации о функции rank() для postgres, возможно, я искал не то место. В любом случае:

Я хотел бы знать ранг идентификатора, который попадает в кластер таблицы на основе даты. Мой запрос выглядит следующим образом:

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 минут и больше не возвращался. (на самом деле повторно запустил его довольно долгое время, и он не дал никаких результатов, но есть строка в кластере 9876 для id 1234). Я ожидаю, что это скажет мне что-то вроде "id 1234 было пятым по заданным критериям". вернет относительный ранг в соответствии с моими ограничениями запроса, правильно?

Это postgres 8.4 btw.

Ответ 1

Поместив функцию ранга() в подзапрос и не указывая PARTITION BY в разделе over или любом предикате в этом подзапросе, ваш запрос просит создать ранг по всей таблице url_info, упорядоченной по pub_date. Вероятно, поэтому он запускался до тех пор, пока он не был ранжирован по всему url_info, Pg должен сортировать всю таблицу по pub_date, что займет некоторое время, если таблица очень большая.

Кажется, вы хотите сгенерировать ранг только для набора записей, выбранных предложением where, и в этом случае все, что вам нужно, это исключить подзапрос, а функция ранга неявно находится над набором записей, соответствующих этому предикату.

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;

Ответ 2

Разделите другой пример DENSE_RANK() PostgreSQL. Выбирайте пример из 3-х учеников. Ссылка, взятая из этого блога:

Создать таблицу с образцами данных:

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(), Calculate 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)