ruby-on-rails - 使用postgres在Rails中为用户找到最长的日子

我正试图确定history的最连续天数。我试图使用here中的解决方案，但由于某种原因，我收到了nil。
我知道还有其他类似于here的SQL解决方案，但是SQL不是我的强项。
我需要找到什么是一个用户最长的连续历史记录，没有中断（任何没有历史记录的天数将打破该记录）。
表格示例：

Class History
user_id: 1, created_at: '2018-01-02' # streak 1 #=> start
user_id: 1, created_at: '2018-01-03' # streak 2
user_id: 1, created_at: '2018-01-04' # streak 3
user_id: 1, created_at: '2018-01-04' # streak 3 #=> end
user_id: 1, created_at: '2018-01-06' # streak 1 #=> start and end
user_id: 1, created_at: '2018-01-08' # streak 1 #=> start
user_id: 1, created_at: '2018-01-09' # streak 2 #=> end
user_id: 1, created_at: '2018-01-11' # streak 1 #=> start and end
user_id: 1, created_at: '2018-01-17' # streak 1 #=> start
user_id: 1, created_at: '2018-01-18' # streak 2
user_id: 1, created_at: '2018-01-19' # streak 2
user_id: 1, created_at: '2018-01-20' # streak 3
user_id: 1, created_at: '2018-01-21' # streak 4
user_id: 1, created_at: '2018-01-22' # streak 5 # end # the longest steak 5 I need to receive 5
user_id: 1, created_at: '2018-01-25' # ...

我需要得到数字5作为它的最长连胜（连续5天）。
找到最长连胜的开始日和结束日也不错，但不是必须的。
我试过的是：

qry  = <<-SQL
  SELECT (CURRENT_DATE - series_date::date) AS days
  FROM generate_series(
         ( SELECT created_at::date FROM histories
           WHERE histories.user_id = :user_id
           ORDER BY created_at
           ASC LIMIT 1
         ),
         CURRENT_DATE,
         '1 day'
       ) AS series_date
  LEFT OUTER JOIN histories ON histories.user_id = :user_id AND
                           histories.created_at::date = series_date
  GROUP BY series_date
  HAVING COUNT(histories.id) = 0
  ORDER BY series_date DESC
  LIMIT 1
SQL

History.find_by_sql([ qry, { user_id: current_user.id } ])

但答案是：

# => [#<History id: nil>]

也许是因为我在用Postgres，但我是通过转换器运行的，所以看起来没问题。还是不呢？
我真的很感激任何解决这个问题的有效方法。

最佳答案

这个问题在this blog post中得到了分析和解决（我认为是非常出色的）。
稍微适应一下就可以解决你的问题了。我已经替换了您的表和列名，并修改了日期差行以在Postgres中工作：

# Based on https://blog.jooq.org/2015/11/07/how-to-find-the-longest-consecutive-series-of-events-in-sql/

WITH

  dates(date) AS (
    SELECT DISTINCT CAST(created_at AS DATE)
    FROM histories
    WHERE histories.user_id = :user_id
  ),

  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY date) AS rn,
      date - (ROW_NUMBER() OVER (ORDER BY date) * interval '1' day) AS grp,
      date
    FROM dates
  )

SELECT
  COUNT(*) AS consecutive_dates,
  MIN(date) AS min_date,
  MAX(date) AS max_date
FROM groups
GROUP BY grp
ORDER BY 1 DESC, 2 DESC

我强烈建议您阅读完整的博客文章以了解代码的工作原理。

关于ruby-on-rails - 使用postgres在Rails中为用户找到最长的日子，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48567613/