查询滚动日期范围内不同值的计数 [英] Query for count of distinct values in a rolling date range

问题描述

我有一组电子邮件地址和日期，这些电子邮件地址已添加到表格中。对于各种不同的日期，可以存在电子邮件地址的多个条目。例如，如果我有下面的数据集。我会寻找获得在所述日期和3天前的不同电子邮件的日期和数量。

I have a data set of email addresses and dates that those email addresses were added to a table. There can be multiple entries of an email address for various different dates. For example, if I have the data set below. I would be looking to get the date and count of distinct emails that we have between said date and 3 days ago.

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

Result set would look something like this if we use a date period of 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

我可以使用下面的查询得到不同的日期范围计数，以天为单位的范围计数，因此我不必手动更新数百个日期的范围。

I can get a distinct count of a date range using the query below, but looking to get a count of a range by day so I do not have to manually update the range for hundreds of dates.

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', '[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;

或 - 在指定范围内返回全部没有行的日期：

Or - return all days in the specified range, even if there are no rows for the day:

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

这听起来像是窗口函数，但我没有找到一种方法来定义合适的窗口框架。此外，每个文档：

This sounded like a job for window functions at first, but I did not find a way to define the suitable window frame. Also, per documentation:

聚合窗口函数与正常聚合函数不同，不要
允许 DISTINCT 或 ORDER BY 以在函数参数列表中使用。

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

解决它与相关的子查询。我想这是最聪明的方式。

So I solved it with correlated subqueries instead. I guess that's the smartest way.

我将您的日期列重命名为 day ，因为这是不好的做法，

I renamed your date column to day, because it is bad practice to use type names as identifiers.

BTW，介于所述日期和3天前是指 4 天的期间。

BTW, "between said date and 3 days ago" would be a period of 4 days. Your definition is contradictory there.

有点短，但只有几天慢：

A bit shorter, but slower for only a few days:

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;

这篇关于查询滚动日期范围内不同值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！