Nümunə: Son 3 ay üzrə sifarişlər üçün orta çekin hesablanması

Bu mühazirədə maraqlı bir praktiki nümunəyə baxacağıq.

Orta çek — bu, bir müştərinin bir alış üçün orta hesabla nə qədər pul xərclədiyini göstərən metrikadır. Bu, əsas biznes metriklərindən biridir və imkan verir ki:

• alıcı gücündəki dəyişiklikləri analiz edəsən,
• satış trendlərini aşkar edəsən,
• marketinq kampaniyalarının effektivliyini qiymətləndirəsən.

Tapşırığın qoyuluşu

Təsəvvür elə ki, bizdə orders adlı cədvəl olan bir verilənlər bazası var və burada sifarişlər saxlanılır. Məqsədimiz:

1. Son üç ayda verilmiş sifarişlər üçün orta çeki hesablamaq.
2. Bu hesablamanı prosedur vasitəsilə avtomatlaşdırmaq.
3. Nəticəni ayrıca bir cədvəldə saxlamaq ki, sonradan analiz edə bilək.

Verilənlər bazamızı genişləndiririk: orders cədvəlinin strukturu

Əvvəlcə əmin olaq ki, lazım olan məlumatları saxlayan bir cədvəlimiz var. orders cədvəli təxminən belə görünə bilə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
);

• order_id — sifarişin unikal identifikatoru.
• customer_id — sifarişi edən müştəri.
• order_date — sifarişin verildiyi tarix.
• total_amount — sifarişin ümumi məbləği.

Nümunə üçün cədvələ bir neçə qeyd əlavə edək ki, işləməyə materialımız olsun:

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

Orta çekin əl ilə hesablanması

Avtomatlaşdırmaya keçməzdən əvvəl, gəlin son 3 ay üçün orta çeki hesablayan əsas bir sorğu yazaq. Burada cari tarixi (CURRENT_DATE) və AVG() funksiyasını istifadə edəcəyik.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

Burada nə baş verir:

• AVG(total_amount) — total_amount üçün orta dəyəri hesablayan aqreqat funksiyadır.
• CURRENT_DATE - INTERVAL '3 months' — son üç ayda verilmiş sifarişləri seçir.
• ROUND(..., 2) — nəticəni iki onluq rəqəmə qədər yuvarlaqlaşdırır.

Sorğunun nəticəsi təxminən belə olacaq:

Prosedur ilə avtomatlaşdırma

İndi məqsədimiz — bu hesablamanı avtomatik edən bir prosedur yaratmaq və nəticəni ayrıca bir cədvəldə loglamaqdır. Əvvəlcə analitika logları üçün cədvəl yaradaq.

log_analytics cədvəlinin yaradılması

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — qeydiyyatın tarixi və vaxtı.
• metric_name — metrik adı (bizim halda "averagecheck_3_months").
• metric_value — hesablanmış metrik dəyəri.

Prosedurun yaradılması

İndi belə bir prosedur yazaq ki:

1. Son üç ay üçün orta çeki hesablayır.
2. Nəticəni log_analytics cədvəlinə yazır.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- Addım 1: Orta çekin hesablanması
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- Addım 2: Nəticənin loglanması
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Debug üçün məlumat çıxışı (istəyə bağlı)
    RAISE NOTICE 'Orta çek: %', avg_check;
END;
$$ LANGUAGE plpgsql;

İndi bu funksiyanı çağırmaqla nəticə avtomatik olaraq log_analytics cədvəlinə yazılacaq:

SELECT calculate_average_check();

Task scheduler ilə avtomatlaşdırma

Keçən mühazirədə artıq task scheduler quraşdırmışdıq. Əgər Linux-da işləyirsənsə — bu pg_cron extension idi; Windows və ya macOS-da isə yəqin ki, sistem scheduler-i (cron və ya Task Scheduler) ilə işləmisən. İndi hər şey hazırdırsa, prosedurumuzu cədvələ bağlayaq.

Əgər Linux-dasan və pg_cron istifadə edirsənsə, bu extension-un lazım olan bazada aktiv olduğuna əmin ol:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Xatırlatma: pg_cron-un özü və shared_preload_libraries parametrinin sazlanması keçən dərsdə izah olunmuşdu.)

İndi funksiyamızın — məsələn, hər gecə saat 00:00-da — icrasını planlaşdıra bilərik:

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

İzah:

• 'daily_avg_check' — taskın adı;
• '0 0 * * *' — hər gün saat 00:00-da işləmək üçün cron ifadəsi;
• $$ içindəki komanda — icra olunacaq SQL-dir.

Əgər Windows və ya macOS-dasan, pg_cron bu sistemlərdə işləmir (Windows-da ümumiyyətlə, macOS-da isə əl ilə yığmaq lazımdır). Amma artıq sistem scheduler-i sazlamısan — sadəcə SQL faylını qoşmaq qalır.

1. Sorğu ilə fayl yarat:

   echo "SELECT calculate_average_check();" > /path/to/script.sql
   

2. psql ilə faylı cədvəl üzrə icra et:

   • Linux/macOS-da:

       0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

     (crontab -e ilə əlavə olunur)
   • Windows Task Scheduler-də:
     • psql.exe yolunu göstər.
     • Argumentlərdə:
       -U postgres -d your_database -f "C:\path\to\script.sql"

Beləliklə, hansı sistemdə işləməyindən asılı olmayaraq, prosedur avtomatik və müntəzəm olaraq orta çeki log_analytics cədvəlinə yazacaq. Hansı üsuldan istifadə etdiyinə əmin deyilsənsə, əvvəlki mühazirəyə bax — orada müxtəlif platformalar üçün scheduler-in quraşdırılması və sazlanması izah olunub.

Nəticələrin yoxlanması və analizi

Gəlin baxaq, nə alındı. log_analytics cədvəlindən məlumatları soruşaq:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Nümunə nəticə:

İndi artıq bütün orta çek hesablamalarının logu var! Bu məlumatları hesabatlar üçün və ya metrikdəki dəyişiklikləri zamanla analiz etmək üçün istifadə edə bilərsən.

Tez-tez rast gəlinən səhvlər və onlardan necə qaçmaq olar

Orta çekin hesablanması üçün analitik prosedurlarla işləyərkən bir neçə tipik səhvlə qarşılaşmaq olar.

Bunlardan biri — boş nəticələri nəzərə almamaqdı. Əgər son üç ayda sifariş olmayıbsa, AVG() funksiyası NULL qaytaracaq və bu, loglama zamanı problem yarada bilər. Bunun qarşısını almaq üçün COALESCE() istifadə edə bilərsən:

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

Başqa bir səhv — orders cədvəlində səhv məlumatların olmasıdır. Məsələn, mənfi sifariş məbləğləri və ya düzgün olmayan tarixlər. Məlumatları müntəzəm yoxlamaq və ya verilənlər bazası səviyyəsində məhdudiyyətlər əlavə etmək tövsiyə olunur (məsələn, CHECK (total_amount > 0)).

Təbrik edirəm, artıq səndə tam işlək bir prosedur var ki, son üç ay üçün orta çeki avtomatik hesablayır və nəticəni sonrakı analiz üçün saxlayır. Bu, PostgreSQL və PL/pgSQL-in analitik tapşırıqları necə avtomatlaşdıra biləcəyinə dair bir çox nümunədən sadəcə biridir. Növbəti mühazirədə daha mürəkkəb analitik ssenariləri öyrənəcəyik. Görüşənədək!