每天查询计数，并有多个星期的日期限制

Question

我每天都在努力寻找＃个活跃用户。

当他做了一个用户是活动 超过 每周10个请求4 个连续周 。

IE。自2014年10月31日起，如果用户每周总共在以下之间进行10次以上的请求，则该用户处于活动状态：

1. 2014年10月24日至10月30日 ，以及
2. 2014年10月17日至10月23日 ，
3. 2014年10月10日至10月16日 ，并且
4. 2014年10月3日至10月9日

我有一张桌子requests：

CREATE TABLE requests (
  id text PRIMARY KEY, -- id of the request
  amount bigint,       -- sum of requests made by accounts_id to recipient_id,
                       -- aggregated on a daily basis based on "date"
  accounts_id text,    -- id of the user
  recipient_id text,   -- id of the recipient
  date timestamp       -- date that the request was made in YYYY-MM-DD
);

样本值：

INSERT INTO requests2
VALUES
    ('1',  19, 'a1', 'b1', '2014-10-05 00:00:00'),
    ('2',  19, 'a2', 'b2', '2014-10-06 00:00:00'),
    ('3',  85, 'a3', 'b3', '2014-10-07 00:00:00'),
    ('4',  11, 'a1', 'b4', '2014-10-13 00:00:00'),
    ('5',  2,  'a2', 'b5', '2014-10-14 00:00:00'),
    ('6',  50, 'a3', 'b5', '2014-10-15 00:00:00'),
    ('7',  787323, 'a1', 'b6', '2014-10-17 00:00:00'),
    ('8',  33, 'a2', 'b8', '2014-10-18 00:00:00'),
    ('9',  14, 'a3', 'b9', '2014-10-19 00:00:00'),
    ('10', 11, 'a4', 'b10', '2014-10-19 00:00:00'),
    ('11', 1628, 'a1', 'b11', '2014-10-25 00:00:00'),
    ('13', 101, 'a2', 'b11', '2014-10-25 00:00:00');

输出示例：

Date       | # Active users
-----------+---------------
10-01-2014 | 600
10-02-2014 | 703
10-03-2014 | 891

这是我尝试查找特定日期（例如2014年10月1日）的活动用户数的方法：

SELECT count(*)
FROM
  (SELECT accounts_id
   FROM requests
   WHERE "date" BETWEEN '2014-10-01'::date - interval '2 weeks' AND '2014-10-01'::date - interval '1 week'
   GROUP BY accounts_id HAVING sum(amount) > 10) week_1
JOIN
  (SELECT accounts_id
   FROM requests
   WHERE "date" BETWEEN '2014-10-01'::date - interval '3 weeks' AND '2014-10-01'::date - interval '2 week'
   GROUP BY accounts_id HAVING sum(amount) > 10) week_2 ON week_1.accounts_id = week_2.accounts_id
JOIN
  (SELECT accounts_id
   FROM requests
   WHERE "date" BETWEEN '2014-10-01'::date - interval '4 weeks' AND '2014-10-01'::date - interval '3 week'
   GROUP BY accounts_id HAVING sum(amount) > 10) week_3 ON week_2.accounts_id = week_3.accounts_id
JOIN
  (SELECT accounts_id
   FROM requests
   WHERE "date" BETWEEN '2014-10-01'::date - interval '5 weeks' AND '2014-10-01'::date - interval '4 week'
   GROUP BY accounts_id HAVING sum(amount) > 10) week_4 ON week_3.accounts_id = week_4.accounts_id

由于这只是获取1天数字的查询，因此我需要每天随时间获取此数字。我认为这个想法是进行联接以获取日期，因此我尝试执行以下操作：

SELECT week_1."Date_series",
       count(*)
FROM
  (SELECT to_char(DAY::date, 'YYYY-MM-DD') AS "Date_series",
          accounts_id
   FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 day') DAY, requests
   WHERE to_char(DAY::date, 'YYYY-MM-DD')::date BETWEEN requests.date::date - interval '2 weeks' AND requests.date::date - interval '1 week'
   GROUP BY "Date_series",
            accounts_id HAVING sum(amount) > 10) week_1
JOIN
  (SELECT to_char(DAY::date, 'YYYY-MM-DD') AS "Date_series",
          accounts_id
   FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 day') DAY, requests
   WHERE to_char(DAY::date, 'YYYY-MM-DD')::date BETWEEN requests.date::date - interval '3 weeks' AND requests.date::date - interval '2 week'
   GROUP BY "Date_series",
            accounts_id HAVING sum(amount) > 10) week_2 ON week_1.accounts_id = week_2.accounts_id
AND week_1."Date_series" = week_2."Date_series"
JOIN
  (SELECT to_char(DAY::date, 'YYYY-MM-DD') AS "Date_series",
          accounts_id
   FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 day') DAY, requests
   WHERE to_char(DAY::date, 'YYYY-MM-DD')::date BETWEEN requests.date::date - interval '4 weeks' AND requests.date::date - interval '3 week'
   GROUP BY "Date_series",
            accounts_id HAVING sum(amount) > 10) week_3 ON week_2.accounts_id = week_3.accounts_id
AND week_2."Date_series" = week_3."Date_series"
JOIN
  (SELECT to_char(DAY::date, 'YYYY-MM-DD') AS "Date_series",
          accounts_id
   FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 day') DAY, requests
   WHERE to_char(DAY::date, 'YYYY-MM-DD')::date BETWEEN requests.date::date - interval '5 weeks' AND requests.date::date - interval '4 week'
   GROUP BY "Date_series",
            accounts_id HAVING sum(amount) > 10) week_4 ON week_3.accounts_id = week_4.accounts_id
AND week_3."Date_series" = week_4."Date_series"
GROUP BY week_1."Date_series"

但是，我认为我没有得到正确的答案，也不确定为什么。任何提示/指导/指针将不胜感激！:) :)

PS。我正在使用Postgres 9.3

Answer 1

这是一个很长的答案，如何使您的查询简短。:)

table

在我的表上构建（在为表定义提供不同的（ 奇数！ ）数据类型之前：

CREATE TABLE requests (
   id           int
 , 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_id
FROM  (
   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
   ) sub
GROUP  BY 1
HAVING count(*) = 4;

步骤1

在最里面的 子查询w（“周”）中CROSS JOIN，用给定天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)::int
FROM  (
   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?
   ) sub2
GROUP  BY date
ORDER  BY date DESC
LIMIT  _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有 一排为每个用户相关的每一个相关的一天 。LEFT JOIN到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 OR NULL)）的用户。在日期上加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作为标识符的基本类型名称。曾经。