Mürəkkəb array sorğuları nümunələri: aqreqasiya, filtrasiya, sıralama

Bu gün array-lərdən sorğularda maksimum fayda necə almaq olar, onu öyrənəcəyik: dəyərləri qruplaşdırmaq, tərkibinə görə filtrasiya etmək və hətta array-lərin içində birbaşa sıralamaq. Bu, sadəcə nəzəriyyə deyil — belə fəndlər hesabatlarda, analitikada, fərdiləşdirmədə və bir çox real situasiyada qarşımıza çıxır. Prinsipi başa düşsən, hər şey asandır — indi də məhz bunu edəcəyik.

Array-lərlə datanın aqreqasiyası

Array-lərlə işləmək xüsusilə lazım olanda ortaya çıxır ki, datanı qruplaşdırasan. Bir neçə sətri almaqdansa — lazım olan dəyərləri bir səliqəli array-ə yığırıq. Bu, analizi asanlaşdırır, nəticələri daha kompakt edir və çox vaxt əlavə subquery-lərdən qurtarır. Gəlin, praktikada necə işlədiyinə baxaq.

Nümunə 1: array_agg() ilə datanı array-lərə qruplaşdırmaq

Bir qrupda bir neçə sətrin dəyərlərini bir array-ə toplamaq istəyəndə, köməyə array_agg() gəlir. Bu, aqreqasiya zamanı array-lərlə işləmək üçün ən faydalı funksiyadır.

-- Bizdə students adlı cədvəl var: id, name və course sahələri ilə
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- Bir neçə sətr əlavə edirik
INSERT INTO students (name, course) VALUES
('Alisa', 'Riyaziyyat'),
('Bob', 'Riyaziyyat'),
('Çarli', 'Fizika'),
('Deyv', 'Fizika'),
('Emma', 'Riyaziyyat');

-- Tələbələri kurslara görə array-lərə qruplaşdırırıq
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

Nəticə:

Dəyərləri array-lərə toplamaq rahatdır, əgər datanı asanlıqla parse etmək üçün formatda ötürmək istəyirsənsə, məsələn, JSON-da.

Nümunə 2: iç-içə array-lər yaratmaq

Bəs əgər bizdə başqa bir cədvəl də varsa və iki cədvəldən datanı array-lərə toplamaq istəyirik? Məsələn, courses adlı cədvəl müəllimlər haqqında məlumatla.

-- Müəllimlər üçün cədvəl yaradırıq
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- Data əlavə edirik
INSERT INTO courses (name, teacher) VALUES
('Riyaziyyat', 'Prof. Min'),
('Fizika', 'Prof. Peterson');

-- Array-lər yaratmaq üçün nested sorğu
SELECT
    c.name AS course_name,
    array_agg(s.name) AS students,
    c.teacher
FROM
    courses c
LEFT JOIN
    students s
ON
    c.name = s.course
GROUP BY
    c.name, c.teacher;

Nəticə:

İndi bizdə kursları, onların müəllimlərini və tələbələri array formatında göstərən rahat bir cədvəl var.

Array-lərlə datanın filtrasiyası

Array-lər özü-özlüyündə güclü alətdir, amma əsl sehr onda başlayır ki, biz array-lərə əsaslanaraq datanı filtrləməyi öyrənirik. Lazımdır ki, maraq siyahısında konkret söz olan istifadəçiləri seçəsən? Yaxud hər qiyməti müəyyən həddən yuxarı olan sifarişləri? Bütün bunları birbaşa SQL-də etmək olar — əlavə application tərəfli məntiq olmadan.

Nümunə 1: array elementlərinə görə sətrlərin filtrasiyası

Tutaq ki, biz elə sətrləri tapmaq istəyirik ki, array müəyyən bir dəyəri saxlayır, məsələn, riyaziyyat kursuna yazılmış tələbələri axtarırıq.

-- `ANY` ilə kurslara yazılmış tələbələri filtrləyirik
SELECT *
FROM students
WHERE course = ANY(ARRAY['Riyaziyyat', 'Fizika']);

Burada ANY bizə dəyərlərdən ibarət array göstərməyə imkan verir və sorğu course array-dəki ən azı bir dəyərə uyğun gələn sətrləri qaytarır.

Nümunə 2: Array-lərin kəsişməsini yoxlamaq

İndi isə fərz edək ki, bizdə student_interests adlı cədvəl var, burada tələbələrin maraqları array şəklində saxlanılır. Biz istəyirik ki, maraqları bizim kriteriyalara uyğun olan tələbələri tapaq.

-- Tələbələrin maraqları ilə cədvəl yaradırıq
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- Data əlavə edirik
INSERT INTO student_interests (name, interests) VALUES
('Alisa', ARRAY['proqramlaşdırma', 'musiqi']),
('Bob', ARRAY['idman', 'proqramlaşdırma']),
('Çarli', ARRAY['oxumaq', 'fotoqrafiya']),
('Emma', ARRAY['musiqi', 'idman']);

-- Proqramlaşdırma və ya musiqi ilə maraqlanan tələbələri axtarırıq
SELECT *
FROM student_interests
WHERE interests && ARRAY['proqramlaşdırma', 'musiqi'];

&& operatoru iki array-in kəsişməsini yoxlayır. Soldakı array-dən ən azı bir element sağdakına uyğun gəlirsə, sətir filtri keçir.

Nəticə:

Array-lərin sıralanması

Bəzən array-in içindəki dəyərlərin sırası vacib olur — xüsusilə array-i müxtəlif sətrlərdən topladıqda və ya datanı göstərməyə hazırlayanda. PostgreSQL elementləri birbaşa sorğuda sıralamağa imkan verir, əlavə işləməyə ehtiyac olmadan.

Nümunə 1: array-in içindəki dəyərlərin sıralanması

Bəzən array-in elementlərini sıralamaq lazımdır. Məsələn, tələbələrin maraq array-lərini əlifba sırası ilə düzəldək.

-- `array_sort()` funksiyası ilə array elementlərini sıralayırıq
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

Nəticə:

Nümunə 2: array uzunluğuna görə sətrlərin sıralanması

İndi isə fərz edək ki, tələbələri maraqlarının sayına görə sıralamaq istəyirik — ən çox maraqlananlardan ən "sıxıcı"lara doğru.

-- Array uzunluğuna görə sətrləri sıralayırıq
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

Nəticə:

Bu nümunədə bütün tələbələrin maraq sayı eynidir, amma oxşar sorğunu böyük cədvəllər üçün dəyişmək olar.