SQL クエリ変数

SQL クエリ変数は、モジュール内のオブジェクトセットやほかの変数に対して SQL クエリを実行することで値が計算される Workshop 変数です。変数エディターで単一の Ontology SQL SELECT 文を記述し、オブジェクトセット変数とインターフェースセット変数をテーブルとして、ほかの変数をパラメーターとしてバインドします。Workshop は、入力が変わるたびにクエリを実行して変数の値を求めます。

SQL クエリ変数は、SQL では自然に記述できるものの、ほかの方法では表現しにくい計算に最適です。たとえば、RANK()、ROW_NUMBER()、LAG()、LEAD() などのウィンドウ関数を使うと、関数を記述してデプロイすることなく、オントロジーデータに対して直接、順位、累計、前期比を計算できます。また、複数のオブジェクトセットを結合し、カスタムウィジェット用に構造体の配列を返すなど、複雑なオントロジークエリを単一のリクエストにまとめる場合にも効果的です。

SQL クエリ変数を使用する場面

SQL クエリ変数と、変数の値を計算するほかの方法を比較して検討します。

• SQL クエリ変数： ロジックを SQL で記述するのが最も自然で、1個の Workshop アプリケーションに固有の値に使用します。クエリはモジュール内で定義されるため、個別に公開したりバージョン管理したりするものはありません。
• SQL 関数： ロジックを複数のアプリケーションや Foundry の機能間で共有し、再利用する必要がある場合に使用します。SQL クエリ変数のスコープは、それを定義したモジュールに限定され、ほかの場所では再利用できません。
• TypeScript および Python の関数ベース変数： ロジックを TypeScript または Python で表現する方が容易な場合や、SQL の SELECT 文では実現できない機能が必要な場合に使用します。
• 変数変換： クエリやコードを記述せずに、一般的な操作の組み合わせとして定義できる、アプリケーション内の単純なロジックに使用します。
• オブジェクトセット変数： プロパティ値による絞り込みやリンクされたオブジェクトへのピボットなど、アプリケーション内でオブジェクトセットに対してポイントアンドクリックで行うネイティブな変換に使用します。

サポートされる変数タイプ

SQLクエリ定義タイプは、次の変数タイプで利用可能です。

現在、オブジェクトセット変数、Geopoint 変数、Geoshape 変数は SQL クエリを元にすることができません。

SQL クエリ変数を作成する

1. Workshop エディターで変数パネルを開き、サポートされるタイプの変数を選択または作成します。
2. 変数定義タイプドロップダウンメニューをSQLクエリに設定します。
3. エディターで、クエリが参照するテーブルとパラメーターを追加し、クエリを記述します。保存して実行を選択してクエリを実行し、変数に値を設定します。

テーブルを追加する

オブジェクトセット変数は、クエリでテーブルとして利用可能になります。テーブルの下でテーブルを追加を選択し、オブジェクトセット変数を選んでエイリアスを指定します。クエリ内では、@alias 構文でテーブルを参照します。

Copied!
1
SELECT COUNT(*) FROM @orders;

エイリアスは英字またはアンダースコアで始め、英数字またはアンダースコアのみを含める必要があります。テーブルの列は @alias.column_name で修飾します。date という名前の列はバッククォートでエスケープする必要があります。

Copied!
1
SELECT AVG(@orders.total) FROM @orders WHERE @orders.`date` >= '2026-01-01';

バインドされた各テーブルには、列を1つ以上選択する必要があります。テーブルバインディングの下にある列検索を使用して、クエリが参照するプロパティを追加します。列が定義されていないテーブルバインディングは、エラー Table binding '@alias' does not have any columns defined で失敗します。

オブジェクトセット変数とインターフェースセット変数の両方をテーブルとしてバインドできます。インターフェースセット変数の場合、クエリで利用可能な列はインターフェースのプロパティです。そのため、単一のクエリで、そのインターフェースを実装するすべてのオブジェクトタイプを対象にできます。

複数のテーブルを結合する

複数のテーブルをバインドして、クエリ内で結合できます。結合は主キーと外部キーの関係に基づく必要があり、一方のテーブルの外部キープロパティを、もう一方のテーブルの主キーと一致させます。

Copied!
1
2
3
SELECT COUNT(*)
FROM @orders
INNER JOIN @customers ON @orders.customerId = @customers.id;

ON 句で使用するすべての列は、SELECT リストの列と同様に、そのテーブルバインディングで選択された列に含まれている必要があります。

多対多の関係は、主キーと外部キーの直接的な関係ではなく結合テーブルを介して解決されるため、まだサポートされていません。

パラメーターを追加する

ほかの変数を、パラメーターとしてクエリにバインドできます。パラメーターの下でパラメーターを追加を選択し、変数を選んでエイリアスを指定します。同じ @alias 構文でパラメーターを参照します。実行時に変数の現在の値が代入され、バインドされたパラメーターが変わるたびにクエリが再計算されます。

Copied!
1
SELECT COUNT(*) FROM @orders WHERE @orders.region = @selectedRegion;

クエリを記述して実行する

クエリエディターでは、バインドされたテーブル、列、パラメーターを自動補完できます。保存して実行を選択するか、Cmd + Enter（macOS）または Ctrl + Enter（Windows）のキーボードショートカットを使用して、クエリを実行し、変数の値を更新します。クエリでは Ontology SQL 方言を使用します。サポートされる構文と関数については、SQL 方言リファレンスを参照してください。

エディターの下にあるクエリプレビューパネルを展開すると、クエリが実際に返す行と列を確認できます。プレビューは最大50行を読み込み、生の SQL 結果を表示するため、変数自体では拒否されるクエリをまだ調整している段階でも役立ちます。

結果の形式

変数タイプによって、Workshop がクエリ結果を読み取る方法が決まります。どの場合でも、行の順序はクエリが返す順序です。順序が重要な場合は ORDER BY を使用します。

スカラー変数

スカラー変数には、結果がちょうど1行1列である必要があります。複数の行または列を返すクエリは、最初の値だけを暗黙に読み取るのではなく、失敗します。

配列変数

配列変数には、結果がちょうど1列である必要があります。すべての行が配列に追加されます。セル自体が配列フィールドの場合は、そのすべての要素が追加され、順序を保持した単一の配列にフラット化されます。

Copied!
1
2
3
4
5
-- 1個の配列セル: ['a', 'b', 'c'] に解決されます
SELECT array('a', 'b', 'c') AS value;

-- 行ごとに1個のスカラー値：返されたすべての名前を含む1個の配列に解決されます
SELECT name FROM @employees ORDER BY name;

次の動作にも留意してください。

• 行と要素の両方のレベルで、null 値はスキップされます。
• 結果が空の場合は空の配列が設定され、変数が未設定のままになることはありません。
• 結果の上限は10,000行です。 10,000行以上を返すクエリは、配列を切り詰めるのではなく失敗し、クエリに LIMIT を追加するよう求められます。

構造体変数

構造体変数では、フィールドは変数の設定で宣言するのではなく、クエリ結果から導出されます。動作は次の3つのルールで定義されます。

1. 各列が、その列名を名前とする構造体フィールドになります。
2. 各行が1個の構造体になります。
3. 構造体はネストできません。

変数タイプによって外側の形式が決まり、クエリによってフィールドが決まります。

実行に成功するたびにフィールドが再導出され、追加、削除、名前の変更、データ型の変更が変数に適用されます。ウィジェットがバインドされているフィールドを新しいクエリが返さなくなると、そのバインディングは機能しなくなります。構造体クエリが選択する列を変更した後は、ウィジェットの設定を確認してください。

NULL を返す列からは、そのフィールドに値がない構造体が生成されます。フィールド自体は変数に引き続き存在します。

構造体クエリの要件

各フィールドはちょうど1列から生成される必要があるため、SELECT リストにはいくつかの要件があります。

• すべての列に使用可能な名前が必要です。 列名がフィールド名になるため、通常の識別子である必要があります。計算列にはエイリアスを指定します。例：SELECT COUNT(*) AS order_count。
• 列名は一意である必要があります。 同じ名前の列を2列返すクエリは失敗します。AS を使用してそれぞれにエイリアスを指定します。
• 構造体、マップ、バイナリの列はフィールドにできません。 構造体列を直接選択するのではなく、@alias.column.* を使用してフィールドごとに1列に展開します。
• 配列列に対応しています。 データ型が ARRAY<STRING> の列からは、文字列のリストを保持するフィールドが生成されます。構造体の配列はフィールドとして利用できません。

次の例は、name 列と、city フィールドを含む addr 構造体列を持つ、バインドされたテーブル @t に対して、これらのルールを示しています。

Copied!
1
2
3
4
5
SELECT name FROM @t;          -- 構造体: {'name': 'Ada'}
SELECT @t.addr.* FROM @t;     -- 構造体: {'city': 'Springfield'}; これには列参照 @t が必要です。
SELECT @t.addr FROM @t;       -- エラー：代わりに構造体の列を展開します
SELECT name, name FROM @t;    -- エラー：列名が重複しています
SELECT COUNT(*) FROM @t;      -- エラー：列にエイリアスを付けます

Copied!
1
2
SELECT @t.structField.*
FROM @t;

構造体クエリを保存する

値が構造体である変数では、クエリとそこから導出されるフィールドが一緒に保存されます。保存して実行では、まずクエリが実行され、クエリが成功し、その列を構造体の生成元として使用できる場合にのみ、クエリと導出されたフィールドが保存されます。クエリの実行に失敗した場合、または構造体の生成元として使用できない列が返された場合は、何も保存されません。ドラフトはエディターに残り、以前に保存されたクエリとフィールドが引き続き有効になります。失敗は、クエリプレビューの上部と、エディター内のインラインエラーの両方で報告されます。

保存されたフィールドは、実行時に変数が解決される際にも検証されます。そのため、公開済みアプリケーションの元データが変更された場合、異なる形式の値に解決されるのではなく、影響を受ける列名を示すエラーが報告されます。

AIP で SQL を生成する

手動でクエリを記述する代わりに、自然言語の説明からクエリを生成できます。計算したい値を説明するプロンプトを入力すると、AIP が設定済みのテーブル、パラメーター、変数の出力タイプを使用して SQL クエリを生成します。AIP はオントロジー SQL の方言と定義済みのエイリアスに制約されるため、生成されるクエリでは、その変数で利用可能なテーブル、列、パラメーターのみが参照されます。生成されたクエリはエディターに入力され、自動的に実行されます。結果を確認し、必要に応じてクエリを編集します。

例：ウィンドウ関数でオブジェクトを順位付けする

ウィンドウ関数は、SQL クエリ変数を使用する一般的な理由の1つです。次のクエリは、@employees オブジェクトセットと @department パラメーターをバインドし、選択した部門で最も収入が高い従業員の名前を返します。

Copied!
1
2
3
4
5
6
7
8
SELECT name
FROM (
    SELECT name AS name,
           RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM @employees
    WHERE department = @department
)
WHERE salary_rank = 1;

例：概要を構造体として返す

構造体変数には、1回のクエリで計算された関連する複数の値を格納でき、ウィジェットはそれらにフィールドごとにバインドできます。次のクエリは、選択した部門の概要をまとめた単一の構造体を返します。

Copied!
1
2
3
4
5
SELECT COUNT(*) AS headcount,
       AVG(salary) AS average_salary,
       MAX(salary) AS top_salary
FROM @employees
WHERE department = @department;

例：順位付きリストを構造体配列として返す

同じ変数の変数タイプを構造体配列に設定すると、行ごとに1個の構造体が返されます。これは、ループレイアウトで順位付きリストを表示するのに役立ちます。

Copied!
1
2
3
4
5
6
SELECT name,
       salary
FROM @employees
WHERE department = @department
ORDER BY salary DESC
LIMIT 10;