sqlite - sqlite count 1 value based on all previous row and restart count when row value is 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

标签： sqlite

Answer 1

表是无序集，因此您需要一列来指示排序（id，update_time，...）。拥有它后，您可以使用窗口函数：

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<>小提琴演示

---

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<>小提琴演示2

输出：

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