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以降[ 9 ]）、MySQL（8.0以降）、SQLite（3.8.3以降）、HyperSQL、Informix（14.10以降）[ 10 ] Google BigQuery、Sybase（バージョン9以降）、Vertica、H2（実験的）[ 11 ]など、多数の製品でサポートされています。OracleではCTEを「サブクエリファクタリング」と呼んでいます。[ 12 ]

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

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

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

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

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

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

SQL:1999では、再帰クエリ（CTE）はクエリが許される場所であればどこにでも記述できます。例えば、CREATE[ RECURSIVE]を使って結果に名前を付けることが可能ですVIEW。[ 16 ]内でCTEを使用することでINSERT INTO、再帰クエリから生成されたデータをテーブルに取り込むことができます。この手法を用いることで、手続き型文を一切使用せずにランダムなデータ生成が可能になります。[ 17 ]

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

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

WITH recursive 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

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

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

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の各従業員の姓、階層内でその従業員の上位の各マネージャー、マネージャーと従業員間のレベル数、および両者間のパスを返します 

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

参照

参考文献

  1. ^ a b Jim Melton; Alan R. Simon (2002). SQL:1999: リレーショナル言語コンポーネントの理解. Morgan Kaufmann. ISBN 978-1-55860-456-8。
  2. ^ a b Microsoft. 「共通テーブル式を使用した再帰クエリ」 . 2009年12月23日閲覧。
  3. ^ Helen Borrie (2008年7月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日閲覧。mysqlserverteam.com
  7. ^ Paragon Corporation：PostgreSQLユーザ定義関数を使用したツリー問題の解決、2004年2月15日、2015年9月19日アクセス
  8. ^ https://firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-2.5-LangRef-Update.pdf
  9. ^ 「MariaDB 10.2.0 変更ログ」 . MariaDB ナレッジベース. 2024年12月22日閲覧。
  10. ^ 14.10 より前では一時テーブルで可能https://stackoverflow.com/questions/42579298/why-does-a-with-clause-give-a-syntax-error-on-informix
  11. ^ 「上級」。
  12. ^カレン・モートン;ロビン・サンズ。ジャレッド・スティル。リヤジ・シャムスディーン。ケリー・オズボーン (2010)。プロのOracle SQL。アプレス。 p. 283.ISBN​ 978-1-4302-3228-5。
  13. ^ 「IBM Docs」
  14. ^ 「IBM Docs」
  15. ^ Regina Obe、Leo Hsu (2012). PostgreSQL: Up and Running . O'Reilly Media. p. 94. ISBN 978-1-4493-2633-3。
  16. ^ジム・メルトン、アラン・R・サイモン (2002). SQL:1999: リレーショナル言語コンポーネントの理解. モーガン・カウフマン. p. 352. ISBN 978-1-55860-456-8。
  17. ^ドン・チェンバリン (1998). DB2ユニバーサルデータベース完全ガイド. モーガン・カウフマン.  253～ 254ページ. ISBN 978-1-55860-482-7。
  18. ^ 「ビューの作成」 2022年2月10日
  19. ^ 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。
  20. ^サンジェイ・ミシュラ、アラン・ボーリュー (2004). 『Mastering Oracle SQL』 . O'Reilly Media, Inc. p. 227. ISBN 978-0-596-00632-7。
  21. ^階層的クエリ2008年6月21日Wayback Machineにアーカイブ、EnterpriseDB
  22. ^階層的クエリ、Oracle
  23. ^ 「CUBRID Hierarchical Query」 . 2013年2月14日時点のオリジナルよりアーカイブ。2013年2月11日閲覧。
  24. ^階層句、IBM Informix
  25. ^ジョナサン・ジェニック (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拡張機能はカバーしていないことに注意してください

  • アブラハム・シルバーシャッツ、ヘンリー・コルト、S・スダルシャン (2010).データベースシステムコンセプト（第6版）. マグロウヒル. pp.  187– 192. ISBN 978-0-07-352332-3。
  • ラグ・ラマクリシュナン、ヨハネス・ゲールケ（2003年）。データベース管理システム（第3版）。マグロウヒル。ISBN 978-0-07-246563-1。第24章
  • ヘクター・ガルシア＝モリーナ、ジェフリー・D・ウルマン、ジェニファー・ウィドム（2009年）。『データベースシステム：完全版（第2版）』。ピアソン・プレンティス・ホール。437  ～ 445ページ。ISBN 978-0-13-187325-4。
「 https://en.wikipedia.org/w/index.php?title=SQLにおける階層的および再帰的クエリ& oldid=1302987466#共通テーブル式」より取得