代码之家 › 专栏 › 技术社区 › tdma

分块查询中的全局行号

postgresql-9.4 row-number pagination postgresql sql

tdma · 技术社区 · 9 年前

我想在结果集中包含一列row_number和行号序列,其中1是最新的项,没有空格。这项工作:

SELECT id, row_number() over (ORDER BY id desc) AS row_number, title
FROM mytable
WHERE group_id = 10;

现在,我想查询相同的数据,每个数据块有1000个,这样内存更容易:

SELECT id, row_number() over (ORDER BY id desc) AS row_number, title
FROM mytable
WHERE group_id = 10 AND id >= 0 AND id < 1000
ORDER BY id ASC;

这里,对于每个块,row_number从1开始重新启动,但我希望它像第一种情况一样,是全局查询的一部分。有什么简单的方法可以做到这一点吗?

4 回复 | 直到 9 年前

Community CDub 8 年前

假设:

id 定义为 PRIMARY KEY -这意味着 UNIQUE 和 NOT NULL 。否则,您可能需要处理NULL值和/或重复项(ties)。
您对表没有并发写访问权限,或者您不关心在拍摄快照后会发生什么。

A. MATERIALIZED VIEW 就像你展示的那样 in your answer ,是一个不错的选择。

CREATE MATERIALIZED VIEW mv_temp AS
SELECT row_number() OVER (ORDER BY id DESC) AS rn, id, title
FROM   mytable
WHERE  group_id = 10;

但索引和后续查询必须在行号上 rn 得到

1000块数据

CREATE INDEX ON mv_temp (rn);

SELECT * FROM mv_temp WHERE rn BETWEEN 1000 AND 2000;

您的实施需要更少的保证差距 身份证件 列-这将不需要添加以…开头的行号。。。

完成后:

DROP MATERIALIZED VIEW mv_temp;

索引与表一起自动终止(在本例中是物化视图)。

相关,详细信息:

Optimize query with OFFSET on large table

Thorsten Kettner 9 年前

您想查询前1000行,然后查询下1000行,依此类推?

通常你只需要编写一个查询(你已经使用过的查询),让你的应用程序获取1000条记录,对它们进行处理,然后获取接下来的1000条,依此类推。因此,不需要单独查询。

然而,编写这样的部分查询会很容易:

select *
from
(
  SELECT id, row_number() over (ORDER BY id desc) AS rn, title
  FROM mytable
  WHERE group_id = 10
) numbered
where rn between 1 and 1000; -- <- simply change the row number range here
                             --    e.g. where rn between 1001 and 2000 for the second chunk

Madhivanan 9 年前

您需要分页。试试这个

SELECT id, row_number() over (ORDER BY id desc)+0 AS row_number, title
FROM mytable
WHERE group_id = 10 AND id >= 0 AND id < 1000
ORDER BY id ASC;

下次,当您在WHERE子句中更改id的起始值时,请在row_number()中更改它,如下所示

SELECT id, row_number() over (ORDER BY id desc)+1000 AS row_number, title
FROM mytable
WHERE group_id = 10 AND id >= 1000 AND id < 2000
ORDER BY id ASC;

或者更好,您可以使用OFFSET和LIMIT方法进行分页 https://wiki.postgresql.org/images/3/35/Pagination_Done_the_PostgreSQL_Way.pdf

tdma 9 年前

最后,我这样做了:

首先,我创建一个临时物化视图:

CREATE MATERIALIZED VIEW vw_temp AS SELECT id, row_number() over (ORDER BY id desc) AS rn, title
FROM mytable
WHERE group_id = 10;

然后我定义索引:

CREATE INDEX idx_temp ON vw_temp USING btree(id);

现在,我可以非常快速地执行所有操作,并使用编号的行:

SELECT * FROM vw_temp WHERE id BETWEEN 1000 AND 2000;

操作完成后,清理:

DROP INDEX idx_temp;
DROP MATERIALIZED VIEW vw_temp;

尽管托尔斯滕·凯特纳的回答似乎是最清晰的,但由于速度太慢,这对我来说并不实用。感谢大家的贡献。对于那些对实际用例感兴趣的人,我使用它向Sphinx索引器提供数据。