分页:找出某项在哪个页面上(给定主键和排序顺序) [英] Pagination: Find out which page an item is on (given primary key & sorting order)

问题描述

让我说这样的分页:

SELECT article_id, 
       article_content 
FROM articles 
ORDER BY article_rating 
OFFSET (page - 1) * items_per_page 
LIMIT items_per_page;

我在(article_rating，article_id)上有一个索引.

我的问题是:如果我是，找出文章在哪一页上的最有效方式是什么?

a)知道article_id

b)知道排序是按article_rating排序的吗?

它必须高效，因为我将非常频繁地进行此类查询.

如果不仅要吐出页码，还要吐出该页面上的所有文章，那就更好了.

因此，例如，如果所有文章均按其评分排序，并且每十篇文章都放在不同的页面上，那么我想找出ID 839的文章在哪一页上.

我正在使用PostgreSQL 8.4(如果需要，我愿意进行更新).

谢谢！

正如下面的评论中指出的那样，我的查询可能看起来像这样:

SELECT article_id, 
       article_content 
FROM articles 
ORDER BY article_rating,
         article_id
OFFSET (page - 1) * items_per_page 
LIMIT items_per_page;

解决方案

编辑请参见下面的第二个查询，它比第一个查询要好得多.

假设Postgres 9.0或更高版本，则必须利用window函数将row_number赋予每个项目.然后，将特定文章的row_number除以items_per_page(并四舍五入)以得到页码.唯一可以提高效率的方法是至少不查询出现问题的 之后的文章.这样您会得到如下内容:

Select ceiling(rowNumber/items_per_page)
  from (
SELECT article_id
     , article_content 
     , row_number() over (order by article_rating, article_id)
       as rowNumber
  FROM articles 
 where article_rating <= (select article_rating
                            from articles
                           where article_id = 'xxxx' )
 ORDER BY article_rating,
          article_id
       ) x
 where article_id = 'xxxx'

编辑用于回应评论中的问题.是的，我刚刚意识到有一种更好的方法可以做到这一点.通过运行count(*)相反，我们仅遍历索引.

Select ceiling(count(*)/items_per_page)
  FROM articles 
 where article_rating < (select article_rating
                           from articles
                          where article_id = 'xxxx' )
    or ( article_rating = (select article_rating
                           from articles
                          where article_id = 'xxxx' )
        and article_id <= 'xxxx')

通常我们不喜欢WHERE子句中的OR子句，因为它们会降低性能，但是这应该是非常安全的，因为如果对article_rating进行索引，则每个子句都应该是可优化的.

Let's say I do pagination like this:

SELECT article_id, 
       article_content 
FROM articles 
ORDER BY article_rating 
OFFSET (page - 1) * items_per_page 
LIMIT items_per_page;

I have an index over (article_rating, article_id).

My question is: What is the most efficient way to find out on which page an article is if I

a) know the article_id

b) know the sorting is ORDER BY article_rating?

It needs to be efficient because I am going to do this type of query very often.

It would be even better if it not only spit out the page number, but also all articles on that page.

So, for example, if all articles are sorted by their rating and every ten of them are put on a different page, I want to figure out on which page the article with the ID 839 is.

I am using PostgreSQL 8.4 (I am willing to update, if necessary).

Thank you!

EDIT:

As pointed out in the comments below, my query should probably look like this:

SELECT article_id, 
       article_content 
FROM articles 
ORDER BY article_rating,
         article_id
OFFSET (page - 1) * items_per_page 
LIMIT items_per_page;

解决方案

EDIT See second query below, it is much better than this first one.

Assuming Postgres 9.0 or better, you have to make use of a window function to get a row_number onto each item. Then you divide the row_number of the particular article by items_per_page (and round) to get page number. The only efficiency improvement available is to at least not query the articles that come after the one in question. So you get something like this:

Select ceiling(rowNumber/items_per_page)
  from (
SELECT article_id
     , article_content 
     , row_number() over (order by article_rating, article_id)
       as rowNumber
  FROM articles 
 where article_rating <= (select article_rating
                            from articles
                           where article_id = 'xxxx' )
 ORDER BY article_rating,
          article_id
       ) x
 where article_id = 'xxxx'

EDIT In response to question in comments. Yes, I just realized there is a much better way to do this. By running a count(*) instead we traverse only the index.

Select ceiling(count(*)/items_per_page)
  FROM articles 
 where article_rating < (select article_rating
                           from articles
                          where article_id = 'xxxx' )
    or ( article_rating = (select article_rating
                           from articles
                          where article_id = 'xxxx' )
        and article_id <= 'xxxx')

Normally we do not like OR clauses in WHERE clauses because they can degrade performance, but this one should be pretty safe because each clause ought to be optimizable if article_rating is indexed.

这篇关于分页:找出某项在哪个页面上(给定主键和排序顺序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！