对于较大的数据集,窗口函数是执行此类查询的最有效方式 - 表格只会被扫描一次,而不是像自连接一样每个日期都要扫描一次。 它看起来也更简单。 :) PostgreSQL 8.4及以上版本支持窗口函数。
它是这个样子的:
SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM subscriptions
GROUP BY created_at;
OVER
创建窗口;ORDER BY created_at
表示必须按照created_at
的顺序汇总计数。
编辑:如果您想在一天内删除重复的电子邮件,可以使用sum(count(distinct email))
。不幸的是,这无法消除跨越不同日期的重复项。
如果要删除所有重复项,则最简单的方法是使用子查询和DISTINCT ON
。这将把电子邮件归属到它们最早的日期(因为我按升序排序created_at
,所以它会选择最早的日期):
SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM (
SELECT DISTINCT ON (email) created_at, email
FROM subscriptions ORDER BY email, created_at
) AS subq
GROUP BY created_at;
如果你在
(email, created_at)
上创建了一个索引,那么这个查询也不应该太慢。
(如果您想进行测试,这是我创建样本数据集的方法)
create table subscriptions as
select date '2000-04-04' + (i/10000)::int as created_at,
'foofoobar@foobar.com' || (i%700000)::text as email
from generate_series(1,1000000) i;
create index on subscriptions (email, created_at);
over
正在做的是将count
数字相加,但我仍然需要在每个后续日期重新计算唯一的电子邮件。 - khairulDISTINCT ON
子查询。它仍然比Andriy的答案快得多 - 可以在几秒钟内处理一百万行数据 - 但可能更加复杂。 - intgrDISTINCT ON
也可以转换为具有GROUP BY
的等效查询;在这种情况下,SELECT email,MIN(created_at)as created_at FROM subscriptions GROUP BY email
。哪种方法更有效可能会有所不同,尽管从DISTINCT ON
准备排序的子查询似乎为窗口函数需要的排序提供了一些优势。 - IMSoProllup
的答案。在2021年及以后,rollup
似乎是更高效的方法。 - Little Code