CTE w SQLite: klauzula WITH w praktyce

CTE to nazwane podzapytanie

Common Table Expression (CTE) to podzapytanie wyciągnięte na zewnątrz i opatrzone nazwą. Zamiast zagnieżdżać SELECT wewnątrz innego SELECT, definiujesz je na początku przez WITH, nadajesz mu nazwę i używasz tej nazwy w głównym zapytaniu jak tabeli.

Kształt jest zawsze taki sam:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

Czytaj od góry do dołu: najpierw zbuduj nazwany wynik customer_totals, potem odpytaj ten wynik. CTE zachowuje się jak tymczasowy widok, który istnieje tylko przez czas wykonania tej jednej instrukcji.

To samo zapytanie bez CTE

Oto ta sama logika zapisana jako podzapytanie, żeby było widać, co zastępuje CTE:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

Ta sama odpowiedź. Ale zwróć uwagę na kolejność czytania: wzrok musi zanurkować w nawiasy, zrozumieć, co jest liczone, i wrócić na zewnątrz. Wersja z CTE czyta się w kolejności, w jakiej wykonuje się praca: zdefiniuj wynik pośredni, a potem go użyj. Przy małym zapytaniu to bez różnicy. Przy zapytaniu z trzema czy czterema krokami to różnica między kodem, który się przegląda, a kodem, który trzeba rozszyfrować.

Kilka CTE w jednym zapytaniu

Możesz połączyć kilka CTE, oddzielając je przecinkami. Każde może odwoływać się do zdefiniowanych przed nim, więc budujesz potok nazwanych kroków:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

Jedno WITH, a potem definicje CTE oddzielone przecinkami. Drugie CTE (big_spenders) czyta z pierwszego (customer_totals) tak jak z tabeli. Główny SELECT następuje po definicji ostatniego CTE.

Częsty błąd: ponowne wpisanie WITH przed drugim CTE. Nie rób tego, to błąd składni. Jedno WITH obejmuje wszystkie.

Wielokrotne odwołania do CTE

Tu CTE naprawdę wyprzedzają podzapytania. Jeśli ten sam wynik pośredni jest potrzebny w dwóch miejscach, CTE pozwala obliczyć go raz i odwołać się do niego dwa razy:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

Do CTE są dwa odwołania: raz, aby obliczyć średnią, i raz jako główne źródło. Bez CTE trzeba by powielić zapytanie z GROUP BY, a każdą zmianę wprowadzać w dwóch miejscach.

CTE z INSERT, UPDATE i DELETE

CTE nie służą tylko do SELECT. Klauzulę WITH możesz postawić przed INSERT, UPDATE lub DELETE, aby użyć nazwanego podzapytania przy zapisie:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

CTE opisuje, które wiersze oznaczyć. INSERT ... SELECT używa go jako źródła. Ta sama sztuczka działa z DELETE FROM ... WHERE id IN (SELECT id FROM cte) przy etapowym usuwaniu, gdy logika wyboru wierszy jest zawiła.

Kiedy sięgać po CTE

Kilka praktycznych zasad:

• Zapytanie ma więcej niż jeden krok logiczny. Agregacja, potem filtrowanie po agregacie, potem złączenie wyniku: to potok, a jedno CTE na krok czyni go czytelnym.
• W przeciwnym razie powtarzasz to samo podzapytanie. Zdefiniuj je raz, odwołaj się dwa razy.
• Podzapytanie zasługuje na nazwę. Jeśli nad podzapytaniem przydałby się komentarz wyjaśniający, co przedstawia, nazwa CTE jest tym komentarzem, a do tego pilnuje jej składnia.
• Zaraz napiszesz zapytanie rekurencyjne. To możliwe tylko z WITH RECURSIVE, omówionym na następnej stronie.

Kiedy nie warto się trudzić:

• Pojedyncze proste podzapytanie użyte w jednym miejscu. WHERE id IN (SELECT id FROM ...) jest w porządku takie, jakie jest.
• Zapytania krytyczne dla wydajności, dla których już sprawdzono, że wstawienie logiki w miejscu pomaga. SQLite zwykle traktuje CTE jako barierę optymalizacji mniej agresywnie niż niektóre inne bazy, ale na gorących ścieżkach warto to sprawdzić przez EXPLAIN QUERY PLAN.

Przykład w praktyce

Składając wszystko razem: mały raport, który znajduje największe zamówienie każdego klienta i porównuje je z jego średnią:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

Dwa CTE, każde robi jedną rzecz. Główny SELECT formatuje wynik. Zapytanie można czytać od góry do dołu i rozumieć każdy krok osobno, a o to właśnie chodzi w CTE.

Dalej: rekurencyjne CTE

Wszystko do tej pory było zwykłym CTE: nazwanym podzapytaniem obliczanym raz. SQLite obsługuje też WITH RECURSIVE, w którym CTE odwołuje się do samego siebie, aby przechodzić hierarchie, generować sekwencje lub przemierzać grafy. O tym na następnej stronie.