gpt4 book ai didi

database - 如何在 PostgreSQL 窗口分区中执行过滤查询?

转载 作者:搜寻专家 更新时间:2023-10-30 22:09:43 25 4
gpt4 key购买 nike

我试图更好地理解 PostgreSQL (9.3) 窗口函数。假设我有一个简单的表:

SimpleTable
id int,
tservice timestamp

并希望:

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

SimpleTable 中的记录有 40 年前的 tservice 时间,但是计数需要仅限于每条记录的 tservice 时间戳之前的三年。

如何为 SimpleTable 中的每条记录生成计数?

推论问题:如何更改同一查询以添加今天日期之前三年发生的所有记录的计数?

编辑 #1:现在我明白这个问题哪里太模糊了(我学到了一些东西 :))。使用下面的答案,我想获得 3 年的计数和当前日期的计数,例如:

                             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)

编辑#2:这可以得到我需要的答案,但不使用窗口分区。我认为 PostgreSQL 没有实现带间隔的 RANGE - 我认为这是这个问题所需要的。

     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 所述,使用带有游标的函数式方法可将执行时间缩短至 146 毫秒。感谢大家的精彩教程。

最佳答案

您的想法只是不可能使用frame definition的一个窗口函数。 (您已经开始怀疑了。) RANGEROWS 子句对不同的值或行进行计数,并且没有值含义的概念。

您想要计算特定时间段内的所有行,并且需要采用不同的方法。您可以运行相关子查询或 LATERAL 子查询来计算每一行,但这很昂贵。

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

扩展很多更好。我在那里添加了详细的基准测试。

关于database - 如何在 PostgreSQL 窗口分区中执行过滤查询?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34625811/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com