Licz łącznie w Postgresql

Question

Licz łącznie w Postgresql

Używam count i group by Aby uzyskać liczbę subskrybentów zarejestrowanych każdego dnia:

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

Wynik:

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

Chcę zamiast tego uzyskać skumulowaną liczbę subskrybentów każdego dnia. Jak to zdobyć?

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

49

sql aggregate-functions postgresql

Author: OMG Ponies, 2011-04-18

Source

5 answers

Użycie:

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

6

Author: OMG Ponies,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-18 04:19:53

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

2

Author: Andriy M,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-18 06:48:47

Zakładam, że chcesz tylko jeden wiersz dziennie i chcesz nadal wyświetlać dni bez żadnych subskrypcji (Załóżmy, że nikt nie subskrybuje określonej daty, Czy chcesz pokazać tę datę z saldem poprzedniego dnia?). W takim przypadku możesz użyć funkcji "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

2

Author: Endy Tjahjono,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-18 07:23:17

Najlepszym sposobem jest posiadanie tabeli kalendarza: kalendarz ( data data, miesiąc int, kwartał int, half int, tydzień int, rok int )

Następnie możesz dołączyć do tej tabeli, aby utworzyć podsumowanie dla potrzebnego pola.

-3

Author: mentat,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-07-18 09:56:16

score 79 · Accepted Answer

Z większymi zbiorami danych, funkcje okna są najskuteczniejszym sposobem wykonywania tego rodzaju zapytań-tabela będzie skanowana tylko raz, zamiast raz dla każdej daty, jak zrobiłoby to samo-dołączenie. Wygląda też o wiele prościej. :) PostgreSQL 8.4 i nowsze mają wsparcie dla funkcji okien.

Tak to wygląda:

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

Tutaj OVER tworzy okno; ORDER BY created_at oznacza, że musi zsumować liczbę w created_at spokój.

Edit: Jeśli chcesz usunąć zduplikowane wiadomości e-mail w ciągu jednego dnia, możesz użyć sum(count(distinct email)). Niestety nie usunie to duplikatów, które krzyżują się z różnymi datami.

Jeśli chcesz usunąć Wszystkie duplikaty, myślę, że najłatwiej jest użyć subquery i DISTINCT ON. Spowoduje to przypisanie wiadomości e-mail do ich najwcześniejszej daty (ponieważ sortuję według created_at w porządku rosnącym, wybierze najwcześniejszą):

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;

Jeśli utworzysz indeks na (email, created_at), to zapytanie nie powinno być zbyt wolne.

(Jeśli chcesz przetestować, w ten sposób stworzyłem sample dataset)

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);