Sadə CTE-lərlə data hazırlığı: nümunələr və real case-lər

Sadə CTE-lərlə data hazırlığı: nümunələr və real case-lər

Görünür, artıq CTE-nin əsaslarını öyrənmisən və bəlkə də WITH yazmaq sənin üçün artıq avtomatik bir şeyə çevrilib. Bu gün bir az daha dərin gedək — və baxaq, CTE-ləri real situasiyalarda data hazırlamaq üçün necə istifadə etmək olar. Təsəvvür elə ki, report və ya çətin bir SQL sorğusu yazırsan: əvvəlcə inqrediyentləri düzürsən — sonra isə dadlı analitik “şorba” bişirirsən.

CTE burada aralıq addımlar üçün əla alətdir: filtrasiya, sayma, aqreqasiya, orta dəyərlərin hesablanması — data hazırlığı üçün lazım olan hər şey. Çətin sorğunu başa düşülən məntiqi bloklara bölə bilərsən, hər blok bir işi görür: lazım olan sətrləri seçir, orta hesablayır və ya son seçmə üçün datanı hazırlayır. Bu, kodun oxunmasını asanlaşdırır, təkrarlanan hissələrdən qurtarır və əgər ehtiyac yoxdursa, temporary table-lardan istifadə etməyə ehtiyac qalmır.

CTE yanaşması xüsusilə report hazırlayanda, çətin filtrasiya quranda və ya datanı “təmizləmək” istəyəndə çox faydalıdır. Bu mənada CTE təkcə texniki fənd deyil, tam bir strategiyadır — addım-addım məntiq qurmaq və nə baş verdiyinə nəzarəti itirməmək üçün.

Hazırsan? İndi nümunələrə keçirik.

CTE ilə datanın filtrasiyası

CTE — ümumi table-dan lazım olan datanı “çıxarmaq” üçün əla üsuldur, beləliklə, sonra artıq yalnız lazım olanla işləyirsən. Böyük nested query-lər yazmaq əvəzinə, əvvəlcə datanı filtr edirsən, bu addıma ad verirsən — və sonra nəticə ilə adi table kimi rahat işləyirsən.

Təsəvvür elə, bizdə students adlı table var, burada tələbələrin qiymətləri saxlanılır:

students cədvəli

Tutaq ki, sən bütün qiyməti 85-dən yuxarı olanları seçmək istəyirsən. CTE ilə bunu çox rahat eləmək olur:

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

Nəticə:

Bunun nəyi rahatdır?

Lazım olan sətrləri əvvəlcədən seçdin və bu addıma ad verdin — excellent_students. İndi bu nəticəni başqa table ilə join edə bilərsən, əlavə filtrasiya və ya orta qiymət hesablaya bilərsən. Hər şey oxunaqlıdır, sadədir və qarışmır, xüsusilə sorğu böyük olanda.

CTE ilə datanın aqreqasiyası

İndi isə keçək elə bir case-ə ki, burada sətrlərin sayını və ya orta dəyərləri hesablamaq lazımdır. Məsələn, bizdə enrollments adlı table var, burada hansı tələbənin hansı kursa yazıldığı saxlanılır.

enrollments cədvəli

Biz hər kursda neçə tələbə olduğunu bilmək istəyirik.

Sorğu nümunəsi:

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

Nəticə:

Burada vacibdir:

• Biz course_id üzrə datanı qruplaşdırdıq və hər kurs üçün tələbə sayını hesablamaq üçün COUNT istifadə etdik.
• course_enrollments table-ı artıq bu informasiyanı saxlayır və onu növbəti analiz üçün istifadə edə bilərsən.

Report üçün datanın hazırlanması

Əgər sənə bir neçə addımdan ibarət data işləmə əsasında detallı report toplamaq lazımdırsa, CTE əsl tapıntıdır. Bütün məntiqi başa düşülən bloklara bölməyə imkan verir və əlavə temporary table yaratmağa ehtiyac olmur. Təsəvvür elə, səndə grades adlı qiymətlər və students adlı tələbə informasiyası olan table-lar var. Sənə elə report lazımdır ki, orada yalnız orta balı 80-dən yuxarı olan tələbələr olsun.

grades cədvəli

students cədvəli

Böyük nested query əvəzinə hər şeyi addım-addım yığmaq olar:

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

Birinci addımda (avg_grades) hər tələbə üçün orta balı hesablamaq və dərhal yalnız yaxşı nəticə göstərənləri — 80-dən yuxarı olanları seçmək olur. İkinci addımda (students_with_grades) bu datanı students table-ı ilə birləşdiririk ki, ad və soyadlar da olsun. Nəticədə final SELECT səliqəli table qaytarır, onu birbaşa reporta əlavə etmək olar — hər şey artıq hesablanıb, filtr olunub və gözəl şəkildə düzülüb.

Nəticə:

Məhz bu yanaşma CTE-ni rahat edir: sən məntiq və struktura fokuslana bilirsən, əlavə işlərə — məsələn, temporary table yaratmaq və silmək kimi — vaxt itirmirsən.

Çətin metriklərin hesablanması

Bəzən bir sorğuda müxtəlif dataları birləşdirmək lazım olur. Məsələn, hər kurs üçün hesablamaq istəyirik:

1. Tələbə sayı.
2. Kurs üzrə orta bal.

Sorğu nümunəsi:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

Qaçınılmalı səhvlər

CTE ilə işləyəndə asanlıqla qarışıq salmaq və bir neçə tipik səhv etmək olar.

Birinci — artıq materializasiya. Əgər çoxlu CTE yaradırsansa, PostgreSQL onların nəticələrini temporary table kimi saxlayır, hətta bir dəfə lazım olsa belə. Nəticədə sorğu istədiyindən daha yavaş işləyə bilər.

İkinci səhv — filtrin düzgün tətbiq olunmaması. Əgər filtrlər səhv ardıcıllıqla və ya müxtəlif mərhələlərdə fərqli tətbiq olunursa, nəticə gözlədiyindən fərqli ola bilər. Məsələn, vacib datanı təsadüfən çox tez silə bilərsən.

Ona görə də CTE ən yaxşısı datanın bir neçə ardıcıl transformasiyadan keçdiyi yerdə istifadə olunur — məhz burada bu alət bütün üstünlüklərini göstərir və təmiz, başa düşülən və effektiv kod yazmağa kömək edir.