postgresql 如何计算一个用户的每日连胜？

y1aodyip 于 11个月前发布在 PostgreSQL

关注(0)|答案(2)|浏览(132)

我的目标
我试图跟踪和显示用户每天在我的应用程序上发布的连续记录，但很难编写一个可靠的查询，并返回一个准确的计数。

一些背景

我的应用程序有一个prompt和一个post表。用户可以为每个提示提交一个帖子（提示是每天创建的，所以每个用户每天一个帖子）。
简化后的prompt表如下所示：
| ID|日期键|文本|
| --|--|--|
| 1 | 20240101 |这是一个示例提示。|
| 2 | 20240102 |下面是第二个提示。|
简化的post表类似于：
| ID|内容|提示ID| 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

字符串
这似乎适用于下面的数据，但如果您添加一个新的“missed”提示（这将重置条纹），此查询仍将返回2（我想我明白为什么，但不确定如何更正它）。
我需要的是
我基本上需要从最新的提示符开始，然后沿着列表向下走，直到找到一个没有该用户帖子的提示符。
例如，此关联数据的条纹为2：
| ID|日期键|文本|发布内容|authorId|
| --|--|--|--|--|
| 1 | 20240104 |这是一个示例提示。|这是我的回应。| 90 |
| 2 | 20240103 |下面是第二个提示。|第二个回应。| 90 |
| 3 | 20240102 |第三个提示。|null| null|
| 4 | 20240101 |我的第四个提示。|第三次回应，但我错过了一天。| 90 |
功能性是最重要的，但如果它的性能也很好（prompt可能有1000行，post可能有数百万行，streak可能达到1000行）。
我对PostgreSQL在这方面的能力有点困惑，所以希望有一个简单的解决方案！
小提琴：https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/11431

postgresql

来源：https://stackoverflow.com/questions/77756315/how-to-calculate-a-users-daily-streak

2条答案

按热度按时间

i5desfxk1#

对于自然值级数的单个表，有更简单的解决方案。但是对于两个表的组合，以及（看似）任意的下一个promptId，我希望recursive 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  -- your author here!
   
   UNION ALL
   SELECT c.the_day - 1, p."authorId"   -- assuming no gaps in prompt!
   FROM   cte   c
   JOIN   prompt pt ON pt."dateKey" = c.the_day - 1
   JOIN   post  p  ON p."promptId" = pt.id
   WHERE  p."authorId" = c.author_id
   )
SELECT count(*)
FROM   cte;

字符串
fiddle的
绝对需要索引支持快速。

理想情况下 *，prompt("dateKey", id)上有一个索引，post("authorId", "promptId")上有一个索引。

假设...

.我们查询一个给定的用户，
我们从“今天”开始
.没有提示的间隙-每天只输入一次，
... prompt."dateKey"是date类型（应该是这样）。

DB设计

如果每天最多出现一次提示，可以考虑将日期（数据类型date！）作为prompt表中的PK和post表中的FK，这样可以使查询更加简单，请参阅：

Select longest continuous sequence的

赞(0）回复(0）举报 11个月前

7kqas0il2#

这似乎是岛屿和差距问题和差距问题。
我已经按照问题中的要求为单个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;

字符串

赞(0）回复(0）举报 11个月前

我来回答

postgresql 如何计算一个用户的每日连胜？

一些背景

当前查询

2条答案

DB设计

相关问题

热门标签

最新问答