Postgresql에서 누적 합계 계산

Postgresql에서 누적 합계 계산

---

나는 매일 등록 된 구독자 수를 사용 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

---

I assume you want only one row per day and you want to still show days without any subscriptions (suppose nobody subscribes for a certain date, do you want to show that date with the balance of the previous day?). If this is the case, you can use the 'with' feature:

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

---

The best way is to have a calendar table: calendar ( date date, month int, quarter int, half int, week int, year int )

Then, you can join this table to make summary for the field you need.

ReferenceURL : https://stackoverflow.com/questions/5698452/count-cumulative-total-in-postgresql