Блог

Я использую count и group by , чтобы получать количество зарегистрированных подписчиков каждый день:

   SELECT created_at, COUNT(email)  
    FROM subscriptions 
GROUP BY created at;
  

Результат:

 created_at  count
-----------------
04-04-2011  100
05-04-2011   50
06-04-2011   50
07-04-2011  300
  

Вместо этого я хочу получать совокупный итог подписчиков каждый день. Как мне это получить?

 created_at  count
-----------------
04-04-2011  100
05-04-2011  150
06-04-2011  200
07-04-2011  500
  

При больших наборах данных оконные функции являются наиболее эффективным способом выполнения запросов такого рода — таблица будет проверяться только один раз, а не по одному разу для каждой даты, как это было бы при самосоединении. Это также выглядит намного проще. 🙂 В 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,
          'foofoobar@foobar.com' || (i%700000)::text as email
   from generate_series(1,1000000) i;
create index on subscriptions (email, created_at);
  

Использовать:

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

 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
  

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

 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
  

Для всех, кто видит этот ответ сегодня (2021), вы можете использовать rollup

 SELECT created_at, COUNT(email)  
    FROM subscriptions 
GROUP BY rollup(created_at);
  

это даст вам новую строку с общим

 created_at  count
-----------------
04-04-2011  100
05-04-2011   50
06-04-2011   50
07-04-2011  300
NULL        500
  

Вы также можете использовать сводку для частичных результатов, если у вас есть более одного параметра для отображения в вашей группе. Если у вас есть created_by , например:

 SELECT created_at, created_by COUNT(email)  
    FROM subscriptions 
GROUP BY rollup(created_at, created_by);
  

это даст вам новую строку с общим

 created_at  created_by  count
-----------------------------
04-04-2011     1        80
04-04-2011     2        20
04-04-2021    NULL      100
05-04-2011     1        20
05-04-2011     2        30
05-04-2011    NULL      50
NULL          NULL      150
  

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

Здесь rollup() имеет значение порядок, касающийся того, как будут отображаться частичные итоги

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

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