BigQueryでの配列の扱い
SQLを書くときの観点
スキーマ作成時の観点
SQLシンタックスにおけるデータ型と、スキーマ作成時のデータ型のニュアンスの違いを把握する。
前置き
SQLシンタックスの配列
BigQuery でいう配列とは、ゼロ個以上の同じデータ型の値で構成された順序付きリストのことです
値にリストが存在して、リストの中の値(要素)を操作したいことは多い。
session -> hit -> hitベースのカスタムディメンジョン,コンテンツグループ、商品 と階層化されている。recordはstruct型となる。
errorの確認: SELECT hits.page, from xxx.99999.ga_sessions_20191119 とアクセスしても、
Array<Struct<hitNumber....>> と error messageが出る。
indexアクセス: SELECT hits[offset(0)].page, from xxx.99999.ga_sessions_20191119
とすれば、各セッションの最初のhitがでるけど、あまり役に立たない。
length: SELECT array_length(hits) from xxx.99999.ga_sessions_20191119
とすれば、セッションごとのhit数がわかるが、
StructレコードのArrayのflat化: SELECT hits.page from xxx.99999.ga_sessions_20191119, unnest(hits) as hits
とすれば、SQLの行の単位が、セッションから、hitに変わる。
(hits.page).*とすると、structで出力されずに、strucのpropertyのデータ型で列に展開される。便利なことも多いかも。
大事。これが使えると、自由度が高い。式がおけるところにはどこでもおける。
返す値の型はつぎの4つのもの
code: bq_subquery_GA.sql
SELECT
(select count(cd) from unnest(hits.customDimensions) as cd ) #スカラ型 ,array(select as struct cd.index, cd.value from unnest(hits.customDimensions) as cd ) #Array型 ,"PLP" in (select cd.value from unnest(hits.customDimensions) as cd where cd.index=36) #Boolean( In演算子) FROM
'xxx.0000.ga_sessions20191101
把握すべき?用語
unnest(operator, flatteringする)
the best way to ...on a row-by-row basis
the unnest function takes an array and returns a value table of the array's element type.
value tableができる。
このvalue tableが使える場所は、from句のみ(subquery内でもfrom句ならOK)
subquery(どこで使えるかは把握する)の2つ。
式サブクエリ
テーブルサブクエリ from句
あとは、配列用の関数を一度はさらっておく。
配列の作成
自分で組み立てることはqueryでは少ないだろうけど。
construct リテラルでも、型指定でもどちらでもOK
select [1,2,3] as numbers 普通のリテラル
SELECT ARRAY<FLOAT64>[1, 2, 3] as floats; 型指定も可能
indexing, slicing
index指定には、offset(0ベース), ordinal(1ベース)を使う。
safe_offset, safe_ordinalはindex範囲外でもnull返却対応してくれる
slicingは? offsetをつけて、そのoffsetに対して、where句を当てる?
length
array_length()
unnest
unnest(arraypath) を使って配列を行に変換してから処理。行単位では値はスカラー(数値、文字)になる
他のarrayでない列は、同一の値で拡張さえる(たぶん, 例外はないと思うが..)
オプションの WITH OFFSET 句を使用して
配列の(行)展開時に順番が保証されないので、offsetで別途、もとの配列の順番を出力する
select句で、配列を対象(from)にして、サブクエリで結果を返す。
配列の処理なので、複数の値になってしまい、サブクエリの要請である、単一の値を返す、に反してしまうが、
array(サブクエリ) とすることで、配列として返せる。 map(function(e){return ..})が array(subquery ....)のイメージ
The ARRAY function returns an ARRAY with one element for each row in a subquery.
where や distinctを使って、配列内のfilter化、ユニーク化もできる。
reduce処理は,,,,group byすればいいのか??未検証.
array_agg
これで、行の値を集約して、配列にできる。
order byやlimitの中で適切なexpをして、whereも、distinctも使って、必要なものを取り出す。
aggregation
集約関数、array_agg, sum, array_concat_aggなど、、
convert to strings
array_to_string()
combine
array_concat(), array_concat_aggとの違いは???
build arrays of arrays
BigQuery does not support building arrays of arrays directly. Instead, you must create an array of structs, with each struct containing a field of type ARRAY.
参考
JSのUDFは遅いとの評判なので、もっといい方法あれば変更したい
code:sql
CREATE TEMPORARY FUNCTION greeting(a ARRAY<String>)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
return a.reduce(function(r, e){ if(rr.length-1 != e)r.push(e); return r},[]); """;
reduceを使った集約、Hannah, Max,Jakob. Max と重複が消せる。
array_generate()でindex列を作っておいて、複数の配列の同じindexの値を、expressionに得ることができる。
追記で、offsetの使用にも言及。