PostgreSQL：查找到现在为止的连续天数

一尘不染

PostgreSQL：查找到现在为止的连续天数

sql

给定一堆带有时间戳字段的记录（代表我的应用程序中的签到），哪种方法是确定连续签到的当前连胜的好方法？

换句话说，随着签入时间按签入时间降序排列，直到用户错过一天之前有多少条记录？

目前，我正在使用这种技术：

SELECT distinct(uca.created_at::date) as created_at
    FROM user_challenge_activities as uca INNER JOIN user_challenges as uc
    ON user_challenge_id = uc.ID WHERE uc.user_id = #{user.id}
    order by (uca.created_at::date) DESC;

…我将签到时间戳转换为日期（以例如2012-03-20结尾），然后在代码中遍历记录并增加一个计数器，直到记录与下一条记录之间的日期大于1天。

但是，这种方法对我来说似乎很笨拙，而且似乎是Postgres擅长的事情。

那么实际上是否有更好的方法来实现这一目标？

阅读 359

2021-05-23

共1个答案

一尘不染

with t as (
    SELECT distinct(uca.created_at::date) as created_at
    FROM user_challenge_activities as uca 
    INNER JOIN user_challenges as uc ON user_challenge_id = uc.ID 
    WHERE uc.user_id = #{user.id}
    )
select count(*)
from t
where t.create_at > (
    select d.d
    from generate_series('2010-01-01'::date, CURRENT_DATE, '1 day') d(d)
    left outer join t on t.created_at = d.d::date
    where t.created_at is null
    order by d.d desc
    limit 1
)

2021-05-23