リレーショナルデータベース クイックスタート
リレーショナルデータベース クイックスタート
| はじめに | EntityフレームワークとSQL |
| リレーショナルデータベースに接続する | プログラムによるクエリの構築と生成 |
| Entityフレームワークとリレーショナルデータベース | 実践的なクエリ構築手法 |
| 中核的なクエリ構成要素 | エラーとエラーの処理 |
| その他のツール | すべてをまとめる:より複雑なクエリの例 |
このチュートリアルの目的
このチュートリアルを読むと,Entityフレームワークのクエリを構築してそれを実行する方法の中心的部分が理解できる.より高度なマテリアル,微妙な部分,稀なケースは意図的に除外し,チュートリアルを短めかつ入門レベルに抑えた.しかし,中心的なクエリ構築機構のほとんどはカバーしており,例題を使って解説してある.入門レベルではこれで十分である.
リレーショナルデータベースの接続性の役割
リレーショナルデータベースは,情報の保管,処理,ルックアップについて広く使われている重要な方法である.関連するテクノロジーは,ここ十年ほどで非常に発達し,最新のリレーショナルデータベースのバックエンドには,小さいデータ量から非常に大きいデータ量までの構造化データを保存し,処理するための優れた機能が備わっている.したがって,データサイエンスからWebアプリケーションまで多数の領域の作業を行うために,リレーショナルデータベースをワークフローにシームレスに統合することが重要である.
ここで重要な点は,データベース側で極端に大きいデータセットでさえ処理できる機能,つまりの「アウトオブコア」である.ほとんどのリレーショナルデータベースのバックエンドは非常に最適化されており,最新のテクノロジーを適用してそのような大きいデータセットを非常に効率的に処理できるようにする.
Wolfram言語では,大きいデータセットの計算をデータベースエンジンに任せられるということは,特に重要である.そのような計算の多くにはWolfram言語の最大級の計算パワーを必要としなくて済むからである.一方,結果のデータセットは大幅に縮小されていることがあるため,Wolfram言語で簡単にさらに処理することができる.
Wolfram言語におけるリレーショナルデータベースの接続性
一般的考察
リレーショナルデータベースを自分の作業に直接統合しようとすると,さまざまな理由から複雑になる可能性がある.最初の理由は,多くの分野の専門家は,リレーショナルデータベースの直接の使用に必要なSQLの知識を持っていないということである.2つ目は,通常の使用におけるさまざまなデータベースのバックエンドについて,SQL方言とサポートされる機能の間に,通常わずかではあるが違いがあるという点である.3つ目は,自分で手動でデータパイプラインを構築して,データベースをプログラミング環境に接続しなければならない点である.これらの理由により,リレーショナルデータベースを言語に直接密接に統合できるということは非常に有益なことなのである.これでユーザは技術的なインフラストラクチャではなく問題の解決に集中することができる.
Wolfram言語は非常に高レベルな記号的性質があるため,リレーショナルデータベースを言語に統合するフレームワークに厳しい条件を課している.フレームワークは高レベルで記号的,しかも使い方が慣用的でなければならず,生のSQLに関してクエリの構築および他の機能を制限し過ぎないように十分な構造を示すものではければならないのである,特に慣用的なWolfram言語のプログラミングは関数型プログラミングスタイル,関数の合成と不変性に傾倒している.ゆえに,接続性フレームワークはそのような機能を利用し,サポートしなければならない.
Entityフレームワークの役割
Entityフレームワークは上記の必要条件をほぼ満足する.Wolfram言語にはOOPの意味でのオブジェクトはないが,リレーショナルデータベースについてのWolfram言語のEntityフレームワークは多くの点で,他の言語のオブジェクトリレーショナルマッパー(ORM)に類似している.やや簡略しすぎだが,実体はデータベース表の1行に相当し,実体クラスはデータベース表(既存,空想にかかわらない)に相当し,実体特性はデータベースの列に相当する.
その結果,データベースのクエリが必要なとき,大半の場合Entityフレームワークの関数型クエリ言語を使って構築された高レベルのEntityフレームワーククエリを介して,Wolfram言語内からデータベースと交信することが可能となる.この後フレームワークはクエリを適切なSQL方言に変換し,データベース側でそれを実行し,結果を適切な形式でユーザに戻す.
EntityフレームワークとDatabaseLink
長い間,Wolfram言語でリレーショナルデータベースとインタラクトする主なツールはDatabaseLinkであった.これはJ/LinkおよびJDBCの上に構築されており,リレーショナルデータベースとのインタラクトのためのフル装備のツールボックスを提供する.
DatabaseLinkツールボックスと,このチュートリアルで論じるテクノロジーとの違いを理解することは重要である.最も重要な違いは,これらのツールのいずれかによって提供される抽象化のレベル,およびデータベースとのインタラクトに対してサポートされる機能にある.
DatabaseLinkによって提供されるツールボックスはかなり低レベルである.このツールボックスは,簡単なものはデータベースのクエリを記号的に構築することができるが,現実世界のクエリの大部分はDatabaseLinkで使うとき,SQL文字列として書かなければならない.これはユーザにとって多くのことを意味している.SQL(および使用される特定のデータベースバックエンドに対応する特定のSQL方言)に慣れている必要があるという点や,Wolfram言語の記号的機能および高レベルの抽象化という長所を使うことができないという点である.このすべてにより,Wolfram言語へのデータベースの接続は不完全で低レベルなものとなる.
Entityフレームワークに基づくテクノロジーは,Wolfram言語にハイレベルでシームレスなリレーショナルデータベースの統合を提供するために土台から設計されたものであり,Wolfram言語のリレーショナルデータベースにかかわる,よりパワフルで高レベルなワークフローを可能にする機能が含まれている.この機能の例として,記号的クエリ言語,関係,自動SQL生成とバックエンドの特化,組込みの型チェック等が挙げられる.
一方でDatabaseLinkは書込み操作(SQL INSERT / UPDATE / DELETE),トランザクションサポート,タイムアウトの制御,接続プール等,データベースにかかわる一般的なワークフローに必要な中心的な機能を多くサポートする.また,これは効率の目的で,データベースの結果集合への低レベルアクセスも提供する.これらのEntityフレームワークベースのテクノロジーは現在ないため,現在DatabaseLinkの方が機能が充実しているということもできる.
この2つのテクノロジーはどちらもWolfram言語に統合されているということ意外,この2つの間の深い相互運用性は現在存在しない.しかし,相互運用性のレベルは将来のWolfram言語では向上する可能性がある.
このチュートリアルの構造
2番目のセクションでは,リレーショナルデータベースのコンテキストにおけるEntityフレームワークの主な機能を見,例示する.
3番目のセクションでは,より複雑なクエリを構築するためにEntityフレームワークが提供する中核的なクエリ構築ブロックを紹介する.
4番目のセクションには追加の便利な構造とツールについての簡単な説明が含まれている.これらはやや高度に感じるかもしれないが,実際には非常に便利である.
5番目のセクションでは,Entityフレームワークでサポートされるさまざまなクエリ構築プリミティブに対して,どのようなSQLが通常生成されるかを説明する.
6番目のセクションでは,プログラムでクエリを生成する方法を解説し,これがなぜ便利であるかを例示する.
7番目のセクションではクエリ構築の実践的なテクニックをいくつか示す.
8番目のセクションでは,エラーの対処法,およびクエリを構築し実行しているときに出くわすかもしれないよくあるエラーについて簡単に説明する.
最後のセクションには,さまざまなクエリ構築ブロックを使ってより複雑なクエリを構築する方法を示す,より複雑なクエリの例が含まれている.
サンプルデータベース
このチュートリアルの例は,サンプルのパブリックドメインデータベースであるClassic Models(特にこのSQLiteバージョン)に基づいている.これはクラッシックカー模型の小売業者のデータベースである.
[image]- offices表はemployees表と1対多の関係を持つ.通常同じ営業所で働く従業員は多い.この関係は,offices表の主キーofficeCodeに対応する,employees表の外部キーofficeCodeで実現される.
- employees表はそれ自身と1対多の関係を持つ.一般に各従業員には部下が0人,1人あるいは複数人いる.この関係は,同じemployees表の主キーemployeeNumberにリンクされた外部キーreportsToによって実現される.
- employees表はcustomers表と1対多の関係を持つ.各従業員は0人,1人あるいは複数人の顧客に対応する.一方各顧客は常に多くて1人の従業員に対応される.この関係は,employees表の主キーemployeeNumberに対応する,customers表の外部キーsalesRepEmployeeNumberにより実現される.
- customers表はpayments表と1対多の関係を持つ.各顧客は0回,1回あるいは複数回の支払いをしている.この関係はcustomers表の主キーcustomerNumberに対応する,payments表の外部キーcustomerNumberで実現される.
- customers表はorders表と1対多の関係を持つ.各顧客は0回,1回あるいは複数回の注文をしている.この関係はcustomers表の主キーcustomerNumberに対応する,orders表の外部キーcustomerNumberで実現される.
- orders表はorderdetails表と1対多の関係を持つ.注文はそれぞれ1つあるいは複数のアイテムからなる.異なる製品タイプの各アイテムはorderdetails表に単独の記録を持つ.この関係はorders表の主キーorderNumberに対応する,orderdetails表の外部キーorderNumberで実現される.
- orderdetails表はproducts表と1対多の関係を持つ.各製品は0回,1回あるいは複数回注文されている.この関係はproducts表の主キーproductCodeに対応する,orderdetails表の外部キーproductCodeで実現される.
- products表はproductlines表と多対1の関係を持つ.各製品ラインには,それに属す製品が1つあるいは複数個ある.この関係はproductlines表の主キーproductLineに対応するproducts表の外部キーproductLineで実現される.
データベースへの接続
- DatabaseReferenceを使って,データベースへの接続を確立する
- その接続を使ってRelationalDatabaseオブジェクトを生成する(この操作でデータベースを検査し,データベーススキーマ等のデータベースメタデータを取得する).
- RelationalDatabaseオブジェクトから,データベースで裏付けられたEntityStoreオブジェクトを生成する.
- そのEntityStoreオブジェクトをEntityフレームワークに登録する.
conn = DatabaseReference[FindFile["ExampleData/ecommerce-database.sqlite"]]その接続を使ってRelationalDatabaseオブジェクトを構築する:
rdb = RelationalDatabase[conn]次のようにして,データベースで裏付けられたEntityStoreを生成する:
es = EntityStore[rdb]EntityStoreを登録する:
EntityRegister[es]EntityValue[
"offices",
{"officeCode", "addressLine1", "city", "state", "country"},
"Dataset"
]まずEntityStoreの登録を解除する:
EntityUnregister[es]rdb = RelationalDatabase[FindFile["ExampleData/ecommerce-database.sqlite"]]
EntityRegister[EntityStore[rdb]]RelationalDatabaseオブジェクトを使う
先のセクションで説明したように,データベースに裏付けされたEntityStoreを生成するのに必要なステップの一つは,RelationalDatabaseオブジェクトを作成することである.しかしこのオブジェクトはこれだけでも便利である.これはデータベーススキーマ(表,列,制約条件等)に関する情報を含んでおり,関心のある情報の一部を視覚的に調べるためにも,それをプログラムで抽出するためにも使うことができる.
次でRelationalDatabaseオブジェクトを生成する:
rdb = RelationalDatabase[FindFile["ExampleData/ecommerce-database.sqlite"]]RelationalDatabaseオブジェクトのフォーマット付けにより,階層的グループオープナを使って,データベース構造を視覚的に簡単に調べることができる.このオープナを広げると,指定された表や列に関する詳細を調べることができる:
[image]次は,指定されたRelationalDatabaseオブジェクトの表の名前をすべてリストする:
rdb["Tables"]rdb["offices", "Exists"]
rdb["foo", "Exists"]rdb["employees", "Columns"]rdb["employees", "lastName", "Exists"]
rdb["employees", "foo", "Exists"]RelationalDatabaseオブジェクトが認識する,指定された表の列についての特性すべてをリストする:
rdb["employees", "employeeNumber", "Properties"]rdb["employees", "employeeNumber", "Name"]
rdb["employees", "employeeNumber", {"Name", "NativeTypeString"}]
rdb["employees", "employeeNumber", All]さまざまな特性やメソッドについての詳細は,RelationalDatabaseオブジェクトの参照ページをご覧いただきたい.
はじめに
このセクションでは,リレーショナルデータベースのコンテキストで使われる方法で,Entityフレームワークでサポートされる重要な中核的操作について説明する.ここではクエリの構築には重点を置かない.これについては別のセクションで扱う.このセクションは準備のためのものであり,その他の重要な面について示す.そのうちの多くは,Entityフレームワークを介してリレーショナルデータベースを効率的に使うために必須の条件である.
リレーショナルデータベースを使った作業でEntityフレームワークを効率的に使うためには,Entityフレームワークの概念や構造がフィレーショなるデータベースの中核的概念や構造にどのように対応しているかについて,少なくとも基礎的な知識を持っていることが重要である.まず,このトピックについて話す.
次に,クエリの実行,およびいわゆる「リゾルバ」(クエリのコンパイルおよび実行の処理を開始し,結果を返すことによって,記号的に表されたクエリを実際に特定の結果に変換するのに使われる関数)について説明する.
続いて,計算された特性とEntityFunctionを扱う.これらは非常に重要な構成要素であり,これらを使うと新しい特性をすぐに定義し計算することができる.このような新しい特性は,データベース側での複雑な計算の実行を要求することもある.
多くの場合,単独の実体が扱えることが大切である.このことは,結果をよりよく可視化したり理解したりすることからクエリの構築(デバッグやプロトタイプ作成の場合,単独の実体のクエリの一部が実行できることは大変役に立つ)までのさまざまな目的に有用である.このトピックについて簡単に説明する.
リレーショナルデータベースでは,データベース表の主キーの概念は中心的なものである.これに対応するEntityフレームワークは単独の実体でのCanonicalName特性である.正規名とともに,単一の実体を定義するもう一つの部分は,その実体のタイプである.これらのトピックは大変重要なので,別のセクションで説明する.
リレーショナルデータベースを含むほとんどの最新ワークフローは主キーを持つデータベース表を扱うが,表が主キーを持たない,あるいは持っていてもデータベーススキーマレベルで強制されない場合もあり得る.これらはEntityフレームワークのコンテキストではより重要である.そのような表や実体のタイプでは,Entityフレームワークの機能の一部しか使えないからである.この問題についても説明する.
このチュートリアルでは,リレーショナルデータベースとのインタラクトに暗示的に関わる型や型のシステムには焦点を当てないが,効率的な作業のために理解しておくことが重要な1つの型の特徴がある.つまり,実体型には実体値および実体クラス値の特性が含まれることがあるということである.このチュートリアルでは,そのような特性を関係と呼ぶ.これはフレームワークによって,各実体型に対する主要特性(データベース表の列に直接対応する特性)の集合に加えられる.
EntityフレームワークとSQLの間の近似マッピング
EntityフレームワークはWolfram言語で特にリレーショナルデータベースを表すために使われるので,Entityフレームワークとリレーショナルデータベース/SQLの間で中核的概念と構造について明確なマッピングがなければならない.
Entityフレームワークの多数の機能において,このようなマッピングは比較的直接的である.しかし,ある意味でEntityフレームワークの方が豊かなデータモデルを表現する.例えば,Entityフレームワークをインメモリで使用する場合,実体特性はその値として任意のWolfram言語式を持つことができる.実体特性は直接使われたら,リレーショナルデータベースの第1正規形にすら適合しないであろう(実体特性がList値のとき等).別の例として,Entityフレームワークは無限個の実体で型を表すことができるというものがある.これはリレーショナルデータベースでは簡単にできないことである.また,リレーショナルデータベースでは簡単に可能な計算の集合がWolfram言語よりも限られている.Wolfram 言語は「すぐに」使える格段に豊かな計算機能をもともと持っているのである.
関係モデルの制約にも利点はある.例えば,ACID準拠のデータベースのデータ一貫性,トランザクション,その他の関連した便利な機能を強く保証することができる.これらの制約は,データベースに裏付けされた実体ストアについてのEntityフレームワークに課せられる同様の制約も意味する.
データベース(スキーマ) | 実体型の集合(データベース表) | ||
データベース表 | 実体タイプ | 類似した特性の集合(表の行)を持つ実体集合(実体集合ハンドル) | |
データベース表の行 | Entity(単一の実体) | 一意性を持つ単一の「もの」(表の行)を表す,特性/値の集合(集合のハンドル) | |
データベース表のフィールド(列の名前) | 特定の特性.通常特定の型を持つ | ||
主キー | 与えられた実体型(データベース表)に対して(特性集合について一緒にとられた場合)一意であることが保証された,特性または特性の集合 | ||
外部キー | 実体値特性 | 値が単一の実体(同じ実体型でも異なる実体型でもあり得る)である実体特性.データベースの場合は,表(同じ表でも異なる表でもよい)の一意の行を指すフィールド | |
派生表 | 同じ型(登録されたものあるいはクエリによって定義されたもの)の実体の集合(のハンドル)データベース側でsh,派生表は,FROM句で使われる仮想表/サブクエリである |
前述の通り,このマッピングにはいくらかの非対称性がある.これにより,既存のリレーショナルデータベースを実体ストアとして表現することが簡単になっている.しかし,既存のインメモリの実体ストアすべてが,追加の抽象化レイヤーを生成せずにリレーショナルデータベースに簡単にマップできるというわけではない.特に以下は存在する制約のいくつかである:
これらの制約は,既存のインメモリの実体ストアをリレーショナルデータベースで強化する(個の機能は現在Entityフレームワークではサポートされていない))必要がある場合により重要であると同時に,全体的な概要をよりよく理解するために覚えておくと便利である.
別の方向に動作する制約もある.例えば,主キーを持たないデータベース表を作成し,これらをデータベース側で使うことは技術的に可能である.しかし,Entityフレームワーク側では,実体はどれも指定された実体クラス内で一意のCanonicalNameを持たなければならない.つまり,単一の実体がそのようなデータベース表に対して定義されることはなく,Entityフレームワークの単一実体に関する機能はそのようなデータベース表/型では動作しないということである.特に,特定の修飾子("EntityPropertyAssociation"等)を持つEntityListやEntityValue等の関数はそのような場合には動作しない.この問題はこちらのセクションで詳しく説明する.
EntityValueとEntityListを持つクエリを実行する
メインとなり,より広く使われているのがEntityValueである.これは通常与えられたクエリを実行し,さまざまな形式で結果を得るために使われる.リレーショナルデータベースの場合,クエリは通常(仮想)データベース表を表す.これはEntityフレームワークでは(仮想)実体型に相当する.この場合のEntityValueの役割は,与えられたクエリを適したSQL方言にコンパイルし,データベースでそれを実行し,さまざまな形式(値のリスト,あるいは連想)で実体特性の集合(結果の表の)に対する値を抽出することである.
以下はEntityValueを使った簡単な例である.
EntityValue["offices", {"officeCode", "city", "state", "country"}]EntityValue[
"offices",
{"officeCode", "city", "state", "country"},
"PropertyAssociation"
]EntityValue[
"offices",
{
EntityProperty["offices", "officeCode"],
"city",
"state",
EntityProperty["offices", "country"]
},
"PropertyAssociation"
]結果をDatasetの形式で得ることもできる:
EntityValue[
"offices",
{"officeCode", "city", "state", "country"},
"Dataset"
]場合によっては,与えられた実体型/クラスに含まれる実体のリストを得たい場合もある.これにはEntityListを使う.
EntityList["offices"]計算された特性とEntityFunction
既存の特性の抽出に加え,計算された特性,つまりデータベース側で複雑な計算を必要とする可能性がある,その場で生成された特性を抽出することもできる.このような特性はEntityFunctionを使って表さなければならない.この場合のEntityFunctionのセマンティックはFunctionと似ている.いくつかある重要な違いについては皇族のセクションで扱う.EntityFunctionは,(このセクションのコンテキストでは)単一の実体を取り,その実体で実行された計算の結果を返すEntityPropertyに似た構造と考えることができる.現在EntityFunctionはスカラーのみを返すことができ,値のリスト,実体,実体クラス等は返せない.
以下は,EntityFunctionを使ってデータベース側での計算を必要とする特性を取り出す例である.
EntityValue[
"offices",
{
"officeCode",
EntityFunction[
o,
StringJoin[
o["addressLine1"],
If[MissingQ[o["addressLine2"]], "", " " <> o["addressLine2"]],
", ",
o["city"],
", ",
If[MissingQ[o["state"]], "", o["state"]], " ", o["postalCode"],
", ",
o["country"]
]
]
}
]EntityFunctionは,変数や本体が早まって評価されないよう,Functionと同様にHoldAll属性を持つ.EntityFunctionの本体は式であり,EntityFunctionが理解しSQLにコンパイルすることができるプリミティブの限定された集合を使うことができる.このようなプリミティブの完全リストはEntityFunctionのドキュメントに記載されている.EntityFunctionの実際の使い方についての多数の例は,後続のセクションで見ることができる.
単一の実体を使う
単一の実体はEntityフレームワークにおいて重要な概念であり,構成要素である.単一の実体を使うことができると,ワークフローにインタラクティブ機能が加わり,最終結果が多数の実体(実体クラス)で動作しなければならない場合でも,データがよりよく理解できる.
単一の実体は,実質的に実際のデータのハンドル(参照)であるということを理解することが重要である.単一の実体には型と正規名以外,データは含まれない.それほど怠け者なのである.実態からデータを抽出する必要があるたびに,新しいクエリを実行しなければならない.実体のこの怠惰な性質こそがEntityフレームワークの非常に便利な特徴なのである.それにってより抽象的に実体が操作できるからである.しかし,気をつけなければならない点もある.例えば,同じクエリの2度の連続した実行の間で特定の実体の特性が変更された場合,返される結果もこの変更を反映して,通常異なる.また,ある時点で指定された実体クラスに存在していた実体が削除され,もはや存在しなくなっていることもあり得る.もちろんこのような複雑なことは,時間内に変化するデータについてのみ発生することである.
allOffices = EntityList["offices"]
office = First @ allOffices特性値はEntityValueを使って抽出することができる:
EntityValue[
office, {
"officeCode",
"city",
"state",
"country",
EntityFunction[o, StringJoin[o["city"], " : ", o["state"]]]
}
]office[{
"officeCode",
"city",
"state",
"country",
EntityFunction[o, StringJoin[o["city"], " : ", o["state"]]]
}]EntityValue[
office,
{"officeCode", "city", "state", "country"},
"PropertyAssociation"
]EntityFunctionを使うと,単一の実体について計算された特性を抽出することができる.これは迅速なルックアップ,およびより複雑なクエリのプロトタイプ作成の両方に便利なことがある.
EntityFunction[o, StringJoin[o["city"], " : ", o["state"]]][office]office[EntityFunction[o, StringJoin[o["city"], " : ", o["state"]]]]最後の例はEntityFunctionの特性のような性質も表している.
実体型,単一実体の一意性,CanonicalName
Entityフレームワークでは,それぞれの実体はその実体型,実体クラス内で一意でなければならない.換言すると,それぞれの実体は一意の独自性を持っていなければならず,複数の実体を含むことのできる実体クラス,実体型はない.
実体型と正規名
実体のInputFormを見る:
EntityList["offices"] // InputForm実体の正規名もCanonicalNameを使って抽出することができる.
office = First @ EntityList["offices"]
CanonicalName @ officeCanonicalName関数は実体のリストにも使える:
CanonicalName @ EntityList["offices"]EntityList[SampledEntityClass["orderdetails", 5]]
InputForm[CanonicalName @ %]実体の実体型は常にEntity[]の第1引数であるが,常に文字列であるとは限らない.特に,型が登録された型ではなく,Entityフレームワークのクエリによって陰的に定義された「ランタイム」型である場合,実体型はそのクエリ自体となる.
countryCityCustomers = SampledEntityClass[
AggregatedEntityClass[
"customers", "customerCount" -> EntityFunction[c, Length[c["customerNumber"]]],
{"city", "country"}
],
10
]First @ EntityList @ countryCityCustomersFirst @ %正規名がリストになり得る場合
- 実体が,CombinedEntityClass[...]で表される「ランタイム」型に属す
- 実体が,AggregatedEntityClass[...]で表される「ランタイム」型に属す.集合に対する実体グループは複数の特性に基づいて形成される.
直前の例は最後の場合に該当し,グループ化の特性は"city"と"country"であった."orderdetails"型の実体を使ったその前の例は,最初の場合に該当し,"orderdetails"表の主キーは特性"orderNumber"と"productCode"から作成された復号キーであった.
officeEmployees = CombinedEntityClass["employees", "offices", "officeCode"]
employee = First @ EntityList @ officeEmployees
CanonicalName @ employee単一の実体と,主キーのないデータベース表
あるデータベース表がデータベーススキーマに主キー制約を持たない場合は一般的ではないが,実際には重要である.そのような場合すべてにおいて,一意の値を持つ列が実際に表にないわけではない.しかし現在のところ,Entityフレームワークがそのような表の主キーとして使う列を示す方法はない.主キーについての情報はすべて,現在データベースの検査時間(RelationalDatabaseオブジェクトが構築されるとき)に,既存のデータベース制約から完全に取得されるのである.
これがEntityフレームワークベースのワークフローにとって意味することは,このような表では,データベースの行に正規名を付加する明確な方法がないため,単一の実体は定義できないということである.だからといって,このような表では役に立つことが何もできないわけではなく,Entityフレームワークの機能のある部分が動作しないということである.
Entityフレームワークの機能に関し,このような表に存在する制約を例示するために,「タイタニック(Titanic)」データベースを使う.これはWolfram CloudでホストされているSQLiteデータベースであり,以下の「タイタニック」データセットに基づいている:
ExampleData[{"Dataset", "Titanic"}]データセットの構造から分かるように,主キーとして自然に動作するようなフィールド,あるいはフィールドの組合せが表には存在しない.データには乗客の名前はなく,客室のクラス,年齢,性別,生存したかどうかしかないため,データセットに重複した記録がないという保障はない.それでもこのデータセットは便利な情報源である.
まずRelationalDatabaseオブジェクトを作成する:
titanicDB = RelationalDatabase[URLDownload[CloudObject[
"https://www.wolframcloud.com/obj/documentation/titanic.sqlite"
]]]次に,データベースに基づいてEntityStoreオブジェクトを作成し,登録する.
EntityRegister @ EntityStore @ titanicDBEntityList["titanic"]ある修飾子のEntityValueも使えない:
EntityValue["titanic", {"class", "age"}, "EntityPropertyAssociation"]EntityValue["titanic", {"class", "age"}, "PropertyAssociation"]//ShortEntityValue[
FilteredEntityClass[
"titanic",
EntityFunction[p, p["class"] == "1st" && p["age"] <= 15 && p["survived"] == 1]
],
{"class", "age", "sex", "survived"},
"PropertyAssociation"
]次のクエリでは,乗客の年齢クラスを粗く分ける.0才から20才までをグループ1,20才から40才までをグループ2というようにしていく.その後,それぞれの客室クラス,年齢グループ,性別について,乗客の生存率を計算し,結果を降順に並べ替える:
EntityValue[
SortedEntityClass[
AggregatedEntityClass[
ExtendedEntityClass[
"titanic",
"ageGroup" -> EntityFunction[t, Ceiling[t["age"] / 20]]
],
"surviverFraction" -> EntityFunction[t, Mean[t["survived"]]],
{"class", "ageGroup", "sex"}
],
"surviverFraction" -> "Descending"
]
,
{"class", "ageGroup", "sex", "surviverFraction" },
"PropertyAssociation"
] // Dataset- データベーススキーマに主キー制約がないデータベース表は,CanonicalNameが定義されていない実体型に相当する.つまり,そのような型について単一の実体を定義することはできないということである.
- EntityStoreオブジェクトのそのような型を登録することはできるが,Entityフレームワークの機能すべてが使えるわけではない.
- 単一の実体がそのような型で使えない(またある修飾子についてEntityListおよびEntityValue等の関数が使えない)一方,EntityValueの形式の多く,およびその型の単一の実体を含まない多くのクエリはまだ動作する.
実体値および実体クラス値の特性
emps = office["employees"]EntityListを使うと,この実体クラスを実体のリストに拡張することができる:
EntityList @ empsemp = First @ EntityList @ emps
EntityValue[emp, "PropertyAssociation"]emp["offices"]
emp["offices"] === officeここで重要なのは,実体クラス値の特性は,実体のリストに自動的に分解されるのではないので,結果として実体のリストが欲しい場合は,EntityListをその値に明示的に適用する必要があるということである.
生成された特性は関係についてのセクションでさらに説明する.
結果の欠損値と不正な特性名
parisOffice = Part[EntityList["offices"], 4]この営業所はフランスのパリにあるため,"state"フィールドには値がない.このような欠損値は,データベース側のNULL値に対応し,結果ではMissing["NotAvailable",...]で表される:
EntityValue[parisOffice, {"officeCode", "city", "state", "country"}]結果としてMissing[]値になる入力の別の例に,不正な特性名に対して値を要求した場合がある.しかしこの場合は,値が欠損している理由が異なる: Missing["UnknownProperty",...].
EntityValue["offices", {"officeCode", "city", "state", "country", "foo"}]はじめに
まず特性のようなクエリを扱う.これらについては,計算された特性を使って利用可能な実体特性の集合を大きく拡張する方法,およびこれらの計算された特性を表すためにEntityFunctionを使う方法に関する多数の例を使って説明する.
このセクションの残りの部分では,実体クラス(および他のパラメータ)を取り,新しい(変形した)実体クラスを返すさまざまなプリミティブを扱う.Entityフレームワークは,以下のような一般的なデータ処理操作に対して,そのようなプリミティブを提供する:
EntityFunctionと実体のようなクエリ
実体のようなクエリは,Entityフレームワーククエリの一部を構築するために使われるWolfram言語式である.これはクエリのコンパイルと実行の処理においてSQL式にコンパイルされる.特性のようなクエリを構築するために使われる主な構造はEntityFunctionである.
特性のようなクエリは,計算された特性を定義することや,データのフィルタリング,基準の並べ替え,集計のための述語等のさまざまな場所で便利である.このチュートリアルでは,そのうちの一般的な例をいくつか考える.
EntityValue[
"orderdetails",
EntityFunction[od, od["priceEach"] * od["quantityOrdered"]]
]//ShortEntityValue[
"offices",
{"state", EntityFunction[o, StringStartsQ[o["state"], "C"]]}
]欠損値をテストするためにはMissingQ述語を使うことができる.
EntityValue["offices", {"state", EntityFunction[o, MissingQ[o["state"]]]}]EntityValue["offices", EntityFunction[o, o["state"] == "CA"]]
EntityValue["offices", EntityFunction[o, o["state"] === "CA"]]
EntityValue["offices", EntityFunction[o, o["state"] != "CA"]]
EntityValue["offices", EntityFunction[o, o["state"] =!= "CA"]]EntityValue[
"products", {
"productName",
EntityFunction[p, (p["MSRP"] - p["buyPrice"]) / (p["MSRP"])]
}
]//Short[#, 7]&EntityValue[
"products", {
"productName",
EntityFunction[
p,
Sqrt[(p["MSRP"] ^ 2 - p["buyPrice"] ^ 2) / (p["MSRP"] ^ 2 + p["buyPrice"] ^ 2)]
]
}
]//Short[#, 7]&次のクエリは,"orderdetails"タイプの各実体について,数量が30より大きくアイテムごとの価格が200ドル以上でTrueを返すブール特性を計算する.Wolfram言語側の後処理では,Trueとなった製品のみが選ばれる:
EntityValue[
"orderdetails",
{
"productCode", EntityFunction[o, o["quantityOrdered"] > 30 && o["priceEach"] ≥ 200]
}
]//Cases[{x_, True}]次のクエリは注文数が100で割り切れる場合にのみTrueとなる特性を計算する:
EntityValue[
"orderdetails", {
"productCode",
"orderNumber",
EntityFunction[ord, Mod[ord["orderNumber"], 100] == 0]
}
]EntityValue[
"offices", {
"officeCode",
"state",
"city",
EntityFunction[
o,
StringContainsQ[o["city"], "a"] || StringContainsQ[o["city"], "o"] && Not[MissingQ[o["state"]]]
]
}]次のクエリは,注文日から8日以内に発送しなければならないすべての注文にTrueを返すブール特性を計算する:
EntityValue[
"orders",
EntityFunction[
o,
FromUnixTime[o["requiredDate"]] < DatePlus[FromUnixTime[o["orderDate"]], 8]
]
]//Short次の例では,ブール値のクエリ式がFilteredEntityClass内のフィルタリング基準として使われており,注文の数量が30より多いアイテムの注文をすべて求める:
EntityValue[
FilteredEntityClass[
"orderdetails",
EntityFunction[od, od["quantityOrdered"] > 30]
],
{"orderNumber", "quantityOrdered"}
]//Take[#, 10]&実体クラスの変換器に関する注意
次のセクションで説明する演算子FilteredEntityClass,SortedEntityClass,SampledEntityClass,ExtendedEntityClass,AggregatedEntityClass,CombinedEntityClassはすべて,実体クラスの変換器と呼ぶことができる.これらはすべて実体クラスを1個(CombinedEntityClassの場合は2個)取り,新しい実体クラスを返す.
これらの構造は完全に記号的で不活性であることを理解することが重要である.個の中のいずれかが実体クラスの引数に適用されても実際の作業は実行されない.結果のクエリは記号的なままで,実際にクエリを実行するためにはリゾルバ関数のいずれ(EntityValueかEntityList)を呼び出す必要がある.
query =
SampledEntityClass[
SortedEntityClass[
CombinedEntityClass[
"customers",
AggregatedEntityClass[
"payments",
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
],
"customerNumber"
],
"totalPaid" -> "Descending"
], 5]この記号的なEntityフレームワークのクエリを評価すると,それ自身になり,不活性な記号式のままである.それでも,このクエリが表す実体クラスについてのすべての実体特性を求める(これにはデータベースの呼出しは必要ない)等,便利なことができる.
EntityProperties[query]EntityValueを使って実際にクエリを実行する.
EntityValueを使って次のクエリを実行することができる:
EntityValue[query, { "customerName", "totalPaid"}, "PropertyAssociation"]//Datasetクエリ自体は不活性なWolfram言語式であり,手動あるいはプログラムで小さい構成要素から構築することができるため,Entityフレームワークの記号的で不活性な性質により,プログラムによるクエリ構築の方法が開かれる.
FilteredEntityClassを使ったフィルタリング
最も広く必要とされる操作の一つに,ある基準に従ってデータベースのデータをフィルタリングするというものがある.EntityフレームワークではこのためにFilteredEntityClassが使われる.フィルタリングの基準はEntityFunctionを使って表され,第2引数としてFilteredEntityClassに渡される.
次の例はFilteredEntityClassの一般的な使用法を示す.
EntityValue[
FilteredEntityClass[
"employees",
EntityFunction[e, e["jobTitle"] ≠ "Sales Rep"]
],
{"firstName", "lastName", "jobTitle"},
"PropertyAssociation"
] // DatasetEntityValue[
FilteredEntityClass[
"employees",
EntityFunction[
e,
StringMatchQ[e["email"], Repeated[_, {1, 5}] ~~ "@" ~~ __]
]
],
{"firstName", "lastName", "email"}
] // DatasetEntityValue[
FilteredEntityClass[
"employees",
EntityFunction[e, StringMatchQ[e["firstName"], ("M" | "P" | "D") ~~ __]]
],
{"firstName", "lastName"}
]EntityValue[
FilteredEntityClass[
"employees",
EntityFunction[
e,
MemberQ[{"M", "P", "D"}, StringTake[e["firstName"], 1]]
]
],
{"firstName", "lastName"}
]SortedEntityClassを使った並べ替え
ある基準に従って実体(データベース表の行)を並べ替えることも,広く使われている操作の一つである.データベース側では,これはSQLのORDER BY句を使って行われる.Entityフレームワーク側ではSortedEntityClassを使うことができる.
最も簡単な例では,既存の単一の実体の特性値(データベースの列)について昇順で並べ替えるというものがある.この場合,文字列フィールドの名前は第2引数としてSortedEntityClassに渡される.
EntityValue[
SortedEntityClass["employees", "officeCode"], {"employeeNumber", "lastName", "officeCode"}
]EntityValue[
SortedEntityClass["employees", "officeCode" -> "Descending"], {"employeeNumber", "lastName", "officeCode"}
]EntityValue[
SortedEntityClass[
"employees",
EntityFunction[e, StringLength[e["firstName"]]]
],
{"employeeNumber", "firstName", "lastName"}
]複数の特性で並べ替えることもできる.この場合,1番目の特性の値が同じ場合には2番目の特性の値が使われる.同様に,次は3番目の特性という風に並べ替える.実体の(サブ)グループ内の順番を制御するために,それぞれの並べ替え特性に別々に"Ascending"または"Descending"という修飾子を追加することができる.
EntityValue[
SortedEntityClass[
"products",
{"productLine", "quantityInStock" -> "Descending"}
],
{"productLine", "quantityInStock", "productName"},
"PropertyAssociation"
] // Dataset第3引数を使うと,SortedEntityClassによって返される結果の数を制限することができる.
EntityValue[
SortedEntityClass[
"employees",
EntityFunction[e, StringLength[e["firstName"]]],
7
],
{"employeeNumber", "firstName", "lastName"}
]SampledEntityClassを使ったサブセット化
実体クラスから一定数の実体だけを選ぶ(データベース用語では,表あるいは仮想表から行のあるサブセットだけを選ぶ)と便利な場合がある.Entityフレームワークでこれを行うためには,SampledEntityClassを使う.
EntityValue[
SampledEntityClass["payments", 10],
{"customerNumber", "amount"},
"PropertyAssociation"
] // DatasetEntityValue[
SampledEntityClass["payments", {11, 20}],
{"customerNumber", "amount"},
"PropertyAssociation"
] // Dataset結果の順序は,SampledEntityClassがSortedEntityClassに適用されたときにのみ保証される.しかし,少数のサンプルだけをまず使って,大きいデータセットの様子を見たり,クエリのプロトタイプを作成したりしたい場合等でも非常に便利なことがある.
EntityValue[
SampledEntityClass[
SortedEntityClass["products", "buyPrice" -> "Descending"],
5
],
{"productName", "buyPrice"},
"Dataset"
]この場合,サブセット化の前にデータセットが並べ替えられているため,順序が保証される.これと同じ結果はSortedEntityClassに3つの引数を与えるとより経済的に得ることができる:
EntityValue[
SortedEntityClass["products", "buyPrice" -> "Descending", 5],
{"productName", "buyPrice"},
"Dataset"
]ExtendedEntityClassを使った新規特性の導入
指定された型/実体クラスで利用可能な実体特性の集合に,新規の計算された特性を加えることができる.その結果は新しい実体クラスになる.新しく加えられた特性は,EntityValueの中,さらにそのクエリの外側等,もとの特性が使えるところならどこでも使うことができる.このような拡張された新規の実体クラスを生成する構造はExtendedEntityClassである.
EntityValue[
ExtendedEntityClass[
"employees", "fullName" -> EntityFunction[e, e["firstName"] <> " " <> e["lastName"]]
],
{"employeeNumber", "fullName"}
]EntityValue[
ExtendedEntityClass[
"employees", {
"managerFirstName" -> EntityFunction[e, e["employees-reportsTo"]["firstName"]],
"managerLastName" -> EntityFunction[e, e["employees-reportsTo"]["lastName"]]
}
],
{
"employeeNumber", "firstName", "lastName", "managerFirstName", "managerLastName"
},
"PropertyAssociation"
]//DatasetEntityValue[
ExtendedEntityClass[
"customers",
"adjustedCreditLimit" -> EntityFunction[
c,
If[c["creditLimit"] ≥ 100000,
c["creditLimit"] + 15000,
c["creditLimit"]
]
]
],
{"customerName", "creditLimit", "adjustedCreditLimit"}, "PropertyAssociation"
]//DatasetAggregatedEntityClassを使った集約
集約は非常に一般的に必要とされる操作である.これは値を計算するために使われ,1つ以上の実体(データベースの表の行)上で集約される. 集約の一般的な例として,ある範囲の実体上で,ある特性またはより複雑な式の合計,平均,最小値,最大値を計算するというものがある.Entityフレームワーク内では,週やうを実行するのに使われる構造はAggregatedEntityClassである.
その名の通り,これを適用した結果は新しい実態クラスになる.集約がもとの実体クラス全体で行われたら,結果の実体クラスは単一の実体しか含まず,特性として計算された集約された特性を持つ.まず特性の一定の集合の値に基づいて実体をグループ化し,各グループに対して集約を行うこともできる.この場合,新しい実体クラスにはグループと同数の,新しく集約された実体が含まれる.
現在,データベースを使った実体ストアについて,Entityフレームワークによってサポートされている中心的な集約関数の制約された小さい集合がある.これにはTotal,Length,Min,Max,Mean,Variance,StandardDeviationが含まれている.しかし,標準の算術関数や他の特性のようなクエリの構成要素を使って,これらの中核的な操作を組み合せ,より複雑な式を計算することができる.
集約の場合のEntityFunctionは,そのクラスの個々の実体ではなく,集約されている実体クラス全体に結合している.
次は,単一の集約値を計算するAggregatedEntityClassの簡単な使用例である.
EntityValue[
AggregatedEntityClass[
"orderdetails", "totalOrdered" -> EntityFunction[o, Total[o["quantityOrdered"]]]
],
"totalOrdered"
]この場合のEntityFunctionのセマンティックを理解するために,Wolfram言語を使って同じものをトップレベルで計算すると便利である.
次のコードは集約のためのWolfram言語を使って,同じ数量を計算する.ここでFunctionは類似性をより明確にするために使われる:
Function[o, Total[o["quantityOrdered"]]][EntityClass["orderdetails", {}]]特性の名前が集約されるため,結果となる集約特性に同じ名前を使う,より簡単なシンタックスを使うことができる.これは.それぞれの場合に集約されている単一の特性がある簡単な場合に使えるが,1つ以上の集約を行うことができる:
EntityValue[
AggregatedEntityClass["orderdetails", "quantityOrdered" -> Total],
"quantityOrdered"
]EntityValue["orderdetails", "quantityOrdered", Total]最後の例に関する重要なコメントは,現行バージョンのEntityフレームワークでは,トップレベルのクエリに使われるEntityValueの3引数の形式はデータベース側ではなくWolfram言語側で集約を実行するということである(このシンタックスがサブクエリで使われるときには,起らない).一方,AggregatedEntityClassを明示的に使って実行される集約はすべて,常にデータベース側で実行される.
次のクエリは,すべての注文で注文されたアイテムを1アイテムの注文に分割した("orderdetails" 表が提供するもの)ときの,アイテムの最小,最大,平均の数量の3つの集約特性を持つ集約実体を定義する:
EntityValue[
AggregatedEntityClass[
"orderdetails",
{
"maxOrdered" -> EntityFunction[o, Max[o["quantityOrdered"]]],
"minOrdered" -> EntityFunction[o, Min[o["quantityOrdered"]]],
"avgOrdered" -> EntityFunction[o, Mean[o["quantityOrdered"]]]
}
],
{"maxOrdered", "minOrdered", "avgOrdered"},
"PropertyAssociation"
]EntityValue[
AggregatedEntityClass[
"products",
"avgBuyPrice" -> EntityFunction[p, Mean[p["buyPrice"]]],
"productLine"
],
"EntityPropertyAssociation"
]EntityValue[
AggregatedEntityClass[
"employees",
"employeeCount" -> EntityFunction[e, Length[e["employeeNumber"]]],
"officeCode"
],
"PropertyAssociation"
]EntityValue[
SortedEntityClass[
AggregatedEntityClass[
"customers", "customerCount" -> EntityFunction[c, Length[c["customerNumber"]]],
{"city", "country"}
],
"customerCount" -> "Descending"
],
"PropertyAssociation"
]//Short次のクエリは,すべての注文に対して支払われた合計金額を計算する(この場合意味的には,o["priceEach"]とo["quantityOrdered"]の両方がデータベースの列を表している.これらは同じ表(つまり同じ長さ)であり,ベクトル化された要素全体の操作として理解されなければならないので,その乗算は可能である.この乗算の結果は意味的に別の列であり,これはTotalに渡される):
EntityValue[
AggregatedEntityClass[
"orderdetails",
"totalAmount" -> EntityFunction[
o,
Total[o["priceEach"] * o["quantityOrdered"]]
]
],
"totalAmount"
]次のクエリは,注文されたアイテム1個に対する平均金額を2つの方法で計算する.明示的に計算する方法と組込み関数(Mean)を使った方法である:
EntityValue[
AggregatedEntityClass[
"orderdetails",
{
"avgComputed" -> EntityFunction[
x,
Total[x["quantityOrdered"] * x["priceEach"]] / Length[x["orderLineNumber"]]
],
"avg" -> EntityFunction[
x,
Mean[x["quantityOrdered"] * x["priceEach"]]
]
}
],
{"avgComputed", "avg"}
]CombinedEntityClassを使った実体クラスの統合
リレーショナルデータベースでは,データは通常,外部キー制約を介して相互に接続されたいくつかの表に保存されている.正規化されているデータが通常分割され複数(多数にもなり得る)の関係した表に保存されることを考えると,書きたい現実世界のクエリに対する単一の表だけを操作することがまれにある.
SQLクエリの複数の表からのデータを使う最も一般的な方法は,SQLのJOIN演算を使う方法である.EntityフレームワークでSQLのJOINに相当するのはCombinedEntityClass構造である.これは2つの実体クラス/タイプ,どのように統合されるかの指定を取り,オプションでJOINのタイプを取る.結果はもとの実体クラスの両方からの特性を含む新しい実体を持つ,新しい実体クラスになる.
次の例はCombinedEntityClassの一般的な使用法を示す.
次のクエリは"officeCode"を介して実体クラス"employees"と"offices"を組み合せ,特性"employeeNumber","firstName","lastName","city","country"を抽出する.最初の3つの特性は"employees"型に属し,後の2つは"offices"型に属す:
EntityValue[
CombinedEntityClass["employees", "offices", "officeCode"],
{"employeeNumber", "firstName", "lastName", "city", "country"}
]これは,新しいクラスの特性の完全リストを見ると(実際にはそのInputFormを見た方がより有益である)より明確になる:
EntityProperties[CombinedEntityClass["employees", "offices", "officeCode"]]//InputForm新しいクラスの実体は新しい型であり,EntityList等を使って見ることができる:
EntityList[CombinedEntityClass["employees", "offices", "officeCode"]]CombinedEntityClassを適用した結果の新しい実体クラスは,他の実体クラスのように,より複雑なクエリにさらに使うことができる.
EntityValue[
FilteredEntityClass[
CombinedEntityClass["employees", "offices", "officeCode"],
EntityFunction[eo, MemberQ[{"UK", "France"}, eo["country"]]]
],
{"employeeNumber", "firstName", "lastName", "country"},
"PropertyAssociation"
]//Dataset次は,クレジットカード限度額が$120,000を超える顧客について,顧客番号,名前,支払額,支払日を返す(データベース側では日付はUnix時間で保存されているため,個々でWolfram言語の後処理ステップが必要となる):
EntityValue[
FilteredEntityClass[
CombinedEntityClass["payments", "customers", "customerNumber"],
EntityFunction[pc, pc["creditLimit"] > 120000]
],
{
EntityProperty["customers", "customerNumber"],
"customerName",
"amount",
"paymentDate"
}
]//MapAt[FromUnixTime, #, {All, -1}]&//Dataset最後の例では,"customers" と"payments"の両方の型にこの名前の特性があり,どちらの特性が要求されているかという曖昧さを厳密に解消しなければならないため,EntityValueの特性のリストで明示的にEntityProperty["customers","customerNumber"]を示す必要があった.
EntityValue[
FilteredEntityClass[
CombinedEntityClass[
"customers",
"employees",
"salesRepEmployeeNumber" -> "employeeNumber"
],
EntityFunction[ce, ce["country"] === "France"]
],
{"customerName", "firstName", "lastName"},
"PropertyAssociation"
]//Dataset型をそれ自身と組み合せることは可能であり,それが望ましいこともよくある.リレーショナルデータベースの操作では,このような場合は自己結合に相当する.これでは特性名の曖昧さを解消するためにより注意が必要である.組み合せられる同じ型の少なくとも一つに(文字列)エイリアスを導入しなければならない.この例を以下に示す.
EntityValue[
CombinedEntityClass[
"employees",
"manager" -> "employees",
"reportsTo" -> "employeeNumber"
],
{
"firstName",
"lastName",
EntityProperty["manager", "firstName"],
EntityProperty["manager", "lastName"]
}
]//Dataset組み合せられようとしている2つの実体クラスからの2つの実体の組合せが結果の実体クラスに含まれるべきかどうかを決定するためにテストされる条件は,両方の実体の特性(の組合せ)の間の単純な等価性よりも複雑である.このような場合,各引数が,組み合せられようとしている該当する実体クラスの実体に結合した,2つの引数を持つEntityFunctionを使うことができる.このようなEntityFunctionの本体はブール値を返さなければならないが,そうでないと任意に複雑な(コンパイル可能な)式になる可能性がある.
次は,CombinedEntityClassの使い方のさまざまな例を示すので,おもしろいのではなかろうか.このクエリは2つ以上の営業所が存在する国すべてを求める方法の一つを表す.これは"offices"型をそれ自体と組み合せ,CombinedEntityClassの条件として,組み合されようとしている2つの実体の国が同じで営業所コードが異なることをチェックする述語を使うというものである.2つの"office"型の実体のこのような組合せは2つ以上の営業所を持つ国に相当する.従って,これは結果となる組み合わされた実体の国名を抽出し,可能な重複を削除することになる:
EntityValue[
CombinedEntityClass[
"offices",
"other" -> "offices",
EntityFunction[{o, other}, o["country"] == other["country"] && o["officeCode"] != other["officeCode"]]
],
EntityProperty["offices", "country"],
DeleteDuplicates
]直前の例では,自己結合の例で行ったように,特性の曖昧さを解消するために,組み合せられた"offices"型の一つに接頭辞を付ける必要もある.最後にEntityValueの第3引数としてDeleteDuplicatesが使われ,結果から重複したものが除かれる(前述の通り,このような場合EntityValueの第3引数は現在はデータベース側ではなく,Wolfram言語側で実行される):
サブクエリ
サブクエリは,さらに複雑なクエリにおける構成要素として使われる.サブクエリは,その結果がスカラーであるときに最も簡単な形式を取る.このセクションでは,サブクエリの例をいくつか使って,一般的な使用法を説明する.
EntityValue["products", "MSRP", Max]Entity[AggregatedEntityClass["products", "MSRP" -> Max], {}]["MSRP"]
Entity[
AggregatedEntityClass[
"products",
"maxMSRP" -> EntityFunction[p, Max[p["MSRP"]]]
],
{}
]["maxMSRP"]しかしこのようなクエリが大きいクエリのサブクエリとして使われる場合,EntityValueの3引数の短い形式を含め,クエリは常にデータベース側で実行される.
EntityValue[
FilteredEntityClass[
"products",
EntityFunction[
p,
p["MSRP"] ≥ 0.9 * EntityValue["products", "MSRP", Max]
]
],
{"productName", "MSRP"},
"PropertyAssociation"
] // Dataset直前の例のようなサブクエリは,クエリの外側の実体(表の行)を参照しないので,非相関サブクエリと呼ばれる.そのような参照を含む(そのため修正なしでそれ自身では実行できない),いわゆる相関サブクエリを構築することもできる.
EntityValue[
FilteredEntityClass[
"employees",
EntityFunction[e, e["officeCode"] == "4"]
],
"employeeNumber",
Length
]Entity["offices", "4"][
EntityFunction[
o,
EntityValue[
FilteredEntityClass[
"employees",
EntityFunction[e, e["officeCode"] == o["officeCode"]]
],
"employeeNumber",
Length
]
]
]EntityValue[
SortedEntityClass[
ExtendedEntityClass[
"offices",
"employeeCount" -> EntityFunction[
o,
EntityValue[
FilteredEntityClass[
"employees",
EntityFunction[
e,
e["officeCode"] == o["officeCode"]
]
],
"employeeNumber",
Length
]
]
],
"employeeCount" -> "Descending"
],
{"officeCode", "phone", "employeeCount"},
"PropertyAssociation"
]//DatasetEntityValue[
SortedEntityClass[
AggregatedEntityClass[
CombinedEntityClass["offices", "employees", "officeCode"],
"employeeCount" -> EntityFunction[
ent,
Length[ent["employeeNumber"]]
],
{EntityProperty["offices", "officeCode"], "phone"}
],
"employeeCount" -> "Descending"
],
{EntityProperty["offices", "officeCode"], "phone", "employeeCount"}
]//Datasetサブクエリは強力なツールであるが,すぐに乱用されることがある.場合によって,与えられた問題を解くのにサブクエリが最適な方法であることもそうでないこともあり得る.深く相関したサブクエリは結果的に劣った性能を示すこともあるので,この強力なツールは注意して使わなければならない.
MemberQ
SQLのIN演算子を表すために,Entityフレームワークのクエリではデータベースを使った実体ストアでのMemberQ述語が使われる.これは,最も簡単な形式で,値の明示的なリストにおける値のメンバーシップをテストするために使われる.
EntityValue[
"offices",
{
"officeCode",
EntityFunction[o, MemberQ[{"2", "4", "7"}, o["officeCode"]]]
}
]サブクエリと一緒にMemberQを使うこともできる.この場合,MemberQの第1引数はリテラルリストではなく,サブクエリの結果である. サブクエリはスカラーではなく列を返し,class["property"]または同等のEntityValue[class,"property"]と通常書くことができる.ここでclassは,(内側のクエリによって登録あるいは定義された)ある実体クラスである.
EntityValue[
FilteredEntityClass[
"customers",
EntityFunction[
c,
MemberQ[
FilteredEntityClass[
"offices",
EntityFunction[o, o["country"] == "USA"]
]["city"]
,
c["city"]
]
]
],
{"customerNumber", "customerName", "city"},
"PropertyAssociation"
]//DatasetDeleteDuplicatesとSQL DISTINCT
場合によっては,選ばれた値,あるいは異なる値のグループだけを保存する必要がある.SQLでは,この目的で特別なキーワードDISTINCTが使われる.Entityフレームワークでは,EntityValueの第3引数としてDeleteDuplicatesを使うことで同様の効果を得ることができる.
EntityValue["orders", "status", DeleteDuplicates]先に述べたように,前の例ではDeleteDuplicatesは現在Wolfram言語側で実行されている.しかし,前のようなクエリがサブクエリとして使われると,データベース側で重複の削除が行われる.
EntityValue[
AggregatedEntityClass[
"orderdetails", {
"exactPrices" -> EntityFunction[
o,
Length @ DeleteDuplicates @ o["priceEach"]
],
"roundedPrices" -> EntityFunction[
o,
Length @ DeleteDuplicates @ Round[o["quantityOrdered"]]
],
"avgExactPrice" -> EntityFunction[
o,
Mean @ DeleteDuplicates @ o["priceEach"]
],
"avgRoundedPrice" -> EntityFunction[
o,
Mean @ DeleteDuplicates @ Round[o["priceEach"]]
]
}],
{"exactPrices", "roundedPrices", "avgExactPrice", "avgRoundedPrice"},
"PropertyAssociation"]関係
既存の表の列に対応する特性に加え,他の表に関係する表のEntityフレームワークによって新しい特性が生成される.これらは,クエリの中の複数の関係する実体型(データベース表)からデータを使うより高いレベル(明示的な結合あるいはサブクエリについて)の方法を提供するメカニズムを構成する.
このような特性は以前すでに考えたが,それは最も基本的なレベルであった.
中核的プリミティブで行えない関係で行えることは何もないことを理解することは重要である.しかし多くの場合,関係を使うことで,構築および理解にずっと少ない労力しか要求しないかなり簡潔なクエリが作成できる.
EntityProperties["employees"]emp = Part[EntityList["employees"], 10]emp[EntityProperty["employees", "customers"]]
emp[EntityProperty["employees", "offices"]]
emp[EntityProperty["employees", "employees-reportsTo"]]
emp[EntityProperty["employees", "employees-reverse"]]emp[EntityProperty["employees", "offices"]]["employees"]EntityList @ FilteredEntityClass[
emp[EntityProperty["employees", "offices"]]["employees"],
EntityFunction[e, e["reportsTo"] == emp["reportsTo"]]
]EntityValue[
"offices",
{"officeCode", EntityFunction[o, EntityValue[o["employees"], "employeeNumber", Length]]}
]//Datasetこの特性がクエリの中でさらに必要な場合,ExtendedEntityClassを使って与えられた実体クラスをその特性で拡張することができる:
EntityValue[
ExtendedEntityClass[
"offices",
"employeeCount" -> EntityFunction[
o,
EntityValue[o["employees"], "employeeNumber", Length]
]
],
{"officeCode", "employeeCount"},
"PropertyAssociation"
]//Dataset次のクエリは,各注文に,注文した顧客名とその顧客を担当した従業員の姓名の2つの新しい特性を加えることによって,関係の使い方を示すものである.関係する型(データベース表)に属すデータを抽出するために,関係がどのように続いているかに注目のこと:
EntityValue[
ExtendedEntityClass[
"orders",
{
"customer" -> EntityFunction[o, o["customers"]["customerName"]],
"employee" -> EntityFunction[
o,
StringJoin[
o["customers"]["employees"]["firstName"],
" ",
o["customers"]["employees"]["lastName"]]
]
}
],
{"orderNumber", "customer", "employee"},
"PropertyAssociation"
]//Dataset次の例は,各従業員について,顧客の合計数およびクレジットカードのローンの上限が高い($50000を超える)顧客の合計数を計算し,これらの値を新しい特性として加える.この場合e["customers"]は実体クラス値の関係であり,例えばFilteredEntityClass等で使うことができる.
EntityValue[
ExtendedEntityClass[
"employees",
{
"customerCount" -> EntityFunction[
e,
EntityValue[e["customers"], "customerNumber", Length]
],
"highCreditLimitCustomerCount" -> EntityFunction[
e,
EntityValue[
FilteredEntityClass[
e["customers"],
EntityFunction[c, c["creditLimit"] > 50000]
],
"customerNumber",
Length
]
]
}
],
{
"employeeNumber", "firstName", "lastName", "customerCount", "highCreditLimitCustomerCount"
},
"PropertyAssociation"
]//Dataset関係を使ってもっと複雑なクエリを構築する例は,このチュートリアルの最後のセクションで考える.
はじめに
このセクションの目的は,サポートされるさまざまな構造のために,フレームワークによって通常作成されるSQLコードの種類についての基本的な概念を提供することである.ここで提供される,生成されたSQLはほとんど例示のためのものである.
このせくしょんで提示するSQLクエリの多くは,(SQLiteバックエンドの)Entityフレームワークのクエリコンパイラの最新版によって生成されたSQLコードに相当するが,生成されたSQLは常に厳密な形式を取ると想定してはならない.形式はバージョンによって異なる可能性がある.Entityフレームワークの視点からいうと,生成されたSQLの厳密形式は,最終結果が正しく,妥当な効率を持っている限り,内部的な実装の詳細である.従って,このセクションで示されている生成されたSQLコードの詳細には,絶対に依存してはならない.
EntityValueとEntityListの呼出し
まず,基本的なEntityValueの呼出しから始める:
EntityValue["offices", {"officeCode", "city", "state", "country"}]SELECT officeCode, city, state, country
FROM offices
EntityValue[
"offices",
EntityFunction[o, StringStartsQ[o["state"], "C"]]
]SELECT REGEXP(state, '^C', 0) AS synthetic_prop_9
FROM offices
EntityList["offices"]SELECT officeCode
FROM offices
計算された特性とEntityFunction式
計算された特性はEntityFunctionを使って定義される.これらは通常SQL式にコンパイルされる.以下がその例である.
EntityFunction[od, od["priceEach"] * od["quantityOrdered"]]orderdetails.priceEach * orderdetails.quantityOrdered
EntityFunction[o, MissingQ[o["state"]]]offices.state IS NULL
EntityFunction[
o,
StringContainsQ[o["city"], "a"] || StringContainsQ[o["city"], "o"] && Not[MissingQ[o["state"]]]
]REGEXP(offices.city, 'a', 0) OR REGEXP(offices.city, 'o', 0) AND NOT (offices.state IS NULL)
EntityFunction[p, (p["MSRP"] - p["buyPrice"]) / (p["MSRP"])](products.MSRP - products.buyPrice) / products.MSRP
EntityFunction[
p,
Sqrt[(p["MSRP"] ^ 2 - p["buyPrice"] ^ 2) / (p["MSRP"] ^ 2 + p["buyPrice"] ^ 2)]
]power(CAST((power(CAST(products.MSRP AS REAL), 2) - power(CAST(products.buyPrice AS REAL), 2)) / (power(CAST(products.MSRP AS REAL), 2) + power(CAST(products.buyPrice" AS REAL), 2)) AS REAL), 0.5)
EntityFunction[o, o["quantityOrdered"] > 30 && o["priceEach"] ≥ 200]orderdetails.quantityOrdered > 30 AND orderdetails.priceEach >= 200
EntityFunction[ord, Mod[ord["orderNumber"], 100] == 0]orderdetails.orderNumber % 100 = 0
EntityFunction[o, Total[o["quantityOrdered"]]]sum(orderdetails.quantityOrdered)
EntityFunction[
x,
Total[x["quantityOrdered"] * x["priceEach"]] / Length[x["orderLineNumber"]]
]sum(orderdetails.quantityOrdered * orderdetails.priceEach) / CAST(count(orderdetails.orderLineNumber) AS REAL)
EntityFunction[
e,
MemberQ[{"M", "P", "D"}, StringTake[e["firstName"], 1]]
]CASE
WHEN (1 > length(employees.firstName)) THEN NULL
WHEN 1 THEN substr(employees.firstName, 1, 1)
END IN ('M', 'P', 'D')
中核的なクエリ構築プリミティブ
FilteredEntityClass
EntityValue[
FilteredEntityClass[
"employees",
EntityFunction[e, e["jobTitle"] ≠ "Sales Rep"]
],
{"firstName", "lastName", "jobTitle"}
]SELECT employees.firstName, employees.lastName, employees.jobTitle
FROM employees
WHERE employees.jobTitle != 'Sales Rep'
例えば,次のクエリは3つのネストされたFilteredEntityClass構造を使い,アメリカカリフォルニア州のサンフランシスコ,ロサンゼルス,サンノゼのいずれかの都市にいる顧客すべてを求める:
EntityValue[
FilteredEntityClass[
FilteredEntityClass[
FilteredEntityClass[
"customers",
EntityFunction[c, c["country"] == "USA"]
],
EntityFunction[c, c["state"] == "CA"]
],
EntityFunction[c,
MemberQ[{"San Francisco", "Los Angeles", "San Jose" }, c["city"]]
]
],
{"customerNumber", "customerName", "creditLimit", "city"}
]SELECT
customers.customerNumber,
customers.customerName,
customers.creditLimit,
customers.city
FROM customers
WHERE customers.country = 'USA' AND customers.state = 'CA' AND customers.city IN ('San Francisco', 'Los Angeles', 'San Jose')
SortedEntityClass
EntityValue[
SortedEntityClass["employees", "officeCode" -> "Descending"], {"employeeNumber", "lastName", "officeCode"}
]SELECT employees.employeeNumber, employees.lastName, employees.officeCode
FROM employees
ORDER BY employees.officeCode DESC
SQLとEntityフレームワークのソート機能の大きな違いの一つは,すべてのSQLバックエンドが直接ORDER BY句の式をサポートするわけではないのに対し,SortedEntityClassでは何でソートするかについての特性は簡単な実体特性でもEntityFunction式でもよいという点である.
EntityValue[
SortedEntityClass[
"employees",
EntityFunction[e, StringLength[e["firstName"]]]
],
{"employeeNumber", "firstName", "lastName"}
]SELECT "T308"."employeeNumber", "T308"."firstName", "T308"."lastName"
FROM (
SELECT
"employees_T306"."employeeNumber" AS "employeeNumber",
"employees_T306"."firstName" AS "firstName",
"employees_T306"."lastName" AS "lastName",
length("employees_T306"."firstName") AS synthetic_prop_17
FROM employees AS "employees_T306"
) AS "T308"
ORDER BY "T308".synthetic_prop_17
SampledEntityClass
この構造に対してSQLで最も直接的に相当するのは,LIMITおよびOFFSETのSQLキーワードである.しかし,サブセットについての実際のストラテジーと内部実装は.他のストラテジーの方が同じ結果を得るのにより効率的なこともあるため,異なる場合がある.
EntityValue[
SampledEntityClass["payments", {11, 20}],
{"customerNumber", "amount"},
"PropertyAssociation"
] // DatasetSELECT
"payments_T316"."customerNumber" AS "customerNumber",
"payments_T316".amount AS amount
FROM payments AS "payments_T316"
LIMIT 10 OFFSET 10
ExtendedEntityClass
EntityValue[
ExtendedEntityClass[
"employees", "fullName" -> EntityFunction[e, e["firstName"] <> " " <> e["lastName"]]
],
{"employeeNumber", "fullName"}
]SELECT
"employees_T328"."employeeNumber" AS "employeeNumber",
("employees_T328"."firstName" || ' ') || "employees_T328"."lastName" AS "fullName"
FROM employees AS "employees_T328"
これらは複雑になり得る上,関係を使う以下のクエリのような相関サブクエリ等を含むこともある:
EntityValue[
ExtendedEntityClass[
"employees", {
"managerFirstName" -> EntityFunction[e, e["employees-reportsTo"]["firstName"]],
"managerLastName" -> EntityFunction[e, e["employees-reportsTo"]["lastName"]]
}
],
{
"employeeNumber", "firstName", "lastName", "managerFirstName", "managerLastName"
},
"PropertyAssociation"
]//DatasetSELECT
"employees_T352"."employeeNumber" AS "employeeNumber",
"employees_T352"."firstName" AS "firstName",
"employees_T352"."lastName" AS "lastName",
(
SELECT "employees_T355"."firstName" AS "firstName_1"
FROM employees AS "employees_T355"
WHERE "employees_T355"."employeeNumber" = "employees_T352"."reportsTo"
) AS "managerFirstName",
(
SELECT "employees_T358"."lastName" AS "lastName_1"
FROM employees AS "employees_T358"
WHERE "employees_T358"."employeeNumber" = "employees_T352"."reportsTo"
) AS "managerLastName"
FROM employees AS "employees_T352"
AggregatedEntityClass
AggregatedEntityClassは第3引数なしで使われると,第1引数(実体クラス)全体で実行される集約を表し,SQLの集約クエリに相当する.この場合,AggregatedEntityClass[...]に相当するSQL側の特殊なキーワードはないが,SELECTリストのフィールドはすべてSQLの集約関数を使わなければならない.
EntityValue[
AggregatedEntityClass[
"orderdetails",
{
"maxOrdered" -> EntityFunction[o, Max[o["quantityOrdered"]]],
"minOrdered" -> EntityFunction[o, Min[o["quantityOrdered"]]],
"avgOrdered" -> EntityFunction[o, Mean[o["quantityOrdered"]]]
}
],
{"maxOrdered", "minOrdered", "avgOrdered"},
"PropertyAssociation"
]SELECT
max("orderdetails_T403"."quantityOrdered") AS "maxOrdered",
min("orderdetails_T403"."quantityOrdered") AS "minOrdered",
avg("orderdetails_T403"."quantityOrdered") AS "avgOrdered"
FROM orderdetails AS "orderdetails_T403"
agg = AggregatedEntityClass[
"customers", "customerCount" -> EntityFunction[c, Length[c["customerNumber"]]],
{"city", "country"}
];
EntityValue[agg, "PropertyAssociation"]//ShortSELECT
"customers_T434".city AS city,
"customers_T434".country AS country,
count("customers_T434"."customerNumber") AS "customerCount"
FROM customers AS "customers_T434"
GROUP BY "customers_T434".city, "customers_T434".country
EntityValue[
SortedEntityClass[agg, "customerCount" -> "Descending"], "PropertyAssociation"
]//ShortSELECT
"T497".city,
"T497".country,
"T497"."customerCount"
FROM (
SELECT
"customers_T495".city AS city,
"customers_T495".country AS country,
count("customers_T495"."customerNumber") AS "customerCount"
FROM customers AS "customers_T495"
GROUP BY "customers_T495".city, "customers_T495".country
) AS "T497"
ORDER BY "T497"."customerCount" DESC
CombinedEntityClass
EntityValue[
CombinedEntityClass["employees", "offices", "officeCode"],
{"employeeNumber", "firstName", "lastName", "city", "country"}
]SELECT
"employees_T529"."employeeNumber" AS "employeeNumber",
"employees_T529"."firstName" AS "firstName",
"employees_T529"."lastName" AS "lastName",
"T534".city,
"T534".country
FROM employees AS "employees_T529"
JOIN (
SELECT
"offices_T532".city AS city,
"offices_T532".country AS country,
"offices_T532"."officeCode" AS "officeCode"
FROM offices AS "offices_T532"
) AS "T534"
ON "employees_T529"."officeCode" = "T534"."officeCode"
EntityValue[
CombinedEntityClass[
"offices",
"other" -> "offices",
EntityFunction[{o, other}, o["country"] == other["country"] && o["officeCode"] != other["officeCode"]]
],
EntityProperty["offices", "country"],
DeleteDuplicates
]これは以下のようなSQLに変換される(現在トップレベルのEntityValueのDeleteDuplicatesがWolfram言語側で実行されているため,SQLクエリにはDISTINCTキーワードはない):
SELECT "offices_T571".country AS country
FROM offices AS "offices_T571"
JOIN (
SELECT
"offices_T574".country AS country_1,
"offices_T574"."officeCode" AS "officeCode"
FROM offices AS "offices_T574"
) AS "T576"
ON "offices_T571".country = "T576".country_1 AND "offices_T571"."officeCode" != "T576"."officeCode"
サブクエリ
このチュートリアルでは,サブクエリは通常大きなクエリの一部を意味するもので,EntityValueを使って表すことができる. SQL側では,ほとんどの場合これは一つのフィールドを持つ内部のSELECT文に相当し,通常スカラーを返す(単独の行があるため,あるいは集約が実行されているため)が,列を返すこともある(IN句で使われるもので,Entityフレームワーク側のMemberQの使用に相当する).
EntityValue[
FilteredEntityClass[
"products",
EntityFunction[
p,
p["MSRP"] ≥ 0.9 * EntityValue["products", "MSRP", Max]
]
],
{"productName", "MSRP"},
"PropertyAssociation"
] // DatasetSELECT
"products_T583"."productName" AS "productName",
"products_T583"."MSRP" AS "MSRP"
FROM products AS "products_T583"
WHERE "products_T583"."MSRP" >= 0.9 * (
SELECT max("products_T586"."MSRP")
FROM products AS "products_T586"
)
EntityValue[
SortedEntityClass[
ExtendedEntityClass[
"products",
"closelyPricedProductsCount" ->
EntityFunction[
p,
Entity[
AggregatedEntityClass[
FilteredEntityClass[
"products",
EntityFunction[pr, Abs[pr["MSRP"] - p["MSRP"]] ≤ 15]
],
"count" -> EntityFunction[pr, Length[pr["productCode"]]]
],
{}
]["count"]
]
],
{"closelyPricedProductsCount" -> "Descending", "MSRP" -> "Descending"}
],
{"productName", "MSRP", "closelyPricedProductsCount"}, "PropertyAssociation"]//Datasetここでは,製品のフィルタリングがフィルタリング/集約のサブクエリから参照されなければならない現在の製品の価格に依存しているため, "closelyPricedProductsCount"で拡張された特性を定義するEntityFunction内部のサブクエリは,相関サブクエリである.
これは相関サブクエリを含むクエリとなる.相関サブクエリは内部クエリのSELECTリストの中にあり,"closelyPricedProductsCount"によってエイリアスが付けられ,新しい拡張特性になる.
SELECT
"T637"."productName",
"T637"."MSRP",
"T637"."closelyPricedProductsCount"
FROM (
SELECT
(
SELECT "T640".count
FROM (
SELECT count("products_T638"."productCode") AS count
FROM products AS "products_T638"
WHERE abs("products_T638"."MSRP" - "products_T635"."MSRP") <= 15
) AS "T640"
) AS "closelyPricedProductsCount",
"products_T635"."MSRP" AS "MSRP",
"products_T635"."productName" AS "productName"
FROM products AS "products_T635"
) AS "T637"
ORDER BY "T637"."closelyPricedProductsCount" DESC, "T637"."MSRP" DESC
Entityフレームワークのクエリコンパイラによって実行されたサブクエリを含む,生成されたSQLコードの自動最適化(サブクエリをJOIN等に変換するよう試みるような)は現在のところない.SQLのサブクエリに類似した,Entityフレームワークの相関サブクエリの使用が暗示する性能に気を付けなければならない.
関係
関係は,明示的な結合を実行することなく,指定された(関係データベース)表に関係する実体クラス/型の特性のルックアップを実行する,高レベルな方法を提供する.その内部実装は,サブクエリおよび/または結合等の目的を達成するためにさまざまなツールを利用することができるが,これらはユーザからは見えない.
関係を使うクエリから生成することのできるSQLの型の例として,次のクエリを考える.これは,各営業所について,その営業所の1人の従業員が担当する顧客の最大数を計算するものである(これはこのチュートリアルの最後のセクションで再び考える):
EntityValue[
ExtendedEntityClass[
"offices",
"maxEmployeeCustomerCount" -> EntityFunction[
o,
EntityValue[
ExtendedEntityClass[
o["employees"],
"customerCount" -> EntityFunction[
e,
EntityValue[e["customers"], "customerNumber", Length]
]
],
"customerCount",
Max
]
]
],
{"officeCode", "maxEmployeeCustomerCount"},
"PropertyAssociation"
]//DatasetSELECT
"offices_T652"."officeCode" AS "officeCode",
(
SELECT "T662".synthetic_prop_20
FROM (
SELECT max("T657"."customerCount") AS synthetic_prop_20
FROM (
SELECT
(
SELECT "T660".synthetic_prop_21
FROM (
SELECT count("customers_T658"."customerNumber") AS synthetic_prop_21
FROM customers AS "customers_T658"
WHERE "employees_T655"."employeeNumber" = "customers_T658"."salesRepEmployeeNumber"
) AS "T660"
) AS "customerCount"
FROM employees AS "employees_T655"
WHERE "offices_T652"."officeCode" = "employees_T655"."officeCode"
) AS "T657"
) AS "T662"
) AS "maxEmployeeCustomerCount"
FROM offices AS "offices_T652"
はじめに
ここで重要なのは,EntityFunctionはHoldAllであるため,変数に保存されたクエリの一部をEntityFunctionの本体で使う必要がある場合,そのクエリの部分をEntityFunctionの本体に入れるためにWith(または同様の構造)を使わなければならないということである.
異なるクエリでクエリの部分を再利用する
いくつかの大きいクエリの中で不活性なクエリのブロックを再利用することは,実際によく使われることである.クエリは不活性なため,すぐに再利用できる.特に,変数にクエリの小さい部分を保存して,大きいクエリでこれらの変数を使うことができる.
totalPayments = AggregatedEntityClass[
"payments",
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
]EntityValue[
SampledEntityClass[
SortedEntityClass[
totalPayments,
"totalPaid" -> "Descending"
],
5],
{"customerNumber", "totalPaid"},
"PropertyAssociation"
] // DatasetEntityValue[
AggregatedEntityClass[
CombinedEntityClass["customers", totalPayments, "customerNumber"],
"customersTotalForEmployee" -> EntityFunction[c, Total[c["totalPaid"]]],
"salesRepEmployeeNumber"
],
{"salesRepEmployeeNumber", "customersTotalForEmployee"},
"PropertyAssociation"
] // Dataset同じクエリの中でクエリの部分を再利用する
1つのクエリや同一の大きいクエリの中で同じクエリを2回以上使う必要がある場合がある.これを行うために,SQLではWITHキーワードを使う.Entityフレームワークでも将来的にこの構造をネイティブでサポートするようになると思われるが,これは簡単にエミュレートできる.
totalOrderDetails =
AggregatedEntityClass[
"orderdetails",
"productOrderCount" -> EntityFunction[od, Total[od["quantityOrdered"]]],
"productCode"
]EntityValue[
FilteredEntityClass[
totalOrderDetails,
EntityFunction[o, o["productOrderCount"] > 1050]
],
{"productCode", "productOrderCount"},
"PropertyAssociation"
] // Datasetしかし,例えば最も多く注文されたアイテムをすべて求めるために使うこともできる.この場合,これはクエリの中に2回現れる.次のクエリではtotalOrderDetailsがEntityFunctionの本体に挿入されなければならないため,Withが使われることに注意する:
With[{totalOrderDetails = totalOrderDetails},
EntityValue[
FilteredEntityClass[
CombinedEntityClass["products", totalOrderDetails, "productCode"],
EntityFunction[po, po["productOrderCount"] == EntityValue[
totalOrderDetails,
"productOrderCount",
Max
]
]
],
{"productName", "productOrderCount"},
"PropertyAssociation"
]]プログラムによるクエリの生成
まずAggregatedEntityClassを作成する:
genAgg = AggregatedEntityClass[
"orderdetails",
Map[
ToString[#] -> EntityFunction[o, #[o["quantityOrdered"]]]&, {Min, Max, Length, Total, Mean, StandardDeviation, Variance}
]
]EntityValue[
genAgg, Map[ToString, {Min, Max, Length, Total, Mean, StandardDeviation, Variance}], "PropertyAssociation"
]記号的クエリ変換
例として,一般的なクエリの構成要素のための演算子形式を考える.現在のところ,さまざまな理由により,中核的なEntityフレームワーククエリの構成要素に対する演算子形式の直接のサポートはない.しかしユーザはこのスタイルでクエリを書くことを好むため,クエリのネストの量を減らすことによって,クエリをより読みやすくすることができる場合もある.
ClearAll[filter, sample, aggregate, combine, extend, sortby]
$translations = {
filter -> FilteredEntityClass, sample -> SampledEntityClass,
aggregate -> AggregatedEntityClass, combine -> CombinedEntityClass,
extend -> ExtendedEntityClass, sortby -> SortedEntityClass
};entityClassQ[_String ? EntityFramework`EntityTypeExistsQ] := True
entityClassQ[ (Alternatives @@ Keys[ $translations])[___]] := True
entityClassQ[_] := FalsecompileQuery[q_ ? entityClassQ] := ReplaceAll[q, $translations ]Scan[
Function[p, p[args___][c_ ? entityClassQ] := p[c, args]],
Keys[ $translations]
]EntityValue[
compileQuery[
"payments" // RightComposition[
aggregate[
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
],
combine["customers", "customerNumber"], sortby["totalPaid" -> "Descending"],
sample[5]
]
],
{
"customerName",
EntityProperty["customers", "customerNumber"],
"totalPaid"
}
]//Datasetはじめに
複雑なクエリを構築する際に困難なことの一つに,その過程がコンパイルされた言語でコードを書くのと,外観も雰囲気も似ているということがある.複雑なクエリ構築の過程では,中間結果やコードの一部をテストするのが難しいため,それをコンパイルしてテストするための完全な関数やプログラムを考えなければならない.
このセクションでは,Entityフレームワークのクエリではそのようなことはなく,適切な手法を使うと,クエリ構築過程をWolfram言語の他の操作のようにインタラクティブにすることができるということを示す.
徐々にクエリを構築する
samplePayments = SampledEntityClass["payments", 20]EntityValue[
samplePayments,
{"customerNumber", "amount"},
"PropertyAssociation"
] // DatasetEntityValue[
AggregatedEntityClass[
samplePayments,
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
],
{"customerNumber", "totalPaid"},
"PropertyAssociation"
] // Dataset顧客名も必要なので,次のようにCombinedEntityClassを使うのも一つの方法である:
EntityValue[
CombinedEntityClass[
"customers",
AggregatedEntityClass[
samplePayments,
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
],
"customerNumber"
],
{
"customerName",
EntityProperty["customers", "customerNumber"],
"totalPaid"
}
]//Dataset最後のクエリの中の特性リストでは,"customerNumber"だけでなく完全なEntityProperty["customers","customerNumber"]を使うことが重要である.CombinedEntityClass[...]に2つの"customerNumber"特性が含まれており,どちらが要求されているかをはっきりさせる必要があるためである.
次のステップでは,特定の指定でSortedEntityClassを使うことによって,支払い総額についての結果を並べ替える:
EntityValue[
SortedEntityClass[
CombinedEntityClass[
"customers",
AggregatedEntityClass[
samplePayments,
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
],
"customerNumber"
],
"totalPaid" -> "Descending"
],
{
"customerName",
EntityProperty["customers", "customerNumber"],
"totalPaid"
}
]//Dataset次にSampledEntityClassを使って上位5人の顧客だけを選ぶ:
EntityValue[
SampledEntityClass[
SortedEntityClass[
CombinedEntityClass[
"customers",
AggregatedEntityClass[
samplePayments,
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
],
"customerNumber"
],
"totalPaid" -> "Descending"
],
5
],
{
"customerName",
EntityProperty["customers", "customerNumber"],
"totalPaid"
}
]//DatasetEntityValue[
SampledEntityClass[
SortedEntityClass[
CombinedEntityClass[
"customers",
AggregatedEntityClass[
"payments",
"totalPaid" -> EntityFunction[p, Total[p["amount"]]],
"customerNumber"
],
"customerNumber"
],
"totalPaid" -> "Descending"
],
5
],
{
"customerName",
EntityProperty["customers", "customerNumber"],
"totalPaid"
}
]//Dataset単一の実体を使って,ネストされた複雑なクエリのプロトタイプを作成する
office = First @ EntityList["offices"]employees = EntityList @ office["employees"]Map[EntityList[#["customers"]]&, employees]EntityValue[
FilteredEntityClass[
"customers",
EntityFunction[c, c["salesRepEmployeeNumber"] == 1165]
],
"customerNumber",
Length
]Entity["employees", 1165] @
EntityFunction[e,
EntityValue[
FilteredEntityClass[
"customers",
EntityFunction[c, c["salesRepEmployeeNumber"] == e["employeeNumber"]]
],
"customerNumber",
Length
]
]最後のクエリではEntityFunctionの層がもう一つ加えられている.これにより特定の従業員実体が,クエリ内部のハードコードされた番号ではなく,クエリに渡されるようになる.
EntityValue[
employees,
EntityFunction[e,
EntityValue[
FilteredEntityClass[
"customers",
EntityFunction[c, c["salesRepEmployeeNumber"] == e["employeeNumber"]]
],
"customerNumber",
Length
]
]
]office @ EntityFunction[o,
EntityValue[
FilteredEntityClass[
o["employees"],
EntityFunction[e,
EntityValue[
FilteredEntityClass[
"customers",
EntityFunction[c, c["salesRepEmployeeNumber"] == e["employeeNumber"]]
],
"customerNumber",
Length
] > 0
]
],
"employeeNumber",
Length
]
]EntityValue[
FilteredEntityClass[
"offices",
EntityFunction[o,
EntityValue[
FilteredEntityClass[
o["employees"],
EntityFunction[e,
EntityValue[
FilteredEntityClass[
"customers",
EntityFunction[c, c["salesRepEmployeeNumber"] == e["employeeNumber"]]
],
"customerNumber",
Length
] > 0
]
],
"employeeNumber",
Length
] >= 2
]
],
{"officeCode", "state", "country"}
]単一の実体を使って,ネストされた複雑なクエリを徐々に構築すると,各レベルでクエリが動作することを確認することができる.この特定のクエリは,厳密に言うと二重にネストされたサブクエリを含んでおり,そのうちの一つのレベルは相関サブクエリである.
この理論は逆にも使える.適切に機能せず,複雑な内部構造(ネストされたEntityFunction式等)を含むクエリが与えられたとき,それを部分に分けて,単一の実体でクエリの内部の部分をテストすることで,問題の場所を迅速に発見し,修正することができる.
ソフトエラー
EntityValue["offices", {"officeCode", "foo"}]この場合,存在しない特性に対してMissing["UnknownProperty",…]値が返される.
EntityList["foo"]
EntityProperties["foo"]この場合もMissing[]値が返される.
ハードエラー
このチュートリアルでは,ハードエラーはFailureオブジェクトを返すものである.従って,ユーザレベルのエラー処理ではFailureオブジェクトに対するEntityValueの戻り値をチェックすることである.
EntityFunctionにおける不正な特性
このようなエラーの一つの例として,EntityFunctionで不正な特性を使うというものがある.
EntityValue["offices", EntityFunction[o, o["foo"] + 1]]EntityFunctionにおけるコンパイル不可能な式
EntityFunctionの本体にコンパイル不可能な部分が含まれているときは常に,クエリのコンパイルは失敗する.
ClearAll[y]
EntityValue["customers", EntityFunction[x, x + y]]ここでも同じことが起るが,データベース側のBesselJの計算が現在サポートされていないことが原因である:
EntityValue["customers", EntityFunction[x, BesselJ[x["creditLimit"], 0]]]EntityFunctionにおける式の不適合な型
ハードエラーの別の原因に,EntityFunctionで誤った型の式を使った場合がある.
次のクエリでは,Greater演算子は文字列の引数は取れない:
EntityValue[
FilteredEntityClass[
"offices",
EntityFunction[o, o["officeCode"] > "4"]
],
{"officeCode"}
]EntityValue[
"offices",
{"officeCode", EntityFunction[o, o["officeCode"] + 1]}
]クエリに存在するサポートされていない型の値
EntityValue[
"offices",
EntityFunction[o, 10 ^ 100]
]EntityValue[
"offices",
EntityFunction[o, N[10 ^ 100]]
]EntityValue[
"offices",
EntityFunction[o, Sqrt[-4]]
]EntityFunctionにおける不適合な戻り値の型
EntityFunctionを引数として取る操作には特定の戻り値の型を要求するものがある.例えば,EntityFunctionがFilteredEntityClassあるいはCombinedEntityClassの中で使われると,戻り値はブール型でなければならない.
次の例では,フィルタリングの述語EntityFunctionの戻り値の型は整数であるが,ブール型が要求されている:
EntityValue[
FilteredEntityClass[
"offices",
EntityFunction[o, 42]
],
{"officeCode"}
]EntityFunctionから非スカラーを戻そうとする
EntityValue["customers", EntityFunction[x, x["orders"]["orderDate"]]]集約における関係ルックアップの不適切な使用
EntityValue[
"customers",
EntityFunction[
x,
Total[x["orders"]["orderDate"] - x["payments"]["paymentDate"]]
]
]AggregatedEntityClassでEntityFunctionの集約関数を使わない
集約の場合にEntityFunctionで集約される特性を計算するために使われる式は,集約関数のいずれかを使わなければならない.技術的には,集約関数を使わないクエリを構築することは可能であるが,コンパイルできない.
EntityFunctionの本体が実質的にスカラーではなく「列」(値のリスト)の型であるため,クエリは失敗する.これはすでに見たエラーに似ているが,この場合は集約に関してである:
EntityValue[
AggregatedEntityClass[
"orderdetails",
"totalAmount" -> EntityFunction[od, od["quantityOrdered"] * od["priceEach"]],
"orderNumber"
],
"totalAmount"
]EntityValue[
AggregatedEntityClass[
"orderdetails",
"totalAmount" -> EntityFunction[od, Total[od["quantityOrdered"] * od["priceEach"]]],
"orderNumber"
],
"totalAmount"
] // Short操作エラー
操作エラーとは,データベース側で起っているエラーである.Entityフレームワークはクエリの中のシンタックスエラー,型に関連したエラー,その他のエラーを早期に阻止しようとするが,それがまだできていなかったり簡単にはできなかったりする場合がある.
次の例は,実は実体クラスである新しい特性で,型"offices"を拡張しようとするものである.このような操作は,データベースを使った実体では現在のところサポートされておらず,この場合のエラーはデータベース側で起る:
EntityValue[
ExtendedEntityClass[
"offices",
"managers" -> EntityFunction[o, FilteredEntityClass[
o["employees"],
EntityFunction[e, MissingQ[e["reportsTo"]]]
]]
],
{"officeCode", "managers"}
]非常におもしろい現実世界の質問には,いくつかのクエリ構築プリミティブを非自明に組み合せたクエリが必要になる.このセクションでは多数のおもしろい例を使って,どのようにそのようなクエリを作成するかを説明する.
例:顧客を支払い合計額で並べ替える
EntityValue[
SortedEntityClass[
ExtendedEntityClass[
"customers",
"totalPaid" -> EntityFunction[
c,
EntityValue[
FilteredEntityClass[
"payments",
EntityFunction[
p,
p["customerNumber"] == c["customerNumber"]
]
],
"amount",
Total
]
]
],
"totalPaid" -> "Descending"
],
{"customerName", "totalPaid"},
"PropertyAssociation"
]//Dataset代りにCombinedEntityClassおよびグループ化による集約を使っても同じことができる(ここでは,集約の後利用したい"customers"型の特性を,グループ化に使う特性のリストに加えなければならない."customerNumber"の特定の値に対応する"customerName"の単一の値があっても,そのことを明示的に指定しない限りクエリには伝わらない).
次のバージョンではCombinedEntityClassが使われている:
EntityValue[
SortedEntityClass[
AggregatedEntityClass[
CombinedEntityClass["customers", "payments", "customerNumber"],
"totalPaid" -> EntityFunction[c, Total[c["amount"]]],
{EntityProperty["customers", "customerNumber"], "customerName"}
],
"totalPaid" -> "Descending"]
,
{"customerName", "totalPaid"},
"PropertyAssociation"
]//DatasetEntityValue[
SortedEntityClass[
ExtendedEntityClass[
"customers",
"totalPaid" -> EntityFunction[
c,
Total[c["payments"]["amount"]]
]
],
"totalPaid" -> "Descending"
],
{"customerName", "totalPaid"},
"PropertyAssociation"
]//Dataset例:各営業所が担当する顧客の総数
この種の問題に対処する方法の一つに,CombinedEntityClassを使って型を組み合せ,組み合せられた型を集約し,ある特性でグループ化するというものがある.
以下のクエリは,各営業所について,そこの従業員すべてが担当する顧客の総数を計算する.このクエリは"offices","employees","customers"の3つの型を組み合せ,営業所コードの値で集約を実行する:
EntityValue[
AggregatedEntityClass[
CombinedEntityClass[
CombinedEntityClass["offices", "employees", "officeCode"],
"customers",
"employeeNumber" -> "salesRepEmployeeNumber"
],
"totalCustomers" -> EntityFunction[o, Length[o["customerNumber"]]],
EntityProperty["offices", "officeCode"]
],
{EntityProperty["offices", "officeCode"], "totalCustomers"}
]//Dataset上のクエリでは,"officeCode"だけではなく,長い形式のEntityProperty["offices","officeCode"]を使うことが重要であった.CombinedEntityClass["offices","employees","officeCode"]には短い"officeCode"の2つの特性EntityProperty["offices","officeCode"]およびEntityProperty["offices","officeCode"](この場合どちらでも使用可能)が含まれているので,どちらの特性かをはっきりさせるためである.
次は,上のクエリをより複雑にして.各営業所が担当し,その営業所と同じ国に在住する顧客の総数を計算する.この場合,型"offices"と"customers"は両方とも"country"特性を持つので,"country"特性に対して,長いEntityPropertyを使う必要がある:
EntityValue[
AggregatedEntityClass[
FilteredEntityClass[
CombinedEntityClass[
CombinedEntityClass["offices", "employees", "officeCode"],
"customers",
"employeeNumber" -> "salesRepEmployeeNumber"
],
EntityFunction[
e,
e[EntityProperty["offices", "country"]] == e[EntityProperty["customers", "country"]]
]
],
"totalCustomers" -> EntityFunction[o, Length[o["customerNumber"]]],
EntityProperty["offices", "officeCode"]
],
{EntityProperty["offices", "officeCode"], "totalCustomers"}
]//Dataset例:各営業所における従業員一人あたりの最大顧客数
EntityValue[
ExtendedEntityClass[
"offices",
"maxEmployeeCustomerCount" -> EntityFunction[
o,
EntityValue[
ExtendedEntityClass[
o["employees"],
"customerCount" -> EntityFunction[
e,
EntityValue[e["customers"], "customerNumber", Length]
]
],
"customerCount",
Max
]
]
],
{"officeCode", "maxEmployeeCustomerCount"},
"PropertyAssociation"
]//DatasetSELECT
"offices_T652"."officeCode" AS "officeCode",
(
SELECT "T662".synthetic_prop_20
FROM (
SELECT max("T657"."customerCount") AS synthetic_prop_20
FROM (
SELECT
(
SELECT "T660".synthetic_prop_21
FROM (
SELECT count("customers_T658"."customerNumber") AS synthetic_prop_21
FROM customers AS "customers_T658"
WHERE "employees_T655"."employeeNumber" = "customers_T658"."salesRepEmployeeNumber"
) AS "T660"
) AS "customerCount"
FROM employees AS "employees_T655"
WHERE "offices_T652"."officeCode" = "employees_T655"."officeCode"
) AS "T657"
) AS "T662"
) AS "maxEmployeeCustomerCount"
FROM offices AS "offices_T652"
例:借金をしている顧客
支払わなければならない総額を計算するためには,指定された顧客のすべての注文から開始する.これは関係c["orders"]で与えられ,型"orders"の実体クラスである.それぞれの注文には複数のアイテムが含まれている可能性や,それぞれのアイテムが複数個注文されている可能性があるので,このクラスを特性"totalToPay"で拡張する.これは,この注文に関係するすべての"orderdetails"実体まで調べ,製品価格と注文数の合計を計算することによって,各注文に対して支払われるべき金額を計算する.ここでは各注文に対して関係o["orderdetails"]が使われていることに注意する.その後,指定された顧客に対するすべての注文で2度目の集計を行い,顧客が支払わなければならない合計額を得る.
EntityValue[
SortedEntityClass[
FilteredEntityClass[
"customers",
EntityFunction[
c,
EntityValue[
ExtendedEntityClass[
c["orders"],
"totalToPay" -> EntityFunction[
o,
Total[o["orderdetails"]["priceEach"] * o["orderdetails"]["quantityOrdered"]]
]
],
"totalToPay",
Total
] - EntityValue[c["payments"], "amount", Total] > 0.00001
]
],
"customerNumber"
],
{"customerNumber", "customerName"},
"PropertyAssociation"
]//Dataset次のクエリでも同じことが行える.関係サブクエリとCombinedEntityClassが使われている:
With[{
orderedTotal := EntityValue[
ExtendedEntityClass[
CombinedEntityClass[
FilteredEntityClass[
"orders",
EntityFunction[
o,
o["customerNumber"] == c["customerNumber"]
]
],
"orderdetails",
"orderNumber"
],
"orderSubtotal" -> EntityFunction[
o,
o["priceEach"] * o["quantityOrdered"]
]
],
"orderSubtotal",
Total
],
paidTotal := EntityValue[
FilteredEntityClass[
"payments",
EntityFunction[
p,
p["customerNumber"] == c["customerNumber"]
]
],
"amount",
Total
]
},
EntityValue[
SortedEntityClass[
FilteredEntityClass[
"customers",
EntityFunction[
c,
orderedTotal - paidTotal > 0.00001
]
],
"customerNumber"],
{"customerNumber", "customerName"},
"PropertyAssociation"
]
] // Dataset最後の例のWithは可読性のために使われているもので,必須ではない.単一の大きいクエリを使うこともできた.しかし,Withはスコープ変数の:=の初期化で使われるので,スコープ変数は評価しなくてもメインのEntityFunctionの本体に差し込まれる.
例:各従業員について,支払額の多い上位5人の顧客の合計支払額
以下のクエリは関係を使っており,関係に従い,都合よく集計を計算する(以下のTotal[c["payments"]["amount"]]のように)方法を示している.この場合,関係を使うことで,3つの別々のデータベース表("employees","customers","payments")から,簡潔で経済的にデータを使うことが可能になっている:
EntityValue[
SortedEntityClass[
ExtendedEntityClass[
"employees",
"totalOrderedByTop5" -> EntityFunction[
e,
Total[
SampledEntityClass[
SortedEntityClass[
ExtendedEntityClass[
e["customers"],
"totalOrdered" -> EntityFunction[
c,
Total[c["payments"]["amount"]]
]
],
"totalOrdered" -> "Descending"
],
5
]["totalOrdered"]
]
]
],
"totalOrderedByTop5" -> "Descending"
],
{"employeeNumber", "firstName", "lastName", "totalOrderedByTop5"},
"PropertyAssociation"
]//DatasetWith[{top5CustomersTotalPaid := EntityValue[
SampledEntityClass[
SortedEntityClass[
AggregatedEntityClass[
CombinedEntityClass[
FilteredEntityClass[
"customers",
EntityFunction[
c,
c["salesRepEmployeeNumber"] == e["employeeNumber"]
]
],
"payments",
"customerNumber"
],
"totalOrdered" -> EntityFunction[p, Total@p["amount"]],
EntityProperty["customers", "customerNumber"]
],
"totalOrdered" -> "Descending"
],
5
],
"totalOrdered",
Total]
},
EntityValue[
SortedEntityClass[
ExtendedEntityClass[
"employees",
"totalOrderedByTop5" -> EntityFunction[e, top5CustomersTotalPaid]
],
"totalOrderedByTop5" -> "Descending"
],
{"employeeNumber", "firstName", "lastName", "totalOrderedByTop5"},
"PropertyAssociation"
]
]//Dataset上記の例でも,その前の例のように,Withはほぼ読みやすさのために使われている.