我有一張帶日期戳的事件表，我需要將其捆綁到 7 天的組中，從每個 event_id 的最早出現開始。
最終輸出應回傳每個捆綁包的開始和結束日期以及每個捆綁包中最近事件的“值”列。
沒有預定的開始日期，“7 天”視窗是任意的，而不是“一年中的一周”。
我嘗試了其他帖子中的大量示例，但沒有一個完全適合我的需求或使用我不確定如何為 BigQuery 重構的東西
樣本資料;
| Event_Id | 活動日期 | 價值 |
|---|---|---|
| 1 | 2022-01-01 | 010203 |
| 1 | 2022-01-02 | 040506 |
| 1 | 2022-01-03 | 070809 |
| 1 | 2022-01-20 | 101112 |
| 1 | 2022-01-23 | 131415 |
| 2 | 2022-01-02 | 161718 |
| 2 | 2022-01-08 | 192021 |
| 3 | 2022-02-12 | 212223 |
預期產出；
| Event_Id | 開始日期 | 結束日期 | 價值 |
|---|---|---|---|
| 1 | 2022-01-01 | 2022-01-03 | 070809 |
| 1 | 2022-01-20 | 2022-01-23 | 131415 |
| 2 | 2022-01-02 | 2022-01-08 | 192021 |
| 3 | 2022-02-12 | 2022-02-12 | 212223 |
uj5u.com熱心網友回復：
你可以考慮下面。
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/524916.html
標籤：Google Cloud Collective 日期谷歌大查询分组
上一篇：為小于今天的日期列印TRUE？