sqlite - sqlite计数1的值基于所有先前的行，并在行值为0时重新启动计数

Question

我有一个带0和1值的sqlite列，我需要获取最大连续1个值。谢谢你的帮助

sqlite> SELECT data_suites  from my_table ;
0
0
1
1
1 <--- 3 consecutive 1
0
1
1
1
1 <--- 4 consecutive 1
0
1
1
1
1
1
1 <---  6 consecutive 1

我期望得到

6

Answer 1

表格是无序设置的，因此您需要一列来指示排序（id，update_time等）。一旦拥有它，就可以使用windowed functions：

WITH cte AS (
 SELECT *,ROW_NUMBER() OVER(ORDER BY id)-ROW_NUMBER() OVER(PARTITION BY data_suites ORDER BY id) grp
 FROM my_table
)
SELECT COUNT(*) cnt
FROM cte
WHERE data_suites = 1
GROUP BY grp
ORDER BY cnt DESC LIMIT 1;
-- answer 6

db<>fiddle demo

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(ORDER BY id) - ROW_NUMBER() OVER(PARTITION BY data_suites ORDER BY id) grp
  FROM my_table
)
SELECT grp,MIN(id) AS start, MAX(id) AS end, COUNT(*) cnt
FROM cte
WHERE data_suites = 1
GROUP BY grp;

db<>fiddle demo2

输出：

+------+--------+------+-----+
| grp  | start  | end  | cnt |
+------+--------+------+-----+
|   2  |     3  |   5  |   3 |
|   3  |     7  |  10  |   4 |
|   4  |    12  |  17  |   6 |
+------+--------+------+-----+