给定一些记录(代表我的应用程序中的签名),带有时间戳记字段,确定当前连续签入的条件是什么? >换句话说,按照签到时间降序排序的签到记录,直到用户错过了一天之后,有多少条记录?
目前我使用这种技术: / 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:查找到目前为止的连续天数
发布评论