这是一个很长的答案，如何使您的查询简短。:)
table
在我的表上构建（在为表定义提供不同的（ 奇数！ ）数据类型之前：
CREATE TABLE requests ( idint , accounts_id int -- (id of the user) , recipient_id int -- (id of the recipient) , date date -- (date that the request was made in YYYY-MM-DD) , amount int -- (# of requests by accounts_id for the day));
特定日期的活跃用户
某一天 的“活跃用户”列表：
SELECt accounts_idFROM ( SELECt w.w, r.accounts_id FROM ( SELECt w, day - 6 - 7 * w AS w_start, day - 7 * w AS w_end FROM (SELECt '2014-10-31'::date - 1 AS day) d -- effective date here, generate_series(0,3) w ) w JOIN requests r ON r."date" BETWEEN w_start AND w_end GROUP BY w.w, r.accounts_id HAVINg sum(r.amount) > 10 ) subGROUP BY 1HAVINg count(*) = 4;
步骤1
在最里面的 子查询w
（“周”）中
CROSSJOIN，用给定天1的a来构建4个感兴趣的周的边界，其输出为
generate_series(0-3)。
要向/从
date（而不是从时间戳！）中添加/减去天
integer数，只需将数字相加/减去即可。该表达式
day - 7 *w从给定日期减去7天的0-3倍，得出每个星期（）的 结束 日期
w_end。
分别减去另外6天（而不是7天）来计算相应的 开始时间 （
w_start）。
此外，请保留星期数
w（0-3），以进行以后的汇总。
第2步
在 子查询中，sub
联接行从
requests到4周的集合，其中日期位于开始日期和结束日期之间。
GROUPBY周号
w和
accounts_id。
只有总请求数超过10个的星期才有资格。
第三步
在 外部SELECT
计数中，每个用户（
accounts_id）合格的周数。必须为4才能成为“活动用户”
每天活跃用户数
这是 炸药 。
封装在一个简单的SQL函数中以简化常规用法，但是查询也可以单独使用：
CREATE FUNCTION f_active_users (_now date = now()::date, _days int = 3) RETURNS TABLE (day date, users int) AS$func$WITH r AS ( SELECT accounts_id, date, sum(amount)::int AS amount FROM requests WHERe date BETWEEN _now - (27 + _days) AND _now - 1 GROUP BY accounts_id, date )SELECt date + 1, count(w_ct = 4 OR NULL)::intFROM ( SELECt accounts_id, date , count(w_amount > 10 OR NULL) OVER (PARTITION BY accounts_id, dow ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS w_ct FROM ( SELECt accounts_id, date, dow , sum(amount) OVER (PARTITION BY accounts_id ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS w_amount FROM (SELECt _now - i AS date, i%7 AS dow FROM generate_series(1, 27 + _days) i) d -- period of interest CROSS JOIN ( SELECt accounts_id FROM r GROUP BY 1 HAVINg count(*) > 3 AND sum(amount) > 39 -- enough rows & requests AND max(date) > min(date) + 15) a -- can cover 4 weeks LEFT JOIN r USING (accounts_id, date) ) sub1 WHERe date > _now - (22 + _days) -- cut off 6 trailing days now - useful? ) sub2GROUP BY dateORDER BY date DESCLIMIT _days$func$ LANGUAGE sql STABLE;
该函数需要任何天（
_now），默认为“ today”，
_days结果中的天数（），默认为3。称呼：
SELECt * FROM f_active_users('2014-10-31', 5);或不带参数以使用默认值：
SELECt * FROM f_active_users();
该方法 不同于第一个查询 。
SQL Fiddle 具有查询和表定义的变体。
步骤0
为了获得更好的效果，在CTE中，仅针对感兴趣的期间
r预先汇总了每笔金额
(accounts_id, date)。该表仅扫描 一次，建议的索引（请参见打击）将在此处插入。
步骤1
在内部子查询中，
d生成必要的天数列表：
27 + _days行，其中，
_days是输出中所需的行数，有效地是28天或更长时间。
进行此操作时，请计算
dow要在步骤3中进行汇总的星期几（）
i%7与每周间隔一致，但是查询适用于 任何 间隔。
在内部子查询中，
a生成一个唯一的用户列表（
accounts_id），这些用户存在于CTE中，
r并通过了一些初步的表面测试（足够多的行跨越了足够的时间，并且有足够的总请求数）。
第2步
生成一个笛卡尔积
d，并
a用
CROSS JOIN有 一排为每个用户相关的每一个相关的一天 。
LEFTJOIN到
r追加请求的量（如果有的话）。没有
WHERe条件，我们希望每天都在结果，即使有根本没有活跃用户。
w_amount使用带有
自定义框架
的Window函数，在同一步骤中计算上周（）的总金额。
第三步
现在关闭最近6天；这是 可选的 ，可能会也可能不会帮助您提高性能。测试一下：
WHERe date >= _now - (21 + _days)
w_ct在类似的窗口函数中计算满足最低金额的星期（），此时间除以
dow另外的间隔，以使框架中的过去4周仅具有相同的工作日（其中包含各自过去一周的总和）。该表达式
count(w_amount10 OR NULL)仅计算具有10个以上请求的行。
第4步
在外部
SELECT群组中，按
date并计算通过了全部4周（
count(w_ct = 4 ORNULL)）的用户。在日期上加1以补偿不等于1的日期，
ORDER并
LIMIT加到请求的天数中。
表现与展望
这两个查询的理想索引是：
CREATE INDEX foo ON requests (date, accounts_id, amount);
性能应该不错，但是由于新的 移动聚合* 支持，即将推出的Postgres 9.4 甚至会更好（甚至更多）： *
*Postgres Wiki中的 *移动聚合支持
。
在9.4手册中移动聚集体
另外：请勿将
timestamp列称为“日期”，而是
timestamp，而不是
date。更好的是，永远不要使用诸如
date或
timestamp作为标识符的基本类型名称。曾经。
