SQL における階層的および再帰的なクエリ

階層クエリは、階層モデルデータを処理するSQL クエリの一種です。これは、推移閉包を計算する、より一般的な再帰固定点クエリの特殊なケースです。

標準SQL:1999では、階層クエリは再帰共通テーブル式(CTE)によって実装されています。Oracle の以前の connect-by 句とは異なり、再帰 CTE は最初から固定小数点セマンティクスを使用して設計されました。 ^[1]標準の再帰 CTE は、IBM DB2 バージョン 2 の既存の実装に比較的近いものでした。^[1]再帰 CTE は、Microsoft SQL Server (SQL Server 2008 R2 以降)、^[2] Firebird 2.1、^[3] PostgreSQL 8.4+、^[4] SQLite 3.8.3+、^[5] IBM Informixバージョン 11.50+、CUBRID、MariaDB 10.2+、およびMySQL 8.0.1+でもサポートされています。^[6] Tableau には、CTE の使用方法を説明したドキュメントがあります。 TIBCO Spotfire は CTE をサポートしていませんが、Oracle 11g Release 2 の実装には固定小数点セマンティクスがありません。

共通テーブル式やconnected-by句がなくても、ユーザー定義の再帰関数を使用して階層的なクエリを実現できます。^[7]

共通テーブル式

共通テーブル式 (CTE) ( SQLの場合) は、単純なクエリから派生しSELECT、、、、またはステートメントの実行スコープ内で定義される一時的な名前付き結果セットです。 INSERTUPDATEDELETE

CTE は、派生テーブル (サブクエリ)、ビュー、およびインラインユーザー定義関数の代替として考えることができます。

共通テーブル式は、Teradata（バージョン14以降）、IBM Db2、Informix（バージョン14.1以降）、Firebird（バージョン2.1以降）^{[8] 、} Microsoft SQL Server（バージョン2005以降）、Oracle（11gリリース2以降の再帰を含む）、PostgreSQL（8.4以降）、MariaDB（10.2以降）、MySQL（8.0以降）、SQLite（3.8.3以降）、HyperSQL、Informix（14.10以降）^[9] 、 Google BigQuery、Sybase（バージョン9以降）、Vertica、H2（実験的）^[10]など、多数の製品でサポートされています。OracleはCTEを「サブクエリファクタリング」と呼んでいます。^[11]

CTE の構文 (再帰的である場合とそうでない場合があります) は次のとおりです。

WITH [ RECURSIVE ] with_query [, ...] SELECT ...

with_queryの構文は次のとおりです。

クエリ名[ (列名[,...]) ] AS ( SELECT ...)

再帰CTEは関係（グラフやツリーなど）をトラバースするために使用できますが、自動的に疑似列が作成されないため（LEVEL以下のように）、構文ははるかに複雑になります。これらが必要な場合は、コードで作成する必要があります。チュートリアルの例については、MSDNドキュメント^[2]またはIBMドキュメント^[12]^[13]を参照してください。

RECURSIVEPostgreSQL以外のシステムでは、WITHの後にこのキーワードは通常必要ありません。 ^[14]

SQL:1999では、再帰（CTE）クエリはクエリが許可されている場所であればどこにでも出現できます。たとえば、CREATE[ RECURSIVE] を使用して結果に名前を付けることができますVIEW。^[15]内でCTEを使用するとINSERT INTO、再帰クエリから生成されたデータをテーブルに入力できます。この手法を使用すると、手続き型ステートメントを使用せずにランダムなデータ生成が可能です。^[16]

PostgreSQLのような一部のデータベースは、内部的にWITH RECURSIVEコーディングに変換される、より短いCREATE RECURSIVE VIEW形式をサポートしています。^[17]

0 から 9 までの数値の階乗を計算する再帰クエリの例を次に示します。

WITH再帰temp ( n 、fact ) AS ( SELECT 0、1 --初期サブクエリUNION ALL SELECT n + 1 、( n + 1 ) * fact FROM temp WHERE n < 9 -- 再帰サブクエリ) SELECT * FROM temp ;

接続方法

代替構文は非標準CONNECT BY構文で、1980年代にOracleによって導入されました。^[18] Oracle 10gより前では、この構文は循環を検出するとエラーを返すため、非循環グラフの走査にのみ有効でした。バージョン10gでは、OracleはNOCYCLE機能（およびキーワード）を導入し、循環があっても走査が機能するようになりました。^[19]

CONNECT BYは、 Snowflake、EnterpriseDB、^[20] Oracleデータベース、^[21] CUBRID、^[22] IBM Informix ^[23]およびIBM Db2でサポートされていますが、互換モードとして有効になっている場合のみです。^[24]構文は次のとおりです。

SELECT select_list FROM table_expression [ WHERE ... ] [ START WITH start_expression ] CONNECT BY [ NOCYCLE ] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr } [ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ... ] [ GROUP BY ... ] [ HAVING ... ] ...

例えば、

SELECT LEVEL 、LPAD ( ' ' 、2 * ( LEVEL - 1 )) || ename "従業員" 、empno 、mgr "マネージャー" FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ;

上記のクエリの出力は次のようになります。

レベル | 従業員 | 従業員番号 | マネージャー
-------+-----------+------+-----------
     1 | キング | 7839 |
     2 | ジョーンズ | 7566 | 7839
     3 | スコット | 7788 | 7566
     4 | アダムス | 7876 | 7788
     3 | フォード | 7902 | 7566
     4 | スミス | 7369 | 7902
     2 | ブレイク | 7698 | 7839
     3 | アレン | 7499 | 7698
     3 | 病棟 | 7521 | 7698
     3 | マーティン | 7654 | 7698
     3 | ターナー | 7844 | 7698
     3 | ジェームズ | 7900 | 7698
     2 | クラーク | 7782 | 7839
     3 | ミラー | 7934 | 7782
(14行)

擬似列

レベル
イスリーフで接続
接続サイクル
ルートによる接続

単項演算子

次の例では、部門 10 の各従業員の姓、階層内でその従業員より上位の各マネージャー、マネージャーと従業員間のレベル数、および 2 つの間のパスが返されます。

SELECT 
ename "Employee" , CONNECT_BY_ROOT ename "Manager" , LEVEL - 1 "Pathlen" , SYS_CONNECT_BY_PATH ( ename , '/' ) "Path" FROM emp WHERE LEVEL > 1 AND deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Employee" , "Manager" , "Pathlen" , "Path" ;

機能

SYS_CONNECT_BY_PATH

参照

Datalogは固定小数点クエリも実装している
通常のパスクエリはグラフデータベースにおける特定の種類の再帰クエリです。
演繹データベース
階層モデル
到達可能性
推移閉包
ツリー構造

参考文献

^ ab Jim Melton、Alan R. Simon (2002)。SQL:1999: リレーショナル言語コンポーネントの理解。Morgan Kaufmann。ISBN 978-1-55860-456-8。
^ ab Microsoft. 「共通テーブル式を使用した再帰クエリ」。2009年 12 月 23 日閲覧。
^ Helen Borrie (2008-07-15). 「Firebird 2.1 リリースノート」 . 2015-11-24閲覧。
^ 「WITH Queries」. 2022年2月10日.PostgreSQL
^ 「WITH句」。SQLite
^ 「MySQL 8.0 ラボ: [再帰的] MySQL の共通テーブル式 (CTE)」。2019 年 8 月 16 日時点のオリジナルよりアーカイブ。2017年 12 月 20 日閲覧。翻訳元
^ Paragon corporation: PostgreSQL ユーザー定義関数を使用してツリー問題を解決する、2004 年 2 月 15 日、2015 年 9 月 19 日にアクセス
^ https://firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-2.5-LangRef-Update.pdf ^{[ベア URL PDF ]}
^ 14.10 より前では一時テーブルで可能 https://stackoverflow.com/questions/42579298/why-does-a-with-clause-give-a-syntax-error-on-informix
^ 「上級」。
^ カレン・モートン;ロビン・サンズ。ジャレッド・スティル。リヤジ・シャムスディーン。ケリー・オズボーン (2010)。プロ Oracle SQL。アプレス。 p. 283.ISBN 978-1-4302-3228-5。
^ 「IBM Docs」.
^ 「IBM Docs」.
^ Regina Obe、Leo Hsu (2012)。PostgreSQL: Up and Running。O'Reilly Media。p. 94。ISBN 978-1-4493-2633-3。
^ Jim Melton、Alan R. Simon (2002)。SQL:1999: リレーショナル言語コンポーネントの理解。Morgan Kaufmann。p. 352。ISBN 978-1-55860-456-8。
^ ドン・チェンバリン (1998)。DB2 ユニバーサルデータベース完全ガイド。モーガン・カウフマン。pp. 253–254。ISBN 978-1-55860-482-7。
^ 「ビューの作成」。2022年2月10日。
^ Benedikt, M.; Senellart, P. (2011). 「データベース」。Blum, Edward K.; Aho, Alfred V. (編)。コンピュータサイエンス。ハードウェア、ソフトウェア、そしてその中心。p. 189。doi :10.1007/ 978-1-4614-1168-0_10。ISBN 978-1-4614-1167-3。
^ Sanjay Mishra、Alan Beaulieu (2004)。『Mastering Oracle SQL』。O'Reilly Media, Inc.、p. 227。ISBN 978-0-596-00632-7。
^ 階層的クエリアーカイブ 2008-06-21 at the Wayback Machine、 EnterpriseDB
^ 階層クエリ、Oracle
^ 「CUBRID Hierarchical Query」。2013年2月14日時点のオリジナルよりアーカイブ。2013年2月11日閲覧。
^ 階層句、IBM Informix
^ Jonathan Gennick (2010). SQL ポケットガイド (第 3 版). O'Reilly Media, Inc. p. 8. ISBN 978-1-4493-9409-7。

さらに読む

CJ Date (2011) 。SQL とリレーショナル理論: 正確な SQL コードの書き方(第 2 版)。O'Reilly Media。pp. 159–163。ISBN 978-1-4493-1640-2。

学術教科書。これらは SQL:1999 標準 (および Datalog) のみをカバーしており、Oracle 拡張機能はカバーしていないことに注意してください。

Abraham Silberschatz、Henry Korth、S. Sudarshan (2010)。データベースシステムの概念(第 6 版)。McGraw-Hill。pp. 187–192。ISBN 978-0-07-352332-3。
ラグー・ラマクリシュナン。ヨハネス・ゲールケ (2003)。データベース管理システム(第 3 版)。マグロウヒル。ISBN 978-0-07-246563-1。第24章。
Hector Garcia-Molina、Jeffrey D. Ullman、Jennifer Widom (2009)。データベースシステム: 完全版(第 2 版)。Pearson Prentice Hall。pp. 437–445。ISBN 978-0-13-187325-4。

外部リンク

https://stackoverflow.com/questions/1731889/cycle-detection-with-recursive-subquery-factoring
http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/
https://web.archive.org/web/20131114094211/http://gennick.com/with.html
http://www.cs.duke.edu/courses/fall04/cps116/lectures/11-recursion.pdf
http://www.blacktdn.com.br/2015/06/blacktdn-mssql-usando-consulta-cte.html

[JimMelton1-1] Jim Melton、Alan R. Simon (2002)。SQL:1999: リレーショナル言語コンポーネントの理解。Morgan Kaufmann。ISBN 978-1-55860-456-8。

[msdnRCTEs-2] Microsoft. 「共通テーブル式を使用した再帰クエリ」。2009年 12 月 23 日閲覧。

[3] Helen Borrie (2008-07-15). 「Firebird 2.1 リリースノート」 . 2015-11-24閲覧。

[4] 「WITH Queries」. 2022年2月10日.PostgreSQL

[5] 「WITH句」。SQLite

[6] 「MySQL 8.0 ラボ: [再帰的] MySQL の共通テーブル式 (CTE)」。2019 年 8 月 16 日時点のオリジナルよりアーカイブ。2017年 12 月 20 日閲覧。翻訳元

[7] Paragon corporation: PostgreSQL ユーザー定義関数を使用してツリー問題を解決する、2004 年 2 月 15 日、2015 年 9 月 19 日にアクセス

[8] ttps://firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-2.5-LangRef-Update.pdf ^{[ベア URL PDF ]}

[9] 14.10 より前では一時テーブルで可能 https://stackoverflow.com/questions/42579298/why-does-a-with-clause-give-a-syntax-error-on-informix

[10] 「上級」。

[MortonSands2010-11] カレン・モートン;ロビン・サンズ。ジャレッド・スティル。リヤジ・シャムスディーン。ケリー・オズボーン (2010)。プロ Oracle SQL。アプレス。 p. 283.ISBN 978-1-4302-3228-5。

[12] 「IBM Docs」.

[13] 「IBM Docs」.

[ObeHsu2012-14] Regina Obe、Leo Hsu (2012)。PostgreSQL: Up and Running。O'Reilly Media。p. 94。ISBN 978-1-4493-2633-3。

[15] Jim Melton、Alan R. Simon (2002)。SQL:1999: リレーショナル言語コンポーネントの理解。Morgan Kaufmann。p. 352。ISBN 978-1-55860-456-8。

[ChamberlinChamberlin1998-16] ドン・チェンバリン (1998)。DB2 ユニバーサルデータベース完全ガイド。モーガン・カウフマン。pp. 253–254。ISBN 978-1-55860-482-7。

[17] 「ビューの作成」。2022年2月10日。

[18] ^ Benedikt, M.; Senellart, P. (2011). 「データベース」。Blum, Edward K.; Aho, Alfred V. (編)。コンピュータサイエンス。ハードウェア、ソフトウェア、そしてその中心。p. 189。doi :10.1007/ 978-1-4614-1168-0_10。ISBN 978-1-4614-1167-3。

[MishraBeaulieu2004-19] Sanjay Mishra、Alan Beaulieu (2004)。『Mastering Oracle SQL』。O'Reilly Media, Inc.、p. 227。ISBN 978-0-596-00632-7。

[20] 階層的クエリアーカイブ 2008-06-21 at the Wayback Machine、 EnterpriseDB

[21] 階層クエリ、Oracle

[22] 「CUBRID Hierarchical Query」。2013年2月14日時点のオリジナルよりアーカイブ。2013年2月11日閲覧。

[23] 階層句、IBM Informix

[Gennick2010-24] Jonathan Gennick (2010). SQL ポケットガイド (第 3 版). O'Reilly Media, Inc. p. 8. ISBN 978-1-4493-9409-7。

共通テーブル式

接続方法

擬似列

単項演算子

機能

参照

参考文献

さらに読む

外部リンク

関連するトピック

階層モデル

SQL クエリ

推移閉包を計算する、より一般的な再帰

固定点