Article Outline
UNNEST関数
TOC
Collection Outline
[BIGQUERY] 分析入門
[BIGQUERY] SQL
■ 関数
- approx_count_distinct()
- coalesce()
- countif()
- extract()
- ifnull()
- lead() / lag()
- left() / right()
- lower()
- nullif()
- parse_date()
- percentile_()
- row_number()
- safe_cast()
- split()
- st_distance()
- struct()
- unnest()
■ 演算子
■ 句(節)
■ BigQuery特有
■ ERROR
- To copy a table, the destination and source datasets must be in the same region. Copy an entire dataset to move data between regions.
- Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex
[BIGQUERY] ML
[BIGQUERY] DS
■ DS100ノック
■
[BIGQUERY] GA
|| unnest()
UNNEST を使うと、ARRAYなどの配列や、REPEATEDなカラムを、開くことができる。
select
fullVisitorId as UU
, visitNumber as SS
, struct (
h.eventInfo.eventCategory as CATEGORY
, h.eventInfo.eventAction as ACTION
, h.eventInfo.eventLabel as LABEL
, h.eventInfo.eventValue as VALUE
) as EVENT
, ht.customDimensions as CUSTOM_DIMENSIONS_HITS
, ht.experiment as EXPERIMENT
from
ga
, unnest(hits) as ht
# unnest を用いて、親テーブルに無い値を付与
, search_list_marge as (
select distinct KEY_CODE, KEY_NAME from `project.datasets.search_table_20*` where _TABLE_SUFFIX = (select TODAY from ts)
union all
select * from
unnest(
array
<struct< KEY_CODE STRING, KEY_NAME STRING>>
[("reserve_tomorrow","明日予約可"), ("reserve_today","今日予約可")]
)
)
| cf.
BigQuery 活用術: UNNEST 関数- Google Developers
BigQuery で複数の配列をフラット化する - FLINTERS Engineer's Blog
BigQuery での JSON、配列、構造体の操作 - Google Cloud
BigQueryで1列に格納されている配列を別々の列に分解したい - teratail
BigQueryのArrayを理解する。 - Zenn
BigQuery で実行できる SQL と実行できない SQL - DevelopersIO