functions - postgresql string_agg

Cuente el total acumulado en Postgresql

Question

Estoy usando count y group by para obtener 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 obtener 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

Answer 1

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

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 ventana; ORDER BY created_at significa que tiene que resumir los recuentos en created_at orden.

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

Si desea eliminar todos los duplicados, creo que lo más fácil es usar una subconsulta y DISTINCT ON . Esto atribuirá los correos electrónicos a su fecha más temprana (porque estoy ordenando por created_at en orden 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 conjunto de datos de muestra)

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

Answer 2

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

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

Answer 3

Supongo que solo quiere una fila por día y desea mostrar días sin suscripciones (supongamos que nadie se suscribe para una fecha determinada, ¿desea mostrar esa fecha con el saldo del día anterior?). Si este es el caso, puede usar la función ''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

Answer 4

Utilizar:

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

Answer 5

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