HOME/🔍 BigQuery/

UNNEST関数

Article Outline

|| 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