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

我有一组电子邮件地址和日期,这些电子邮件地址已添加到表格中.各种不同日期的电子邮件地址可以有多个条目.例如,如果我有下面的数据集.我希望得到我们在所述日期和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
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;

（编辑：东莞站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!