4 SQLベースのプロパティ・グラフの問合せおよび分析
SQLを使用して、Oracle Spatial and Graphのプロパティ・グラフ・データを問合せることができます。
Oracle Spatial and Graphのプロパティ・グラフ・サポートのために、すべての頂点とエッジはリレーショナル形式でOracleデータベースに保存されます。Oracle Spatial and Graphプロパティ・グラフ・スキーマ・オブジェクトの詳細は、「Oracle Databaseのプロパティ・グラフ・スキーマ・オブジェクト」を参照してください。
この章では、SQLを使用して実装された一般的なグラフの問合せの例を示します。対象者には、DBAと、SQL構文およびプロパティ・グラフ・スキーマ・オブジェクトを理解しているアプリケーション開発者が含まれます。
SQLを使用してプロパティ・グラフを直接問合せするメリットには次のものがあります。
-
Oracleデータベースの外にデータを持ち出す必要がありません。
-
Oracleデータベースにより提供される業界で実績のあるSQLエンジンを活用できます。
-
別のデータ・タイプ(リレーショナル、JSON、XMLなど)のプロパティ・グラフ・データを容易に結合または統合できます。
-
既存のOracle SQLチューニングおよびデータベース管理ツールおよびユーザー・インタフェースを活用できます。
この例では、現在のスキーマにconnections
という名前のプロパティ・グラフがあると想定しています。このSQL問合せと出力例は、説明のためだけのものであり、実際の出力は、connections
グラフのデータにより異なる可能性があります。いくつかの例では、出力は、読みやすくするために変更が加えられています。
- 単純なプロパティ・グラフ問合せ
このトピックの例では、グラフの頂点、エッジ、およびプロパティを問合せます。 - プロパティ・グラフのテキスト問合せ
プロパティの値(頂点プロパティまたはエッジ・プロパティ)にフリー・テキストが含まれている場合、V列にOracle Text索引を作成するのを手助けできます。 - ナビゲーションおよびグラフ・パターン一致
グラフ・データ・モデルの使用の主なメリットは、リンクやエッジとしてモデル化された関係を使用して、頂点としてモデル化されたエンティティ(人、ムービー、製品、サービス、イベントなど)間を容易に移動できることです。さらに、パターンの検出、個人の集計、傾向の分析などを実行するために、グラフ一致テンプレートを定義することができます。 - ナビゲーション・オプション: CONNECT BYおよび並列反復
CONNECT BY句および並列反復は、高度なナビゲーションおよび問合せのオプションを提供します。 - Pivot
PIVOT句により、動的に列を表に追加して、新しい表を作成できます。 - SQLベース・プロパティ・グラフ分析
インメモリー・アナリストで提供される分析機能に加えて、Oracle Spatial and Graphのプロパティ・グラフ機能は、いくつかのネイティブな、SQLベースのプロパティ・グラフ分析をサポートします。
4.1 単純なプロパティ・グラフ問合せ
このトピックの例では、グラフの頂点、エッジ、およびプロパティを問合せます。
例4-1 指定した頂点IDの頂点の検索
この例では、connections
グラフの頂点IDが1の頂点を検索します。
SQL> select vid, k, v, vn, vt
from connectionsVT$
where vid=1;
出力は次のようになります。
1 country United States 1 name Robert Smith 1 occupation CEO of Example Corporation ...
例4-2 指定したエッジIDのエッジの検索
この例では、connections
グラフのエッジIDが100のエッジを検索します。
SQL> select eid,svid,dvid,k,t,v,vn,vt
from connectionsGE$
where eid=1000;
出力は次のようになります。
1000 1 2 weight 3 1 1
前の出力で、エッジ・プロパティのKは"weight"で、値のタイプIDは3で、浮動小数点の値を示しています。
例4-3 単純なカウントの実行
この例では、connections
グラフで単純なカウントを実行します。
SQL> -- Get the total number of K/V pairs of all the vertices
SQL> select /*+ parallel */ count(1)
from connectionsVT$;
299
SQL> -- Get the total number of K/V pairs of all the edges
SQL> select /*+ parallel(8) */ count(1)
from connectionsGE$;
164
SQL> -- Get the total number of vertices
SQL> select /*+ parallel */ count(distinct vid)
from connectionsVT$;
78
SQL> -- Get the total number of edges
SQL> select /*+ parallel */ count(distinct eid)
from connectionsGE$;
164
例4-4 使用しているプロパティ・キーのセットの取得
この例では、connections
グラフの頂点で使用されているプロパティ・キーのセットを取得します。
SQL> select /*+ parallel */ distinct k
from connectionsVT$;
company
show
occupation
type
team
religion
criminal charge
music genre
genre
name
role
political party
country
13 rows selected.
SQL> -- get the set of property keys used for edges
SQL> select /*+ parallel */ distinct k
from connectionsGE$;
weight
例4-5 値のある頂点の検索
この例では、connections
グラフで、文字列型の値(任意のプロパティ)を持つ頂点、大文字小文字にかかわらずa、e、i、o、uが隣接して2つ含まれる値とその場所を検索します。
SQL> select vid, t, k, v
from connectionsVT$
where t=1
and regexp_like(v, '([aeiou])\1', 'i');
6 1 name Jordan Peele
6 1 show Key and Peele
54 1 name John Green
...
前述のような種類の問合せでは、どの正規表現が使用されるかを事前に知ることは困難なので、B-Tree索引を活用するのは通常困難です。上のような問合せの場合、次の実行計画を入手できます。表の完全スキャンはオプティマイザーにより選択されることに注意してください。
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 795 | 28 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 15 | 795 | 28 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 15 | 795 | 28 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| CONNECTIONSVT$ | 15 | 795 | 28 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(INTERNAL_FUNCTION("V") AND REGEXP_LIKE ("V",U'([aeiou])\005C1','i') AND "T"=1 AND INTERNAL_FUNCTION("K"))
Note
-----
- Degree of Parallelism is 2 because of table property
Oracleデータベースのインメモリー・オプションが利用可能でメモリーも十分にある場合、これにより表(表全体、または関係する列のセット)をメモリーに配置するのを手助けできます。これを実行する方法の1つを次に示します。
SQL> alter table connectionsVT$ inmemory;
Table altered.
正規表現を含む同じSQLを入力すると、"TABLE ACCESS INMEMORY FULL"を実行する計画が表示されます。
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 795 | 28 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 15 | 795 | 28 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 15 | 795 | 28 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS INMEMORY FULL| CONNECTIONSVT$ | 15 | 795 | 28 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(INTERNAL_FUNCTION("V") AND REGEXP_LIKE ("V",U'([aeiou])\005C1','i') AND "T"=1 AND INTERNAL_FUNCTION("K"))
Note
-----
- Degree of Parallelism is 2 because of table property
親トピック: SQLベースのプロパティ・グラフの問合せおよび分析
4.2 プロパティ・グラフのテキスト問合せ
プロパティの値(頂点プロパティまたはエッジ・プロパティ)にフリー・テキストが含まれている場合、V列にOracle Text索引を作成するのを手助けできます。
Oracle Textではデータベースに直接格納されたテキストを処理できます。テキストは短い文字列(名前やアドレスなど)でも、ドキュメント全体でもかまいません。ドキュメントは様々なテキスト形式をとることができます。
テキストは数多くの様々な言語でもかまいません。Oracle Textはスペースで区切られた言語(GreekやCyrillicなどの文字セットを含む)を処理できます。さらに、Oracle Textは中国語、日本語および韓国語の絵文字的言語も処理できます。
プロパティ・グラフ機能はUnicodeをより適切にサポートするためにNVARCHARタイプの列を使用するので、データベースの文字セットとしてUTF8 (AL32UTF8)を使用することを強くお薦めします。
頂点表(またはエッジ表)にOracle Text索引を作成するには、ALTER SESSION権限が必要です。次に例を示します。
SQL> grant alter session to <YOUR_USER_SCHEMA_HERE>;
カスタマイズが必要な場合、CTX_DDLにEXECUTE権限も付与します。
SQL> grant execute on ctx_ddl to <YOUR_USER_SCHEMA_HERE>;
次に、SCOTTにこれらの権限を付与する文の例をいくつか示します。
SQL> conn / as sysdba
Connected.
SQL> -- This is a PDB setup --
SQL> alter session set container=orcl;
Session altered.
SQL> grant execute on ctx_ddl to scott;
Grant succeeded.
SQL> grant alter session to scott;
Grant succeeded.
例4-6 テキスト索引の作成
この例では、SCOTTスキーマのconnectionsグラフの頂点表(V列)にOracle Text索引を作成します。ここで作成されるOracle Text索引は、プロパティ・キーのいずれかのみまたはサブセットに対するものではなく、すべてのプロパティ・キーに対するものであることに注意してください。さらに、新しいプロパティがグラフに追加され、プロパティ値が文字列データ型である場合、それは同じテキスト索引に自動的に含まれます。
この例はMDSYSが所有するOPG_AUTO_LEXERレクサーを使用します。
SQL> execute opg_apis.create_vertices_text_idx('scott', 'connections', pref_owner=>'MDSYS', lexer=>'OPG_AUTO_LEXER', dop=>2);
カスタマイズが必要な場合、ctx_ddl.create_preference APIを使用できます。次に例を示します。
SQL> -- The following requires access privilege to CTX_DDL
SQL> exec ctx_ddl.create_preference('SCOTT.OPG_AUTO_LEXER', 'AUTO_LEXER');
PL/SQL procedure successfully completed.
SQL> execute opg_apis.create_vertices_text_idx('scott', 'connections', pref_owner=>'scott', lexer=>'OPG_AUTO_LEXER', dop=>2);
PL/SQL procedure successfully completed.
Oracle Textが提供する豊富な関数のセットを使用して、グラフ要素に対する問合せを実行できるようになりました。
ノート:
Oracle Text索引が必要なくなった場合、drop_vertices_text_idxまたはopg_apis.drop_edges_text_idx APIを使用して削除することができます。次の文は、SCOTTが所有するconnections
という名前のグラフの頂点およびエッジのテキスト索引を削除します。
SQL> exec opg_apis.drop_vertices_text_Idx('scott', 'connections');
SQL> exec opg_apis.drop_edges_text_Idx('scott', 'connections');
例4-7 プロパティ値を持つ頂点の検索
次の例は、キーワード「Smith」を含むプロパティ値(文字列型)を持つ頂点を検索します。
SQL> select vid, k, t, v
from connectionsVT$
where t=1
and contains(v, 'Smith', 1) > 0
order by score(1) desc
;
前の文からの出力およびSQL実行計画は次のように表示されます。DOMAIN INDEXは実行計画で操作として表示されることに注意してください。
1 name 1 Robert Smith
Execution Plan
----------------------------------------------------------
Plan hash value: 1619508090
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 5 (20) | 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 56 | 5 (20) | 00:00:01 | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$ | 1 | 56 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | DOMAIN INDEX | CONNECTIONSXTV$ | | | 4 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"=1 AND INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
3 - access("CTXSYS"."CONTAINS"("V",'Smith',1)>0)
例4-8 ファジー・マッチ
次の例は、ファジー・マッチが使用されたバリアント"ameriian"(この例のための故意のスペルミス)を含むプロパティ値(文字列型)を持つ頂点を検索します。
SQL> select vid, k, t, v
from connectionsVT$
where contains(v, 'fuzzy(ameriian,,,weight)', 1) > 0
order by score(1) desc;
前の文からの出力およびSQL実行計画は次のように表示されます。
8 role 1 american business man 9 role 1 american business man 4 role 1 american economist 6 role 1 american comedian actor 7 role 1 american comedian actor 1 occupation 1 44th president of United States of America 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1619508090 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 56 | 5 (20)| 00:00:01 | | | | 1 | SORT ORDER BY | | 1 | 56 | 5 (20)| 00:00:01 | | | |* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$ | 1 | 56 | 4 (0)| 00:00:01 | ROWID | ROWID | |* 3 | DOMAIN INDEX | CONNECTIONSXTV$ | | | 4 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V"))
例4-9 問合せ緩和
次の例は、問合せ緩和を実装した高度なOracle Text問合せで、これを使用すると、最も制限されたバージョンの問合せを最初に実行して、必要な一致数を得るまで、問合せを徐々に緩和できます。問合せ緩和を複数の文字列を含む問合せとともに使用すると、他の可能性のある一致よりも先に結果に出現するように、“最適な”一致を決定するための指針を与えることができます。
この例は、"american actor"を問合せ緩和のシーケンスを使用して検索します。
SQL> select vid, k, t, v
from connectionsVT$
where CONTAINS (v,
'<query>
<textquery lang="ENGLISH" grammar="CONTEXT">
<progression>
<seq>{american} {actor}</seq>
<seq>{american} NEAR {actor}</seq>
<seq>{american} AND {actor}</seq>
<seq>{american} ACCUM {actor}</seq>
</progression>
</textquery>
<score datatype="INTEGER" algorithm="COUNT"/>
</query>') > 0;
前の文からの出力およびSQL実行計画は次のように表示されます。
7 role 1 american comedian actor 6 role 1 american comedian actor 44 occupation 1 actor 8 role 1 american business man 53 occupation 1 actor film producer 52 occupation 1 actor 4 role 1 american economist 47 occupation 1 actor 9 role 1 american business man 9 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2158361449 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 56 | 4 (0)| 00:00:01 | | | |* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| CONNECTIONSVT$ | 1 | 56 | 4 (0)| 00:00:01 | ROWID | ROWID | |* 2 | DOMAIN INDEX | CONNECTIONSXTV$ | | | 4 (0)| 00:00:01 | | | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V")) 2 - access("CTXSYS"."CONTAINS"("V",'<query> <textquery lang="ENGLISH" grammar="CONTEXT"> <progression> <seq>{american} {actor}</seq> <seq>{american} NEAR {actor}</seq> <seq>{american} AND {actor}</seq> <seq>{american} ACCUM {actor}</seq> </progression> </textquery> <score datatype="INTEGER" algorithm="COUNT"/> </query>')>0)
例4-10 エッジの検索
頂点と同じように、プロパティ・グラフのエッジ表(GE$)のV列にOracle Text索引を作成できます。次の例はMDSYSが所有するOPG_AUTO_LEXERレクサーを使用します。
SQL> exec opg_apis.create_edges_text_idx('scott', 'connections', pref_owner=>'mdsys', lexer=>'OPG_AUTO_LEXER', dop=>4);
カスタマイズが必要な場合、ctx_ddl.create_preference APIを使用できます。
親トピック: SQLベースのプロパティ・グラフの問合せおよび分析
4.3 ナビゲーションおよびグラフ・パターン一致
グラフ・データ・モデルの使用の主なメリットは、リンクやエッジとしてモデル化された関係を使用して、頂点としてモデル化されたエンティティ(人、ムービー、製品、サービス、イベントなど)間を容易に移動できることです。さらに、パターンの検出、個人の集計、傾向の分析などを実行するために、グラフ一致テンプレートを定義することができます。
このトピックは、connectionsという名前のプロパティ・グラフの例を使用して、グラフのナビゲーションおよびパターン一致の例を示します。ほとんどのSQL文は比較的単純ですが、より高度な要件を実装するためのブロックの構築としても使用できます。単純なものから始めて、次第に複雑さを増していくのが一般的には最適です。
例4-11 人の協力者は誰ですか
次のSQL文は、ID 1の頂点が協力するすべてのエンティティを検索します。単純化するために、出力の関係のみ考えます。
SQL> select dvid, el, k, vn, v
from connectionsGE$
where svid=1
and el='collaborates';
ノート:
対象となる特定の頂点IDを検索するには、キーワードまたはファジー・マッチを使用して、プロパティ・グラフでテキスト問合せを実行できます。(詳細と例については、「プロパティ・グラフのテキスト問合せ」を参照してください)
前の例の出力および実行計画は次のようになります。
2 collaborates weight 1 1 21 collaborates weight 1 1 22 collaborates weight 1 1 .... 26 collaborates weight 1 1 10 rows selected. ------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 460 | 2 (0)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 460 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION HASH ALL | | 10 | 460 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | | |* 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$ | 10 | 460 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | |* 5 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 20 | | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates' AND INTERNAL_FUNCTION("K") AND INTERNAL_FUNCTION("V")) 5 - access("SVID"=1)
例4-12 人の協力者は誰でその職業は何ですか
次のSQL文はID 1の頂点の協力者と、各協力者の職業を検索します。頂点表(VT$)との結合が必要です。
SQL> select dvid, vertices.v
from connectionsGE$, connectionsVT$ vertices
where svid=1
and el='collaborates'
and dvid=vertices.vid
and vertices.k='occupation';
前の例の出力および実行計画は次のようになります。
21 67th United States Secretary of State 22 68th United States Secretary of State 23 chancellor 28 7th president of Iran 19 junior United States Senator from New York ... -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 525 | 7 (0)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 7 | 525 | 7 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) | | 3 | NESTED LOOPS | | 7 | 525 | 7 (0)| 00:00:01 | | | Q1,00 | PCWP | | | 4 | PX PARTITION HASH ALL | | 10 | 250 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | | |* 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$ | 10 | 250 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | |* 6 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 20 | | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | | 7 | PARTITION HASH ITERATOR | | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | | |* 8 | TABLE ACCESS BY LOCAL INDEX ROWID | CONNECTIONSVT$ | | | | | KEY | KEY | Q1,00 | PCWP | | |* 9 | INDEX UNIQUE SCAN | CONNECTIONSXQV$ | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates') 6 - access("SVID"=1) 8 - filter(INTERNAL_FUNCTION("VERTICES"."V")) 9 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'occupation') filter(INTERNAL_FUNCTION("VERTICES"."K"))
例4-13 人の敵対者の検索と国別の集計
次のSQL文はID 1の頂点の敵対者(つまり、feuds
関係)を検索し、それらを国別に集計します。頂点表(VT$)との結合が必要です。
SQL> select vertices.v, count(1)
from connectionsGE$, connectionsVT$ vertices
where svid=1
and el='feuds'
and dvid=vertices.vid
and vertices.k='country'
group by vertices.v;
例の出力および実行計画は次のようになります。この場合、ID 1の頂点には米国に3人、ロシアに1人敵対者がいます。
United States 3 Russia 1 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 5 | 375 | 5 (20)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | | 3 | HASH GROUP BY | | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,00 | P->P | HASH | | 6 | HASH GROUP BY | | 5 | 375 | 5 (20)| 00:00:01 | | | Q1,00 | PCWP | | | 7 | NESTED LOOPS | | 5 | 375 | 4 (0)| 00:00:01 | | | Q1,00 | PCWP | | | 8 | PX PARTITION HASH ALL | | 5 | 125 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | | |* 9 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CONNECTIONSGE$ | 5 | 125 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | |* 10 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 20 | | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | | 11 | PARTITION HASH ITERATOR | | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | | |* 12 | TABLE ACCESS BY LOCAL INDEX ROWID | CONNECTIONSVT$ | | | | | KEY | KEY | Q1,00 | PCWP | | |* 13 | INDEX UNIQUE SCAN | CONNECTIONSXQV$ | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 9 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'feuds') 10 - access("SVID"=1) 12 - filter(INTERNAL_FUNCTION("VERTICES"."V")) 13 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'country') filter(INTERNAL_FUNCTION("VERTICES"."K"))
例4-14 人の協力者の検索と集計およびソート
次のSQL文はID 1の頂点の協力者を検索し、国別に集計し、昇順にソートします。
SQL> select vertices.v, count(1)
from connectionsGE$, connectionsVT$ vertices
where svid=1
and el='collaborates'
and dvid=vertices.vid
and vertices.k='country'
group by vertices.v
order by count(1) asc;
例の出力および実行計画は次のようになります。この場合、ID 1の頂点には米国に最も多くの協力者がいます。
Germany 1 Japan 1 Iran 1 United States 7 --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 750 | 9 (23)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10002 | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,02 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,02 | PCWP | | | 5 | PX SEND RANGE | :TQ10001 | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,01 | P->P | RANGE | | 6 | HASH GROUP BY | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,01 | PCWP | | | 7 | PX RECEIVE | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,01 | PCWP | | | 8 | PX SEND HASH | :TQ10000 | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,00 | P->P | HASH | | 9 | HASH GROUP BY | | 10 | 750 | 9 (23)| 00:00:01 | | | Q1,00 | PCWP | | | 10 | NESTED LOOPS | | 10 | 750 | 7 (0)| 00:00:01 | | | Q1,00 | PCWP | | | 11 | PX PARTITION HASH ALL | | 10 | 250 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | | |* 12 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | CONNECTIONSGE$ | 10 | 250 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | |* 13 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 20 | | 1 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | | 14 | PARTITION HASH ITERATOR | | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | | |* 15 | TABLE ACCESS BY LOCAL INDEX ROWID | CONNECTIONSVT$ | | | | | KEY | KEY | Q1,00 | PCWP | | |* 16 | INDEX UNIQUE SCAN | CONNECTIONSXQV$ | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 12 - filter(INTERNAL_FUNCTION("EL") AND "EL"=U'collaborates') 13 - access("SVID"=1) 15 - filter(INTERNAL_FUNCTION("VERTICES"."V")) 16 - access("DVID"="VERTICES"."VID" AND "VERTICES"."K"=U'country') filter(INTERNAL_FUNCTION("VERTICES"."K"))
親トピック: SQLベースのプロパティ・グラフの問合せおよび分析
4.4 ナビゲーション・オプション: CONNECT BYおよび並列反復
CONNECT BY句および並列反復は、高度なナビゲーションおよび問合せのオプションを提供します。
-
CONNECT BY句により、一致を階層的な順序でナビゲートおよび検索することができます。出力エッジを追うために、前のdvid = svidを使用してナビゲーションの手順を示すことができます。
-
並列反復により、指定したホップ数までナビゲーションを実行できます。
例ではconnectionsという名前のプロパティ・グラフを使用します。
例4-15 CONNECT WITH
次のSQL文は出力エッジを1ホップだけ進みます。
SQL> select G.dvid
from connectionsGE$ G
start with svid = 1
connect by nocycle prior dvid = svid and level <= 1;
前の例の出力および実行計画は次のようになります。
2 3 4 5 6 7 8 9 10 ... ------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 273 | 3 (67)| 00:00:01 | | | | | | |* 1 | CONNECT BY WITH FILTERING| | | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 12 | 0 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) | | 4 | PX PARTITION HASH ALL | | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | | |* 5 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | |* 6 | FILTER | | | | | | | | | | | | 7 | NESTED LOOPS | | 5 | 95 | 1 (0)| 00:00:01 | | | | | | | 8 | CONNECT BY PUMP | | | | | | | | | | | | 9 | PARTITION HASH ALL | | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | | | | |* 10 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | | | | ------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("SVID"=PRIOR "DVID") filter(LEVEL<=2) 5 - access("SVID"=1) 6 - filter(LEVEL<=2) 10 - access("connect$_by$_pump$_002"."prior dvid "="SVID")
1ホップから複数ホップに拡張するには、前の例の1を別の整数に変更します。たとえば、2ホップに変更するには、次のように指定します。level <= 2
例4-16 並列反復
次のSQL文はWITH句内で反復を使用して、4ホップまでのナビゲーションを実行します。反復的に定義されたグラフ拡張: g_exp
の使用は、問合せ内のg_exp
を参照し、それが反復を定義します。この例はまた、パラレル実行のためのPARALLELオプティマイザ・ヒントも使用します。
SQL> WITH g_exp(svid, dvid, depth) as
(
select svid as svid, dvid as dvid, 0 as depth
from connectionsGE$
where svid=1
union all
select g2.svid, g1.dvid, g2.depth + 1
from g_exp g2, connectionsGE$ g1
where g2.dvid=g1.svid
and g2.depth <= 3
)
select /*+ parallel(4) */ dvid, depth
from g_exp
where svid=1
;
例の出力および実行計画は次のようになります。CURSOR DURATION MEMORY
が実行中に選択され、グラフ拡張がメモリーに中間データを格納することを示します。
22 4
25 4
24 4
1 4
23 4
33 4
22 4
22 4
... ...
Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 801 | 31239 | 147 (0)| 00:00:01 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6614_11CB2D2 | | | | | | | | | |
| 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | | | | | | | | | | |
| 4 | PX COORDINATOR | | | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ20000 | 2 | 12 | 0 (0)| 00:00:01 | | | Q2,00 | P->S | QC (RAND) |
| 6 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6614_11CB2D2 | | | | | | | Q2,00 | PCWP | |
| 7 | PX PARTITION HASH ALL | | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | Q2,00 | PCWC | |
|* 8 | INDEX RANGE SCAN | CONNECTIONSXSE$ | 2 | 12 | 0 (0)| 00:00:01 | 1 | 8 | Q2,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10000 | 799 | 12M| 12 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 11 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6614_11CB2D2 | | | | | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 799 | 12M| 12 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 13 | BUFFER SORT (REUSE) | | | | | | | | Q1,00 | PCWP | |
| 14 | PARTITION HASH ALL | | 164 | 984 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
| 15 | INDEX FAST FULL SCAN | CONNECTIONSXDE$ | 164 | 984 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | | | | | | | Q1,00 | PCWC | |
|* 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6614_11CB2D2 | | | | | | | Q1,00 | PCWP | |
| 18 | PX COORDINATOR | | | | | | | | | | |
| 19 | PX SEND QC (RANDOM) | :TQ30000 | 801 | 31239 | 135 (0)| 00:00:01 | | | Q3,00 | P->S | QC (RAND) |
|* 20 | VIEW | | 801 | 31239 | 135 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 21 | PX BLOCK ITERATOR | | 801 | 12M| 135 (0)| 00:00:01 | | | Q3,00 | PCWC | |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6614_11CB2D2 | 801 | 12M| 135 (0)| 00:00:01 | | | Q3,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("SVID"=1)
12 - access("G2"."DVID"="G1"."SVID")
17 - filter("G2"."INTERNAL_ITERS$"=LEVEL AND "G2"."DEPTH"<=3)
20 - filter("SVID"=1)
親トピック: SQLベースのプロパティ・グラフの問合せおよび分析
4.5 ピボット
PIVOT句により、動的に列を表に追加して、新しい表を作成できます。
プロパティ・グラフのスキーマ設計(VT$ and GE$)は、広い("fat")ではなく狭い("skinny")です。つまり、頂点またはエッジに複数のプロパティがある場合、これらのプロパティ・キー、値、データ型などは、複数の列ではなく複数の行を使用して格納されます。このような設計は、追加する列が多すぎる、または表の物理的な列数の制限に達することを心配する必要なく、プロパティを動的に追加できるという意味で、非常に柔軟です。しかし、アプリケーションによっては、プロパティが多少均一ならば、広い表の方が好まれることもあります。
例4-17 ピボット
次のCREATE TABLE ... AS SELECT文はPIVOTを使用して4つの列、‘company’、’occupation’、’name’および‘religion’を追加します。
SQL> CREATE TABLE table pg_wide
as
with G AS (select vid, k, t, v
from connectionsVT$
)
select *
from G
pivot (
min(v) for k in ('company', 'occupation', 'name', 'religion')
);
Table created.
次のDESCRIBE文は、追加した4つの列を含む新しい表の定義を表示します。(出力は、読みやすくするために変更が加えられています。)
SQL> DESCRIBE pg_wide;
Name Null? Type
--------------------------------------------------- -------- --------------------
VID NOT NULL NUMBER
T NUMBER(38)
'company' NVARCHAR2(15000)
'occupation' NVARCHAR2(15000)
'name' NVARCHAR2(15000)
'religion' NVARCHAR2(15000)
親トピック: SQLベースのプロパティ・グラフの問合せおよび分析
4.6 SQLベース・プロパティ・グラフ分析
インメモリー・アナリストで提供される分析機能に加えて、Oracle Spatial and Graphのプロパティ・グラフ機能は、いくつかのネイティブな、SQLベースのプロパティ・グラフ分析をサポートします。
SQLベースの分析の利点は次のとおりです。
-
物理メモリー内に収まらない大規模なグラフの分析が容易になる
-
データベースの外部にグラフ・データが転送されないため分析コストが低くなる
-
プロパティ・グラフ・データベースの現在の状態を使用してより的確な分析を行う
-
インメモリー・グラフとグラフ・データベースの最新更新との同期ステップをなくして分析をシンプルにする
ただし、グラフ(またはサブグラフ)がメモリーに収まっている場合、インメモリー・アナリストにより提供された実行中の分析は、SQLベースの分析を使用するよりも、通常、パフォーマンスは上回ります。
多くの分析の実装には中間データ構造が必要なため、ほとんどのSQL (およびPL/SQL)ベースの分析APIには作業表(wt)用のパラメータがあります。典型的なフローには、次のステップが含まれます。
-
作業表を準備します。
-
分析を実行します(1つまたは複数のコール)。
-
クリーンアップを実行します
次のサブトピックでは、一般的なタイプのプロパティ・グラフ分析の、SQLベースの例を示します。
4.6.1 最短パスの例
次の例は、SQLベースの最短パス分析を示しています。
例4-18 最短パスの設定および計算
たとえば、最短パスについて考えます。内部的に、Oracle Databaseは双方向Dijkstraアルゴリズムを使用します。次のコード・スニペットは、準備、実行、およびクリーンアップのワークフロー全体を示しています。
set serveroutput on
DECLARE
wt1 varchar2(100); -- intermediate working tables
n number;
path varchar2(1000);
weights varchar2(1000);
BEGIN
-- prepare
opg_apis.find_sp_prep('connectionsGE$', wt1);
dbms_output.put_line('working table name ' || wt1);
-- compute
opg_apis.find_sp(
'connectionsGE$',
1, -- start vertex ID
53, -- destination vertex ID
wt1, -- working table (for Dijkstra expansion)
dop => 1, -- degree of parallelism
stats_freq=>1000, -- frequency to collect statistics
path_output => path, -- shortest path (a sequence of vertices)
weights_output => weights, -- edge weights
options => null
);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- cleanup (commented out here; see text after the example)
-- opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/
この例では、次のような出力が生成されます。作業表の名前が指定されていない場合、準備ステップで、自動的に一時表の名前が生成され作成されます。一時作業表の名前にはセッションIDが使用されるので、出力はおそらく異なります。
working table name "CONNECTIONSGE$$TWFS12" path 1 3 52 53 weights 4 3 1 1 1 PL/SQL procedure successfully completed.
作用表の定義を知りたい場合は、クリーンアップ・フェーズをスキップします(find_sp_cleanup
へのコールをコメントアウトしている前の例を参照)。計算が実行された後で、作業表を記述できます。
SQL> describe "CONNECTIONSGE$$TWFS12"
Name Null? Type
--------- -------- ----------------------------
NID NUMBER
D2S NUMBER
P2S NUMBER
D2T NUMBER
P2T NUMBER
F NUMBER(38)
B NUMBER(38)
インメモリーや高度な圧縮の使用などの様々な表作成オプションの試行を希望する上級ユーザーの場合、前の作業表を事前に作成して名前を渡すことができます。
例4-19 最短パス:作業表の作成および分析の実行
次の文は、最初に同じ列構造と基本的圧縮を有効にして作業表を作成してからそれをSQLベースの計算に渡す、いくつかの高度なオプションを示しています。このコードは、CREATE TABLE圧縮とインメモリーのオプションを使用して計算用の中間表を最適化します。
create table connections$MY_EXP(
NID NUMBER,
D2S NUMBER,
P2S NUMBER,
D2T NUMBER,
P2T NUMBER,
F NUMBER(38),
B NUMBER(38)
) compress nologging;
DECLARE
wt1 varchar2(100) := 'connections$MY_EXP';
n number;
path varchar2(1000);
weights varchar2(1000);
BEGIN
dbms_output.put_line('working table name ' || wt1);
-- compute
opg_apis.find_sp(
'connectionsGE$',
1,
53,
wt1,
dop => 1,
stats_freq=>1000,
path_output => path,
weights_output => weights,
options => null
);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- cleanup
-- opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/
計算の終了時に、作業表が削除または切り捨てられていない場合は、次のように作業表の内容をチェックできます。作業表の構造はリリース間で異なる可能性があることに注意してください。
SQL> select * from connections$MY_EXP;
NID D2S P2S D2T P2T F B
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 0 1.000E+100 1 -1
53 1.000E+100 0 -1 1
54 1.000E+100 1 53 -1 1
52 1.000E+100 1 53 -1 1
5 1 1 1.000E+100 0 -1
26 1 1 1.000E+100 0 -1
8 1000 1 1.000E+100 0 -1
3 1 1 2 52 0 0
15 1 1 1.000E+100 0 -1
21 1 1 1.000E+100 0 -1
19 1 1 1.000E+100 0 -1
...
例4-20 最短パス:同じグラフへの複数コールの実行
同じグラフへの複数のコールを実行するには、準備ステップへの1つのコールだけが必要です。次に、同じグラフ内で複数の頂点のペアの最短パスを計算する例を示します。
DECLARE
wt1 varchar2(100); -- intermediate working tables
n number;
path varchar2(1000);
weights varchar2(1000);
BEGIN
-- prepare
opg_apis.find_sp_prep('connectionsGE$', wt1);
dbms_output.put_line('working table name ' || wt1);
-- find shortest path from vertex 1 to vertex 53
opg_apis.find_sp( 'connectionsGE$', 1, 53,
wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- find shortest path from vertex 2 to vertex 36
opg_apis.find_sp( 'connectionsGE$', 2, 36,
wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- find shortest path from vertex 30 to vertex 4
opg_apis.find_sp( 'connectionsGE$', 30, 4,
wt1, dop => 1, stats_freq=>1000, path_output => path, weights_output => weights, options => null);
dbms_output.put_line('path ' || path);
dbms_output.put_line('weights ' || weights);
-- cleanup
opg_apis.find_sp_cleanup('connectionsGE$', wt1);
END;
/
この例の出力は次のようになります。指定した頂点の複数のペアに対し、3つの最短パスが見つかりました。
working table name "CONNECTIONSGE$$TWFS12" path 1 3 52 53 weights 4 3 1 1 1 path 2 36 weights 2 1 1 path 30 21 1 4 weights 4 3 1 1 1 PL/SQL procedure successfully completed.
親トピック: SQLベース・プロパティ・グラフ分析
4.6.2 協調フィルタリングの概要および例
協調フィルタリング(ソーシャル・フィルタとも呼ばれる)は、他の人の推奨を使用して情報をフィルタリングします。協調フィルタリングは、似た好みを持つ他の人の購入内容に基づいて購入を推奨するシステムで広く使用されます。
次の例では、SQLベースの協調フィルタリング分析を示します。
例4-21 協調フィルタリングの設定および計算
この例では、SQLベースの協調フィルタリングの使用方法、具体的には、行列因子分解を使用した顧客への電話ブランドのお薦め方法を示します。この例では、データベース内に「PHONES」というグラフが存在すると想定しています。このグラフ例には、顧客頂点と品目頂点、および一部の顧客頂点を他の一部の品目頂点にリンクする「評価」ラベルを持つエッジが含まれています。評価ラベルには、特定の顧客(エッジOUT頂点)が、指定された商品(エッジIN頂点)に割り当てた評価に対応する数値があります。
次の図は、このグラフを示しています。
次のコードは、行列因子分解アルゴリズムを内部で使用する、SQLベースの協調フィルタリング・アルゴリズムを実行するためのエンドツーエンド・フローを示しています。set serveroutput on
DECLARE
wt_l varchar2(32); -- working tables
wt_r varchar2(32);
wt_l1 varchar2(32);
wt_r1 varchar2(32);
wt_i varchar2(32);
wt_ld varchar2(32);
wt_rd varchar2(32);
edge_tab_name varchar2(32) := 'phonesge$';
edge_label varchar2(32) := 'rating';
rating_property varchar2(32) := '';
iterations integer := 100;
min_error number := 0.001;
k integer := 5;
learning_rate number := 0.001;
decrease_rate number := 0.95;
regularization number := 0.02;
dop number := 2;
tablespace varchar2(32) := null;
options varchar2(32) := null;
BEGIN
-- prepare
opg_apis.cf_prep(edge_tab_name,wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
dbms_output.put_line('working table wt_l ' || wt_l);
dbms_output.put_line('working table wt_r ' || wt_r);
dbms_output.put_line('working table wt_l1 ' || wt_l1);
dbms_output.put_line('working table wt_r1 ' || wt_r1);
dbms_output.put_line('working table wt_i ' || wt_i);
dbms_output.put_line('working table wt_ld ' || wt_ld);
dbms_output.put_line('working table wt_rd ' || wt_rd);
-- compute
opg_apis.cf(edge_tab_name,edge_label,rating_property,iterations,
min_error,k,learning_rate,decrease_rate,regularization,dop,
wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd,tablespace,options);
END;
/
このフローは、後ほど計算に渡される一時作業表の作成で開始されます。計算の最後に、例は次の出力を生成する可能性があります。作業表の名前が指定されていない場合は、準備ステップによって自動的に一時表名が生成されて作成される点に注意してください。一時作業表の名前にはセッションIDが使用されるので、出力はおそらく異なります。working table wt_l "PHONESGE$$CFL57" working table wt_r "PHONESGE$$CFR57" working table wt_l1 "PHONESGE$$CFL157" working table wt_r1 "PHONESGE$$CFR157" working table wt_i "PHONESGE$$CFI57" working table wt_ld "PHONESGE$$CFLD57" working table wt_rd "PHONESGE$$CFRD57" PL/SQL procedure successfully completed.
例4-22 協調フィルタリング:中間エラーの検証
作業表内のデータがまだ削除されていないかぎり、すべての計算の最後に次の問合せを使用してアルゴリズムの現在のエラーを確認できます。次のSQL問合せは、協調フィルタリング・アルゴリズムの現在の実行の中間エラーを取得する方法を示しています。
SELECT /*+ parallel(48) */ SQRT(SUM((w1-w2)*(w1-w2) +
<regularization>/2 * (err_reg_l+err_reg_r))) AS err
FROM <wt_i>;
正則化パラメータと作業表名(パラメータwt_i
)は、OPG_APIS.CFアルゴリズムを実行しているときに使用される値に応じて置き換える必要があることに注意してください。前述の例では、次のように、<regularization>
を0.02に置き換え、<wt_i>
を「PHONESGE$$CFI149」に置き換えます。
SELECT /*+ parallel(48) */ SQRT(SUM((w1-w2)*(w1-w2) + 0.02/2 * (err_reg_l+err_reg_r))) AS err
FROM "PHONESGE$$CFI149";
この問合せでは、次の出力が生成される可能性があります。
ERR ---------- 4.82163662
現在のエラーの値が大きすぎるか、協調フィルタリングの行列因子分解の結果から得た予想がまだ有用でない場合は、作業表とこれまでの進展を再利用して、アルゴリズムのさらなる反復を実行できます。次の例は、SQLベースの協調フィルタリングを使用した予測の実行方法を示しています。
例4-23 協調フィルタリング:予測の実行
協調フィルタリング・アルゴリズムの結果が、行列積の2つの因子であるwt_l
表とwt_r
表に格納されます。協調フィルタリングの予測をする場合、これらの行列因子を使用する必要があります。
このアルゴリズムの一般的なフローでは、OPG_APIS.CF_CLEANUPプロシージャをコールする前に、2つの行列因子を使用して予測を行うことができます。または、後で使用するために、それらを他の表にコピーして永続的なものにすることができます。次の例は、後者の場合を示しています。
DECLARE
wt_l varchar2(32); -- working tables
wt_r varchar2(32);
wt_l1 varchar2(32);
wt_r1 varchar2(32);
wt_i varchar2(32);
wt_ld varchar2(32);
wt_rd varchar2(32);
edge_tab_name varchar2(32) := 'phonesge$';
edge_label varchar2(32) := 'rating';
rating_property varchar2(32) := '';
iterations integer := 100;
min_error number := 0.001;
k integer := 5;
learning_rate number := 0.001;
decrease_rate number := 0.95;
regularization number := 0.02;
dop number := 2;
tablespace varchar2(32) := null;
options varchar2(32) := null;
BEGIN
-- prepare
opg_apis.cf_prep(edge_tab_name,wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
-- compute
opg_apis.cf(edge_tab_name,edge_label,rating_property,iterations,
min_error,k,learning_rate,decrease_rate,regularization,dop,
wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd,tablespace,options);
-- save only these two tables for later predictions
EXECUTE IMMEDIATE 'CREATE TABLE customer_mat AS SELECT * FROM ' || wt_l;
EXECUTE IMMEDIATE 'CREATE TABLE item_mat AS SELECT * FROM ' || wt_r;
-- cleanup
opg_apis.cf_cleanup('phonesge$',wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
END;
/
この例は、次の出力のみを生成します。
PL/SQL procedure successfully completed.
これで行列因子がcustomer_mat表とitem_mat表に保存されたので、次の問合せを使用して、実際の値(以前、「評価」としてグラフに存在していた値)と見積予想(特定の顧客行と品目列での行列の乗算の結果)の間の「エラー」(差分)を確認できます。
次の問合せは、数値IDではなく、頂点のNVARCHARプロパティ(たとえば、nameプロパティ)を返すために、頂点表で結合によってカスタマイズされていることに注意してください。この問合せは、グラフ内のすべての品目頂点に対するすべての顧客頂点のすべての予測を返します。
SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer,
MIN(vertex2.v) AS item,
MIN(edges.vn) AS real,
SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges,
CUSTOMER_MAT l,
ITEM_MAT r,
PHONESVT$ vertex1,
PHONESVT$ vertex2
WHERE l.k = r.k
AND l.c = edges.svid(+)
AND r.p = edges.dvid(+)
AND l.c = vertex1.vid
AND r.p = vertex2.vid
GROUP BY l.c, r.p
ORDER BY l.c, r.p -- This order by clause is optional
;
この問合せでは、次のような出力が生成される可能性があります(簡略化するために、一部の行は省略されています)。
CUSTOMER ITEM REAL PREDICTED ------------------------------------------------ Adam Apple 5 3.67375703 Adam Blackberry 3.66079652 Adam Danger 2.77049596 Adam Ericsson 4.21764858 Adam Figo 3.10631337 Adam Google 4 4.42429022 Adam Huawei 3 3.4289115 Ben Apple 2.82127589 Ben Blackberry 2 2.81132282 Ben Danger 3 2.12761307 Ben Ericsson 3 3.2389595 Ben Figo 2.38550534 Ben Google 3.39765075 Ben Huawei 2.63324582 ... Don Apple 1.3777496 Don Blackberry 1 1.37288909 Don Danger 1 1.03900439 Don Ericsson 1.58172236 Don Figo 1 1.16494421 Don Google 1.65921807 Don Huawei 1 1.28592648 Erik Apple 3 2.80809351 Erik Blackberry 3 2.79818695 Erik Danger 2.11767182 Erik Ericsson 3 3.2238255 Erik Figo 2.3743591 Erik Google 3 3.38177526 Erik Huawei 3 2.62094201
予測結果が準備できたか、アルゴリズムの反復をさらに実行する必要があるかどうかを判断するために一部の行のみを確認する場合は、外部問合せの中に前の問合せをラップできます。次の例では、最初の11個の結果のみが選択されます。
SELECT /*+ parallel(48) */ * FROM (
SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer,
MIN(vertex2.v) AS item,
MIN(edges.vn) AS real,
SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges,
CUSTOMER_MAT l,
ITEM_MAT r,
PHONESVT$ vertex1,
PHONESVT$ vertex2
WHERE l.k = r.k
AND l.c = edges.svid(+)
AND r.p = edges.dvid(+)
AND l.c = vertex1.vid
AND r.p = vertex2.vid
GROUP BY l.c, r.p
ORDER BY l.c, r.p
) WHERE rownum <= 11;
この問合せでは、次のような出力が生成される可能性があります。
CUSTOMER ITEM REAL PREDICTED ------------------------------------------------ Adam Apple 5 3.67375703 Adam Blackberry 3.66079652 Adam Danger 2.77049596 Adam Ericsson 4.21764858 Adam Figo 3.10631337 Adam Google 4 4.42429022 Adam Huawei 3 3.4289115 Ben Apple 2.82127589 Ben Blackberry 2 2.81132282 Ben Danger 3 2.12761307 Ben Ericsson 3 3.2389595
特定の頂点(顧客、品目、またはその両方)の予測を取得するには、希望するID値によって問合せを制限できます。たとえば、頂点1(顧客)の予測値と頂点105(品目)の予測値を取得するには、次の問合せを使用できます。
SELECT /*+ parallel(48) */ MIN(vertex1.v) AS customer,
MIN(vertex2.v) AS item,
MIN(edges.vn) AS real,
SUM(l.v * r.v) AS predicted
FROM PHONESGE$ edges,
CUSTOMER_MAT l,
ITEM_MAT r,
PHONESVT$ vertex1,
PHONESVT$ vertex2
WHERE l.k = r.k
AND l.c = edges.svid(+)
AND r.p = edges.dvid(+)
AND l.c = vertex1.vid
AND vertex1.vid = 1 /* Remove to get all predictions for item 105 */
AND r.p = vertex2.vid
AND vertex2.vid = 105 /* Remove to get all predictions for customer 1 */
/* Remove both lines to get all predictions */
GROUP BY l.c, r.p
ORDER BY l.c, r.p;
この問合せでは、次のような出力が生成される可能性があります。
CUSTOMER ITEM REAL PREDICTED ------------------------------------------------ Adam Ericsson 4.21764858
親トピック: SQLベース・プロパティ・グラフ分析