Блог

Извините, если это стало двойным сообщением.

Я пишу запрос, который определяет количество клиентов, владеющих акциями в определенном фонде, сгруппированных по транзакциям каждого дня, произошедшим в этом фонде.

Итак, моя таблица транзакций:

 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 человека владели долями в этом фонде и так далее.

Любая помощь?

Что вам нужно, так это оконная функция, в частности, с использованием функции «задержка». Я не уверен, какая версия PostgreSQL у вас есть и когда эти оконные функции были впервые поддержаны, но вот документация для текущей серии 9.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 ) и используйте функцию windowing в запросе, который использует CTE. Значение:

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

Что-то в этом роде. Надеюсь, это поможет :).