データサイエンティストの阿部です。今後不定期に、データ分析のためのデータハンドリングTipsを、Youtubeで発信します。各回3分でBigQueryに関する話題が中心です。"Sexy Tech for You" と題して、思わず使いたくなるような魅力的なTipsを紹介していきたいと思います。
動画中に記載したコードや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。では早速、第1回目の配信についてです。
Youtube
コード
#StandardSQL
DECLARE id,target,not_target STRING;
# INFORMATION_SCHEMA.COLUMNSからフィールド名取得
SET (target, not_target) = (
SELECT AS STRUCT
CONCAT('(', STRING_AGG(IF(STARTS_WITH(column_name,'string_field_')=TRUE , column_name, NULL), ','), ')'),
IFNULL(CONCAT('EXCEPT (', STRING_AGG(IF(STARTS_WITH(column_name,'string_field_')=FALSE, column_name, NULL), ','), ')'), ' ') #string_field_*以外のフィールドがあった場合にUNPIVOT対象から除外するためのフィールドセット。NULLのとき半角スペースにしておかないと後続でエラーになる
FROM
`project_id.dataset_name`.INFORMATION_SCHEMA.COLUMNS #データセット名（青）
WHERE
table_name = 'table_name' #テーブル名のみ（青）
AND column_name != 'string_field_1' #キーにしたいフィールド名（赤）
);
# 動的なフィールド名SETを読み込むため、EXECUTE IMMEDIATE FORMATでクエリ発行
EXECUTE IMMEDIATE FORMAT("""
WITH base AS (
SELECT
*,
string_field_1 AS value_type
FROM
(SELECT * EXCEPT %s FROM `project_id.dataset_name.table_name` #キーにしたいフィールド名（赤）
UNPIVOT(value FOR key IN %s)
)
SELECT
B.value AS id,
A.value_type,
A.value
FROM
base A INNER JOIN
(SELECT * FROM base WHERE value_type = '月') B ON A.key = B.key #キーにしたいスプレッドシート入力値（黄）
WHERE
A.value_type != '月' #キーにしたいスプレッドシート入力値（黄）
""", not_target, target);
補足
結果は以下のように出てきます。3つ目のステージの[結果を表示]から、最終的な出力結果を確認できます
string_field_* をtarget というフィールド名のセットとして使い、それ以外のフィールドをnot_target として除いてUNPIVOT しています。別の型のフィールドを扱う必要がある場合は、この辺りを変更すれば対応できるかと思います。
以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした！次回またお会いしましょう。
