查询滚动日期范围内不同值的计数

Question

我有一组电子邮件地址和将这些电子邮件地址添加到表中的日期的数据集。电子邮件地址在不同的日期可以有多个条目。例如，如果我有下面的数据集。我希望获得上述日期和3天前之间不同电子邮件的日期和计数。

Date   | email  
-------+----------------
1/1/12 | [e-mail]
1/1/12 | [e-mail]
1/1/12 | [e-mail]
1/2/12 | [e-mail]
1/2/12 | [e-mail]
1/3/12 | [e-mail]
1/4/12 | [e-mail]
1/5/12 | [e-mail]
1/5/12 | [e-mail]
1/6/12 | [e-mail]
1/6/12 | [e-mail]
1/6/12 | [e-mail]

如果我们使用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;

感谢您的帮助。

Answer 1

测试用例：

CREATE TEMP TABLE tbl (day date, email text);
INSERT INTO tbl VALUES
 ('2012-01-01', '[e-mail]')
,('2012-01-01', '[e-mail]')
,('2012-01-01', '[e-mail]')
,('2012-01-02', '[e-mail]')
,('2012-01-02', '[e-mail]')
,('2012-01-03', '[e-mail]')
,('2012-01-04', '[e-mail]')
,('2012-01-05', '[e-mail]')
,('2012-01-05', '[e-mail]')
,('2012-01-06', '[e-mail]')
,('2012-01-06', '[e-mail]')
,('2012-01-06', '[e-mail]`');

查询-仅返回条目中存在的天数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

起初，这听起来像是完成窗口功能的工作，但是我没有找到定义合适的窗口框架的方法。另外，根据文档：

与普通的聚合函数不同，聚合窗口函数不允许DISTINCT或ORDER BY不能在函数参数列表中使用。

因此，我改为使用相关子查询来解决它。我想那是最聪明的方法。

我将您的日期列重命名为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;