sql - 时间序列中的SQL滚动计数

Question

我有一个SQLite公司表，并且十年中每天为每个公司写多少篇新闻文章（约有3000家公司）。我想进行“滚动”计数，在每家公司的情况下，我会在3天的时间内计算新闻总数，前提是新闻总数为正。例如，从第1天开始，如果文章数为0，则跳过并转到第2天，依此类推，直到我们找到有1条文章的一天（例如第4天），然后计算该文章中的文章总数接下来的3天（因此为4,5,6天）。之后，我进入第7天，继续扫描，直到找到第一篇有新闻报道的日期，然后重复这3天的总和，然后继续扫描，以此类推。我将为每个公司重复此操作。

我曾考虑过使用窗口函数进行滚动总和，但是对于3000家公司而言，365 * 10天的数据滚动总和在计算上可能会花费太长的时间，而且我不需要在跳过的那一天计算总和（因此，要么是0天，要么不是3天间隔中的第一天。

例如，每个公司的时间序列可能是（第＃天：文章数）

Day 1:0
Day 2:0
Day 3:0
Day 4:1
Day 5:3
Day 6:2
Day 7:0
Day 8:0
Day 9:20
Day 10:2
Day 11:0

然后输出将是

Day 4:6 (1 from Day 4, 3 from Day 5, and 2 from Day 6)
Day 9:22 (20 from day 9, 2 from day 10, 0 from day 11).

Answer 1

在较新版本的SQLite中，应使用row_number()：

select company, min(date), max(date), sum(num_articles)
from (select t.*,
             row_number() over (partition by company order by date) as seqnum
      from t
      where num_articles > 0
     ) t
group by company, floor((seqnum - 1) / 3);