sql - rails/SQL : count the number of consecutive records having a specific status

Question

我正在尝试计算具有特定状态的连续请求组的数量。

Example:

Request 1 status=bad
Request 2 status=good
Request 3 status=good
Request 4 status=bad
Request 5 status=bad
Request 6 status=bad
Request 7 status=good
Request 8 status=bad

所以我们这里有两组连续的 good:[2, 3] 和 [7]。

目标是让 result = 2。

这是我现在正在使用的:

Request.group("date(created_at)").count

结果:

SELECT COUNT(*) AS count_all, date(created_at) AS date_created_at FROM "requests" GROUP BY date(created_at)

{Wed, 07 Aug 2019=>240, Mon, 05 Aug 2019=>245, Tue, 06 Aug 2019=>511}

但这计算了所有请求，而不是连续good的数量。

Answer 1

试试这个

Request.group('DATE(created_at)', :status).count

这给出了结果

a = {[Fri, 15 Jun 2012, "good"]=2, [Fri, 30 Jan 2015, "good"]=>1, [Thu, 02 Oct 2008, "bad"]=>3, [Thu, 26 Jul 2018, "good"]=>1}