[SQL] PostgreSQL을 누적 총 개수

PostgreSQL을 누적 총 개수

나는 매일 등록 된 가입자의 수를 얻을 수에 의해 수와 그룹을 사용하고 있습니다 :

  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

해결법

1. ==============================

   1.- 자체 조인처럼, 대신 각 날짜에 대해 한 번에, 테이블이 한 번만 스캔 할 할 것 - 큰 데이터 세트와 함께, 윈도우 함수는 이러한 종류의 쿼리를 수행 할 수있는 가장 효율적인 방법입니다. 또한 많은 간단 보인다. : PostgreSQL의 8.4 및 최대 창 기능을 지원합니다.

   - 자체 조인처럼, 대신 각 날짜에 대해 한 번에, 테이블이 한 번만 스캔 할 할 것 - 큰 데이터 세트와 함께, 윈도우 함수는 이러한 종류의 쿼리를 수행 할 수있는 가장 효율적인 방법입니다. 또한 많은 간단 보인다. : PostgreSQL의 8.4 및 최대 창 기능을 지원합니다.

   이는 모습입니다 :

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

   여기에 윈도우를 생성한다; 이 created_at 순서로 카운트를 요약하는 것을 created_at 수단 BY ORDER.

   편집 : 당신은 하루에서 중복 메일을 제거하려는 경우, 당신은 합계 사용할 수 있습니다 (카운트 (별개의 이메일을)). 불행하게도이 다른 날짜를 건너 중복 제거되지 않습니다.

   모든 중복을 제거 할 경우에, 나는 가장 쉬운 하위 쿼리와 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;
   

   당신이 (이메일, 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);
   

2. ==============================

   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. ==============================

   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. ==============================

   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. ==============================

   5.가장 좋은 방법은 일정 테이블을하는 것입니다 : 달력 (   날짜 날짜,   달 INT,   분기 INT,   반 INT,   주 INT,   년 INT )

   가장 좋은 방법은 일정 테이블을하는 것입니다 : 달력 (   날짜 날짜,   달 INT,   분기 INT,   반 INT,   주 INT,   년 INT )

   그렇다면, 당신은 당신이 필요로하는 필드의 메이크업 요약이 테이블에 가입하실 수 있습니다.

from https://stackoverflow.com/questions/5698452/count-cumulative-total-in-postgresql by cc-by-sa and MIT license