PostgreSQL：查找到目前为止的连续天数 [英] PostgreSQL: find number of consecutive days up until now

问题描述

给定一些记录（代表我的应用程序中的签名），带有时间戳记字段，确定当前连续签入的条件是什么？ >换句话说，按照签到时间降序排序的签到记录，直到用户错过了一天之后，有多少条记录？

目前我使用这种技术： / p>

  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-03 -20），然后在代码中，通过记录并增加一个计数器，直到记录和下一个记录之间的日期大于1天。

然而，这个方法对我来说似乎很笨拙，而且看起来好像Postgres会擅长的那样。

那么实际上这是一个更好的方式呢？

解决方案

 以t为（
 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} 
）
选择计数（*）
从t 
其中t.create_at> （
从generate_series中选择dd 
（'2010-01-01':: date，CURRENT_DATE，'1天'）d（d）
左外连接t on t.created_at = dd :: date 
其中t.created_at为null 
 order by dd desc 
 limit 1 
）
  

Given a bunch of records (which represent checkins in my app) with a timestamp field, what would be a good way to determine the current streak of consecutive checkins?

In other words, with the checkins sorted by checkin time descending, how many records are there until a user missed a day?

Currently I'm using this technique:

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;

...where I cast the checkin timestamps to a date (to end up with e.g. 2012-03-20), then in code, go through the records and increment a counter until the date between the record and the next record is greater than 1 day.

However, this approach seems clumsy to me, and it seems like the sort of thing that Postgres would excel at.

So is there in fact a better way to accomplish this?

解决方案

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
)

这篇关于PostgreSQL：查找到目前为止的连续天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！