Zenn
📚

BigQuery / SQLでグローバル変数を定義する方法

2024/08/17に公開
SQL
Google Cloud
BigQuery
tech

はじめに

BigQueryでSQLクエリを書く際、同じ値を複数回使用する場面が結構あります。例えば、特定の期間をフィルタリングしたり、特定のグループをターゲットにしたりする場合です。このような場合に、Pythonのようにグローバル変数を定義する方法を使うと、SQLコードの可読性や保守性、さらには再利用性を大幅に向上させることができます。

本記事では、BigQueryでのグローバル変数の定義方法を詳しく解説します。併せて、ベストプラクティスや注意点、そして定数を使用することで得られるメリットを具体的に紹介します。

基本構文

DECLARE 定数名 データ型 DEFAULT 値;

簡単な例

DECLARE start_date DATE DEFAULT DATE('2024-01-01');
DECLARE end_date DATE DEFAULT DATE('2024-12-31');
DECLARE group_id INT64 DEFAULT 100;

簡単な例（日本語での定義）

DECLARE `開始日` DATE DEFAULT DATE('2024-01-01');
DECLARE `終了日` DATE DEFAULT DATE('2024-12-31');
DECLARE `グループID` INT64 DEFAULT 100;

定数の使用例

以下は、定数を使用したより具体的なクエリの例です。
この例では、日付範囲とグループIDを定数として定義し、クエリ内で再利用しています。

DECLARE start_date DATE DEFAULT DATE('2024-01-01');
DECLARE end_date DATE DEFAULT DATE('2024-12-31');
DECLARE target_group_id INT64 DEFAULT 100;

WITH user_activity AS (
    SELECT
        user_id,
        COUNT(*) AS activity_count
    FROM `project.dataset.events`
    WHERE event_date BETWEEN start_date AND end_date
        AND group_id = target_group_id
    GROUP BY user_id
)
,final AS (
    SELECT
        user_id,
        IFNULL(activity_count, 0) AS activity_count
    FROM user_activity
    ORDER BY activity_count DESC
)
SELECT * FROM final

定数を使用することのメリット

  1. コードの意図が明確
    定数に意味のある名前を付けることで、SQLクエリが何を意図しているのかが直感的に理解できるようになります。特にチーム開発において、他のメンバーがコードを読みやすくなる点は大きな利点です。

  2. 保守性が向上
    一度定義した定数を使えば、値を変更する際にはその箇所を修正するだけで済みます。これにより、クエリ全体を修正する必要がなくなり、作業の効率化やエラーの防止につながります。

  3. 再利用性
    同じ値を複数のクエリで使いたい場合に、定数を利用することで一貫性が保たれます。結果として、コードの信頼性が向上し、タイポや値の書き換え忘れなどをなくすことが出来ます。

  4. テストや標準化が容易
    テストでは定数の値を変更するだけでさまざまなシナリオを検証できるため、テストケースの作成や実行がスムーズになります。また、チーム全体で定数を活用する文化を築けば、コードの統一感が生まれ、チーム内のコミュニケーションが効率化されます。

ベストプラクティス

  1. 意味のある名前を使用
    start_dateやtarget_group_idのように、定数の目的が明確に分かる名前を付けましょう。
  2. 適切なデータ型を指定
    DATE、INT64、STRINGなど、値に適したデータ型を必ず指定してください。
  3. コメントを追加
    複雑な計算や特定の選択理由がある場合、コメントで説明を加えると良いでしょう。
-- 先月の初日と末日
DECLARE `先月の初日` DATE DEFAULT DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH);
DECLARE `先月の末日` DATE DEFAULT LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH));

注意点

定数名とカラム名の競合
特に英語で定数名を付ける場合、既存のカラム名との競合に注意が必要です。

DECLARE group_id INT64 DEFAULT 100;

SELECT * FROM users
WHERE group_id = group_id;  -- これは常にtrueとなり、意図した動作にならない

解決策

  • 定数名を明確に区別: DECLARE group_id_const INT64 DEFAULT 100;
  • バッククォートの使用: WHERE group_id =group_id``
  • テーブル名やエイリアスを明示的に指定: WHERE users.group_id = group_id

まとめ

BigQueryでDECLAREを適切に使用することで、SQLクエリの可読性、保守性、そして再利用性を大きく向上させることができます。ぜひ皆さんも使ってみてください！

Discussion