我的目标
我正试图跟踪和显示用户在我的应用程序上的每日连续发帖，但我很难编写一个可靠工作并返回准确计数的查询.
一些背景
我的应用程序有一个prompt表和一个post表.用户可以 for each 提示提交一个帖子(提示是每天创建的，所以每个用户每天一个帖子).
简化后的prompt表如下所示:
| id | dateKey | text |
|---|---|---|
| 1 | 20240101 | This is an example prompt. |
| 2 | 20240102 | Here is a second prompt. |
简化后的post桌是这样的:
| id | content | promptId | authorId |
|---|---|---|---|
| 50 | This is my response to the prompt. | 1 | 90 |
| 51 | A second response to the same prompt. | 1 | 91 |
当前查询
我try 了几种不同的查询方法(使用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
这似乎适用于下面的数据，但如果添加新的"Missing"提示(这应该会重置条纹)，此查询仍将返回2(我认为我理解原因，但不确定如何纠正它).
我需要的是
我基本上需要一些东西开始在最新的提示，并向下列表，直到它发现一个提示没有来自该用户的帖子.
例如，此关联数据的条纹将为2:
| id | dateKey | text | postContent | authorId |
|---|---|---|---|---|
| 1 | 20240104 | This is an example prompt. | This is my response. | 90 |
| 2 | 20240103 | Here is a second prompt. | A second response. | 90 |
| 3 | 20240102 | A third prompt. | null | null |
| 4 | 20240101 | My fourth prompt. | A third response, but I missed a day. | 90 |
功能性是最重要的，但如果它的性能也很好(prompt行可能有prompt0行，post行可能有数百万行，streak行可能达到prompt0行).
我对PostgreSQL处理这类事情的能力有点迷惑，所以希望有一个简单的解决方案！
菲德尔:https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/11431