BigQuery SQL：对 7 天内出现的具有共享 ID 的行进行分组，并返回最近出现的值

我有一个带有日期标记的事件表，我需要将其捆绑到 7 天的组中，从每个 event_id 最早出现的时间开始。

最终输出应返回每个捆绑包的开始日期和结束日期以及每个捆绑包中最新事件的“值”列。

没有预定的开始日期，“7 天”窗口是任意的，而不是“一年中的一周”。

我已经尝试了其他帖子中的大量示例，但没有一个完全符合我的需求或使用我不确定如何重构 BigQuery 的东西

样本数据;

预期产出；

您可以考虑以下。

CREATE TEMP FUNCTION cumsumbin(a ARRAY<INT64>) RETURNS INT64
LANGUAGE js AS """
  bin = 0;
  a.reduce((c, v) => {
    if (c + Number(v) > 6) { bin += 1; return 0; }
    else return c += Number(v); 
  }, 0);

  return bin;
""";

WITH sample_data AS (
    select 1 event_id, DATE '2022-01-01' event_date, '010203' value union all
    select 1 event_id, '2022-01-02' event_date, '040506' value union all
    select 1 event_id, '2022-01-03' event_date, '070809' value union all
    select 1 event_id, '2022-01-20' event_date, '101112' value union all
    select 1 event_id, '2022-01-23' event_date, '131415' value union all
    select 2 event_id, '2022-01-02' event_date, '161718' value union all
    select 2 event_id, '2022-01-08' event_date, '192021' value union all
    select 3 event_id, '2022-02-12' event_date, '212223' value
),
binning AS (
  SELECT *, cumsumbin(ARRAY_AGG(diff) OVER w1) bin
    FROM (
      SELECT *, DATE_DIFF(event_date, LAG(event_date) OVER w0, DAY) AS diff
        FROM sample_data
      WINDOW w0 AS (PARTITION BY event_id ORDER BY event_date)
    ) WINDOW w1 AS (PARTITION BY event_id ORDER BY event_date)
)
SELECT event_id, 
       MIN(event_date) start_date,
       ARRAY_AGG(
         STRUCT(event_date AS end_date, value) ORDER BY event_date DESC LIMIT 1
       )[OFFSET(0)].*
  FROM binning GROUP BY event_id, bin;