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