Zabawa z SQL: rekurencyjne CTE w Postgres

Common Table Expressions (CTE) są potężnym konstruktem w SQL. W codziennej rozmowie możesz usłyszeć klauzule CTE określane jako WITH. Możesz myśleć o CTE jako podobnym do widoku, który zmaterializował się tylko wtedy, gdy to zapytanie jest uruchomione i nie istnieje poza tym zapytaniem. CTE mogą być bardzo użytecznymi elementami pozwalającymi na większą czytelność dużych zapytań SQL. Ale mogą być również używane rekurencyjnie, umożliwiając tworzenie bardzo złożonych zapytań bez konieczności przechodzenia do języka proceduralnego, takiego jak plpgsql lub plv8.

rekurencyjne CTE pozwalają się wywoływać, dopóki nie zostanie spełniony jakiś warunek. Przejdźmy od razu do rekurencyjnego CTE—podstawowego i oczywiście używając PostgreSQL – a następnie przeanalizujmy rekurencyjny CTE nieco dalej, aby zobaczyć, do czego możemy go użyć:

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

po uruchomieniu powyższego otrzymamy następujący wynik:

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

z powyższym możemy również łatwo to zrobić za pomocą generate_series. Ale trzymaj się z nami, a zobaczysz bardziej złożone rzeczy, które możemy zrobić, które nie są możliwe z generate_series. Najpierw przyjrzyjmy się bliżej, jak to działa.

pierwsza część, którą zauważysz, to WITH RECURSIVE. To mówi Postgresowi, że CTE może się rekurencyjnie nazywać. Następna część, którą zauważysz, to pobranie pewnych parametrów. W tym przypadku (n) może również zająć więcej niż jeden, którego potrzebujesz.

przechodząc dalej do CTE, mamy pierwsze zapytanie, które jest wykonywane, SELECT 10, które generuje pierwszą wartość. Druga część to początek zabawy. UNION ALL określa, że mamy zamiar zwrócić wszystkie rekordy, które są produkowane z pętli. Następnie SELECT n+10 FROM tens WHERE n+10<= 100 będzie wywoływać utworzony CTE tens, dopóki warunek nie zostanie spełniony.

więc to są podstawy, ale ciekawe pytanie brzmi: kiedy użyłbyś rekurencyjnego CTE? Gdy masz drzewo lub strukturę hierarchiczną do danych, rekurencyjne CTE mogą znacznie ułatwić życie niż ładowanie wszystkich danych i uruchamianie pętli w kodzie. W przypadku aplikacji, które zajmują się eCommerce i kategoriami zakupów, rekurencyjne CTE są bardzo pomocne.

spróbujmy uczynić korzyści z rekurencyjnych CTE w Postgres nieco bardziej konkretnymi, na przykładzie. Najpierw stworzymy tabelę pracowników, a następnie załadujemy kilka przykładowych pracowników. (Tak, zobaczysz kilka nazwisk z naszego zespołu tutaj w Citus Data na tej liście. To były pierwsze imiona, które przyszły mi do głowy.)

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

teraz napiszę zapytanie, które daje mi wszystkie raporty, które pojawiają się w określonej organizacji w firmie. W tym przypadku zamierzam dostać siebie i wszystkie moje raporty, wraz z identyfikatorem menedżera każdej osoby:

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) 

następnym razem, gdy będziesz musiał wykonać rekurencyjne obliczenia na swoich danych, rozważ zrobienie tego bezpośrednio w SQL, a nie ładowanie wszystkich tych danych do aplikacji. W celu dalszej lektury rozważ przyjrzenie się niektórym z tych pomocnych zasobów:

• PostgreSQL docs on CTE
• rozwiązywanie problemu podróżującego sprzedawcy za pomocą CTE
• uzyskanie drzewa z all it ’ s children

jako Uwaga CTE w tej chwili są ogrodzeniem optymalizacyjnym w PostgreSQL, choć istnieją nadzieje, że to się zmieni w przyszłości. Wspólne wyrażenia tabel są niezwykle użytecznym narzędziem do raportowania. Czasami czytelność CTE przewyższa wpływ na wydajność, ale rozważ kompromisy przy ich użyciu

• zabawa z sql
• Postgres
• porady