Количество совокупных сумм в Postgresql

Ответ 1

С более крупными наборами данных функции окна являются наиболее эффективным способом выполнения таких запросов - таблицы будет сканироваться только один раз, а не один раз для каждой даты, как это делает самосоединение. Это также выглядит намного проще.:) PostgreSQL 8.4 и выше поддерживают функции окна.

Это выглядит так:

SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM subscriptions
GROUP BY created_at;

Здесь OVER создает окно; ORDER BY created_at означает, что он должен суммировать подсчеты в порядке created_at.

Изменить: Если вы хотите удалить повторяющиеся письма в течение одного дня, вы можете использовать sum(count(distinct email)). К сожалению, это не приведет к удалению дубликатов, которые пересекают разные даты.

Если вы хотите удалить все дубликаты, я думаю, что проще всего использовать подзапрос и DISTINCT ON. Это свяжет письма с их ранней датой (потому что я сортирую по created_at в порядке возрастания, он выберет самый ранний из них):

SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM (
    SELECT DISTINCT ON (email) created_at, email
    FROM subscriptions ORDER BY email, created_at
) AS subq
GROUP BY created_at;

Если вы создаете индекс на (email, created_at), этот запрос также должен быть не слишком медленным.

(Если вы хотите протестировать, вот как я создал образец набора данных)

create table subscriptions as
   select date '2000-04-04' + (i/10000)::int as created_at,
          '[email protected]' || (i%700000)::text as email
   from generate_series(1,1000000) i;
create index on subscriptions (email, created_at);

Ответ 2

Использование:

SELECT a.created_at,
       (SELECT COUNT(b.email)
          FROM SUBSCRIPTIONS b
         WHERE b.created_at <= a.created_at) AS count
  FROM SUBSCRIPTIONS a

Ответ 3

SELECT
  s1.created_at,
  COUNT(s2.email) AS cumul_count
FROM subscriptions s1
  INNER JOIN subscriptions s2 ON s1.created_at >= s2.created_at
GROUP BY s1.created_at

Ответ 4

Я предполагаю, что вам нужна только одна строка в день, и вы хотите показывать дни без каких-либо подписки (предположим, что никто не подписывается на определенную дату, вы хотите показать эту дату с балансом предыдущего дня?). Если это так, вы можете использовать функцию "с":

with recursive serialdates(adate) as (
    select cast('2011-04-04' as date)
    union all
    select adate + 1 from serialdates where adate < cast('2011-04-07' as date)
)
select D.adate,
(
    select count(distinct email)
    from subscriptions
    where created_at between date_trunc('month', D.adate) and D.adate
)
from serialdates D

Ответ 5

Лучший способ - иметь таблицу календаря: календарь ( дата даты, месяц int, квартал int, половина int, недельный int, год int )

Затем вы можете присоединиться к этой таблице, чтобы составить сводку для нужного поля.