SQL의 계층 및 재귀 쿼리

Hierarchical and recursive queries in SQL

계층형 조회란 계층형 모델 데이터를 처리하는 SQL 조회 유형이다.그것들은 보다 일반적인 재귀적 고정점 질의의 특별한 경우로서, 전이적 폐쇄를 계산한다.null

표준 SQL:1999에서 계층적 쿼리는 반복적인 공통 테이블 식(CTE)을 통해 구현된다.오라클의 이전 연결 기준 조항과 달리 재귀 CTE는 처음부터 고정점 의미론(fixpoint semantics)으로 설계되었다.[1]표준으로부터의 재귀적 CTE는 IBM DB2 버전 2의 기존 구현에 비교적 가까웠다.[1]재귀 CTE는 마이크로소프트 SQL 서버(SQL 서버 2008 R2, [2]Firebird 2.1, [3]Postgre 이후)에서도 지원된다.SQL 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의 구현에는 픽스포인트 의미론이 부족하다.null

공통 테이블 식이나 연결 기준 절이 없으면 사용자 정의 재귀 함수로 계층적 질의를 달성할 수 있다.[7]null

공통표식

공통 테이블 식 또는 (SQL에서) CTE는 단순한 질의에서 파생되어 a의 실행 범위 내에서 정의된 임시 명명된 결과 집합이다.SELECT,INSERT,UPDATE또는DELETE명세서null

CTE는 파생 테이블(하위 테이블), 뷰 및 인라인 사용자 정의 함수에 대한 대안으로 생각할 수 있다.null

흔한 식탁의 표현 테라데이타(버전 14부터 시작하여), DB2, 인포믹스(버전 14.1부터 시작하여)에서 지원하는, 파이어버드 MicrosoftSQLServer(버전 2005년부터 시작하여), 오라클(회귀과 함께 11g 출시 2), PostgreSQL(8.4이후), MariaDB(10.2부터), MySQL(8.0부터), SQLite(3.8.3부터), Hyp(버전 2.1항에 시작)[8].erSQL, 인포믹스(14.10 이후),[9] 구글 빅쿼리, 사이베이스(버전 9부터), 베르티카, H2(실험)[10] 등 여러 가지다.오라클은 CTE를 "하위 인수"라고 부른다.[11]null

CTE(재귀적일 수도 있고 아닐 수도 있음)의 구문은 다음과 같다.

와 함께 [재귀적] 와 함께_with_with [, ...] 선택 ...

어디에with_query의 구문은 다음과 같다.

query_name [ (column_name [,...]) ] AS (선택 ...)

자동 사이비 색상이 생성되지 않아 구문이 훨씬 더 관련되지만(그래프 또는 나무로) 반복 CTE를 사용하여 관계를 트래버스할 수 있다(예:LEVEL 아래);이러한 내용을 원할 경우 코드에서 작성해야 한다.자습서 예는 MSDN 문서 또는[2] IBM 문서를 참조하십시오[12][13].null

그RECURSIVEPostgre 이외의 시스템에서는 일반적으로 WIT 이후 키워드가 필요하지 않음SQL.[14]

SQL:1999에서는 쿼리가 허용되는 모든 곳에 재귀적(CTE) 쿼리가 나타날 수 있다.예를 들어 다음과 같은 방법으로 결과의 이름을 지정할 수 있다.CREATE[RECURSIVE]VIEW.[15] 내부 CTE 사용INSERT INTO재귀적 질의에서 생성된 데이터로 표를 채울 수 있다. 절차문을 사용하지 않고도 이 기법을 사용하여 무작위 데이터 생성이 가능하다.[16]

Postgre와 같은 일부 데이터베이스SQL, 내부적으로 RECURSIVE 코딩으로 변환되는 짧은 CREATE RECURSIVE VIEW 형식을 지원한다.[17]null

0부터 9까지의 숫자의 요인을 계산하는 재귀적 질의의 예는 다음과 같다.

와 함께 재귀적 임시 변통하다 (n, 사실의) AS  (선택 0, 1 -- 초기 하위 쿼리   유니온 모두   선택 n+1, (n+1)*사실의 From 임시 변통하다 -- 재귀적 서브쿼리         어디에 n < 9) 선택 * From 임시 변통하다;

연결 기준

다른 구문은 비표준이다.CONNECT BY그것은 1980년대에 오라클에 의해 도입되었다.[18]Oracle 10g 이전에 이 구성은 사이클 탐지에 대한 오류를 반환했기 때문에 AC순환 그래프를 통과하는데만 유용했다. 버전 10g에서 Oracle은 NOCYCLE 기능(및 키워드)을 도입하여 사이클이 존재하는 경우에도 통과 작업을 수행했다.[19]

CONNECT BY Snowflake, EnterpriseDB,[20] Oracle 데이터베이스에 의해 [21]지원됨호환성 모드로 활성화된 경우에만 CUBRID, IBM[23] Informix 및 DB2.[22][24]구문은 다음과 같다.

선택 select_list From table_properties [ 어디에 ... ] [ 시작 와 함께 start_lights ] 연결 BY [나시클레] { 선행 child_expr = parent_expr   parent_expr = 선행 child_expr } [ 주문 형제의 BY 컬럼1 [ ASC   DECK ] [, 2열 [ ASC   DECK ] ] ... ] [ 그룹 BY ... ] [ 하고 있다 ... ] ...
예를 들어,
선택 레벨, LPAD (' ', 2 * (레벨 - 1))    에나메 "employee", 엠프노, 곱슬곱슬하게 하다 "관리자" From 폐기하다 시작 와 함께 곱슬곱슬하게 하다 IS NULL 연결 BY 선행 엠프노 = 곱슬곱슬하게 하다;

위 쿼리의 출력은 다음과 같을 것이다.

레벨 직원 엠프노 관리자 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------앨런 7499 7698 3 WARD 7521 7698 3 MARTIN 7654 7698 3 터너 7844 3 JAMES 7900 7698 2 클라크 7782 3 MILR 7934 7782(14열)

의사-색인

  • 레벨
  • Connect_BY_ISLEAF
  • CONNECT_BY_이스사이클
  • Connect_BY_ROOT

단항 연산자

다음 예제는 부서 10의 각 직원의 성, 계층에서 해당 직원보다 위에 있는 각 관리자의 성, 관리자와 직원 사이의 수준 수 및 둘 사이의 경로를 반환한다.

선택 에나메 "직원", Connect_BY_ROOT 에나메 "관리자", 레벨-1 "경로", SYS_CONNECT_BY_PATH(에나메, '/') "길" From 폐기하다 어디에 레벨 > 1 그리고 뎁토노 = 10 연결 BY 선행 엠프노 = 곱슬곱슬하게 하다 주문 BY "직원", "관리자", "경로", "길";

기능들

  • SYS_CONNECT_BY_PATH

참고 항목

참조

  1. ^ a b Jim Melton; Alan R. Simon (2002). SQL:1999: Understanding Relational Language Components. Morgan Kaufmann. ISBN 978-1-55860-456-8.
  2. ^ a b Microsoft. "Recursive Queries Using Common Table Expressions". Retrieved 2009-12-23.
  3. ^ Helen Borrie (2008-07-15). "Firebird 2.1 Release Notes". Retrieved 2015-11-24.
  4. ^ "WITH Queries". PostgreSQL
  5. ^ "WITH Clause". SQLite
  6. ^ "MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs)". mysqlserverteam.com
  7. ^ 파라곤 기업: Postgre 사용트리 문제 해결을 위한 SQL 사용자 정의 기능, 2004년 2월 15일 2015년 9월 19일에 액세스
  8. ^ https://firebirdsql.org/file/documentation/reference_manuals/reference_material/Firebird-2.5-LangRef-Update.pdf
  9. ^ 14.10 이전에 가능한 온도 표 https://stackoverflow.com/questions/42579298/why-does-a-with-clause-give-a-syntax-error-on-informix
  10. ^ http://www.h2database.com/html/advanced.html#recursive_queries
  11. ^ Karen Morton; Robyn Sands; Jared Still; Riyaj Shamsudeen; Kerry Osborne (2010). Pro Oracle SQL. Apress. p. 283. ISBN 978-1-4302-3228-5.
  12. ^ http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.apsg/src/tpc/db2z_xmprecursivecte.htm
  13. ^ http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafyrecursivequeries.htm
  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: Understanding Relational Language Components. Morgan Kaufmann. p. 352. ISBN 978-1-55860-456-8.
  16. ^ Don Chamberlin (1998). A Complete Guide to DB2 Universal Database. Morgan Kaufmann. pp. 253–254. ISBN 978-1-55860-482-7.
  17. ^ https://www.postgresql.org/docs/10/static/sql-createview.html
  18. ^ Benedikt, M.; Senellart, P. (2011). "Databases". In Blum, Edward K.; Aho, Alfred V. (eds.). Computer Science. The Hardware, Software and Heart of It. p. 189. doi:10.1007/978-1-4614-1168-0_10. ISBN 978-1-4614-1167-3.
  19. ^ Sanjay Mishra; Alan Beaulieu (2004). Mastering Oracle SQL. O'Reilly Media, Inc. p. 227. ISBN 978-0-596-00632-7.
  20. ^ Wayback Machine, Enterprise에 2008-06-21 보관된 계층적 쿼리DB
  21. ^ 계층 쿼리, Oracle
  22. ^ "CUBRID Hierarchical Query". Retrieved 11 February 2013.
  23. ^ 계층적 조항, IBM Informix
  24. ^ Jonathan Gennick (2010). SQL Pocket Guide (3rd ed.). O'Reilly Media, Inc. p. 8. ISBN 978-1-4493-9409-7.

추가 읽기

학술 교과서.여기에는 SQL:1999 표준(및 데이터로그)만 포함되며 Oracle 확장자는 포함되지 않는다는 점에 유의하십시오.null

  • Abraham Silberschatz; Henry Korth; S. Sudarshan (2010). Database System Concepts (6th ed.). McGraw-Hill. pp. 187–192. ISBN 978-0-07-352332-3.
  • Raghu Ramakrishnan; Johannes Gehrke (2003). Database management systems (3rd ed.). McGraw-Hill. ISBN 978-0-07-246563-1. 24장.
  • Hector Garcia-Molina; Jeffrey D. Ullman; Jennifer Widom (2009). Database systems: the complete book (2nd ed.). Pearson Prentice Hall. pp. 437–445. ISBN 978-0-13-187325-4.

외부 링크