WITH istifadə edərək müvəqqəti cədvəllərin yaradılması

Müvəqqəti cədvəllər — aralıq nəticələri saxlamaq üçün əla üsuldur ki, sonra onlarla rahat işləyə biləsən. Bu, proqramlaşdırmada olduğu kimidir: eyni böyük kod parçasını təkrar-təkrar yazmaq əvəzinə, onu bir dəfə dəyişkənə yazırsan və sonra rahat istifadə edirsən. SQL dünyasında belə "dəyişkənlərin" rolunu çox vaxt müvəqqəti cədvəllər oynayır.

Amma CTE işi daha da asanlaşdırır — əlavə əziyyətsiz müvəqqəti cədvəllər yaratmağa imkan verir:

• Silinmə dərdi yoxdur.

  CTE yalnız sorğu icra olunan anda yaşayır. SQL işləyib qurtaran kimi — CTE yox olur, sanki görünməz bir köməkçi hər şeyi edib və iz qoymadan qeyb olur.

• Kod daha aydın olur.

  CTE yaratmaq, fiziki müvəqqəti cədvəl yaratmaq və silməklə əlləşməkdən qat-qat asandır. Hər şey göz qabağında, hər şey yerində.

• "Bir dəfəlik" işlər üçün superdir.

  Əgər sadəcə bir mərhələdə məlumatı emal etmək lazımdırsa — CTE bunun öhdəsindən əla gəlir. Sürətli, təmiz, əlavə effekt olmadan.

CTE-nin müvəqqəti cədvəl kimi sintaksisi

Gəlin əvvəlcə CTE-nin sintaksisini yadımıza salaq:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

Burada WITH sorğuda istifadə olunan müvəqqəti "cədvəl ifadəsi" yaradır. Bu ifadə cədvəl kimi görünür, amma yalnız sorğu icra olunanadək yaşayır.

Nümunə: əlaçıları sayaq

Gəlin CTE ilə müvəqqəti cədvəl yaradıb, orta balı (grade) 90-dan yuxarı olan tələbələri tapaq. Sonra isə onların siyahısını çıxaraq.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Burada biz:

• WITH istifadə edərək high_achievers adlı müvəqqəti cədvəl yaradırıq.
• CTE daxilində hər bir tələbə üçün (student_id) qiymətləri (grades) qruplaşdırıb, orta balı hesablayırıq.
• SELECT * sorğusunda isə sadəcə high_achievers müvəqqəti cədvəlinə adi cədvəl kimi istinad edirik.

CTE və müvəqqəti cədvəllərin müqayisəsi

Bəzən sual yaranır: bəs CREATE TEMP TABLE ilə yaradılan müvəqqəti cədvəllərlə CTE arasında fərq nədir?

Həmin məlumatlar üçün ənənəvi müvəqqəti cədvəl (CREATE TEMP TABLE) nümunəsi belədir:

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- Cədvəli silməyi unutma!

Və CTE ilə eyni sorğu:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

CTE-ni nə vaxt, müvəqqəti cədvəli nə vaxt istifadə etməli?

CTE, aralıq məlumatı tez hazırlayıb dərhal işləmək istəyəndə — əlavə əziyyətsiz — superdir. Silinmə dərdi yoxdur: CTE özü yox olur, sorğu bitən kimi. SQL kodunun strukturunda dərhal görünür, haradasa kənarda gizlənmir, müvəqqəti cədvəl kimi. Bu, sorğuları daha təmiz, sadə və başadüşülən edir, xüsusən də bir neçə mərhələli məlumat emalı edəndə. Həm də CTE-ləri birləşdirmək, iç-içə yazmaq və mürəkkəb məntiq qurmaq mümkündür — bu barədə sonra danışacağıq.

Belə struktur bir dəfəlik sorğular üçün, məlumat yalnız həmin sorğuda lazım olanda əladır. Amma nəticəni sistemin müxtəlif hissələrində bir neçə dəfə istifadə etmək və ya aralıq məlumatı bütün sessiya boyu saxlamaq istəyirsənsə — müvəqqəti cədvəllər daha etibarlı seçimdir. Xüsusilə də məlumat çoxdursa və yüksək performans lazımdırsa: belə hallarda fiziki müvəqqəti cədvəllər daha stabil və sürətli işləyir.

Hər şey tapşırıqdan asılıdır: CTE — lokal məlumat emalı üçün sürətli, zövqlü alətdir. Müvəqqəti cədvəl isə daha mürəkkəb və uzunmüddətli ssenarilər üçün işçi atdır.

Nümunə: Məlumatların aqreqasiyası

Tutaq ki, bizdə enrollments adlı cədvəl var və orada hansı tələbənin hansı kursa yazıldığı qeyd olunub. Hər kursda neçə tələbə olduğunu bilmək istəyirik, amma yalnız 5-dən çox tələbəsi olan kursları nəzərə alacağıq.

CTE ilə bunu belə edirik:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

Çoxsaylı CTE: Mərhələlərin qurulması

Bəs tapşırığı bir neçə mərhələyə bölmək lazımdırsa? Məsələn, əvvəlcə yüksək orta balı olan tələbələri seçmək, sonra onların kurslarını tapmaq? Asandır!

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
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, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

İndi sorğuda iki CTE var:

1. high_achievers əlaçıları tapır.
2. student_courses tələbələri kurslarla uyğunlaşdırır.

Nəticə — yüksək orta balı olan tələbələrin və onların kurslarının siyahısı.

CTE istifadə edərkən tipik səhvlər

Çox böyük məlumat. CTE-lər PostgreSQL-də yaddaşda materializasiya olunur. Əgər CTE-nin nəticəsi çox böyükdürsə, sorğunun icrasını ləngidə və ya yaddaş limitini aşa bilər.

Həddindən artıq istifadə. Elə yerlərdə CTE istifadə etmək ki, adi subquery daha sadə olardı, kodu qarışdıra bilər.

Unudulmuş indekslər. Əgər CTE-dəki məlumatlar böyük cədvəllərdən indeks olmadan çıxarılırsa, sorğunu ləngidəcək.