如何在Eclipse的侧栏中更改突出显示的颜色？(How to change highlighted occurrences color in Eclipse's sidebar?)

 使用更大的数据集， 窗口函数是执行这些类型查询的最有效方式 - 表将仅扫描一次，而不是每个日期一次，就像自连接一样。 它也看起来很简单。 :) 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 。 这将把电子邮件归咎于他们的最早日期（因为我按照升序排列按create_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);

With larger datasets, window functions are the most efficient way to perform these kinds of queries -- the table will be scanned only once, instead of once for each date, like a self-join would do. It also looks a lot simpler. :) PostgreSQL 8.4 and up have support for window functions.
 
This is what it looks like:
 
SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM subscriptions
GROUP BY created_at;
 
Here OVER creates the window; ORDER BY created_at means that it has to sum up the counts in created_at order.
 
 
Edit: If you want to remove duplicate emails within a single day, you can use sum(count(distinct email)). Unfortunately this won't remove duplicates that cross different dates.
 
If you want to remove all duplicates, I think the easiest is to use a subquery and DISTINCT ON. This will attribute emails to their earliest date (because I'm sorting by created_at in ascending order, it'll choose the earliest one):
 
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;
 
If you create an index on (email, created_at), this query shouldn't be too slow either.
 
 
(If you want to test, this is how I created the sample dataset)
 
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);