如何计算用户的每日连续记录？

Question

目标：
我想追踪并显示用户每天在应用内连续发布的记录（每日一帖），但在编写一个可靠且能准确计数的查询方面遇到了困难。

背景：
我的应用有一个prompt表和一个post表。用户可以为每个提示提交一条帖子（提示每天创建一次，所以每位用户每天最多一条帖子）。

prompt表简化后如下：

| id | dateKey | text |
|----|---------|--------------------------------------------|
| 1 | 20240101 | 这是一个示例提示。 |
| 2 | 20240102 | 这是一个第二个提示。 |

post表简化后类似这样：

| id | content                         | promptId | authorId |
|----|--------------------------------|----------|----------|
| 50 | 这是我对该提示的回答。           | 1        | 90       |
| 51 | 对同一提示的第二个回答。          | 1        | 91       |

当前查询：
我已经尝试了几种不同的查询方法（包括使用PARTITION BY、dense_rank()等），但只能获取到用户的最长连续记录次数。我尝试过的查询如下所示：

select distinct on (p.authorId) count(distinct "dateKey"::date) as "streak"
from (
    select p.*, dense_rank() over (partition by p.authorId order by "dateKey"::date) as seq
    from post p
    join prompt pt on p."promptId" = pt.id
) p
join prompt pt on p."promptId" = pt.id
where p.authorId = 90
group by p.authorId, "dateKey"::date - seq * interval '1 day'
order by p.authorId, streak desc

这段查询似乎在我提供的数据上有效，但如果添加一个新的用户未发布帖子的日期（即中断了连续记录），查询仍然会返回2天（我认为我理解为什么会这样，但不确定如何纠正这个问题）。

需求：
我需要一个查询能够从最新的提示开始，向下遍历列表，直到找到用户没有发布帖子的那一天为止。

例如，对于下面的数据，这位用户（ID为90）的连续记录应该是2天：

| id | dateKey | text                                     | postContent                           | authorId |
|----|---------|------------------------------------------|---------------------------------------|----------|
| 1  | 20240104 | 这是一个示例提示。                       | 这是我的回答。                        | 90       |
| 2  | 20240103 | 这是一个第二个提示。                     | 第二个回答。                          | 90       |
| 3  | 20240102 | 第三个提示。                             | null                                   | null     |
| 4  | 20240101 | 我的第四个提示。                         | 虽然错过了某一天，但这是第三个回应。 | 90       |

功能正确性最重要，但如果查询性能良好就更好了（prompt表可能有上千行，post表可能有上百万行，连续记录长度可能达到1000天）。

我对PostgreSQL在这方面的能力感到有些困惑，希望能有一个简单的解决方案！

数据库沙盒链接：https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/11431

robertklep · Answer

这确实类似于岛屿与间隙问题（island and gap problem）。我根据问题要求已经创建了一个针对单一 authorid（p.authorId = 90）的查询。若要获取所有 authorIds 的数据，您可以移除连接条件。

解决方案是使用窗口函数，如下所示：

SELECT authorId, MAX(sm) 
FROM (
    SELECT t.*, 
           SUM(CASE WHEN prev_promptId IS NULL THEN 1 END) OVER (PARTITION BY p.authorId ORDER BY "dateKey"::date) AS sm
    FROM (
        SELECT pt.*, p.*, 
               LAG(p.promptId) OVER (PARTITION BY p.authorId ORDER BY "dateKey"::date) AS prev_promptId
        FROM prompt pt
        LEFT JOIN post p ON p.promptId = pt.id AND p.authorId = 90
    ) t
) t
GROUP BY authorId;

Nosredna · Answer

对于具有自然数值递增情况的单个表，通常有更简单的解决方案。但对于结合了两个表且下一个promptId看似随机的情况，预计使用递归公共表表达式（Recursive Common Table Expression，CTE）可以获得最佳性能：

WITH RECURSIVE cte AS (
   SELECT CURRENT_DATE AS the_day, p."authorId" AS author_id
   FROM   prompt pt
   JOIN   post  p ON p."promptId" = pt.id
   WHERE  pt."dateKey" = CURRENT_DATE
   AND    p."authorId" = 90  -- 在这里替换你的作者ID

   UNION ALL
   SELECT c.the_day - INTERVAL '1 day', p."authorId"   -- 假设prompt表中没有日期间隔
   FROM   cte   c
   JOIN   prompt pt ON pt."dateKey" = c.the_day - INTERVAL '1 day'
   JOIN   post  p  ON p."promptId" = pt.id
   WHERE  p."authorId" = c.author_id
)
SELECT count(*)
FROM   cte;

在线示例

绝对需要索引支持才能实现快速查询。理想情况下，应建立一个prompt("dateKey", id)的索引以及一个post("authorId", "promptId")的索引。

假设...

...我们正在查询给定的一个用户，
...我们从“今天”开始，
...prompt表中没有日期间隔——每天恰好有一条记录，
...prompt."dateKey"字段类型为date（正如它应该的那样）。

数据库设计

如果每天最多只有一个prompt，建议在prompt表中使用日期（数据类型为date！）作为主键，并在post表中作为外键。这将允许编写更简洁的查询。参见：

选择最长连续序列