我有一组电子邮件地址和日期,这些电子邮件地址已添加到表格中.各种不同日期的电子邮件地址可以有多个条目.例如,如果我有下面的数据集.我希望得到我们在所述日期和3天前之间的不同电子邮件的日期和数量.
Date | email -------+---------------- 1/1/12 | test@test.com 1/1/12 | test1@test.com 1/1/12 | test2@test.com 1/2/12 | test1@test.com 1/2/12 | test2@test.com 1/3/12 | test@test.com 1/4/12 | test@test.com 1/5/12 | test@test.com 1/5/12 | test@test.com 1/6/12 | test@test.com 1/6/12 | test@test.com 1/6/12 | test1@test.com
如果我们使用3的日期,结果集看起来像这样
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
我可以使用下面的查询获得日期范围的明确计数,但是希望按天计算范围,这样我就不必手动更新数百个日期的范围.
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;
感谢帮助.
解决方法
测试用例：
CREATE TEMP TABLE tbl (day date,email text);
INSERT INTO tbl VALUES
('2012-01-01','test@test.com'),('2012-01-01','test1@test.com'),'test2@test.com'),('2012-01-02',('2012-01-03',('2012-01-04',('2012-01-05',('2012-01-06','test1@test.com`');
查询 – 仅返回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;
或者 – 返回指定范围内的所有日期,即使当天没有行：
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
这听起来像window functions的工作,但我没有找到一种方法来定义合适的窗框.另外,per documentation：
Aggregate window functions,unlike normal aggregate functions,do not
allowDISTINCTorORDER BYto 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;
