BigQuery備忘録 : GA4から取得したデータをBigQueryで扱う時に使う2つの関数(unnest, _table_suffix)

【ネストの解除】
・ ネストとは、一つの要素に対して複数要素が対応しているもの(配列)。
・ 対応している複数の要素のカラム名に対して、unnestする。

例えば、こんなschoolテーブルがあるとします。

absence_date	class.key	class.string	class.int
20181001	room	A	null
	my_number	null	000001
20181003	room	B	null
	my_number	null	000003
20181006	room	C	null
	my_number	null	000005

例えば、20181001に遅刻した学生はAというクラスにいる000001番の人です。

ここで、上記テーブルをこんな感じで書き換えたいと思います。

absence_date	room	my_number
20181001	A	000001
20181003	B	000003
20181006	C	000005

select
   absence_date,

   -- classのネストを解除した後、keyのroomから値を引っ張ってくる。
　　　　　　-- roomの値はstringカラムにある。

   (
     select
        string
     from 
        unnest(class)
     where
        key = 'room'
   ) as room,

   -- classのネストを解除した後に、keyのmy_numberから値を引っ張ってくる。
   -- my_numberの値はintカラムにある。

   (
     select
       int
     from 
       unnest(class)
     where 
       key = 'my_number'
   ) as my_number
from
    school;

これでOKです。

【テーブルの中に変数を埋め込む】
・　GA4から持ってきたデータなどは、日付ごとにテーブルが分割されている
・　例えば、
　　firebase-public-project.analytics_153293282.events``_20181002
・日付部分を * に変えて、_table_suffix関数を用いて、期間を指定する。

-- ユーザー定義関数で直近1年を指定
create temp function start_datesuffix() as (
  format_date('%Y%m%d',date_sub(date(current_timestamp,'Asia/Tokyo'),interval 1 year))
);

create temp function end_datesuffix() as (
  format_date('%Y%m%d',date(current_timestamp,'Asia/Tokyo'))
);
-- 直近1年のデータを取得

select
   *
from 
   `firebase-public-project.analytics_153293282.events_*`
where
   _table_suffix between start_datesuffix() and end_datesuffix();

ポイントは、where 以下です。
ぶっちゃけ、ユーザー定義関数でなくてもいいのですが、大体は直近1年や1ヶ月のデータが必要になるかなと思ったので、これを記載しました。

おしまい。