5
1

More than 1 year has passed since last update.

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

Last updated at Posted at 2021-12-23

ネストの解除
・ ネストとは、一つの要素に対して複数要素が対応しているもの(配列)。
・ 対応している複数の要素のカラム名に対して、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ヶ月のデータが必要になるかなと思ったので、これを記載しました。

おしまい。

5
1
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
1