Fun SQL: rekurzív CTE Postgres

Common Table kifejezések (CTE) egy erős konstrukció az SQL. A napi beszélgetés során a CTE-ket WITH záradékként lehet hallani. Gondolhat úgy, hogy a CTE-k hasonlóak egy olyan nézethez, amely csak a lekérdezés futása közben valósul meg, és azon kívül nem létezik. A CTE-k nagyon hasznos építőelemek lehetnek, amelyek lehetővé teszik a nagy SQL-lekérdezések olvashatóságát. De rekurzív módon is használhatók, lehetővé téve néhány nagyon összetett lekérdezés létrehozását anélkül, hogy le kellene esnie egy olyan eljárási nyelvre, mint a plpgsql vagy a plv8.

a rekurzív CTE-K megengedik magukat meghívni, amíg valamilyen feltétel nem teljesül. Ugorjunk be, és fedezzünk fel egy rekurzív CTE—t-egy alapvető, és természetesen a PostgreSQL használatával—, majd boncoljuk fel egy kicsit tovább a rekurzív CTE-t, hogy lássuk, mire tudjuk használni:

WITH RECURSIVE tens (n) AS ( SELECT 10 UNION ALL SELECT n+10 FROM tens WHERE n+10<= 100 ) SELECT n FROM tens; 

a fentiek futtatásakor a következő eredményt kapjuk:

 n ----- 10 20 30 40 50 60 70 80 90 100 (10 rows) 

a fentiekkel ezt könnyen megtehetjük egy generate_series segítségével is. De maradj velünk, és látni fogod, hogy a bonyolultabb dolgok, amiket tehetünk, nem lehetségesek generate_series. Először nézzük meg közelebbről, hogyan működik.

az első rész, amit észrevesz, WITH RECURSIVE. Ez azt mondja Postgres – nek, hogy a CTE rekurzívan hívhatja magát. A következő rész, amit észrevesz, néhány paramétert vesz bele. Ebben az esetben (n), akkor is több, mint egy kell.

továbbhaladva a CTE-be, megvan az első végrehajtott lekérdezés, SELECT 10, amely létrehozza az első értéket. A második rész az, ahol minden móka kezdődik. A UNION ALL megadja, hogy visszaadjuk a hurokból előállított összes rekordot. Ezután a SELECT n+10 FROM tens WHERE n+10<= 100 folyamatosan hívja a létrehozott tens CTE-t, amíg a feltétel teljesül.

tehát ezek az alapok, de az érdekes kérdés: mikor használna rekurzív CTE-t? Ha van egy fa vagy hierarchikus struktúra az adatokhoz, a rekurzív CTE-k sokkal könnyebbé tehetik az életet, mint az összes adat betöltése és a ciklus futtatása a kódban. Az e-kereskedelmi és vásárlási kategóriákkal foglalkozó alkalmazások számára a rekurzív CTE-k nagy segítséget nyújtanak.

próbáljuk meg egy példával egy kicsit konkrétabbá tenni a rekurzív CTE-k előnyeit a Postgres-ben. Először létrehozunk egy táblázatot az alkalmazottakról, majd feltöltünk néhány példa alkalmazottat. (Igen, látni fogja néhány nevet a csapatunk itt Citus adatok ezen a listán. Ezek voltak az első nevek, amelyek eszembe jutottak.)

CREATE TABLE employees ( id serial, name varchar(255), manager_id int ); INSERT INTO employees VALUES (1, 'Umur', null); INSERT INTO employees VALUES (2, 'Craig', 1); INSERT INTO employees VALUES (3, 'Daniel', 2); INSERT INTO employees VALUES (4, 'Claire', 1); INSERT INTO employees VALUES (5, 'Lindsay', 2); INSERT INTO employees VALUES (6, 'Will', 2); INSERT INTO employees VALUES (7, 'Burak', 2); INSERT INTO employees VALUES (8, 'Eren', 2); INSERT INTO employees VALUES (9, 'Katie', 3); INSERT INTO employees VALUES (10, 'Teresa', 4); 

most írok egy lekérdezést, amely megadja nekem az összes jelentést, amely a vállalaton belül egy bizonyos szervezetbe kerül. Ebben az esetben megkapom magam és az összes jelentésemet, valamint az egyes személyek menedzser azonosítóját:

WITH RECURSIVE managertree AS ( SELECT id, name, manager_id FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN managertree mtree ON mtree.id = e.manager_id ) SELECT * FROM managertree; id | name | manager_id ----+---------+------------ 2 | Craig | 1 3 | Daniel | 2 5 | Lindsay | 2 6 | Will | 2 7 | Burak | 2 8 | Eren | 2 9 | Katie | 3 (7 rows) 

Legközelebb, ha rekurzív számítást kell végeznie az adatok között, fontolja meg közvetlenül az SQL-ben, szemben az összes adat betöltésével az alkalmazásba. További olvasáshoz fontolja meg, hogy vessen egy pillantást ezekre a hasznos forrásokra:

• PostgreSQL dokumentumok a CTE-kről
• az utazó eladó problémájának megoldása egy CTE-vel
• fa megszerzése minden gyermekével

megjegyzésként a CTE-k ebben az időben optimalizálási kerítés a PostgreSQL-ben, bár vannak remények arra, hogy ez a jövőben megváltozik. A közös Táblázatkifejezések hihetetlenül hasznos eszköz a jelentéshez. Időnként a CTE-k olvashatósága meghaladja a teljesítményhatást, de vegye figyelembe a kompromisszumokat, amikor használja őket

• fun with sql
• Postgres
• tippek