SQLの高度な制御構造

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

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

しかし、CodatumではSQLインジェクションなどのセキュリティ上の懸念が優先され、テンプレートエンジンは使用できません。

Parameterのエスケープ:セキュリティと動的識別子のサポート セクションも併せてお読みください:

Parameter

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

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

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

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

ユースケース

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

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

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

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

Codatumのアプローチ

CASE式またはUNIONをParameterと共に使用します:

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

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

-- 集計粒度の例
SELECT
  CASE /param/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

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

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

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

-- 地域固有のビジネスロジックの例
{% 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

2. オプションのフィルター条件(If/Else)

Parameterに基づいて、WHERE句に含める条件を制御します。

ユースケース

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

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

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

Codatumのアプローチ

空Parameterの処理を使用します:

SELECT * FROM orders
WHERE 1=1
  AND (order_date >= /param/start_date OR /param/start_date = '')
  AND (status = /param/status OR /param/status = '')

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

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

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

3. 動的メトリクス集計(For Loop)

選択されたメトリクスに基づいて、複数の列に集計を適用します。

ユースケース

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

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

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

Codatumのアプローチ

Text Multi Input ParameterとCASE式を使用します:

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

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

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

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


FROM sales

4. 動的テーブル参照

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

ユースケース

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

  • 環境(開発/本番)に基づいてテーブルを切り替える

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

Codatumのアプローチ

クオート付きのParameterを使用します:

SELECT * FROM "events_/param/date_suffix"

注:BigQuery接続では、テーブル名の日付Parameterは自動的にYYYYMMDD形式でフォーマットされます。

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

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

SELECT * FROM events_{{date.strftime('%Y%m%d')}}

5. マクロ/関数の再利用(BigQuery UDFを使用)

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

ユースケース

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

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

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

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
);

-- 季節乗数関数
CREATE TEMP FUNCTION get_season_multiplier(target_date DATE)
RETURNS FLOAT64
AS (
  CASE
    -- 夏のピークシーズン(7-8月)
    WHEN EXTRACT(MONTH FROM target_date) IN (7, 8) THEN 1.5
    -- 年末のピークシーズン(12月)
    WHEN EXTRACT(MONTH FROM target_date) = 12 THEN 1.3
    -- ゴールデンウィーク期間
    WHEN FORMAT_DATE('%m-%d', target_date) BETWEEN '04-29' AND '05-05' THEN 1.4
    -- その他の期間
    ELSE 1.0
  END
);

-- 使用例
SELECT
  user_id,
  calculate_ltv(total_revenue, acquisition_cost, months_active) as ltv,
  CASE
    WHEN calculate_ltv(total_revenue, acquisition_cost, months_active) > 1000 THEN 'High Value'
    ELSE 'Standard'
  END as customer_segment,
  sales_amount * get_season_multiplier(order_date) as adjusted_sales
FROM user_metrics

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

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

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

{% 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 %}

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

Codatumのアプローチの利点

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

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

  2. 保守可能なSQL構造

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

  4. Parameterの型安全性

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

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

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

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

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

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

最終更新