sql - 使用 pgsql/activerecord 进行队列分析

Question

我正在对单个表 messages 执行同期群分析。我需要计算创建消息(第 0 天)的用户的保留率，还在第二天、后天等(第 1 天、第 2 天等)创建了一条消息。

我之前在 ruby​​ 迭代中完成了大部分处理后查询。现在我有更大的表要处理。它在 ruby​​ 中太慢且内存密集，所以我需要将繁重的工作卸载到数据库。我也试过 cohort_me gem 并且性能不佳。

我对不带 activerecord 的 SQL 经验不多。这是我到目前为止所拥有的:

SELECT 
date_trunc('day', messages.created_at) as day,
count(distinct messages.user_id) as day_5_users
FROM 
messages
WHERE 
messages.created_at >= date_trunc('day', now() - interval '5 days') AND 
messages.created_at < date_trunc('day', now() - interval '4 days')
GROUP BY 1
ORDER BY 1;

这将返回五天前创建消息的用户数。现在我需要找到第二天、后天等直到当天创建消息的 THOSE 用户数。

我需要在不同的基准日执行相同的分析。所以接下来不是 5 天，而是从 4 天前开始分析作为基准日。

这可以用一个查询完成吗？

编辑 messages.user_id 实际上不是另一个表的键。它只是一个唯一标识符(字符串)，因此没有其他表可以与此查询连接。

Answer 1

堆分析有一个很好的 blog post about lateral joins做一些非常相似的事情。它可能会给你一些想法。你的情况实际上比他们的简单，所以你的解决方案也更容易。

首先是一些注意事项。您似乎不需要 day 输出，因为它总是等于您的输入。其次，无论如何，您每天都需要一个单独的输出列(或将结果累积在一个数组中，这似乎不太理想)，因此如果您想要可变天数，则必须动态构建 SQL那个。

为了测试，我制作了一个表格并给了它几行:

create table messages (user_id integer, created_at timestamp);
insert into messages values (1, now() - interval '5 days'), (1, now() - interval '4 days'), (1, now() - interval '2 days');
insert into messages values (2, now() - interval '10 days'), (2, now() - interval '2 days');
insert into messages values (3, now() - interval '2 days'), (3, now() - interval '1 days');
insert into messages values (4, now() - interval '5 days');

我认为你可以使用横向连接得到一个非常干净的解决方案，有点像上面的文章:

\set start_time '''2016-06-23 06:00:00'''

WITH t(s) AS (
  SELECT  :start_time::timestamp
)
SELECT  COUNT(DISTINCT m1.user_id) AS day_5_messages,
        COUNT(DISTINCT m2.user_id) AS day_4_messages,
        COUNT(DISTINCT m3.user_id) AS day_3_messages,
        COUNT(DISTINCT m4.user_id) AS day_2_messages,
        COUNT(DISTINCT m5.user_id) AS day_1_messages
FROM    messages m1
CROSS JOIN t
LEFT OUTER JOIN LATERAL (
    SELECT * FROM messages msub
    WHERE msub.user_id = m1.user_id
    AND msub.created_at <@
      tsrange(t.s + interval '1 day',
              t.s + interval '2 days')
    LIMIT 1
) m2
ON true
LEFT OUTER JOIN LATERAL (
    SELECT * FROM messages msub
    WHERE msub.user_id = m2.user_id
    AND msub.created_at <@
      tsrange(t.s + interval '2 days',
              t.s + interval '3 days')
    LIMIT 1
) m3
ON true
LEFT OUTER JOIN LATERAL (
    SELECT * FROM messages msub
    WHERE msub.user_id = m3.user_id
    AND msub.created_at <@
      tsrange(t.s + interval '3 days',
              t.s + interval '4 days')
    LIMIT 1
) m4
ON true
LEFT OUTER JOIN LATERAL (
    SELECT * FROM messages msub
    WHERE msub.user_id = m4.user_id
    AND msub.created_at <@
      tsrange(t.s + interval '4 days',
              t.s + interval '5 days')
    LIMIT 1
) m5
ON true
WHERE   m1.created_at <@
  tsrange(t.s,
          t.s + interval '1 day')
;

我在这里使用 t(s) CTE 只是为了避免一次又一次地重复 :start_time。如果您不喜欢它，它是可选的。同样自然地，在 Rails 中，您会使用 ? 而不是 :start_time 来参数化查询。

对于测试，将每个 COUNT(...) 替换为 array_agg(...) 很有帮助，这样您就可以确定是否正确的 user_id 是否包含在内。

我认为如果您在 created_at 和 user_id 上有一个索引(一起)，这应该会表现良好。或者，如果您的每一天总是在同一时刻开始(比如 UTC 午夜)，那么您可以使用仅包含日期(而非时间戳)和 user_id 的函数索引，然后将所有范围条件替换为只是那一天。这会表现得更好。

还有:您的查询(和我的)总是只返回一行，这看起来很可疑。我想知道这是否真的是您想要的，或者这是否只是为您的问题简化事情的意外。如果您希望每个开始日一行，那么您可以将 day 列放回原位，按它分组，删除我的 WHERE 条件，然后根据之前的条件进行所有连接m 表而不是 t.s。