如何在PostgreSQL窗口分区中执行筛选后的查询？

Question

我试图更好地理解PostgreSQL（9.3）窗口函数。假设我有一个简单的表，如下：

SimpleTable
    id int,
    tservice timestamp

并希望：

Select id, tservice , count(*) OVER (PARTITION BY id ....) as counter
from SimpleTable

其中SimpleTable中的记录的tservice时间可以追溯到40年前，但是计数需要限制在每个记录的tservice时间戳之前的三年。

                             3yrs prior   current date
1, 100, '2001-01-01 00:00:00', 0             0
2, 100, '2002-01-01 00:00:00', 1             0
3, 100, '2003-01-01 00:00:00', 2             0 
4, 100, '2004-01-01 00:00:00', 3             0
5, 100, '2005-01-01 00:00:00', 3             0              
6, 100, '2006-01-01 00:00:00', 3             0
7, 100, '2007-01-01 00:00:00', 3             0 
8, 100, '2008-01-01 00:00:00', 3             0
9, 100, '2009-01-01 00:00:00', 3             0
10, 100, '2010-01-01 00:00:00',3             0
11, 100, '2011-01-01 00:00:00',3             0
12, 100, '2012-01-01 00:00:00',3             0
13, 100, '2013-01-01 00:00:00',3             0
14, 100, '2014-01-01 00:00:00',3             1
15, 100, '2015-01-01 00:00:00',3             2
16, 100, '2016-01-01 00:00:00',3             3  (today is 2016-01-06)

     select s1.recid, s1.tservice, s1.client_recid, 
    (select count(*) from simpletable  s2 
        where (s1.tservice - s2.tservice)::INTERVAL <= interval '3 years' and
        s2.tservice < s1.tservice  and
        s2.client_recid = s1.client_recid)
from simpletable s1
order by client_recid, tservice

在几十万张唱片上，这在我的笔记本电脑上需要大约10秒钟。有更快的办法吗？

补充说明：使用Erwin概述的带有游标的函数方法将执行时间减少到146ms。感谢大家提供的优秀教程。

Answer 1

您所考虑的只是不可能使用窗口函数的框架定义。（你也开始怀疑了。）range或rows子句计算不同的值或行，并且不知道值的含义。

您想要计算在某段时间内下降的所有行，并且需要以不同的方式进行计算。您可以运行相关的子查询或横向的子查询来计算每一行，但这很昂贵。

更聪明的方法是并行运行两个游标并保持运行计数。我为一个非常相似的问题实现了这一点：

null