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

英語版を見る

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

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

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

テーブルとビューは一般的にProjectsのような英語の複数形の名前を持ちます。テーブル関数は一般的に英語の複数形の名前に、接尾辞Byと最も重要なパラメータが続く形式を持ちます。

Invantive Query Tool

Invantive Query Toolでは、この3種類は名前の前にあるアイコンで見分けることができます:

テーブル:
Tabel `DeclarationRenderData`

ビュー:
View `NativePlatformScalarRequests`

テーブル関数:
Tabelfunctie `DeclarationRenderDataByDocId`

Invantive Cloud SQL Editor

Invantive CloudのSQL Editorでも、この3種類を見分けることができます:

テーブル:
Tabel `DeclarationRenderData`

ビュー:
View `NativePlatformScalarRequests`

テーブル関数:
Tabelfunctie `DeclarationRenderDataByDocId`

テーブルとビューの違い

テーブルは、その全体または大部分が背後にあるデータコンテナから1対1で取得されるデータです。Invantive UniversalSQLドライバーは、テーブルの内容を取得するために、データコンテナが期待するプロトコルに従ってリクエストを送信し、データを受け取ります。場合によっては、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%'

このビューは、Brouwer方式の勘定科目表において、貸借対照表項目の一意のIDとコードのみを表示します。

テーブル関数

テーブル関数は、少し特殊な存在です。

テーブル関数は、従来の意味での(“セット”)データの集合ではありません。テーブル関数は、多数のデータ集合を内包しています。数学関数のようにパラメータに値を指定することで、クエリで利用可能なデータ集合が初めて得られます。

パラメータの異なる組み合わせによって返される集合は、背後にある関数の設計者が必要と考える通りに、互いに素であったり、重複したり、完全に同一であったりする可能性があります。

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

Invantive UniversalSQLでは、より手続き型志向のAPIからのデータもSQLによる処理のために利用可能にするために、テーブル関数が多用されています。

例えば、TeamleaderはそのV1とV2の両方のAPIにおいて、特定の1人の連絡先のすべてのフィールド(カスタムフィールドの値を含む)を取得するための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のようにパラメータ値同士に相互関係がある場合もあります。

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

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

位置指定の例は以下の通りです:

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

名前指定の例は以下の通りです:

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

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

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

企業ごとのデータのようにパーティション化されたデータを持つプラットフォームでは、異なるcompany_code、division、またはoffice_codeを指定することで、選択された企業ごとにクエリが1回ずつ実行されることに注意してください。選択された企業のリストは、Administratie(s) selecteren voor een 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番目のパラメータにはデフォルト値が使用され、最初と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

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

一部のプラットフォームはリレーショナル理論に近い形を取っており、フィルタを渡すことができ、すべての可能性を反復処理する必要がないフォーマットでデータを提供します。他のプラットフォームは、例えば特定の実務上の問題に対する既存のソリューションからAPIが発展してきたことなどにより、より伝統的なAPIのアプローチを採用しており、その結果、Invantive UniversalSQL上では非常に多くのテーブル関数と少数のテーブルを持っています。

Power BIとInvantive Data Replicatorでは、データがテーブル形式で利用可能であることが望ましいです。利用可能なビューがあれば、それがより容易になります。この例として、Exact Online Salarisのビュー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を通じて作成されます。独自ビューは、複数のデータコンテナのデータを組み合わせることができます。Eigen database-views gebruiken voor SQL en BI-tools も参照してください。

新しいプロバイダー固有のビューが必要な場合は、フォーラムトピックを作成してください。その際、仕様の基礎となる独自のビュー定義をあらかじめ追加しておくと最も便利です。

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

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

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

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

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

What are table functions and table function parameters?
Loket verloonstaat tabel is onwerkbaar langzaam
Ophalen Teamleader endpoints met 'parameter' vanuit Invantive Cloud
Werkgeverskosten per bedrijf en medewerker uit Exact Online Salaris
Waar vind ik de lijst van tabellen en velden van Teamleader?
Invantive Cloud release 2020-11-30 biedt meer snelheid en inzicht
Tabellen Nmbrs uurcomponenten alleen beschikbaar voor huidige periode
Ophalen TeamLeader V1 Subscription_items met OData v4
Waar vind ik de lijst van tabellen en velden van Loket API?
Afrekenstaten uit Bouw7 ophalen
ExactOnlineREST.Payroll.PayrollTransactionsByPayrollYear
SQL startup teamleader - snelheid
Toevoegen Exact Online API endpoints
Vraag Visma Severa uren
Simplicate parameter
Tabel(len) achter Saldi Rapport uit get-my-report
Nieuwe Twinfield-tabellen voor dimensiewaarden
Views gebruiken om centraal te filteren voor Teamleader en Power BI
Lange Laadtijden DealPhaseHistories Tabel
Bouw 7 Project files tabel
Meest populaire Teamleader Focus tabellen voor 2022
Itgenoda061 "The 'FromPeriod' parameter must be specified for filtering" bij Power BI-download Visma.net van ProjectTransactions
BalanceLinesPerPeriod versus ReportingBalance op Exact Online
OData parameters meegeven vanuit Azure Data Factory
Twinfield BudgetByProfitAndLoss in PowerBI
Exact Online extra velden van artikelen niet zichtbaar in ItemsIncremental
Grote performancewinst bij real-time queries op gefilterde views met tabelfuncties
Major performance gains in real-time queries on filtered views with table functions
Loket tabel parameter ALLES AbsencesByEmployeeId
Parameter meegeven aan Easyflex-tabelfunctie via Azure Data Factory
Power BI, Excel en/of SQL editor met Robaws projectmanagementsoftware
Waar vind ik journaalposten-tabel op Easyflex?
Tabellen zoals CostEntryExpensesByProject
Opmerking verlofaanvraag Loket
Waar vind ik journaalposten-tabel op Easyflex?
Meerdere parameters in SQL query
Verschil tussen APOutstandingItems en APOutstandingItemsEx
Itgensgr046 melding bij Aanroep Robaws data
Itgendid357 A value for the parameter 'creditnote_id' is required on table 'Teamleader.V1.creditnote_items'
API BOUW 7 - meer informatie verkrijgen inkoopfacturen
Aanroepen SubjectConnector
Loket eigen veld, Invantive custom field ophalen
ProjectAssigmentBudget / Waarde capaciteitsplanning
Unknown identifier 'COMPANY_CODE
Unknown identifier 'COMPANY_CODE
Hoe werken tabelfuncties?
Tabel PurchaseInvoiceLinesByPurchaseInvoiceId ontbreekt in OData-feed
Nmbrs Power BI aantal tabellen
Itgendid357 melding bij ophalen ProjectAssigneesByProjectIdV2
Niet alle Nmbrs-tabellen beschreven zichtbaar in Power BI
Parallel uitvoeren van tabelfuncties in SQL met de JOIN_PARALLELIZATION hint
Hoe koppel ik JIRA (Servicedesk) of Atlassian Confluence aan Power BI?
Verloven kunnen opvragen voor NMBRS bedrijven
Beschikbare tabellen in Loket / niet alle tabellen lijken aanwezig
Verhoogde doorvoersnelheid met Teamleader Focus V1 en V2 API-tabellen
Teamleader Focus: decision maker
Hoe haal ik Departments o.b.v. employers uit Loket?
Loket: tabelnaam voor GetRunOverviewsByPayrollRunId endpoint
Documentatie Datamodel Snelstart komt niet overeen met (OAuth) tabellen in Power BI
Selecteer kolommen om over te halen naar SQL Server vanaf Exact Online
Teamleader Power BI views voor custom fields die meerdere waardes toestaan
Een bestand of plaatje uploaden naar Knack.com
Bedrijfsonderdeel ("verdeling") medewerker achterhalen