时间序列中的SQL滚动计数

Question

我有一个公司的SQLite表，以及10年来每天有多少关于每家公司的新闻文章(以及大约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);