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

本文介绍了查询滚动日期范围内不同值的计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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