问BigQuery数组上的逐元素函数

EN

ARRAY(SELECT transform(x)
        FROM UNNEST(array) AS x WITH OFFSET AS y ORDER BY y) AS transformed

WITH
  races_with_struct AS (
  SELECT
    "800M" AS race,
    [STRUCT("Rudisha" AS name,
      [23.4, 26.3, 26.4, 26.1] AS splits),
    STRUCT("Makhloufi" AS name,
      [24.5, 25.4, 26.6, 26.1] AS splits),
    STRUCT("Lewandowski" AS name,
      [25.0, 25.7, 26.3, 27.2] AS splits),
    STRUCT("Nathan" AS name,
      ARRAY<FLOAT64>[] AS splits),
    STRUCT("David" AS name,
      NULL AS splits)] AS participants),
  races AS (
  SELECT
    race,
    participant.name AS name,
    participant.splits AS splits
  FROM
    races_with_struct r
  CROSS JOIN
    UNNEST(r.participants) AS participant)
SELECT
  race,
  name,
  splits,
  ARRAY(SELECT x + 2
        FROM UNNEST(splits) AS x WITH OFFSET AS y ORDER BY y) AS transformed
FROM
  races

SELECT
  *, 
  ARRAY(SELECT '' || split FROM t.splits split) converted_splits,
  ARRAY(SELECT 5 + split FROM t.splits split) adjusted_splits
FROM
  races t