新手求助：在BigQuery中提取Timestamp字段的ISO周与年份并更新原表

Hey there! Let's break this down for you—since you're new to BigQuery and SQL, figuring out DML updates can feel a bit overwhelming at first, but we'll get you sorted.

First off, a key point: you can't use UPDATE to add new columns directly. You need to add the columns to your table first, then populate them with your calculated values. Here's how to do both steps:

Step 1: Add the new columns to your table #

Run these ALTER TABLE statements to create the KW (ISO week) and Jahr (year) columns. We'll use INT64 since these are numeric values:

ALTER TABLE MyTable
ADD COLUMN KW INT64;

ALTER TABLE MyTable
ADD COLUMN Jahr INT64;

Step 2: Update the columns with your calculated values #

Now you can use UPDATE to fill these new columns using the timestamp field. Since you're calculating values directly from the same table, you don't need a join—just set the columns equal to your EXTRACT expressions:

UPDATE MyTable
SET
  KW = EXTRACT(ISOWEEK FROM timestamp),
  Jahr = EXTRACT(YEAR FROM timestamp)
WHERE TRUE; -- This updates every row in the table

Quick tips to keep in mind: #

• Filter rows if needed: If you don't want to update every row (e.g., only rows where timestamp isn't null), replace WHERE TRUE with a condition like WHERE timestamp IS NOT NULL.
• ISO year vs calendar year: A quick heads-up—if you want the year that aligns with the ISO week (instead of the calendar year from the timestamp), use EXTRACT(ISOYEAR FROM timestamp) for Jahr. This fixes cases where a week spans two calendar years (like the first few days of January sometimes falling in the last week of the previous year).
• Cost & quota: BigQuery's UPDATE operations count against your DML quota and will process data, so if your table is very large, double-check your project's billing settings first.

That's it! Once you run these two steps, your original table will have the KW and Jahr columns populated with your calculated values.

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