CTE ilə tanışlıq: WITH

Proqramlaşdırmada kodun bir hissəsini ayrıca çıxarıb ona ad vermək olar — yəni funksiya yaratmaq. CTE ilə də eyni şeydir. SELECT-subquery-ni əsas sorğudan ayırıb ona ad verə bilərsən və sonra əsas SQL-sorğunda istifadə edə bilərsən.

CTE (Common Table Expressions, ya da ümumi cədvəl ifadələri) — nested query-lərdən bezmiş developer üçün sanki təzə hava kimi bir şeydir. SQL kodunu təkcə başadüşülən yox, həm də həqiqətən şık edir. Əgər əvvəllər iç-içə subquery-lərlə əlləşib gözlərin qaralırdısa — CTE-nin "sehrini" sınamağın vaxtıdır.

Təsəvvür elə ki, ev tikirsən. Adətən istəyirsən tez pəncərə qoyasan, qapıları bərkidəsən (yəni dərhal nested query-lər yazasan), hətta divarlar hələ hazır olmasa da. Amma CTE ilə hər şey başqa cürdür: əvvəlcə səliqəli bir qaralama edirsən — müvəqqəti cədvəl yaradırsan, sanki evin planını çəkirsən. Sonra isə addım-addım sorğunu qurursan. Şık, etibarlı, texniki.

Əslində, CTE — SELECT-sorğusu ilə anında yaratdığın virtual cədvəllərdir. Subquery kimidir, amma daha cool. Proqramlaşdırmada bir hissə məntiqi ayrıca funksiyaya çıxarıb ad verirsənsə, SQL-də bu işi CTE görür. SELECT yazırsan, ad qoyursan — və sonra onu böyük və mürəkkəb sorğunun bir hissəsi kimi istifadə edirsən. Gözəl deyil? Əlbəttə ki, gözəldir.

SQL-subquery ilə nümunə:

-- əsas sorğu
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- subquery, filtered_students ləqəbini aldı

Subquery-ni ayrıca çıxardıq:

-- CTE/subquery, filtered_students ləqəbini aldı
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- əsas sorğu
SELECT *
FROM filtered_students;

Maraqlıdır ki, subquery-lər CTE-dən 20 il əvvəl yaranıb! SQL-89 standartında artıq subquery-lər var idi, amma CTE yalnız SQL-2009 standartında peyda oldu.

WITH sintaksisi

CTE WITH açar sözü ilə başlayır və təxminən belə görünür:

WITH cte_name AS (
    SELECT ... -- sənin sorğun burda
)

SELECT ...
FROM cte_name;

Burada:

• cte_name — sənin CTE-nin adı. İstənilən məntiqli ad seçə bilərsən, məsələn, high_scores, filtered_data və ya hətta best_students.
• Dairəvi mötərizədə () istifadə üçün lazım olan datanı hazırlayan sorğu yazılır.
• CTE-ni müəyyən etdikdən sonra əsas sorğuda ona adi cədvəl kimi müraciət edə bilərsən.

Nümunə 1: Sadə CTE

Gəlin, CTE-nin necə işlədiyinə canlı nümunədə baxaq. Tutaq ki, bizdə students cədvəli var — tələbələrin siyahısı və onların qiymətləri:

Məqsədimiz — qiyməti 85-dən yuxarı olan bütün tələbələri seçmək və onların datalarını çıxarmaqdır.

CTE olmadan variant:

Bunu subquery ilə belə edə bilərik:

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

Amma CTE ilə belə — gözə daha xoş gəlir:

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

Razılaş, daha təmiz və başadüşüləndir. Data hazırlığını (WITH) əsas sorğudan (SELECT) aydın ayırdıq. Sanki əvvəlcə masanı yığışdırırsan, sonra işləməyə başlayırsan — nəfəs almaq asanlaşır.

Nümunə 2: Bir neçə CTE

Bir sorğuda bir neçə CTE təyin edə bilərsən. Bu, xüsusilə datanı mərhələ-mərhələ hazırlamaq lazım olanda faydalıdır.

Verilən: grades cədvəli, burada tələbələrin kurslar üzrə qiymətləri saxlanılır:

Tapşırıq: hər tələbə üçün orta qiyməti tapmaq, sonra isə bu qiymət 85-dən yuxarı olanları seçmək.

Bir neçə CTE ilə həll:

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- birinci CTE-yə müraciət edirik - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- ikinci CTE-yə müraciət edirik - high_achievers

Burada:

1. student_averages ilkin datanı hazırlayır — tələbələrin orta qiymətləri.
2. high_achievers bu datadan istifadə edib yalnız qiyməti 85-dən yuxarı olanları seçir.

CTE ilə subquery-lərin fərqi

Spoiler: CTE subquery-ləri əvəz etmir, amma bəzi hallarda daha rahatdır.

Subquery — sorğunun içində sorğudur. Tez nəticə almaq lazım olanda faydalıdır, amma çox olanda kod xaosa çevrilir.

Nümunə:

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

Subquery-lər SELECT-in içində, FROM-da, WHERE-də və HAVING-də ola bilər. Həmçinin, xarici sorğunun sütunlarına istinad edə bilirlər. CTE-də bu sonuncu bir az çətindir.

CTE isə kodu daha oxunaqlı edir, yəni onu saxlamaq və dəstəkləmək asan olur və səhvlər azalır. Bir sorğunu digərinin içinə salmaq əvəzinə, CTE sadəcə subquery-nin nəticəsinə ad verməyə və sonra istifadə etməyə imkan verir.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

CTE xüsusilə faydalıdır, əgər bir sorğuda hazırlanan datanı bir neçə dəfə istifadə etmək lazımdırsa.

CTE-ni nə vaxt istifadə etməli?

• Çətin sorğunu bir neçə məntiqli mərhələyə bölmək lazım olanda.
• Sorğu oxunaqlı və dəstəklənən olmalıdırsa. Heç kim spaghetti kimi dolaşıq kod strukturlarında baş çıxarmaq istəmir.
• Yalnız cari sorğuda istifadə olunan müvəqqəti data hazırlamaq üçün.

Son nümunə: kursların analizi

Gəlin, öyrəndiklərimizi birləşdirək:

1. Yüksək orta qiymətə sahib tələbələri tapırıq.
2. Onların adlarını və yazıldıqları kursları çıxarırıq.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Diqqət elə, hər şey necə strukturlaşdırılıb:

1. Əvvəlcə orta qiymətləri hazırladıq.
2. Sonra yalnız ən yaxşı tələbələri seçdik.
3. Sonra onları kurslarla əlaqələndirdik.

İndi sən artıq CTE-dən istifadə edib gözəl, oxunaqlı və güclü SQL-sorğuları yaza bilərsən.

İrəli — öz layihələrinə başla!