Блог

Главная - Вопросы по программированию - PostgreSQL: группировка по дате и получение информации до этой даты

PostgreSQL: группировка по дате и получение информации до этой даты

#sql #database #postgresql #aggregate-functions

#sql #База данных #postgresql #агрегатные функции

Вопрос:

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

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

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

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

Комментарии:

1. Вы рассматривали возможность создания Aggregate Tables ? Это будут таблицы, предварительно заполненные по дням, по месяцам, по тому, что является наиболее распространенными запросами на вашем сайте.

2. Вы хотите иметь одну дату передачи (например, самую последнюю), но при этом учитывать все предыдущие? Или вы хотите подсчитать количество для каждой пересылки отдельно за все дни до определенного?

3. @a_horse_with_no_name Мне нужно количество для каждой пересылки отдельно и для всех дней до этого для этого конкретного фонда.

4. @nightmare: разве тогда простой WHERE transdate <= DATE '2011-11-10' не должен делать то, что вы хотите?

5. Я обновил свой пост… Поскольку я пробовал, но не смог получить желаемый результат.

Ответ №1:

Взгляните на оконные функции. Вероятно, вы можете создать свой запрос следующим образом:

  • начните с запроса, который вы должны получить, чтобы получить дату и количество

  • исходя из этого результата, запросите дату и количество и добавьте столбец с функцией «lag», чтобы получить значение предыдущей строки. Раздел окна можно пропустить, поскольку он должен занимать весь набор строк. Порядок по должен быть по дате. Теперь у вас есть дата, количество и количество за предыдущую дату (предыдущая строка).

  • из этого результата запрашивайте дату и сумму двух других столбцов

Лучше всего просто немного поэкспериментировать с этим. Oracle называет оконные функции «Аналитическими функциями». Вот пример.