sobota, 2024-02-10

Common Table Expression

Common Table Expression (CTE), czyli wspólne wyrażenia tablicowe, to funkcja używana w języku SQL do zarządzania bazami danych. Jest to tymczasowy zestaw wyników, który można wykorzystać w ramach zapytań SELECT, INSERT, UPDATE lub DELETE. CTE są szczególnie przydatne w upraszczaniu skomplikowanych zapytań, ponieważ pozwalają na podzielenie zapytania na prostsze części.

Podstawowe informacje

Składnia – CTE definiuje się za pomocą słowa kluczowego WITH, po którym następuje nazwa CTE i słowo kluczowe AS, które wprowadza zapytanie CTE.
Czytelność – użycie CTE umożliwia zwiększenie czytelności i łatwiejsze utrzymanie skomplikowanych zapytań poprzez podzielenie ich na prostsze części.
Rekurencja – jedną z kluczowych cech CTE jest ich zdolność do bycia rekurencyjnymi. Rekurencyjne CTE to takie, które odwołują się same do siebie. Jest to szczególnie użyteczne w przypadku hierarchicznych lub drzewiastych struktur danych.
Zastosowanie – CTE mogą być używane do organizowania danych przed ich ostatecznym przetworzeniem, co może być przydatne w przypadkach, gdy dane muszą być przetworzone w określony sposób, zanim będą mogły być użyte w głównym zapytaniu.
Zasięg – CTE są dostępne tylko w zapytaniu, w którym zostały zdefiniowane i nie mogą być używane w innych zapytaniach.

Przykład prostego CTE

 WITH CTE_Name AS (
      SELECT * FROM Tabela
      WHERE warunek
 )
 SELECT * FROM CTE_Name;

W powyższym przykładzie CTE_Name jest tymczasowym zestawem wyników, który zawiera wszystkie rekordy z Tabela, spełniające określony warunek. Następnie wykonane zostaje zapytanie SELECT, aby pobrać dane z tego CTE.

Przykłady

Common Table Expressions mogą być wykorzystywane w różnorodnych scenariuszach związanych z zapytaniami SQL. Poniższe przykłady pokazują, jak CTE mogą być używane do upraszczania, organizowania i optymalizowania różnych rodzajów zapytań SQL.

Uproszczenie złożonych zapytań

CTE pozwalają na rozbijanie skomplikowanych zapytań na mniejsze, łatwiejsze w zarządzaniu części. Dzięki temu, zamiast tworzyć długie i skomplikowane zapytania z wieloma połączeniami (joins) i podzapytaniami, można użyć serii prostszych CTE, co ułatwia zrozumienie i utrzymanie kodu.

WITH CTE_1 AS (
     SELECT kolumna1, kolumna2 FROM tabela1 WHERE warunek
),
CTE_2 AS (
     SELECT kolumna3, kolumna4 FROM tabela2
)
SELECT * FROM CTE_1 JOIN CTE_2 ON CTE_1.kolumna2 = CTE_2.kolumna3;

Zapytania rekurencyjne

Rekurencyjne CTE są bardzo przydatne w przypadku pracy z hierarchicznymi lub drzewiastymi strukturami danych, jak np. drzewa genealogiczne, struktury organizacyjne firm, czy kategorie produktów.

WITH RECURSIVE CTE_Tree AS (
     SELECT id, nazwa, parent_id FROM tabela WHERE parent_id IS NULL
     UNION ALL
     SELECT t.id, t.nazwa, t.parent_id FROM tabela t
     INNER JOIN CTE_Tree ct ON ct.id = t.parent_id
)
SELECT * FROM CTE_Tree;

Tworzenie raportów i analiz

CTE mogą być używane do wstępnego przetwarzania danych przed wykonaniem analizy lub stworzeniem raportu. Na przykład, można użyć CTE do agregacji danych przed ich użyciem w bardziej skomplikowanym zapytaniu.

WITH CTE_Summary AS (
     SELECT kategoria, COUNT(*) AS Liczba, AVG(cena) AS SredniaCena
     FROM produkty
     GROUP BY kategoria
)
SELECT kategoria, Liczba, SredniaCena FROM CTE_Summary WHERE Liczba > 10;

Usuwanie duplikatów

CTE mogą być również wykorzystywane do identyfikacji i usuwania duplikatów w danych.

WITH CTE_Duplicates AS (
     SELECT *, ROW_NUMBER() OVER (PARTITION BY kolumna1 ORDER BY kolumna2) AS rn
     FROM tabela
)
DELETE FROM CTE_Duplicates WHERE rn > 1;

Aktualizacje warunkowe

W przypadku skomplikowanych aktualizacji, gdzie wartości w jednej tabeli zależą od wartości w innej, CTE mogą uporządkować logikę i uczynić ją bardziej zrozumiałą.

WITH CTE_Data AS (
     SELECT id, kolumna1, (SELECT SUM(kolumna2) FROM tabela2 WHERE tabela2.id = tabela1.id) AS suma
     FROM tabela1
)
UPDATE tabela1 SET kolumna3 = CTE_Data.suma FROM CTE_Data WHERE tabela1.id = CTE_Data.id;

Obsługa Common Table Expression w popularnych maszynach baz danych

CTE są obecnie standardową funkcją w większości nowoczesnych systemów zarządzania bazami danych, oferując programistom potężne narzędzie do tworzenia bardziej zrozumiałych, modularnych i wydajnych zapytań SQL.

PostgreSQL – obsługuje CTE od wersji 8.4, która została wydana w lipcu 2009 roku. PostgreSQL oferuje pełne wsparcie dla rekurencyjnych CTE.
MySQL – wprowadził wsparcie dla CTE w wersji 8.0, wydanej w kwietniu 2018 roku. Przed tą wersją MySQL nie obsługiwał CTE.
Microsoft SQL Server – obsługuje CTE od wersji SQL Server 2005, wydanej w styczniu 2006 roku. SQL Server zapewnia wsparcie zarówno dla standardowych, jak i rekurencyjnych CTE.
Oracle Database – obsługuje CTE od wersji 9i, wydanej w 2001 roku. Oracle również wspiera rekurencyjne CTE, znane jako „subquery factoring”.
SQLite – wprowadził wsparcie dla CTE w wersji 3.8.3, wydanej w lutym 2014 roku. SQLite obsługuje zarówno zwykłe, jak i rekurencyjne CTE.
IBM DB2 – DB2 obsługuje CTE od dłuższego czasu, ze wsparciem zarówno dla standardowych, jak i rekurencyjnych CTE.
MariaDB – jako fork MySQL, MariaDB zaczął obsługiwać CTE od wersji 10.2, wydanej w maju 2017 roku. Wcześniej nie obsługiwał CTE.