如果要将结果作为json对象，则必须使用sqlite的json1扩展函数：

with cte as (
  select *, sum(val = 0) over (order by timestamp) grp
  from tablename
) 
select 
  json_group_array(
    json_object('id', id, 'timestamp', timestamp, 'val', val)
  ) result
from cte
where val > 0
group by grp

请看演示。
结果：

| result                                                                                                                                                                                                                                                                                    |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [{"id":3,"timestamp":1577644029,"val":1},{"id":4,"timestamp":1577644030,"val":1},{"id":5,"timestamp":1577644031,"val":2},{"id":6,"timestamp":1577644032,"val":2},{"id":7,"timestamp":1577644033,"val":3},{"id":8,"timestamp":1577644034,"val":2},{"id":9,"timestamp":1577644035,"val":1}] |
| [{"id":11,"timestamp":1577644037,"val":1},{"id":12,"timestamp":1577644038,"val":1},{"id":13,"timestamp":1577644039,"val":1}]                                                                                                                                                              |

这个答案假设“之前”和“之后”条件并不重要。也就是说，一集可以是表中的第一行。
你可以通过数一数 0 在每行前面加一个字母。然后过滤掉 0 价值观：

select t.*,
       dense_rank() over (order by grp) as episode
from (select t.*,
             sum(case when val = 0 then 1 else 0 end) over (order by timestamp) as grp
      from t
     ) t
where val <> 0;

如果不是这样，那么 lag() 以及 lead() 累积和可以处理前面的值 0 :

select t.*,
       sum(case when prev_val = 0 and val > 0 then 1 else 0 end) over (order by timestamp) as episode
from (select t.*,
             lag(val) over (order by timestamp) as prev_val,
             lead(val) over (order by timestamp) as next_val
      from t
     ) t
where val <> 0;