如何将更新行的字段设置为与另一个字段的日期最接近的值?

在BigQuery中, update 这是相当罕见的。你似乎想要的逻辑是:

select t.*,
       coalesce(value,
                lag(value ignore nulls) over (partition by thingname order by date)
               ) as value
from my_tbl;

我真的看不出有什么理由把它放回桌子上。

下面是BigQuery标准SQL

在许多(如果不是大多数的话)情况下,您不想更新表(因为它会导致额外的 cost limitations 与…有关 DML statements )而是可以调整查询中的“缺失”值,如以下示例所示:

#standardSQL
SELECT 
  ThingName, 
  date, 
  IFNULL(value, 
    LAST_VALUE(value IGNORE NULLS) 
    OVER(PARTITION BY thingname ORDER BY date)
  ) AS value
FROM `project.dataset.my_tbl`

#standardSQL
SELECT 
  t1.ThingName, t1.date, 
  ARRAY_AGG(t2.Value IGNORE NULLS ORDER BY t2.date DESC LIMIT 1)[OFFSET(0)] AS value
FROM `project.dataset.my_tbl` AS t1
LEFT JOIN `project.dataset.my_tbl` AS t2
ON t2.ThingName = t1.ThingName
AND t2.date <= t1.date
GROUP BY t1.ThingName, t1.date, t1.value

现在您可以使用它来更新您的表,如下例所示

#standardSQL
UPDATE `project.dataset.my_tbl` t
SET value = new_value
FROM (
  SELECT TO_JSON_STRING(t1) AS id, 
    ARRAY_AGG(t2.Value IGNORE NULLS ORDER BY t2.date DESC LIMIT 1)[OFFSET(0)] new_value
  FROM `project.dataset.my_tbl` AS t1
  LEFT JOIN `project.dataset.my_tbl` AS t2
  ON t2.ThingName = t1.ThingName
  AND t2.date <= t1.date 
  GROUP BY id
)
WHERE TO_JSON_STRING(t) = id