SnowflakeのSQLのAs句で変数を使うには？(できなかったため代替案)

この記事は、Supershipグループ Advent Calendar 2023の21日目の記事になります。

要約

SQLのAs句で変数を使うには？(分からなかったため代替案)

• 代替案1：SQLを作成するシェルスクリプトやpythonコードを作成する。
• 代替案2：変数はあきらめてフレキシブルな名称にする。(こちらを採用)

環境

Snowflake

はじめに

去年はGASを用いた便利ツール2点についての記事を作成しましたが今年はSQLでの不明点について調べたことを書こうと思います。

今後はChatGPTなどに質問すればすぐ「できない」という回答と代替方法を回答してくれると思いますが、調べた当時は従来の検索で調べてなかなかたどり着けなかったため記事にしようと思います。

背景

primary_id	sub_id_A	sub_id_B	value_A	value_B	value_C	dt	...
aaa	bbb	ccc	ddd	eee	fff	YYYYMMD	...
...	...	...	...	...	...	...	...

上記のようなテーブルに対してsub_id_A,sub_id_Bで集計するクエリのテンプレートがそれぞれ存在しておりました。
(実際はWITH句や複数テーブルのJOINでもっと複雑なクエリとなります。)

sub_id_Aで集計するテンプレイメージ

SET dt_start='20231201';
SET dt_end='20231231';

SET sub_id_A='zzz';
SET sub_id_B='yyy';

SELECT
  MAX(sub_id_A) AS sub_id_A,
  primary_id AS primary_id,
  SUM(value_A) AS value_A,
  SUM(value_B) AS value_B,
  SUM(value_C) AS value_C
FROM
  table_A
WHERE
  dt BETWEEN $dt_start AND $dt_end
  AND sub_id_A = $sub_id_A
  AND sub_id_B = $sub_id_B
GROUP BY
  sub_id_A
ORDER BY
  sub_id_A

sub_id_Bで集計するテンプレイメージ

SET dt_start='20231201';
SET dt_end='20231231';

SET sub_id_A='zzz';
SET sub_id_B='yyy';

SELECT
  MAX(sub_id_B) AS sub_id_B,
  primary_id AS primary_id,
  SUM(value_A) AS value_A,
  SUM(value_B) AS value_B,
  SUM(value_C) AS value_C
FROM
  table_A
WHERE
  dt BETWEEN $dt_start_sub AND $dt_end
  AND sub_id_A = $sub_id_A
  AND sub_id_B = $sub_id_B
GROUP BY
  sub_id_B
ORDER BY
  sub_id_B

この2種類のテンプレの統合を試みました。

できたこと

できたもの

SET selected_id='sub_id_A'; -- sub_id_A / sub_id_B のいずれかを入力

SET dt_start='20231201';
SET dt_end='20231231';

SET sub_id_A='zzz';
SET sub_id_B='yyy';

SELECT
  MAX(identifier($selected_id)) AS selected_id,
  primary_id AS primary_id,
  SUM(value_A) AS value_A,
  SUM(value_B) AS value_B,
  SUM(value_C) AS value_C
FROM
  table_A
WHERE
  dt BETWEEN $dt_start_sub AND $dt_end
  AND sub_id_A = $sub_id_A
  AND sub_id_B = $sub_id_B
GROUP BY
  selected_id
ORDER BY
  selected_id

MAX(identifier($selected_id))とすることで、selected_idで選択したカラムを抽出することはできました。
ただ、AS句内での変数がうまく行かず、上記では最終的な出力のカラム名がselected_idとなってしまいます。(sub_id_Aとなってほしかった)

本題

上記の背景から、「SQLのAs句で変数を使うには？」ということを調べましたが、なかなかたどり着かず、最終的にslackチャンネルで問い合わせたところ、@shun_173さん(5日目の記事を担当されています)から「できないと思われる」ということと代替案を2ついただきました。

代替案1：SQLを作成するシェルスクリプトやpythonコードを作成する。

今回のクエリテンプレートは非エンジニアも利用するためこちらの案は見送りました。
シェルスクリプトであればsedやperlでの置換で実現が可能と思われます。
(余談ですが、私は生物系の出身で、研究室の他メンバも非IT系な中、バイオインフォマティクスなるIT系の研究テーマをいただき、独学で学んだことで、置換はperlのワンライナーが初遭遇だったため今でもローカル環境でちょっと置換するときはperlを利用しています)

代替案2：変数はあきらめてフレキシブルな名称にする。

上記のできたもののコードが最終産物となります。
クエリテンプレの利用マニュアルに「selected_idカラムをクエリで指定した　sub_id_A / sub_id_B のいずれかに変更する」と記載しました。

おわりに

「はじめに」でも記載しましたが、今後はChatGPTなどに質問すればすぐ回答してくれると思いますが、「会社でChatGPTの利用が許可されていない」などの方向けには場合はまだまだこういった記事も役立つと思いますので記事にしました。

参照

• https://docs.snowflake.com/ja/sql-reference/identifier-literal

最後に宣伝です。

Supershipではプロダクト開発やサービス開発に関わる人を絶賛募集しております。
ご興味がある方は以下リンクよりご確認ください。
Supership 採用サイト
是非ともよろしくお願いします。