PL/pgSQL ilə analitik hesabatların qurulması

Analitik hesabatlar — qərar verməyə kömək edən sistemləşdirilmiş məlumat təqdimatıdır. Məsələn:

• Meneçerlər keçən ayın gəlirini görmək istəyir.
• Analitiklər bazarda trend axtarır.
• Developer-lər tətbiqin performansını monitorinq edir.

Təsəvvür elə ki, sən böyük bir restoranın baş aşpazısan. Hansı yeməklərin daha çox sifariş olunduğunu başa düşmək üçün sənə hesabat lazımdır. PostgreSQL burada sənin resept və sifariş bazandır, PL/pgSQL (prosedurlar) isə mətbəxdə sənə kömək edən asistantdır, sifarişlərin analizini avtomatlaşdırır.

Analitik hesabatların qurulmasının əsasları

Analitik hesabat — faydalı məlumat almaq üçün datanın aggregasiyası, filtrasiya, sort və sıralanması üçün alətdir. Adətən hesabatın strukturu belə mərhələləri əhatə edir:

1. Məlumatların hazırlanması: cədvəllərdən məlumat seçimi, filtrasiya və ilkin emal.
2. Məlumatların aggregasiyası: metriklərin hesablanması (orta çek, ümumi satış məbləği və s.).
3. Formatlaşdırma: məlumatların rahat oxunan formada düzülməsi.
4. Nəticələrin çıxışı: hesabatın istifadəçiyə təqdimatı və ya saxlamaq üçün cədvələ yazılması.

Bu mərhələlərin hər birini PL/pgSQL prosedurları ilə reallaşdırmaq olar.

Analitik hesabat üçün prosedurun yaradılması

Gəlin, analitik hesabat yaratmağın əsas nümunəsinə baxaq. Tutaq ki, bizdə orders adlı cədvəl var, burada sifarişlər haqqında məlumat saxlanılır:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount NUMERIC(10, 2)
);

Bizim tapşırığımız: göstərilən ay üçün satışların məbləği barədə hesabat yaratmaq. Yəni, biz görmək istəyirik:

• Ay.
• Bu ay üçün ümumi satış məbləği.

Prosedurun strukturu

Budur, bizim prosedur planımız (qorxma, PL/pgSQL-də proqramlaşdırma heç də qorxulu deyil):

1. Giriş parametri kimi ay qəbul edirik.
2. Bu ay üçün orders cədvəlindən məlumat seçirik.
3. Ümumi satış məbləğini hesablayırıq.
4. Nəticəni qaytarırıq.

Prosedurun reallaşdırılması

Kod nümunəsi:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- Göstərilən ay üçün məlumatları seçirik və aggregasiya edirik
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY 1;
END;
$$ LANGUAGE plpgsql;

1. Giriş parametri: p_month — tarixdir. Biz bunu datanı ay üzrə filtrləmək üçün istifadə edəcəyik.
2. RETURN QUERY: bu sehrli bir şeydir, prosedurdan birbaşa məlumat qaytarmağa imkan verir.
3. DATE_TRUNC: order_date-i ayın əvvəlinə yuvarlaqlaşdırmaq üçün istifadə olunur.
4. SUM: bütün sifarişlərin məbləğini hesablamaq üçün aggregat funksiyadır.
5. GROUP BY: məlumatları ay üzrə qruplaşdırırıq, çünki hesabatlar aylıq qurulur.

İndi funksiyamızı çağırmaq olar:

SELECT * FROM monthly_sales_report('2023-08-01');

Və nəticə belə olacaq:

Bu funksiya əsasdır. Gəlin bir az çətinləşdirək!

Daha mürəkkəb hesabatın yaradılması

İndi təsəvvür elə ki, satışları müştərilərə görə bölmək istəyirik. Yəni, hesabatımız göstərməlidir:

• Müştəri
• Ay
• Bu müştərinin ay ərzində sifarişlərinin məbləği

Proseduru dəyişirik

CREATE OR REPLACE FUNCTION customer_monthly_report(p_month DATE)
RETURNS TABLE (
    customer_id INT,
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.customer_id,
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY o.customer_id, DATE_TRUNC('month', o.order_date);
END;
$$ LANGUAGE plpgsql;

İndi prosedurun çağırılması:

SELECT * FROM customer_monthly_report('2023-08-01');

Və nəticə belə ola bilər:

Müvəqqəti cədvəllərdən istifadə

Bəzən mürəkkəb hesabatlar quranda müvəqqəti cədvəllərdən istifadə etmək faydalı olur. Məsələn, aralıq məlumatları emal etmək lazım olanda.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- Müvəqqəti cədvəl yaradırıq
    CREATE TEMP TABLE temp_sales AS
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', p_month)
    GROUP BY customer_id, DATE_TRUNC('month', order_date);

    -- Bu cədvəllə əlavə hesablamalar və ya manipulyasiyalar edirik
    -- Məsələn, ay üzrə top-3 müştərini çıxarırıq
    RAISE NOTICE 'Ay % üçün top-3 müştəri:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Müştəri: %, Məbləğ: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Bu halda temp_sales adlı müvəqqəti cədvəl aralıq nəticələri saxlamaq üçün istifadə olunur.

Faydalı məsləhətlər

1. Optimallaşdırma: məlumat seçimini sürətləndirmək üçün index-lərdən istifadə et.
2. Sıfıra bölmə səhvləri: böləni həmişə yoxla ki, hesabatı "öldürməyəsən".
3. Tarix formatlaşdırması: rahat çıxış üçün TO_CHAR kimi funksiyalardan istifadə et.

Ümid edirəm ki, çox darıxmadın! Qarşıda daha çətin və maraqlı məsələlər var, ona görə rahatlama!

1

Sorğu/viktorina

, səviyyə, dərs

Əlçatan deyil

Analitika üçün prosedurlar

Analitika üçün prosedurlar

Açıq