Bigquery Scriptingでラクラク縦横変換 #BigQuery

BigQuery Advent Calendar 2020の18日目の記事です。

はじめに

縦持ち、横持ち

BigQueryではデータを縦持ちで扱うことが多いですが、データを横に展開してエクセルやスプレッドシートで扱いたいこともあります。
ただ、縦から横への変換はSQLの苦手とする処理で、やろうとすると、select後のカラムを出力したいカラム数分手動で記述する必要がありました。（そしてcase・if文、配列による指定など複雑な書き方も必要）

サンプルデータ

この記事では BigQuery Scripting を使って、そんな縦横変換のカラム記述をちょっとラクにしてみます。

今回は、@na0さんがAdvent Calender 2020 1日目の記事BigQueryで祝日判定UDFを公開した&AlphaSQLで依存関係気にせずデプロイで紹介している、 bqfunc.holidays_in_japan.HOLIDAYS() を使い、直近10年分、各年の国民の祝日の曜日ごと祝日数ランキングTOP3を横持ちデータとして出力してみます。

実行スクリプト

こちらをBigQueryのWeb UIから実行させます

# 10年分の
DECLARE year_num INT64 DEFAULT 10;
# 3位までを表示
DECLARE max_rank INT64 DEFAULT 3;

# SQL結果を配列として保持
DECLARE res_array DEFAULT (
with
w_name as (
select 1 as w, '日' as w_ja union all
select 2 as w, '月' as w_ja union all
select 3 as w, '火' as w_ja union all
select 4 as w, '水' as w_ja union all
select 5 as w, '木' as w_ja union all
select 6 as w, '金' as w_ja union all
select 7 as w, '土' as w_ja 
),

sample_data as (
SELECT
  date as d,
  extract(DAYOFWEEK FROM date) as w,
  extract(YEAR FROM date) as y
FROM
  UNNEST(bqfunc.holidays_in_japan.HOLIDAYS())
),

target_holiday as (
select
d,
w,
y,
w_ja
from sample_data
left join w_name using(w)
where date_trunc(d, YEAR) between date_trunc(date_sub(current_date('Asia/Tokyo'), INTERVAL year_num - 1 YEAR), YEAR) and date_trunc(current_date('Asia/Tokyo'), YEAR)
),

result1 as (
select
a.*,
row_number() over(partition by y order by num desc) as rank
from (
select
y,
w_ja,
count(1) as num
from target_holiday
group by 1,2
) a
)

select
array(
select as struct
rank,
array_agg(y order by y desc) as y_arr,
array_agg(w_ja order by y desc) as w_ja_arr,
array_agg(num order by y desc) as num_arr,
from result1
group by 1
order by 1
)
); # ここまでres_array定義

# res_arrayにはrank順(1位, 2位, 3位...), 年の降順(2020, 2019, 2018,..)にデータが入っているものとして進める

DECLARE i, j, k DEFAULT 0;
DECLARE tmpval default res_array[offset(0)];
DECLARE query_str string;
DECLARE query_arr array<string> default [];
DECLARE cols string;
DECLARE result_str default "";

while i < max_rank do
# i位のデータを保持しておく
set tmpval = res_array[offset(i)];
# ここからselect文を完成させる
set query_str = "select ";
# カラム定義を保持する
set cols = "";

set j = 0;
while j < year_num do
set cols = format(
  "%s '%s' as w_ja_%d, %d as num_%d,", 
  cols,
  tmpval.w_ja_arr[offset(j)],
  tmpval.y_arr[offset(j)],
  tmpval.num_arr[offset(j)],
  tmpval.y_arr[offset(j)]
);
set j = j + 1;
end while;

set query_str = format("%s %s", query_str, cols);
# 配列へのpushとしてarray_concatを使う
set query_arr = array_concat(query_arr, [query_str]);

set i = i + 1;
end while;

# 配列query_arrの各要素をunion allでつなげる
set k = 0;
while k < array_length(query_arr) do
set result_str = format('%s %s', result_str, query_arr[offset(k)]);
# 最終行以外にunion all
if k + 1 < array_length(query_arr) then
set result_str = format('%s union all', result_str);
end if;
set k = k + 1;
end while;

# result_strを実行
execute immediate result_str;

解説

結局なにをしているのか？

SQL結果の保持されている配列result_strのデータを使って、出力データを直接書いているSELECT文を動的に組み立ています
組み立てた結果としてはこのようなの文字列になります

select '月' as w_ja_2020, 6 as num_2020, '月' as w_ja_2019, 10 as num_2019, '月' as w_ja_2018, 9 as num_2018, ...

まあ、かなり強引ですね、、（ラクラクではないですね、）

このSELECT文の文字列を EXECUTE IMMEDIATE で実行して最終結果を出力しています

SQL結果

このような縦持ちのデータを

スクリプトのループで扱いやすいように、このような形式の配列にします

[
{rank:1, y_arr:[2020, 2019, 2018, ..], w_ja_arr:['月','月','月',..], num_arr:[6, 10, 9,..]},
{rank:2, y_arr:...},
...
]

これを、変数res_arrayに保持し、スクリプトで扱います

配列push

array_concatを使います
例：

set query = array_concat(query_arr, [query_str])

文字列連結

formatを使います
例：

set result_str = format('%s union all', result_str);

おわりに

BigQuery ScriptingはWeb UI上でスクリプト実行できて、1つのSQLだけではできなかったこともできるようになり便利です。
他のプログラム言語と比べると、まだできないことも多いですが（例えば配列の要素をsetで書き変えることがきないなど）、今後のアップデートに期待

参考

BigQuery Scripting
BigQuery Advent Calendar 2020の18日目
BigQueryで祝日判定UDFを公開した&AlphaSQLで依存関係気にせずデプロイ