Zapytania hierarchiczne i rekurencyjne w SQL - Hierarchical and recursive queries in SQL

Hierarchicznego zapytania to rodzaj zapytania SQL , że uchwyty hierarchicznego modelu danych. Są to specjalne przypadki bardziej ogólnych rekurencyjnych zapytań z punktem stałym, które obliczają domknięcia przechodnie .

W standardowym SQL:1999 zapytania hierarchiczne są implementowane za pomocą rekurencyjnych wspólnych wyrażeń tabelowych (CTE). W przeciwieństwie do Oracle wcześniej connect po klauzuli , rekurencyjne CTE zostały zaprojektowane z fixpoint semantyki od początku. Rekurencyjne CTE ze standardu były stosunkowo zbliżone do istniejącej implementacji w IBM DB2 w wersji 2. Rekurencyjne CTE są również obsługiwane przez Microsoft SQL Server (od SQL Server 2008 R2), Firebird 2.1 , PostgreSQL 8.4+ , SQLite 3.8.3+ , IBM Informix wersja 11.50+, CUBRID , MariaDB 10.2+ i MySQL 8.0.1+ . Tableau ma dokumentację opisującą, w jaki sposób można wykorzystać CTE. TIBCO Spotfire nie obsługuje CTE, podczas gdy implementacji Oracle 11g Release 2 brakuje semantyki punktu stałego.

Bez wspólnych wyrażeń tabelowych lub klauzul połączonych można uzyskać hierarchiczne zapytania ze zdefiniowanymi przez użytkownika funkcjami rekurencyjnymi.

Wspólne wyrażenie tabeli

Wspólna tabela wyrażenie lub CTE (w SQL ) jest tymczasowym o nazwie zestaw wyników, pochodzące z kwerendy prostej i zdefiniowane w zakresie wykonania SELECT, INSERT, UPDATElub DELETEoświadczenia.

CTE można traktować jako alternatywę dla tabel pochodnych ( podzapytanie ), widoków i wbudowanych funkcji zdefiniowanych przez użytkownika.

Wspólne wyrażenia tabelowe są obsługiwane przez Teradata (od wersji 14), DB2 , Informix (od wersji 14.1), Firebird (od wersji 2.1), Microsoft SQL Server (od wersji 2005), Oracle (od wersji 11g 2). ), PostgreSQL (od 8.4), MariaDB (od 10.2), MySQL (od 8.0), SQLite (od 3.8.3), HyperSQL , Informix (od 14.10), Google BigQuery , Sybase (od wersji 9), Vertica , H2 (eksperymentalne) i wiele innych . Oracle nazywa CTE „faktoringiem podzapytań”.

Składnia CTE (które może być rekurencyjne lub nie) jest następująca: 

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

gdzie with_queryskładnia to: 

query_name [ (column_name [,...]) ] AS (SELECT ...)

Rekursywne CTE mogą być używane do przechodzenia przez relacje (jak wykresy lub drzewa), chociaż składnia jest znacznie bardziej skomplikowana, ponieważ nie ma automatycznych pseudokolumn (jak LEVEL poniżej ); jeśli są one pożądane, należy je utworzyć w kodzie. Przykłady kursów można znaleźć w dokumentacji MSDN lub dokumentacji IBM.

Słowo RECURSIVEkluczowe zwykle nie jest potrzebne po WITH w systemach innych niż PostgreSQL.

W SQL:1999 zapytanie rekurencyjne (CTE) może pojawić się wszędzie tam, gdzie jest to dozwolone. Można na przykład nazwać wynik za pomocą CREATE[ RECURSIVE] VIEW. Używając CTE wewnątrz an INSERT INTO, można wypełnić tabelę danymi wygenerowanymi z zapytania rekurencyjnego; Przy użyciu tej techniki możliwe jest losowe generowanie danych bez użycia jakichkolwiek instrukcji proceduralnych.

Niektóre bazy danych, takie jak PostgreSQL, obsługują krótszy format CREATE RECURSIVE VIEW, który jest wewnętrznie tłumaczony na kodowanie Z REKURSYWNYM.

Przykład zapytania rekurencyjnego obliczającego silnię liczb od 0 do 9 jest następujący: 

WITH RECURSIVE temp (n, fact) AS 
(SELECT 0, 1 -- Initial Subquery
  UNION ALL 
 SELECT n+1, (n+1)*fact FROM temp -- Recursive Subquery 
        WHERE n < 9)
SELECT * FROM temp;

POŁĄCZ PRZEZ

Alternatywną składnią jest niestandardowa CONNECT BYkonstrukcja; został wprowadzony przez Oracle w latach 80-tych. Przed Oracle 10g konstrukcja była przydatna tylko do przechodzenia przez grafy acykliczne, ponieważ zwracała błąd przy wykrywaniu jakichkolwiek cykli; w wersji 10g Oracle wprowadził funkcję NOCYCLE (i słowo kluczowe), dzięki czemu przechodzenie działa również w obecności cykli.

CONNECT BYjest obsługiwany przez Snowflake , EnterpriseDB , bazę danych Oracle , CUBRID , IBM Informix i DB2, chociaż tylko wtedy, gdy jest włączony jako tryb zgodności. Składnia jest następująca: 

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 ... ]
...
Na przykład,
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

Wynik powyższego zapytania wyglądałby następująco: 

 level |  employee   | empno | manager
-------+-------------+-------+---------
     1 | KING        |  7839 |
     2 |   JONES     |  7566 |    7839
     3 |     SCOTT   |  7788 |    7566
     4 |       ADAMS |  7876 |    7788
     3 |     FORD    |  7902 |    7566
     4 |       SMITH |  7369 |    7902
     2 |   BLAKE     |  7698 |    7839
     3 |     ALLEN   |  7499 |    7698
     3 |     WARD    |  7521 |    7698
     3 |     MARTIN  |  7654 |    7698
     3 |     TURNER  |  7844 |    7698
     3 |     JAMES   |  7900 |    7698
     2 |   CLARK     |  7782 |    7839
     3 |     MILLER  |  7934 |    7782
(14 rows)

Pseudokolumny

  • POZIOM
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ROOT

Operatory jednoargumentowe

Poniższy przykład zwraca nazwisko każdego pracownika w dziale 10, każdego menedżera powyżej tego pracownika w hierarchii, liczbę poziomów między kierownikiem a pracownikiem oraz ścieżkę między nimi: 

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";

Funkcje

  • SYS_CONNECT_BY_PATH

Zobacz też

Bibliografia

Dalsza lektura

Podręczniki akademickie . Należy zauważyć, że obejmują one tylko standard SQL:1999 (i Datalog), ale nie rozszerzenie Oracle.

Linki zewnętrzne