PostgreSQL：按日期分组，包括前几天的结果

Question

对不起，如果这变成了一个双重帖子。

我正在编写一个查询，根据该基金每天发生的交易来确定持有该基金股票的客户数量。

所以我的事务表是:

CREATE TABLE trans(
transID SERIAL PRIMARY KEY,
sin CHAR(9) REFERENCES customer(sin) ON UPDATE CASCADE ON DELETE CASCADE,
fundID INT REFERENCES fund(fundID) NOT NULL,
transDate DATE,
shares INT,
FOREIGN KEY (fundID) REFERENCES fund(fundID) ON UPDATE CASCADE ON DELETE CASCADE
);

这是我的查询:

   select f.transdate, count (f.sin) 
   from (select t1.transdate, t1.sin, sum(t2.shares) 
          from fund f natural join trans t1 natural join trans t2 
          where f.fundname='Energy' 
          and t1.sin = t2.sin 
          and t2.transdate <= t1.transdate 
          group by t1.transdate, t1.sin 
          having sum(t2.shares) > 0)as f group by f.transdate 
          order by f.transdate;

返回当天持有股票的客户总数。但是，我还想添加几天前一直持有同一只基金的客户。

比如说，如果我添加以下插入:

INSERT INTO trans VALUES (DEFAULT, '1', '3', '2011-10-10', 400);
INSERT INTO trans VALUES (DEFAULT, '3', '3', '2011-10-11', 324);
INSERT INTO trans VALUES (DEFAULT, '5', '3', '2011-10-17', 201);
INSERT INTO trans VALUES (DEFAULT, '8', '3', '2011-10-17', 472);

那么我所说的查询将返回这个:

 transdate  | count 
------------+-------
 2011-10-10 |     1
 2011-10-11 |     1
 2011-10-17 |     2

然而，我希望它是这样的:

 transdate  | count 
------------+-------
 2011-10-10 |     1
 2011-10-11 |     2
 2011-10-17 |     4

如你所见，在2011年10月11日结束时，共有2人持有该基金的股份，以此类推。

帮忙吗?

Answer 1

您需要的是一个窗口函数，特别是使用"lag"函数。我不知道你用的是什么版本的PostgreSQL，也不知道这些窗口函数最初是什么时候被支持的，但这里有当前版本的文档。x系列:

窗口概述:http://www.postgresql.org/docs/9.0/interactive/tutorial-window.html

:

窗口函数:http://www.postgresql.org/docs/9.0/interactive/functions-window.html

可能有一个相当有效的方式重写你的查询与此铭记，但我没有时间工作。我可以说，获得预期结果的最简单(即使不是最好的)方法是获取当前查询，使其成为CTE (http://www.postgresql.org/docs/9.0/interactive/queries-with.html)，并在使用CTE的查询中使用窗口函数。意义:

WITH cte (transdate, peoplecount) AS (
 your_current_query
)
SELECT transdate, lag() OVER (...)
FROM cte;

大概是这个意思。