良く使うBigQuery関数

unnest_operator:
    {
      UNNEST( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

as_alias:
    [AS] alias

SELECT
  offset + 1 as id,
  * except(offset),
  orders
FROM
  UNNEST(ARRAY<STRUCT<customer_id INT64, amount INT64, order_date DATE, payment_method STRING>>[
    (1, 100, '2008-12-25', 'credit'),
    (2, 200,'2008-12-25', 'bank'),
    (3, 300,'2008-12-25', 'credit'),
    (1, 1000,'2009-12-26', 'credit'),
    (2, 2000,'2009-12-26', 'bank'),
    (3, 3000,'2009-12-26', 'credit'),
    (1, 10000,'2010-12-24', 'invoice')
  ]) AS orders WITH OFFSET

SELECT
  value,
  SPLIT(value) as splitted_value,
  'aa' IN UNNEST(SPLIT(value)) AS is_containing_aa
FROM
  UNNEST(['a,aa,aaa', 'aaa,aaaa']) AS value

WITH base AS (
  SELECT
    offset + 1 as id,
    * except(offset),
    orders
  FROM
    UNNEST(ARRAY<STRUCT<customer_id INT64, amount INT64, order_date DATE, payment_method STRING>>[
      (1, 100, '2008-12-25', 'credit'),
      (2, 200,'2008-12-25', 'bank'),
      (3, 300,'2008-12-25', 'credit'),
      (1, 1000,'2009-12-26', 'credit'),
      (2, 2000,'2009-12-26', 'bank'),
      (3, 3000,'2009-12-26', 'credit'),
      (1, 10000,'2010-12-24', 'invoice')
    ]) AS orders WITH OFFSET
),
nested AS (
  SELECT
    customer_id
    , ARRAY_AGG(struct(id, amount, order_date, payment_method)) AS orders
  FROM
    base
  GROUP BY
    customer_id
)
SELECT
  o.id,
  n.customer_id,
  o.amount,
  o.order_date,
  o.payment_method
FROM
  nested n
  CROSS JOIN UNNEST(n.orders) AS o
ORDER BY
  o.id

FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias

WITH produce AS (
  SELECT
    *
  FROM
    UNNEST(ARRAY<STRUCT<product STRING, sales INT64, quarter STRING, year INT64>>[
      ('Kale',51,'Q1',2020),
      ('Kale',23,'Q2',2020),
      ('Kale',45,'Q3',2020),
      ('Kale',3,'Q4',2020),
      ('Kale',70,'Q1',2021),
      ('Kale',85,'Q2',2021),
      ('Apple',77,'Q1',2020),
      ('Apple',0,'Q2',2020),
      ('Apple',1,'Q1',2021)
    ]) AS produce
)
SELECT
  *
FROM
  produce
  PIVOT(
      SUM(sales)
      FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
  )

FROM from_item[, ...] unpivot_operator

unpivot_operator:
    UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] (
        { single_column_unpivot | multi_column_unpivot }
    ) [unpivot_alias]

single_column_unpivot:
    values_column
    FOR name_column
    IN (columns_to_unpivot)

multi_column_unpivot:
    values_column_set
    FOR name_column
    IN (column_sets_to_unpivot)

values_column_set:
    (values_column[, ...])

columns_to_unpivot:
    unpivot_column [row_value_alias][, ...]

column_sets_to_unpivot:
    (unpivot_column [row_value_alias][, ...])

unpivot_alias and row_value_alias:
    [AS] alias

WITH produce AS (
  SELECT
    *
  FROM
    UNNEST(ARRAY<STRUCT<product STRING, sales INT64, quarter STRING, year INT64>>[
      ('Kale',51,'Q1',2020),
      ('Kale',23,'Q2',2020),
      ('Kale',45,'Q3',2020),
      ('Kale',3,'Q4',2020),
      ('Kale',70,'Q1',2021),
      ('Kale',85,'Q2',2021),
      ('Apple',77,'Q1',2020),
      ('Apple',0,'Q2',2020),
      ('Apple',1,'Q1',2021)
    ]) AS produce
)
, pivoted AS (
  SELECT
    *
  FROM
    produce
    PIVOT(
        SUM(sales)
        FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
    )
)
SELECT
  *
FROM
  pivoted
  UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))