问使用bigquery从移动窗口中查找前一天的最大值，前一天的第二天

EN

select day, val_raw,
  max(val_raw) over(prev_days range between 1 preceding and 1 preceding) val_prev_day, 
  max(val_raw) over(prev_days range between 2 preceding and 2 preceding) val_sec_prev_day, 
  max(val_raw) over(prev_days range between 3 preceding and 3 preceding) val_third_prev_day, 
from your_table
window prev_days as (order by unix_date(day))   

with your_table as (
  select date '2022-01-01' as day, 0 as val_raw, union all 
  select '2022-01-01',  10, union all 
  select '2022-01-01', 110, union all 
  select '2022-01-02',  30, union all 
  select '2022-01-02',  70, union all 
  select '2022-01-02',  90, union all 
  select '2022-01-03',   0, union all 
  select '2022-01-03',   5, union all 
  select '2022-01-03',   5, union all 
  select '2022-01-04',   0, union all 
  select '2022-01-04',   5, union all 
  select '2022-01-04',  10 
)

WITH
dataset AS (
    SELECT           1 as day,   0 as val_raw,
    UNION ALL SELECT 1 as day,  10 as val_raw,
    UNION ALL SELECT 1 as day, 110 as val_raw,
    UNION ALL SELECT 2 as day,  30 as val_raw,
    UNION ALL SELECT 2 as day,  70 as val_raw,
    UNION ALL SELECT 2 as day,  90 as val_raw,
    UNION ALL SELECT 3 as day,   0 as val_raw,
    UNION ALL SELECT 3 as day,   5 as val_raw,
    UNION ALL SELECT 3 as day,   5 as val_raw,
    UNION ALL SELECT 4 as day,   0 as val_raw,
    UNION ALL SELECT 4 as day,   5 as val_raw,
    UNION ALL SELECT 4 as day,  10 as val_raw,
),
shift_one_day_max AS (
    SELECT day + 1 as day, max(val_raw) as max_val,
    FROM dataset
    GROUP BY day
),
shift_two_day_max AS (
    SELECT day + 2 as day, max(val_raw) as max_val,
    FROM dataset
    GROUP BY day
)
SELECT *
FROM dataset
LEFT OUTER JOIN shift_one_day_max USING (day)
LEFT OUTER JOIN shift_two_day_max USING (day)
;