SQLの高度な制御構造

Codatumのパラメータ機能を使用して、テンプレートエンジンのような機能を安全に実装する方法を学びます

パラメータ入力に基づいて処理を分岐したり、繰り返したり、再利用したりしたい場合があります。このような場合、多くのSQLツールではJinjaのようなテンプレートエンジンを使用して動的なSQLクエリを生成します。

しかし、CodatumではSQLインジェクションなどのセキュリティ上の懸念を優先し、テンプレートエンジンは使用できません。テンプレートエンジンを使わずに構文解析を行えるようにすることで、以下のような利点があります：

• コンテキストに基づく正確なエスケープ処理により、セキュリティリスクを軽減

• エラーの正確な位置を特定することで、デバッグが容易になる

• コンテキストに基づくサジェストにより、使いやすさが向上

これらのケースのほとんどは、標準的なSQL構文を活用することで対応できます。この記事では、Codatumで高度な制御構造を実装する方法を紹介します。

本セクションを読む前に、パラメータやエスケープと動的参照の生成のセクションも事前にご確認ください。

注意： この記事のテンプレートエンジンの例は、Jinja2/Jinjaテンプレート言語の構文に基づいています。実際のツールでは、類似しているが異なる構文や独自の拡張機能を使用する場合があります。

1. 条件付きデータアクセス（If/Else）

パラメータに基づいて、どのフィールドまたはデータをSELECTするかを制御します。

ユースケース

• 地域/国ごとに異なるビジネスロジックを適用する

• データ集計の粒度を動的に変更する

• ユーザーの権限レベルに基づいてデータアクセスを制限する

• 部門/チームごとに異なるデータビューを提供する

Codatumのアプローチ

CASE式またはUNIONをパラメータと共に使用します：

-- 地域固有のビジネスロジックの例
SELECT
  order_id,
  CASE {{region}}
    WHEN 'EU' THEN amount * 1.20  -- EU: 20% VAT
    ELSE amount
  END as final_price,
  CASE {{region}}
    WHEN 'EU' THEN 'EU'
    ELSE 'Other'
  END as tax_region
FROM orders

-- 集計粒度の例
SELECT
  CASE {{granularity}}
    WHEN 'daily' THEN DATE_TRUNC('day', timestamp)
    WHEN 'weekly' THEN DATE_TRUNC('week', timestamp)
    ELSE DATE_TRUNC('month', timestamp)
  END as period
FROM events

-- 権限ベースの制限の例
SELECT * FROM (
  SELECT sensitive_column as data FROM sensitive_data WHERE {{is_admin}} = TRUE
  UNION ALL
  SELECT public_column as data FROM filtered_data WHERE {{is_admin}} = FALSE
) t

テンプレートエンジンのアプローチ（Codatumでは使用不可）

参考までに、テンプレートエンジン（Jinja2）では以下のように実装されます：

-- 地域固有のビジネスロジックの例
{% raw %}
{% if region == 'EU' %}
  SELECT 
    order_id,
    amount * 1.20 as price_with_vat,  -- EU: 20% VAT
    'EU' as tax_region
  FROM orders
{% else %}
  SELECT 
    order_id,
    amount as price_without_vat,
    'Other' as tax_region
  FROM orders
{% endif %}

-- 集計粒度の例
SELECT
  {% if granularity == 'daily' %}
    DATE_TRUNC('day', timestamp) as period
  {% elif granularity == 'weekly' %}
    DATE_TRUNC('week', timestamp) as period
  {% else %}
    DATE_TRUNC('month', timestamp) as period
  {% endif %}
FROM events

-- 権限ベースの制限の例
{% if user.is_admin %}
  SELECT * FROM sensitive_data
{% else %}
  SELECT public_columns FROM filtered_data
{% endif %}
{% endraw %}

2. オプションのフィルタ条件（If/Else）

パラメータに基づいて、WHERE句に含める条件を制御します。

ユースケース

• ユーザーが検索条件を柔軟に組み合わせられるようにする

• レポートのフィルタを動的に変更する

• 特定の条件が提供された場合にのみフィルタを適用する

Codatumのアプローチ

空パラメータの処理を使用します：

SELECT * FROM orders
WHERE 1=1
  AND (order_date >= {{start_date}} OR {{start_date}} = '1970-01-01')
  AND (status = {{status}} OR {{status}} = '')

テンプレートエンジンのアプローチ（Codatumでは使用不可）

参考までに、テンプレートエンジン（Jinja2）では以下のように実装されます：

SELECT * FROM orders
WHERE 1=1
{% raw %}
{% if start_date %}
  AND order_date >= {{start_date}}
{% endif %}
{% if status %}
  AND status = {{status}}
{% endif %}
{% endraw %}

3. マルチパラメータによる繰り返し（For Loop）

テキスト複数選択/入力パラメータを使用して繰り返しパターンを適用します。

ユースケース

• 複数のメトリクスを同時に集計する

• ユーザーが選択したメトリクスのみを表示する

• 複数の列に類似の計算ロジックを適用する

Codatumのアプローチ

テキスト複数選択/入力パラメータとCASE式を使用します：

SELECT
  SUM(CASE WHEN 'revenue' IN ({{selected_metrics}}) THEN revenue ELSE NULL END) as revenue_total,
  SUM(CASE WHEN 'cost' IN ({{selected_metrics}}) THEN cost ELSE NULL END) as cost_total,
  SUM(CASE WHEN 'profit' IN ({{selected_metrics}}) THEN profit ELSE NULL END) as profit_total
FROM sales

このアプローチはテンプレートエンジンよりもコードが多くなりますが、以下のような信頼性と保守性の向上が得られます：

• 各メトリクスが明示的に定義されているため、コンパイル時にエラーを検出しやすくなります

• カラム名とデータ型をSQLエンジンで検証できます

• テーブルスキーマの変更によってクエリが破損する可能性が低くなります

テンプレートエンジンのアプローチ（Codatumでは使用不可）

参考までに、テンプレートエンジン（Jinja2）では以下のように実装されます：

SELECT
{% raw %}
{% for metric in metrics %}
  SUM({{metric}}) as {{metric}}_total,
{% endfor %}
{% endraw %}


FROM sales

4. 動的識別子の生成

パラメータに基づいて、クエリ対象のテーブルを制御します。

ユースケース

• APIキーでテーブルを選択する

• 日付でパーティション分割されたテーブルにアクセスする

• スキーマやテーブル名を動的に生成する

Codatumのアプローチ

クオート付きのパラメータを使用します：

-- APIキーベースのテーブル選択
SELECT * FROM `api_logs_{{api_key_suffix}}`  -- 例：api_logs_v1, api_logs_v2

-- 日付パーティションテーブルへのアクセス
SELECT * FROM `events_{{date_suffix}}`  -- 例：events_20240131
FROM `sales_{{region}}_{{date_suffix}}`  -- 例：sales_us_20240131

-- 動的な列名
SELECT 
  "{{metric_name}}" as metric,  -- 例：revenue_usd, revenue_eur
  "amount_{{currency}}" as amount  -- 例：amount_usd, amount_eur
FROM transactions

注：BigQueryのコネクションでは、テーブル名の日付パラメータは自動的に YYYYMMDD 形式でフォーマットされます。

テンプレートエンジンのアプローチ（Codatumでは使用不可）

参考までに、テンプレートエンジン（Jinja2）では以下のように実装されます：

-- APIキーベースのテーブル選択
SELECT * FROM `api_logs_{{api_version}}`

-- 日付パーティションテーブルへのアクセス
SELECT * FROM `events_{{date.strftime('%Y%m%d')}}`

-- 動的な列名
SELECT 
  {{metric_name}} as metric,
  amount_{{currency}} as amount
FROM transactions

5. マクロ/関数の再利用

再利用可能な計算ロジックを関数にカプセル化します。

ユースケース

• ドメイン固有の計算ロジックを標準化する

• 複数のクエリで計算ロジックを再利用する

• 複雑な条件付き計算をカプセル化する

Codatumのアプローチ（BigQuery UDFを使用）

-- LTV計算関数
CREATE TEMP FUNCTION calculate_ltv(revenue FLOAT64, acquisition_cost FLOAT64, retention_months INT64)
RETURNS FLOAT64
AS (
  CASE
    WHEN retention_months = 0 THEN 0.0
    ELSE (revenue - acquisition_cost) * POWER(0.95, retention_months)
  END
);

SELECT
  user_id,
  calculate_ltv(total_revenue, acquisition_cost, months_active) as ltv,
FROM user_metrics

UDFはデータベース固有の機能であり、表記法と機能はデータベースによって異なります。

テンプレートエンジンのアプローチ（Codatumでは使用不可）

参考までに、テンプレートエンジン（Jinja2）では以下のように実装されます：

{% raw %}
{% macro calculate_ltv(revenue, acquisition_cost, retention_months) %}
  CASE
    WHEN {{retention_months}} = 0 THEN 0
    ELSE ({{revenue}} - {{acquisition_cost}}) * 
         POWER(0.95, {{retention_months}})  -- 5%の減衰率を適用
  END
{% endmacro %}
{% endraw %}

SELECT
  user_id,
  {{ calculate_ltv('total_revenue', 'acquisition_cost', 'months_active') }} as ltv
FROM user_metrics

Codatumのアプローチの利点

テンプレートエンジンはより柔軟性を提供しますが、Codatumのパラメータベースのアプローチには以下の利点があります：

1. 適切なエスケープによるSQLインジェクション保護

2. 保守可能なSQL構造

3. クエリの分析と最適化の向上

4. パラメータの型安全性

5. 動的な値とSQL構造の明確な分離

テンプレートエンジンの機能が必要な場合は、以下を検討してください：

• 標準的なSQL機能を使用するようにクエリを再構築する

• 複雑なクエリを複数のシンプルなクエリに分割する

• クエリの整理にCTEを使用する

• 可能な場合はデータベース固有の機能を活用する