如何在BigQuery中按ID分组计算每周事件平均间隔时间

编辑说明：我已更新示例以体现逻辑，此前示例仅用于展示数据结构。

现有如下数据：

id    timestamp

3     2022-10-01 12:45:47 UTC
3     2022-10-01 12:45:27 UTC
3     2022-10-01 12:45:17 UTC
1     2022-09-29 15:26:40 UTC
2     2022-09-29 13:15:38 UTC
1     2022-09-29 12:08:28 UTC
2     2022-09-26 16:17:15 UTC

（本质上，每个id在数月时间内的单日可包含大量时间戳。）

我希望计算每周内每两个时间戳之间的平均间隔时间，预期结果如下：

id   week          averageTimeSec

3    2022-09-26    15 (2022-10-01 12:45:27 - 2022-10-01 12:45:17 = 10秒, 2022-10-01 12:45:47 - 2022-10-01 12:45:27 = 20秒)

2    2022-09-26    248303 (2022-09-29T13:15:38Z - 2022-09-26T16:17:15Z = 248303秒)

1    2022-09-26    11892 (2022-09-29 15:26:40 - 2022-09-29 12:08:28 = 11892秒)

目的是查看长时间范围内事件的生成频率，例如：某ID两个月前平均每100秒生成一次事件，一个月前平均每50秒生成一次，以此类推。

我日常不使用BigQuery或SQL，遇到此类任务感到困惑。我能想到用InfluxDB的Flux实现的思路，但该知识无法迁移到BigQuery中……我已开始阅读BigQuery文档，但仍未找到合适的实现方法。若有人能指明方向，我将不胜感激。

我目前仅实现了如下内容（仅查看每周内各ID的平均事件数量，而非事件实际间隔时间）：

SELECT week, AVG(connectionCount)
FROM (SELECT id,
             TIMESTAMP_TRUNC(timestamp, WEEK) week,
             COUNT(timestamp)                 connectionCount
      FROM `allEvents`

      GROUP BY id, week

      ORDER BY id, week)

GROUP BY week

ORDER BY week

解决方案 #

感谢Daryl Wenman Bateson提供的完整方案，以下是含测试数据的完整示例：

WITH testData AS (
  SELECT '3' as id, TIMESTAMP('2022-10-01 12:45:47 UTC') as timestamp UNION ALL
  SELECT '3', TIMESTAMP('2022-10-01 12:45:27 UTC') UNION ALL
  SELECT '3', TIMESTAMP('2022-10-01 12:45:17 UTC') UNION ALL
  SELECT '1', TIMESTAMP('2022-09-29 15:26:40 UTC') UNION ALL
  SELECT '2', TIMESTAMP('2022-09-29 13:15:38 UTC') UNION ALL
  SELECT '1', TIMESTAMP('2022-09-29 12:08:28 UTC') UNION ALL
  SELECT '2', TIMESTAMP('2022-09-26 16:17:15 UTC')
)

  SELECT
    id, 
    week,
    AVG(timeDifference) diff
  FROM 
    (
      SELECT  
        id, 
        timestamp,
        TIMESTAMP_TRUNC(timestamp, ISOWEEK) week, 
        UNIX_SECONDS(timestamp) - LAG(UNIX_SECONDS(timestamp)) OVER (PARTITION BY id ORDER BY timestamp) AS timeDifference 
      FROM
        testData
    ) 
  GROUP BY 
    id, 
    week
  
  ORDER BY
    diff

输出结果如下：

内容的提问来源于stack exchange，提问作者NeverwinterMoon