Postgresql 8.4 - 限制窗口函数范围

Is it possible to limit the range over a window function is calculated?.

是否可以限制窗口函数的计算范围?

I'm using PostgreSql 8.4.

我正在使用PostgreSql 8.4。

An example query:

一个示例查询:

select count(*) over() as TOTAL, id, .... from table limit 100

This query returns the 100 top records, its first column displays all the records without taking into account the limit.

此查询返回100条*记录,其第一列显示所有记录而不考虑限制。

The question is, is there a way to also limit the range inside the count?, I mean, if the original query returns a total of 5000, I only need to tell "more than 1000".

问题是,有没有办法限制计数内的范围?我的意思是,如果原始查询总共返回5000,我只需要告诉“超过1000”。

edit: the question can be replaced by, does PostgreSql has a way to know if a query has more than "n" results?.

编辑:问题可以替换为,PostgreSql是否有办法知道查询是否有超过“n”的结果?

1 个解决方案

#1

That's actually a clever idea. Why count all the way if all you are interested in are the first n rows?

这实际上是一个聪明的主意。如果您感兴趣的是前n行,为什么要一直计算?

I ran a couple of test and window functions with a custom frame definition were faster for big result sets:

我运行了几个测试和窗口函数,自定义框架定义对于大结果集更快:

count(*) OVER (ROWS BETWEEN CURRENT ROW AND 1001 FOLLOWING) AS total_max_1001

The problem is, you get a different count for every row, which may or may not be a problem. If you only consider the first row, this works just fine.

问题是,每行得到一个不同的计数,这可能是也可能不是问题。如果你只考虑第一行,这可以正常工作。

Either way, for big result sets, a (uncorrelated) subquery was much faster, yet:

无论哪种方式,对于大结果集,(不相关的)子查询要快得多,但是:

SELECT id
     ,(SELECT count(*)::int
       FROM  (SELECT 1 FROM tbl WHERE  ... LIMIT  1001) x -- repeat conditions
       ) AS total_max_1001
FROM   tbl
WHERE  ...
LIMIT  100;

Tests were in Postgres 9.1. I'd expect similar results for all versions since 8.4, up to 9.3.

测试在Postgres 9.1中进行。从8.4开始,我期望所有版本都有类似的结果,最高可达9.3。

Gives you an exact full count up to 1001 (which may be read as "more than 1000"), but does not count any further. Also works great with indexes. I am going to tune a couple of my own queries with this ...

为您提供最高1001的完整计数(可以读作“超过1000”),但不再计算。也适用于索引。我将用这个来调整我自己的几个问题......

#1