テーブル関数とテーブル関数パラメータとは?

オランダ語版はこちら

このトピックでは、テーブル関数とテーブル関数パラメータとは何か、またそれらがInvantive SQL内でどのように適用されるかを学びます。SQL文法では、Invantive SQLの背後にある概念についてさらに詳しい情報を確認できます。

Invantive SQLには3種類のテーブルがあります。

  • テーブル:名前を指定することで取得できるデータ行のリスト
  • ビュー:名前を指定することでアクセスできるデータ行のリスト
  • テーブル関数:名前とパラメータの値を組み合わせて指定することで取得できるデータ行のリスト

テーブルとビューは通常、Projectsのように複数形の英語名を持ちます。テーブル関数は通常、複数形の英語名にByという接尾辞を付け、その後に最も重要なパラメータを続けた名前を持ちます。

Invantive Query Tool

Invantive Query Toolでは、名前の前にあるアイコンによって3種類が識別されます。

テーブル:
Table `DeclarationRenderData`

ビュー:
View `NativePlatformScalarRequests`

テーブル関数:
Tabelfuncton `DeclarationRenderDataByDocId`

Invantive Cloud SQL Editor

Invantive CloudのSQL Editorでも、この3種類は同様に見分けることができます。

テーブル:
Table `DeclarationRenderData`

ビュー:
View `NativePlatformScalarRequests`

テーブル関数:
Table function `DeclarationRenderDataByDocId`

テーブルとビューの違い

テーブルは、基盤となるデータコンテナに対して全てまたはほぼ1対1で対応するデータです。Invantive SQLドライバーは、データコンテナが期待するプロトコルに従ってテーブルの内容をリクエストし、その見返りにデータを取得します。場合によっては、Exact OnlineのDivisionLabelのように、派生データを含む限られた数の列が追加されることもあります。

ビューはテーブルと同じ構造を持ちますが、その内容はテーブルや他のビューに基づくSQLクエリから導出されます。したがって、各ビューの内容は最終的にテーブルの内容を組み合わせることで作成されます。

テーブルに基づくシンプルなビューの例は以下の通りです。

select gat.id
,      gat.code
from   exactonlinerest..glaccounts gat
where  gat.code not like '4%'
and    gat.code not like '5%'
and    gat.code not like '6%'
and    gat.code not like '7%'
and    gat.code not like '8%'
and    gat.code not like '9%'

このビューは、オランダの会計原則に従い、貸借対照表エントリーの一意のIDとコードのみを表示します。

テーブル関数

テーブル関数は特異な存在です。

テーブル関数は、従来の解釈でいう「データの集合(「セット」)」ではありません。テーブル関数は、多数のデータ集合を内包しています。数学的な関数のようにパラメータに値を指定することで、特定のデータ集合のみが取得可能になります。

異なるパラメータの組み合わせによって返される集合は、基盤となる関数の設計者の意図次第で、互いに素であったり、重複していたり、完全に同一であったりすることがあります。

なぜテーブル関数は存在するのか?

テーブル関数は、より手続き的なAPIから、SQLで処理できる形でデータを利用可能にするために、Invantive SQLでよく使用されます。

例えば、TeamleaderはV1とV2の両方のAPIにおいて、カスタムフィールドの値を含む特定の連絡先の全フィールドを取得するAPIを持っています。Teamleader V1では、これは次のようにクエリされます。

-- ランダムなIDを選択(Teamleader V1では10進数)。
select *
from   v1.contacts
limit  1

select *
from   v1.contact(10814620)

そしてTeamleader V2 APIでは:

-- ランダムなIDを選択(Teamleader V2ではGUID)。
select id
from   v2.contacts
limit  1

select *
from   v2.contactbyid('57da4314-d89a-0d9f-8771-f8c921391d74')

テーブル関数パラメータ

各テーブル関数には、値を指定できる1つ以上のパラメータがあります。パラメータは、数値、日付、BLOB、テキストなど、さまざまなデータ型を持つことができます。

パラメータの値は完全または部分的に必須とすることができ、startDate < endDateのようにパラメータの値同士に相互関係がある場合もあります。

テーブル関数パラメータの一覧とドキュメントは、Invantive Query Toolで、以下の例のようにExact OnlineのAccountancy Profiles APIからテーブル関数名を選択することで取得できます。

テーブル関数パラメータへの値は、位置指定または名前指定で提供できます。位置指定を使用する場合は、指定する必要がある最初のものから最後のものまで、すべてのパラメータ値を提供する必要があります。名前指定では、特定のパラメータのみに値を割り当てることができます。

位置指定の例:

select *
from   BalancesEx@eol(10523, 2022, 3)

名前指定の例:

select *
from   BalancesEx@eol(Params_PrevYears => 3)

位置指定と名前指定を組み合わせることも可能です。以下の例では、10523が最初のパラメータ(Division)に、3が3番目のパラメータ(Params_PrevYears)に割り当てられています。

select *
from   BalancesEx@eol
       ( 10523
       , Params_PrevYears => 3
       )

会社ごとのデータのように分割されたデータを持つプラットフォームでは、異なるcompany_code、division、office_codeを指定することで、選択された会社ごとにクエリが1回実行されることに注意してください。選択された会社の一覧は、Select companies/partitions for an Invantive Cloud database で説明されているuseステートメントを使用して指定できます。

ドキュメント

テーブル関数パラメータは、ここにあるTeamleaderのテーブル関数ContactByIdのように、各プラットフォームのオンラインドキュメントでも確認できます。

テーブル関数パラメータ
以下のパラメータを使用して、テーブル関数ContactByIdの動作を制御できます。必須パラメータには常に値を指定する必要がありますが、オプションパラメータは一般的に値を指定する必要はなく、実行時には事前定義された動作がデフォルトとして使用されます。値は位置指定と名前指定のどちらでも指定できます。どちらの場合も、指定されなかったパラメータはすべてデフォルト値で処理されます。
位置による値の指定は、最初の値から必要な最後の値までをすべて列挙することで行います。例えば、4つのパラメータを持つテーブルに対してselect * from table(value1, value2, value3)とすると、4番目のパラメータにはデフォルト値が使用され、最初の3つには指定した値が使用されます。
名前による値の指定は、値が必要なすべてのパラメータを列挙することで行います。例えば、同じテーブルに対してselect * from table(name1 => value1, name3 => value3)とすると、2番目と4番目のパラメータにはデフォルト値が使用され、1番目と3番目には指定した値が使用されます。

データディクショナリ

あるいは、この情報はビューSystemTableFunctionParametersを使ってデータディクショナリから取得することもできます。

select name
,      presence_required
,      database_data_type
,      dotnet_data_type
,      documentation
from   systemtablefunctionparameters@datadictionary
where  table_name = 'ContactById'
order
by     sorting_order

テーブル関数とPower BI

少なくとも1つの必須パラメータを持つテーブル関数は、現在のところPower BIでは利用できません。これは、Power BIがまだパラメータ値を指定したり、膨大な数のパラメータ値の組み合わせを反復処理したりする便利な手段を持っていないためです。Invantiveの知識を持つ経験豊富なPower BIコンサルタントであれば、例えばインメモリテーブルを通じて自分でデータを取得することができます。

ビューとPower BI

LoketのPayrollAdministrationPayrollRunPeriodResultsのように、Power BIやData Replicatorでの使用のために、多くのテーブル関数にビューが追加されています。これらのビューは最も一般的な情報を返しますが、数十万回のAPI呼び出しを実行する必要がある場合があるため、処理に時間がかかることがあるという欠点があります。したがって、大量のデータの場合はクエリの最適化が必要になりますが、この最適化は、そのビューが必要な情報を確実に含んでいることが明らかになった段階でのみ行うことをお勧めします。

一部のプラットフォームはリレーショナル理論に近く、フィルターを渡すことができ、すべての可能性を反復処理する必要がないデータ形式を提供します。他のプラットフォームは、例えばAPIがその当時の問題を解決するための既存のソリューションから発展してきたという経緯から、よりAPIに対する伝統的なアプローチを取っており、そのためInvantive SQL上にはテーブル関数が多く、テーブルが少なくなっています。

Power BIおよびInvantive Data Replicatorにとっては、データがテーブル形式で利用可能であることが望ましいです。用意されたビューはこのプロセスを簡素化します。一例として、Exact Online PayrollのビューPayrollComponentsが挙げられます。

このビューPayrollComponentsは、すべての雇用条件グループを反復処理し、対応する給与コンポーネントを取得します。

select pct.*
from   EmploymentConditionGroups ecp
join   ExactOnlineREST..PayrollComponentsByEmploymentConditionGroupCode(ecp.Division, ecp.Code) pct

ビューExactOnlineREST.Payroll.PayrollComponentsにはパラメータがないため、データはすぐに利用可能になります。

含まれている事前定義ビューは、データディクショナリのビューSystemViewsで確認できます。

select QUERY
from   SYSTEMVIEWS@DataDictionary
where  name = 'PayrollComponents'

ビューの種類

Invantive製品内には2種類のビューがあります。

  • プロバイダー固有のビュー:これらはInvantiveによって展開・保守されています。ユーザーによるカスタマイズはできず、単一のデータコンテナからのデータのみを組み合わせます。
  • カスタムビュー:これらはユーザーによって作成されるもので、例えばデータベースのStartup SQLを通じて作成されます。カスタムビューは複数のデータコンテナのデータを組み合わせることができます。Using custom database views for SQL and BI tools も参照してください。

新しいプロバイダー固有ビューを希望する場合は、フォーラムトピックを立ててください。その際、仕様の基礎となるカスタムビュー定義を併せて追加していただくと、最も便利です。

ビュー内のテーブル関数のパフォーマンス

通常のテーブルから取得した異なるパラメータでテーブル関数を繰り返し呼び出すビューは、かなり動作が遅くなることがあります。特に、Teamleaderのように呼び出しレートが低く制限されているプラットフォームでは、ビューからすべてのデータを取得するのに非常に時間がかかることがあります。例えば、Teamleaderでは、呼び出しレートの制限と複数行取得の代替手段がないことにより、V1FlatビューとV2Flatビューを使用した場合、1秒あたり1行しかクエリできません。

他のプラットフォームにおけるテーブル関数

他のハイエンドSQLプラットフォームにも、Invantive SQLのテーブル関数に似た概念があります。

構文はInvantive SQLとは異なる場合がありますが、パラメータ付きの関数呼び出しをデータソースとして使用できるという基本は変わりません。