Pytanie w sprawie postgresql, sql, date, count – Zapytanie o liczbę odrębnych wartości w zakresie dat walcowania

9

Mam zestaw danych adresów e-mail i daty dodania tych adresów e-mail do tabeli. Może istnieć wiele wpisów adresu e-mail dla różnych dat. Na przykład, jeśli mam dane ustawione poniżej. Chciałbym uzyskać datę i liczbę różnych e-maili, które mamy między tą datą a 3 dniami temu.

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

Zestaw wyników wyglądałby mniej więcej tak, gdybyśmy używali okresu daty 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>

Za pomocą poniższego zapytania mogę uzyskać wyraźną liczbę zakresów dat, ale chcę uzyskać liczbę zakresów w ciągu dnia, więc nie muszę ręcznie aktualizować zakresu dla setek dat.

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

Pomoc jest doceniana.

Twoja odpowiedź

4   odpowiedź
11

Przypadek testowy:

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

Zapytanie - zwraca tylko dni, w których wpis istniejetbl:

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

Lub - wróćwszystkie dni w określonym zakresie, nawet jeśli w danym dniu nie ma wierszy:

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

Wynik:

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

To brzmiało jak zadaniefunkcje okna na początku, ale nie znalazłem sposobu na zdefiniowanie odpowiedniej ramy okiennej. Również,na dokumentację:

Funkcje okna agregacji, w przeciwieństwie do zwykłych funkcji agregujących, nie pozwalająDISTINCT lubORDER BY do użycia na liście argumentów funkcji.

Rozwiązałem to za pomocą skorelowanych podkwerend. Myślę, że to najmądrzejszy sposób.

Zmieniłem nazwę kolumny z datą naday, ponieważ niewłaściwą praktyką jest używanie nazw typów jako identyfikatorów.

BTW, „między wspomnianą datą a 3 dniami” byłby okres4 dni. Twoja definicja jest tam sprzeczna.

Trochę krótszy, ale wolniejszy tylko przez kilka dni:

<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>
świetna odpowiedź, dzięki @ErwinBrandstetter Sean
Jak radzić sobie z ulem? Dziękuję @ ErwinBrandstetter user4672728
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

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

mam nadzieję że to pomoże.

Jest to oznaczone jako PostgreSQL. Nieconvert() funkcja tutaj. (Cóż, jestconvert() funkcja, ale służy do konwertowania kodowań, a nie typów danych, jak w serwerze SQL. Widziećtutaj lubtutaj. Erwin Brandstetter
0

<code>test.date > dateadd(dd,-7,getdate())
</code>
W PostgreSQL nie ma „dateadd ()”. Erwin Brandstetter

Powiązane pytania