我有一组电子邮件地址和日期,这些电子邮件地址已添加到表格中。对于各种不同的日期,可以存在电子邮件地址的多个条目。例如,如果我有下面的数据集。我会寻找获得在所述日期和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 | test@test 1/1/12 | test1@test 1/1/12 | test2@test 1/2/12 | test1@test 1/2/12 | test2@test 1/3/12 | test@test 1/4/12 | test@test 1/5/12 | test@test 1/5/12 | test@test 1/6/12 | test@test 1/6/12 | test@test 1/6/12 | test1@test如果我们使用的日期周期为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', 'test@test') ,('2012-01-01', 'test1@test') ,('2012-01-01', 'test2@test') ,('2012-01-02', 'test1@test') ,('2012-01-02', 'test2@test') ,('2012-01-03', 'test@test') ,('2012-01-04', 'test@test') ,('2012-01-05', 'test@test') ,('2012-01-05', 'test@test') ,('2012-01-06', 'test@test') ,('2012-01-06', 'test@test') ,('2012-01-06', 'test1@test`');查询 - 只返回 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;更多推荐
查询滚动日期范围内不同值的计数
发布评论