Pregunta sobre sql, postgresql, date, count – Consulta para el recuento de valores distintos en un rango de fecha variable.

9

Tengo un conjunto de datos de direcciones de correo electrónico y fechas en que esas direcciones de correo electrónico se agregaron a una tabla. Puede haber varias entradas de una dirección de correo electrónico para varias fechas diferentes. Por ejemplo, si tengo el conjunto de datos a continuación. Buscaría obtener la fecha y el número de correos electrónicos distintos que tenemos entre dicha fecha y hace 3 días.

<code>Date   | email  
-------+----------------
1/1/12 | [email protected]
1/1/12 | [email protected]
1/1/12 | [email protected]
1/2/12 | [email protected]
1/2/12 | [email protected]
1/3/12 | [email protected]
1/4/12 | [email protected]
1/5/12 | [email protected]
1/5/12 | [email protected]
1/6/12 | [email protected]
1/6/12 | [email protected]
1/6/12 | [email protected]
</code>

El conjunto de resultados se vería así si usáramos un período de fecha de 3

<code>date   | count(distinct email)
-------+------
1/1/12 | 3
1/2/12 | 3
1/3/12 | 3
1/4/12 | 3
1/5/12 | 2
1/6/12 | 2
</code>

Puedo obtener un conteo distinto de un rango de fechas utilizando la consulta a continuación, pero buscando obtener un conteo de un rango por día, así no tengo que actualizar manualmente el rango para cientos de fechas.

<code>select test.date, count(distinct test.email)  
from test_table as test  
where test.date between '2012-01-01' and '2012-05-08'  
group by test.date;
</code>

Se agradece la ayuda.

Tu respuesta

4   la respuesta
0

<code>SELECT b.day, count(DISTINCT a.user_id)
from glip_production.presences_1d a,
 (SELECT distinct(day), TIMESTAMPADD(day,-6, day) dt_start
  from glip_production.presences_1d t1) b
where a.day >= b.dt_start and a.day <= b.day and b.day > '2017-11-01'
group by b.day
</code>
0

siempre podría usar una función dateadd:

<code>test.date > dateadd(dd,-7,getdate())
</code>
No hay "dateadd ()" en PostgreSQL. Erwin Brandstetter
0

<code>`select test.date, count(distinct test.email) from test_table as test  where convert(date,test.date) between '2012-01-01' and '2012-05-08' group by test.date`
</code>

espero que esto ayude.

Esto se etiqueta PostgreSQL. Noconvert() funciona aqui (Bueno, hay unaconvert() función, pero es para convertir codificaciones, no tipos de datos como en el servidor SQL.) Además, las firmas no se recomiendan, su estilo en la parte inferior derecha se encarga de eso. Veraquí oaquí. Erwin Brandstetter
11

Caso de prueba:

<code>CREATE TEMP TABLE tbl (day date, email text);
INSERT INTO tbl VALUES
 ('2012-01-01', '[email protected]')
,('2012-01-01', '[email protected]')
,('2012-01-01', '[email protected]')
,('2012-01-02', '[email protected]')
,('2012-01-02', '[email protected]')
,('2012-01-03', '[email protected]')
,('2012-01-04', '[email protected]')
,('2012-01-05', '[email protected]')
,('2012-01-05', '[email protected]')
,('2012-01-06', '[email protected]')
,('2012-01-06', '[email protected]')
,('2012-01-06', '[email protected]`');
</code>

Consulta: devuelve solo los días en que existe una entradatbl:

<code>SELECT day
     ,(SELECT count(DISTINCT email)
       FROM   tbl
       WHERE  day BETWEEN t.day - 2 AND t.day -- period of 3 days
      ) AS dist_emails
FROM   tbl t
WHERE  day BETWEEN '2012-01-01' AND '2012-01-06'  
GROUP  BY 1
ORDER  BY 1;
</code>

O - volvertodos los días en el rango especificado, incluso si no hay filas para el día:

<code>SELECT day
     ,(SELECT count(DISTINCT email)
       FROM   tbl
       WHERE  day BETWEEN g.day - 2 AND g.day
      ) AS dist_emails
FROM  (SELECT generate_series('2012-01-01'::date
                            , '2012-01-06'::date, '1d')::date) AS g(day)
</code>

Resultado:

<code>day        | dist_emails
-----------+------------
2012-01-01 | 3
2012-01-02 | 3
2012-01-03 | 3
2012-01-04 | 3
2012-01-05 | 1
2012-01-06 | 2
</code>

Esto sonaba como un trabajo parafunciones de la ventana al principio, pero no encontré una manera de definir el marco de ventana adecuado. También,por documentación:

Las funciones de ventana agregadas, a diferencia de las funciones agregadas normales, no permitenDISTINCT oORDER BY para ser utilizado dentro de la lista de argumentos de la función.

Así que lo resolví con subconsultas correlacionadas. Supongo que esa es la forma más inteligente.

Cambié el nombre de su columna de fecha aday, porque es una mala práctica usar nombres de tipo como identificadores.

Por cierto, "entre dicha fecha y hace 3 días" sería un período de4 dias. Tu definición es contradictoria allí.

Un poco más corto, pero más lento solo por unos pocos días:

<code>SELECT day, count(DISTINCT email) AS dist_emails
FROM  (SELECT generate_series('2013-01-01'::date
                            , '2013-01-06'::date, '1d')::date) AS g(day)
LEFT   JOIN tbl t ON t.day BETWEEN g.day - 2 AND g.day
GROUP  BY 1
ORDER  BY 1;
</code>
gran respuesta, gracias @ErwinBrandstetter Sean
¿Cómo lidiar con la colmena? Gracias. @ ErwinBrandstetter user4672728

Preguntas relacionadas