🔍 UNNEST関数 - GitPress.io

BigQuery

|| unnest()

配列型 |名前|説明| |:-:|:-:| |ARRAY|ARRAY 型ではないゼロ以上の要素の順序付きリスト。|

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

-- from ga
-- cross join unnest(hits) as ht

上記のクエリでは「カンマ演算子（,）」で「CROSSJOIN」を暗黙的に実行されている。（cf.配列操作 - GoogleCloud）

| e.g.

ARRAY<type>[]

    # 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","今日予約可")]
            )
    )

#standardSQL
SELECT 
    event, event.name, event.timestamp_micros
FROM 
    `firebase-analytics-sample-data.android_dataset.app_events_20160607`
    , UNNEST(event_dim) as event
    , UNNEST(event.params) as event_param
WHERE 
        event.name = "round_completed"
    AND event_param.key = "score"
    AND 10000 < event_param.value.int_value

#standardSQL
with
    sequences as (
        select  [
            'あ','い','う','え','お',
            'か','き','く','け','こ',
            'さ','し','す','せ','そ',
            'た','ち','つ','て','と',
            'な','に','ぬ','ね','の',
            'は','ひ','ふ','へ','ほ',
            'ま','み','む','め','も',
            'や','ゆ','よ',
            'ら','り','る','れ','ろ',
            'わ','を','ん'] AS n
    )

# 2単語の組み合わせ
select
      na1 as firstL
    , na2 as lastL
    , concat(na1, na2) as name
from 
    sequences
    , unnest(sequences.n) as na1
    , unnest(sequences.n) as na2
where
      na1 not in ('を','ん')
  and na1 != na2
;

# 3単語の組み合わせ
select 
--     na1 as firstL
--   , na2 as secondL
--   , na3 as lastL
--   , concat(na1, na2, na3) as name
-- from
--   sequences
--   , unnest(sequences.n) as na1
--   , unnest(sequences.n) as na2
--   , unnest(sequences.n) as na3
-- where
--       na1 not in ('を','ん')
--   and na1 != na2
--   and na2 != na3
-- ;

「ABC分析」「アソシエーション分析」等にも応用できそう。（商品分析の手法（ABC分析、アソシエーション分析） - Albeart, どの単語の組み合わせがよく使われるかを分析する方法 - Exporatory)

|| REFERENCE

テーブルスキーマでネストされた列と繰り返し列を指定する - GoogleCloud
BigQueryでrepeated型カラムの行数を数える方法 - ITips
BigqueryでUNNESTを使いこなせ！クエリ効率１００%！！最強！！- medium
BigQuery 活用術: UNNEST 関数- Google Developers
BigQuery で複数の配列をフラット化する - FLINTERS Engineer's Blog
BigQuery での JSON、配列、構造体の操作 -　Google Cloud
BigQueryで1列に格納されている配列を別々の列に分解したい - teratail
BigQueryのArrayを理解する。 - Zenn
GoogleAnalytics 4 のイベントを BigQuery で集計する - Zenn
BigQuery で実行できる SQL と実行できない SQL - DevelopersIO