Bu dərsdə biz sorğu magiyasının əslini göstərəcəyik! Bir neçə CTE istifadə edərək nümunələr yaradacağıq ki, onların bir-biri ilə necə inteqrasiya olunduğunu və mürəkkəb, çoxmərhələli sorğular qurmaq üçün necə işlədiyini göstərək. Bu nümunələr real həyatda, xüsusilə çətin analitik tapşırıqlarda çox işinə yarayacaq.
Nümunə 1: Tələbələrin işinin analizi
Təsəvvür elə ki, universitetin bazasında üç cədvəl var:
students cədvəli:
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name TEXT NOT NULL
);
grades cədvəli:
CREATE TABLE grades (
grade_id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(student_id),
course_id INT NOT NULL,
grade NUMERIC(3, 1) NOT NULL
);
courses cədvəli:
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name TEXT NOT NULL
);
Tapşırıq: Orta balı 85-dən yüksək olan tələbələrin siyahısını, onların orta balı və iştirak etdikləri kursların adları ilə birlikdə əldə et.
Sorğu:
WITH high_achievers AS (
-- Yüksək orta bala sahib tələbələri tapırıq
SELECT
student_id,
AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
HAVING AVG(grade) > 85
),
student_courses AS (
-- Hər tələbənin qeydiyyatda olduğu kursları tapırıq
SELECT
s.student_id,
c.course_name
FROM grades g
JOIN courses c ON g.course_id = c.course_id
JOIN students s ON g.student_id = s.student_id
)
-- Nəticələri birləşdiririk
SELECT
s.student_name,
ha.avg_grade,
sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;
İzah:
- Birinci CTE-də (
high_achievers) hər tələbə üçün orta balı hesablayırıq və balı 85-dən yuxarı olanları seçirik. - İkinci CTE-də (
student_courses) tələbələri onların kursları ilə uyğunlaşdırırıq. - Əsas sorğuda hər iki CTE-dən məlumatları birləşdiririk ki, tələbələrin siyahısı, orta balı və iştirak etdikləri kurslar alınsın.
Nümunə 2: Onlayn mağaza üçün satış hesabatı
Təsəvvür elə ki, onlayn mağazada işləyirik və aşağıdakı cədvəllərimiz var:
orders cədvəli (sifarişlər):
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
customers cədvəli (müştərilər):
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL
);
order_items cədvəli (sifarişdəki məhsullar):
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_id INT NOT NULL,
quantity INT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
Tapşırıq: Hər bir müştəri üçün aşağıdakıları göstərən hesabat qur:
- Sifarişlərin ümumi sayı.
- Son ay ərzində bütün sifarişlərin ümumi məbləği.
- Onun aldığı bütün unikal məhsulların siyahısı.
Sorğu:
WITH recent_orders AS (
-- Son ayın sifarişlərini seçirik
SELECT
order_id,
customer_id,
total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
-- Hər müştəri üçün sifarişlərin ümumi sayını və məbləğini hesablayırıq
SELECT
ro.customer_id,
COUNT(ro.order_id) AS total_orders,
SUM(ro.total_amount) AS total_spent
FROM recent_orders ro
GROUP BY ro.customer_id
),
customer_products AS (
-- Hər müştərinin aldığı unikal məhsulları seçirik
SELECT DISTINCT
ro.customer_id,
oi.product_id
FROM recent_orders ro
JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Nəticələri birləşdiririk
SELECT
c.customer_name,
cs.total_orders,
cs.total_spent,
ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;
İzah:
- Birinci CTE-də (
recent_orders) son ayın sifarişlərini seçirik. - İkinci CTE-də (
customer_summary) hər müştəri üçün sifarişlərin ümumi sayını və məbləğini hesablayırıq. - Üçüncü CTE-də (
customer_products) hər müştərinin aldığı unikal məhsulları tapırıq. - Son sorğuda məlumatları birləşdiririk və
ARRAY_AGG()ilə unikal məhsulların siyahısını düzəldirik.
Nümunə 3: İşçilərin iyerarxiyasının analizi
Bizdə işçilərin cədvəli var:
employees cədvəli:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name TEXT NOT NULL,
manager_id INT NULL
);
Tapşırıq: Baş direktordan başlayaraq işçilərin iyerarxiyasını qur. Hər işçinin iyerarxiyadakı səviyyəsini göstər.
Sorğu:
WITH RECURSIVE employee_hierarchy AS (
-- Meneceri olmayan işçilərdən başlayırıq (baş direktor)
SELECT
employee_id,
employee_name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Cari səviyyənin bütün tabeçiliyində olanları əlavə edirik
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- İyerarxiyanı göstəririk
SELECT
employee_id,
employee_name,
manager_id,
level
FROM employee_hierarchy
ORDER BY level, employee_id;
- Rekursiv sorğuda baş direktordan (meneceri olmayan işçilər) başlayırıq.
- Hər addımda cari səviyyənin tabeçiliyində olanları əlavə edirik və
level-i bir vahid artırırıq. - Əsas sorğuda bütün iyerarxiyanı səviyyə və işçi id-sinə görə sıralayırıq.
Faydalı məsləhətlər və tipik səhvlər
- Çoxlu CTE: Əgər alt-sorğu ilə etmək mümkündürsə, CTE istifadə etmə. CTE bəzən məlumatların materializasiyasına görə performansı azalda bilər.
- CTE adları: CTE-lərə aydın və qısa adlar ver ki, sorğular oxunaqlı qalsın.
- İcra ardıcıllığı: Unutma ki, CTE-lər elan olunduqları ardıcıllıqla icra olunur.
- Məlumatların qruplaşdırılması:
GROUP BY-ı yalnız həqiqətən lazım olanda istifadə et, artıq əməliyyatlardan qaçmaq üçün.
Bütün bu nümunələr göstərir ki, CTE-lər mürəkkəb tapşırıqları mərhələlərə bölmək, sorğuların oxunaqlığını və dəstəyini yaxşılaşdırmaq üçün əladır. İndi sən PostgreSQL ilə mürəkkəb analitik tapşırıqları həll etmək üçün alətlərlə silahlanmısan!
GO TO FULL VERSION