PostgreSQL：查找到目前为止的连续天数

本文介绍了PostgreSQL：查找到目前为止的连续天数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

目前我使用这种技术： / 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 )