Çətin sorğuların oxunaqlılığını artırmaq üçün CTE-dən istifadə

Təsəvvür elə ki, sənə böyük bir SQL-sorğu yazmaq lazımdır və bu sorğu bir neçə bir-biri ilə əlaqəli əməliyyatları yerinə yetirir. Sadəcə bir-birinin içində bir sürü subquery yerləşdirə bilərsən, amma nəticədə kodun makarona oxşayacaq. Əsl SQL-labirint, hətta müəllif özü də orda itə bilər.

CTE — sənin xilasedici dairəndir! CTE çətin sorğunu məntiqi hissələrə bölməyə imkan verir, hər hissə isə ayrıca adlandırılmış bölmə kimi yazılır. Bu da sorğunu başadüşülən və dəstəklənən edir.

Müqayisə: Subquery vs CTE

İlk baxışda hər iki yanaşma eyni işi görür — kurs üzrə qiymətləri filtrləyir və hər tələbə üçün orta balı hesablayır. Amma diqqətlə bax: subquery versiyasında məntiq "mötərizənin içində gizlənib", CTE-də isə çölə çıxarılıb və filtered_grades kimi aydın ad alıb. İndi təsəvvür elə ki, belə ara mərhələlər iki yox, on dənədir!

Subquery:

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE:

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

10 fərqi tap. Əlbəttə, CTE oxumaq baxımından daha rahatdır!

Çətin sorğuları mərhələlərə bölmək üçün CTE-dən istifadə

CTE sorğunu addım-addım qurmağa imkan verir ki, hər mərhələdə nəticə maksimum başadüşülən olsun. Məsələn, əgər tələbələrin kurslar üzrə orta balını və müəllimlərini göstərən siyahı almaq istəyirsənsə, tapşırığı bir neçə hissəyə böl.

Nümunə:

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

Bu oxunaqlı deyil? Hətta bir ay sonra bu sorğuya qayıtsan belə, strukturu aydın qalacaq.

Böyük hesabat üçün bir neçə CTE-dən istifadə

Gəlin bir az daha çətin hesabat nümunəsinə baxaq. Tutaq ki, universitet üçün database var və ən uğurlu tələbələr, onların kursları və müəllimləri barədə hesabat hazırlamaq istəyirik. Plan belədir:

1. Əvvəlcə yüksək orta bala (90-dan yuxarı) sahib tələbələri tapırıq.
2. Sonra onları kurslarla uyğunlaşdırırıq.
3. Axırda müəllimlər barədə məlumat əlavə edirik.

Bir neçə CTE ilə sorğu:

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, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

Bu sorğunun gözəlliyi ondadır ki, tapşırığın hər hissəsini ayrıca, məntiqi tamamlanmış blok kimi yazmışıq. Hansı tələbə uğur qazanıb bilmək istəyirsən? high_achievers-ə bax. Onların kurslarla əlaqəsi maraqlıdır? student_courses-da taparsan. Lazım olan müəllimlər? Hamısı teachers-dadır. Belə yanaşma kodun dəstəyini və dəyişməsini xeyli asanlaşdırır.

Çətin hesablamaları mərhələlərə bölmək

Bəzən sorğularında çətin hesablamalar və ya filtrasiya olur. Hər şeyi bir uzun sorğuya yığmaq əvəzinə, onu bir neçə CTE-yə böl.

Nümunə:

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

Burda əvvəlcə kurslar üzrə statistik məlumatları course_stats-da toplayırıq, sonra populyar kursları popular_courses-da filtr edirik, axırda isə kurs cədvəli ilə birləşdiririk. Bu üsul ara mərhələləri ayırmağa və sorğunu başa düşməyi asanlaşdırmağa kömək edir.

CTE nə vaxt əvəzolunmaz olur?

Budur, CTE-nin xüsusilə faydalı olduğu bir neçə vəziyyət:

1. Analitika və hesabatlar. Məsələn, qruplar üzrə filtrasiya ilə çətin göstəricilərin hesablanması.
2. Hierarxik strukturlarla iş. Kateqoriya ağacı və ya təşkilati struktur qurmaq üçün rekursiv CTE-lər.
3. Məlumatların təkrar istifadəsi. Məsələn, eyni seçimi sorğunun müxtəlif mərhələlərində istifadə edirsənsə.

CTE istifadə edəndə tipik səhvlər

Təbii ki, hər güclü alət kimi, CTE-nin də gizli tələləri var.

Artıq məlumat materializasiyası. PostgreSQL-də CTE-lər default olaraq "materializasiya olunur", yəni nəticə hesablanıb müvəqqəti saxlanılır. Əgər məlumat çoxdursa, bu icranı ləngidə bilər. Bunun qarşısını almaq üçün index-lərdən istifadə elə və lazım olan minimum sütunları seçməyə çalış.

Yanlış join-lar. Bəzən bir neçə CTE ilə çətin sorğular optimizasiya baxımından çətin olur. Sorğunu həmişə EXPLAIN və ya EXPLAIN ANALYZE ilə yoxla.

CTE-dən həddindən artıq istifadə. Əgər CTE-lərin çox uzun və dolaşıq olursa, bu o deməkdir ki, sorğunu bir neçə ayrıca əməliyyata bölmək lazımdır.