Pergunta sobre count, postgresql, date, sql – Consulta para contagem de valores distintos em um intervalo de datas rotativo

9

Eu tenho um conjunto de dados de endereços de email e datas em que esses endereços de email foram adicionados a uma tabela. Pode haver várias entradas de um endereço de e-mail para várias datas diferentes. Por exemplo, se eu tiver o conjunto de dados abaixo. Eu estaria procurando obter a data e a contagem de e-mails distintos que temos entre essa data e três dias atrás.

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

O conjunto de resultados seria semelhante a este se usarmos um período de data 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>

Posso obter uma contagem distinta de um período usando a consulta abaixo, mas procurando obter uma contagem de um intervalo por dia, por isso não preciso atualizar manualmente o intervalo para centenas de datas.

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

Ajuda é apreciada.

Sua resposta

4   a resposta
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 isto ajude.

Isso é chamado de PostgreSQL. Nãoconvert() funcionar aqui. (Bem, há umconvert() função, mas é para converter codificações, não tipos de dados como no servidor SQL.) Além disso, as assinaturas são desencorajadas, seu talento na parte inferior direita cuida disso. VejoAqui ouAqui. Erwin Brandstetter
0

você sempre pode usar uma função dateadd:

<code>test.date > dateadd(dd,-7,getdate())
</code>
Não há "dateadd ()" no PostgreSQL. Erwin Brandstetter
11

Caso de teste:

<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 - retorna apenas os dias em que existe uma 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>

Ou - retornartodos os dias no intervalo especificado, mesmo se não houver linhas para o dia:

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

Isso soou como um trabalho parafunções da janela no início, mas eu não encontrei uma maneira de definir o quadro de janela adequado. Além disso,por documentação:

As funções de janela agregadas, ao contrário das funções agregadas normais, não permitemDISTINCT ouORDER BY para ser usado dentro da lista de argumentos da função.

Então resolvi com subconsultas correlacionadas. Eu acho que é o caminho mais inteligente.

Eu renomei sua coluna de data paraday, porque é uma má prática usar nomes de tipos como identificadores.

BTW, "entre essa data e 3 dias atrás" seria um período de4 dias. Sua definição é contraditória lá.

Um pouco mais curto, mas mais lento por apenas alguns dias:

<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>
Como lidar com a colméia? Obrigado. @ ErwinBrandstetter user4672728
ótima resposta, obrigado @ErwinBrandstetter Sean
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>

Perguntas relacionadas