HAVING-də subquery-lərin tətbiqi ilə aqreqasiya olunmuş datanın filtrasiyası

Bəzən bizə sadəcə datanı qruplaşdırıb nəticəni filtrləmək yox, əlavə məntiqə əsaslanaraq bunu etmək lazımdır — məsələn, tələbələrin qrup üzrə orta qiymətini hansısa xarici kriteriya ilə müqayisə etmək. Burda səhnəyə HAVING və subquery-lər çıxır — bu, SQL sorğusunda birbaşa daha ağıllı qərarlar verməyə kömək edən güclü bir alətdir.

HAVING-i yada salaq

Gəlin, HAVING ilə birlikdə istifadə olunan subquery-lərə fokuslanaq ki, aqreqasiya olunmuş dəyərlər səviyyəsində datanı filtrləyə bilək. Niyə? WHERE ayrı-ayrı sətrləri filtrləyirsə, HAVING artıq qruplaşdırılmış dataya tətbiq olunur — bu, analiz üçün başqa bir səviyyədir və imkanlarını genişləndirir.

Subquery-lərlə HAVING-in birləşməsinə keçməzdən əvvəl, gəlin, HAVING nədir və WHERE-dən nə ilə fərqlənir, bir az xatırlayaq.

• WHERE sətrləri qruplaşdırmadan əvvəl (GROUP BY) filtrləyir.
• HAVING isə datanı aqreqasiyadan sonra filtrləyir, yəni data artıq qruplaşdırılıb.

Təsəvvür elə, tələbələri və onların qiymətlərini analiz edirsən. WHERE ilə müəyyən minimal qiyməti olan tələbələri çıxara bilərsən, amma HAVING sənə imkan verir ki, bütöv tələbə qruplarını onların orta və ya maksimal balına görə çıxarasan.

Data nümunəsi

Budur, tələbələrin nümunə cədvəli:

students cədvəli:

HAVING istifadəsi nümunəsi (subquery olmadan)

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > 75;

Nəticə:

"History" fakültəsi seçilmədi, çünki onun orta balı 75-dən aşağıdır. Hər şey sadədir, hə? İndi isə bir az subquery magiyası əlavə edək. Növbəti nümunədə, məsələn, bütün fakültələr üzrə ümumi orta bal ilə müqayisə edə bilərik.

HAVING-də subquery-lər

HAVING-də subquery-lər — aqreqasiya olunmuş datanı filtrləmək üçün əla elastiklik verir. Onlar sənə imkan verir ki, orta qiymət və ya maksimum kimi aqreqatları bazanın başqa hissələrindən hesablanmış dəyərlərlə müqayisə edəsən. Sadə dillə desək, yoxlaya bilərsən: "Bizim nəticə ümumidən yaxşıdırmı?"

Nümunə: fakültələri orta qiymətə görə filtrləmək

Tutaq ki, elə fakültələri tapmaq istəyirsən ki, orda tələbələr digərlərindən yaxşı oxuyur — yəni fakültənin orta balı universitet üzrə ortadan yüksəkdir.

Budur, datamız:

students cədvəli:

Əvvəlcə bütün tələbələr üzrə orta qiyməti tapırıq:

SELECT AVG(grade) AS university_avg
FROM students;

İndi isə HAVING-də subquery istifadə edirik:

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > (SELECT AVG(grade) FROM students);

Nəticə:

Bura nə baş verir?

1. Subquery (SELECT AVG(grade) FROM students) ümumi orta qiyməti hesablayır — bu halda 77-dir.
2. Əsas sorğu tələbələri fakültələrə görə qruplaşdırır və hər biri üçün orta balı hesablayır.
3. HAVING fakültənin orta balını ümumi orta ilə müqayisə edir və yalnız nəticəsi yüksək olan fakültələri buraxır.

WHERE və HAVING istifadəsinin müqayisəsi

Fərqi başa düşmək üçün təsəvvür elə ki, sən yalnız orta qiymətdən yüksək balı olan tələbələri seçmək istəyirsən. Bunu yalnız WHERE ilə etmək olar:

SELECT name, grade
FROM students
WHERE grade > (SELECT AVG(grade) FROM students);

Nəticə (əvvəlki cədvəldən):

Amma əgər sən baxmaq istəyirsən ki, hansı fakültələrdə tələbələrin orta balı universitet üzrə ortadan yüksəkdir, onda HAVING olmadan keçinmək olmur — çünki sən sətrləri yox, qrupları filtrləyirsən:

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > (SELECT AVG(grade) FROM students);

Nəticə:

Qısa olaraq:

• WHERE qruplaşdırmadan əvvəl ayrı-ayrı sətrlərlə işləyir.
• HAVING isə qruplar aqreqasiya olunduqdan sonra onları filtrləyir.

Nümunə: bir neçə aqreqatla işləmək

Gəlin, başqa bir vəziyyətə baxaq. Tutaq ki, bizdə students cədvəli var və orda tələbələrin qiymətləri və fakültələri saxlanılır:

students cədvəli:

İndi isə fakültələri tapmaq istəyirik ki:

1. Tələbələrin orta qiyməti universitet üzrə ortadan yüksəkdir.
2. Fakültədə maksimal qiymət 90-dan yuxarıdır.

Bunun üçün belə bir sorğu yazırıq:

SELECT department, AVG(grade) AS avg_grade, MAX(grade) AS max_grade
FROM students
GROUP BY department
HAVING AVG(grade) > ( SELECT AVG(grade) FROM students )
   AND MAX(grade) > 90;

Bu sorğuda nə baş verir:

• AVG(grade) > (SELECT AVG(grade) FROM students) — yoxlayırıq ki, fakültə orta hesabla digərlərindən güclüdür.
• MAX(grade) > 90 — yəni orda kimsə imtahanı əla verib.

Nəticə:

"Math" fakültəsi yeganədir ki, həm orta balı ümumidən yüksəkdir, həm də 90-dan yuxarı qiymət alan tələbəsi var.

Nümunə: minimal fərqlə qrupların seçilməsi

Tutaq ki, sən tapmaq istəyirsən ki, hansı qruplarda tələbələrin maksimal və minimal qiymətləri arasındakı fərq universitet üzrə fərqdən azdır.

Budur, işləyəcəyimiz students cədvəli:

Tapşırığı mərhələlərə bölək:

1. Əvvəlcə universitet üzrə maksimum-minimum fərqini hesablayırıq:

   SELECT MAX(grade) - MIN(grade) AS range_university
   FROM students;
   

2. İndi əsas sorğunu yazırıq və onu bu subquery ilə birləşdiririk:

SELECT department, MAX(grade) - MIN(grade) AS range_department
FROM students
GROUP BY department
HAVING (MAX(grade) - MIN(grade)) < ( SELECT MAX(grade) - MIN(grade) FROM students );

Sorğunun nəticəsi:

"Physics" və "Math" qruplarında qiymətlər daha stabildir — onların fərqi universitet üzrə fərqdən azdır.

HAVING və subquery-lərlə sorğuların optimizasiyası

Yadda saxla ki, iç-içə subquery-lər performansa ciddi təsir edə bilər, xüsusən böyük bazalarda. Bir neçə məsləhət:

İndekslərdən istifadə et. Əgər subquery WHERE və ya JOIN-da iştirak edən sütun üzrə işləyirsə, həmin sütunda indeks olduğuna əmin ol.

Data daşqınından qaç. Əgər subquery çoxlu aralıq nəticə qaytarırsa, onu mərhələlərə böl və ya müvəqqəti cədvəllərdən istifadə et.

EXPLAIN ilə sorğunu profil et. Həmişə yoxla ki, PostgreSQL sənin sorğunu necə icra edir. Əgər görürsən ki, subquery dəfələrlə işləyir, optimizasiya barədə düşün.

CTE ilə müqayisə et. Bəzi hallarda WITH (Common Table Expressions) istifadə etmək həm daha sürətli, həm də oxunaqlı olur. Amma bu barədə növbəti leksiyalarda :P

Subquery-lər, HAVING və GROUP BY-nin kombinasiyası

HAVING-də subquery-lərlə daha mürəkkəb filtrlər qurmaq olur, xüsusən eyni vaxtda aqreqatları, orta dəyərləri və başqa metrikləri nəzərə almaq lazım olanda. Bütün bunlar real datada maraqlı insight-lar tapmağa kömək edir.

Nümunə: fakültələri orta bal və tələbə sayı ilə müqayisə etmək

Tutaq ki, sən fakültələri seçmək istəyirsən ki:

1. Orta bal universitet üzrə ortadan yüksəkdir.
2. Tələbə sayı ən aşağı orta balı olan fakültədən çoxdur.

Budur, ilkin students cədvəli:

Sorğu:

SELECT department, AVG(grade) AS avg_grade, COUNT(*) AS student_count
FROM students
GROUP BY department
HAVING AVG(grade) > ( SELECT AVG(grade) FROM students )
   AND COUNT(*) > (
       SELECT COUNT(*)
       FROM students
       GROUP BY department
       ORDER BY AVG(grade)
       LIMIT 1
   );

Bu sorğu HAVING və GROUP BY-da subquery-lərin birləşməsinin imkanlarını göstərir və birdən çox kriteriya üzrə analiz aparmağa imkan verir. Nəticə:

History fakültəsi seçilmədi, çünki onun həm orta balı ən aşağıdır, həm də tələbə sayı ən azdır. Physics və Math — hər ikisi həm bal, həm də say baxımından ortadan yuxarıdır.

Tipik səhvlər və onların qarşısını almaq

NULL ilə səhv. Əgər datada NULL varsa, HAVING ilə subquery-lər gözlənilməz nəticə verə bilər. Belə hallarda COALESCE istifadə et:

SELECT AVG(grade)
FROM students 
WHERE grade IS NOT NULL;

Subquery-də artıq data. Əgər subquery artıq nəticə qaytarırsa, bu performansa təsir edəcək. Həmişə subquery şərtlərini dəqiqləşdir.

İcra ardıcıllığını səhv başa düşmək. Yadda saxla ki, HAVING qruplaşdırmadan sonra işləyir, amma subquery-lər əsas sorğudan əvvəl işləyə bilər.

İndekslərin olmaması. Əgər subquery-də iştirak edən sütunlarda indeks yoxdursa, sorğunun icrası xeyli yavaşlayacaq.

HAVING-də subquery-lər sənə aqreqat səviyyəsində data analizində bir çox imkanlar açır. Qrupları mürəkkəb şərtlərlə filtrləyə, nəticələri qruplar arasında müqayisə edə və mürəkkəb analitik sorğular yaza bilərsən. Təbriklər, artıq bu bilikləri real layihələrdə tətbiq etməyə hazırsan!