Вопрос по postgresql, date, count, sql – Запрос на подсчет различных значений в скользящем диапазоне дат

9

У меня есть набор данных адресов электронной почты и даты, когда эти адреса были добавлены в таблицу. Для разных дат может быть несколько записей адреса электронной почты. Например, если у меня есть набор данных ниже. Я хотел бы получить дату и количество различных электронных писем, которые мы имеем между указанной датой и 3 днями назад.

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

Набор результатов будет выглядеть примерно так, если мы будем использовать период даты 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>

Я могу получить различный счетчик диапазона дат, используя запрос ниже, но хочу получить счетчик диапазона по дням, поэтому мне не нужно вручную обновлять диапазон для сотен дат.

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

Помощь приветствуется.

Ваш Ответ

4   ответа
0

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
0

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

надеюсь это поможет.

Это называется PostgreSQL. нетconvert() функционировать здесь. (Ну, естьconvert() функция, но она предназначена для преобразования кодировок, а не типов данных, как в SQL-сервере.) Кроме того, подписи не приветствуются, об этом позаботится ваш талант в правом нижнем углу. Увидетьhere или жеhere.
11

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]`');

Запрос - возвращает только те дни, когда запись существуетtbl:

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;

Или - вернутьall days в указанном диапазоне, даже если для дня нет строк:

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)

Результат:

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

Это звучало как работа дляоконные функции сначала, но я не нашел способа определить подходящую оконную раму. Также,по документации:

Aggregate window functions, unlike normal aggregate functions, do not allow DISTINCT or ORDER BY to be used within the function argument list.

Поэтому я решил это с помощью коррелированных подзапросов. Я думаю, что это самый умный способ.

Я переименовал ваш столбец даты вdayпотому что это плохая практика использовать имена типов в качестве идентификаторов.

Кстати, "между указанной датой и 3 днями назад" будет период4 дней. Ваше определение там противоречиво.

Немного короче, но медленнее всего на несколько дней:

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;
Как бороться с ульем? Спасибо. @ ErwinBrandstetter
отличный ответ, спасибо @ErwinBrandstetter
0

test.date > dateadd(dd,-7,getdate())
Нет & quot; dateadd () & quot; в PostgreSQL.

Похожие вопросы