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;

（编辑：湖南网）

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