Cuente el total acumulado en Postgresql

Estoy usando count y group by para get la cantidad de suscriptores registrados cada día:

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

Resultado:

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

Quiero get el total acumulativo de suscriptores todos los días en su lugar. ¿Cómo obtengo esto?

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

Con sets de datos más grandes, las funciones de window son la forma más eficiente de realizar este tipo de consultas: la tabla se escaneará solo una vez, en lugar de una vez para cada date, como lo haría una autocombinación. También parece mucho más simple. 🙂 PostgreSQL 8.4 y posteriores tienen soporte para funciones de window.

Esto es lo que parece:

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

Aquí OVER crea la window; ORDER BY created_at significa que tiene que resumir los recuentos en created_at order.


Editar: si desea eliminar correos electrónicos duplicates en un solo día, puede usar sum(count(distinct email)) . Lamentablemente, esto no eliminará los duplicates que cruzan diferentes dates.

Si desea eliminar todos los duplicates, creo que lo más fácil es usar una subconsulta y DISTINCT ON . Esto atribuirá los correos electrónicos a su date más temprana (porque estoy orderando por created_at en order ascendente, elegirá el más antiguo):

 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; 

Si crea un índice en (email, created_at) , esta consulta tampoco debería ser demasiado lenta.


(Si desea probar, así es como creé el set de datos de muestra)

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

Utilizar:

 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 

Supongo que solo quiere una fila por día y desea mostrar días sin suscripciones (supongamos que nadie se suscribe para una date determinada, ¿desea mostrar esa date con el saldo del día anterior?). Si este es el caso, puede usar la function 'con':

 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 

La mejor manera es tener una tabla de calendar: calendar (date de date, mes int, trimestre int, mitad int, semana int, año int)

Luego, puede join a esta tabla para hacer un resumen del campo que necesita.