PostgreSQL CTE

PostgreSQL veritabanı sisteminde karmaşık sorguları, alt sorguları bir isim altında saklamak için kullanılan Common Table Expression (CTE) özelliğinin kullanımı örneklerle yer alıyor.

SQL içerisinde yer Alt Sorgular veya iç içe sorgular özelliği bir sorgu sonucunun başka sorgu içerisinde kullanımı sağlar.

PostgreSQL içerisinde yer alan CTE veya Common Table Expression özelliği ise alt sorguların geçici olarak isimlendirilmesini sağlar.

Özellik sayesinde geliştirilen gelişmiş sorgular daha düzenli, okunabilir ve modüler hale gelir.

PostgreSQL CTE özelliğinin genel kullanımı aşağıdaki gibidir.

WITH cte_sorgusu (sutun1, sutun2, ...) AS (
    -- CTE sorgusu
    SELECT ...
)
-- CTE kullanımı
SELECT ... FROM cte_name;

WITH anatar kelimesi sorgunun CTE olduğunu ve sorgu ismini alır. Parametre olarak verilen sutunlar ise CTE sorgusu sonucunda alınacak sütunları belirtmek için kullanılır.

Aşağıda PostgreSQL CTE kullanımı ile ilgili örnek yer almaktadır.

WITH action_films AS (
  SELECT 
    f.title, 
    f.length 
  FROM 
    film f 
    INNER JOIN film_category fc USING (film_id) 
    INNER JOIN category c USING(category_id) 
  WHERE 
    c.name = 'Action'
) 
SELECT * FROM action_films;

Tabloları birleştirmek için kullanılan JOIN yapıları PostgreSQL CTE ile birlikte kullanıım aşağıdaki gibidir.

WITH cte_rental AS (
  SELECT 
    staff_id, 
    COUNT(rental_id) rental_count 
  FROM 
    rental 
  GROUP BY 
    staff_id
) 
SELECT 
  s.staff_id, 
  first_name, 
  last_name, 
  rental_count 
FROM 
  staff s 
  INNER JOIN cte_rental USING (staff_id);

Birden fazla PostgreSQL CTE kullanım örneği aşağıda yer almaktadır.

WITH film_stats AS (
    -- CTE 1: Film istatistiklerini hesapla
    SELECT
        AVG(rental_rate) AS avg_rental_rate,
        MAX(length) AS max_length,
        MIN(length) AS min_length
    FROM film
),
customer_stats AS (
    -- CTE 2: Müşteri istatistiklerini hesapla
    SELECT
        COUNT(DISTINCT customer_id) AS total_customers,
        SUM(amount) AS total_payments
    FROM payment
)
-- CTE sorgularını kullan
SELECT
    ROUND((SELECT avg_rental_rate FROM film_stats), 2) AS avg_film_rental_rate,
    (SELECT max_length FROM film_stats) AS max_film_length,
    (SELECT min_length FROM film_stats) AS min_film_length,
    (SELECT total_customers FROM customer_stats) AS total_customers,
    (SELECT total_payments FROM customer_stats) AS total_payments;

PostgreSQL CTE örneklerinde yer aldığı gibi özellik sayesinde uzun ve karmaşık sorguların daha okunabilir olmasını sağlayarak anlaşılır olmasını sağlamıştır.

PostgreSQL CTE özelliği sorguları isimlendirmenin yanında Recursive veya özyinelemeli olarak kendisini çağıran sorgu hazırlamak için RECURSIVE anahtar kelimesiyle birlike kullanılır.

WITH RECURSIVE cte_name (column1, column2, ...)
AS(
    -- ilk çalıştırılacak sorgu
    SELECT select_list FROM table1 WHERE condition

    UNION [ALL]

    -- recursive sorgu
    SELECT select_list FROM cte_name WHERE recursive_condition
) 
SELECT * FROM cte_name;

Aşağıda PostgreSQL CTE Recursive özelliğinin kullanım örneği yer almaktadır.

WITH RECURSIVE subordinates AS (
  SELECT 
    employee_id, 
    manager_id, 
    full_name 
  FROM 
    employees 
  WHERE 
    employee_id = 2 
  UNION 
  SELECT 
    e.employee_id, 
    e.manager_id, 
    e.full_name 
  FROM 
    employees e 
    INNER JOIN subordinates s ON s.employee_id = e.manager_id
) 
SELECT * FROM subordinates;

Örnekte ilk sorguda çalışanlara ait tablodan bilgi alınarak ikinci sorguda JOIN işlemi ile kullanılmıştır.

PostgreSQL, SQL tarafından gelen alt sorgular gibi birçok özelliğin yanında CTE gibi özelliklerle uzun sorguların düzenli, okunaklı ve anlaşılabilir olmasını sağlamaktadır.

PostgreSQL Derslerine buradan ulaşabilirsiniz.

Hayırlı günler dilerim.