Zaman məlumatları üçün pəncərə funksiyaları: LEAD(), LAG()

İndi bizim tapşırığımız — bir az da irəli gedib, pəncərə funksiyalarından zaman məlumatlarının analizi üçün istifadə etməyi öyrənməkdir. Hazırsan? Ümid edirəm, bir fincan kofe götürmüsən, çünki maraqlı olacaq.

Beləliklə, həmişəki kimi, əsas sualdan başlayırıq: bizə pəncərə funksiyaları (LEAD(), LAG()) niyə lazımdır? Təsəvvür elə ki, sən zaman məlumatları ilə işləyirsən, fərqi yoxdur, event log-lar, iş saatları, zaman sıraları və ya hər hansı bir şey, harada ki, hadisələrin ardıcıllığı vacibdir.

Məsələn, sən istəyirsən:

• Hazırkı hadisədən sonra növbəti hadisənin nə vaxt baş verdiyini bilmək.
• Hazırkı hadisə ilə əvvəlki hadisə arasındakı vaxt fərqini hesablamaq.
• Məlumatları sortlayıb, qeydlər arasındakı fərqi hesablamaq.

Burada səhnəyə iki əla funksiya çıxır: LEAD() və LAG(). Bunlar müəyyən bir pəncərə daxilində əvvəlki və ya növbəti sətrin məlumatını götürməyə imkan verir. Elə bil sehrli bir kitabın var, növbəti səhifədə nə olduğunu baxa bilirsən, amma indiki səhifəni çevirmədən.

LEAD() və LAG(): sintaksis və əsas prinsiplər

Hər iki funksiya oxşar sintaksisdən istifadə edir:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — hansı sütundan məlumat götürmək istəyirik.
• offset (opsional) — hazırkı sətrə nisbətən neçə sətr irəli/geri baxmaq. Default olaraq 1-dir.
• default_value (opsional) — əgər lazım olan offset-də sətir yoxdursa (məsələn, sonuncu sətrə çatanda), qaytarılacaq dəyər.
• OVER() — burada "pəncərə" təyin olunur, yəni hesablama hansı çərçivədə aparılacaq. Ən çox ORDER BY istifadə olunur, bəzən PARTITION BY ilə qruplara bölmək olur.

Nümunə: Sadə LEAD() və LAG()

Gəlin, eksperiment üçün sadə bir events cədvəli yaradaq:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Event A', '2023-10-01 10:00:00'),
    ('Event B', '2023-10-01 11:00:00'),
    ('Event C', '2023-10-01 12:00:00'),
    ('Event D', '2023-10-01 13:00:00');

İndi isə hər bir hadisəyə görə əvvəlki və növbəti hadisənin nə vaxt baş verdiyinə baxmaq istəyirik:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS previous_event,
    LEAD(event_date) OVER (ORDER BY event_date) AS next_event
FROM events;

Nəticə belə olacaq:

Burada LAG() əvvəlki sətrin məlumatını götürür, LEAD() isə növbəti sətrin. Birinci hadisənin arxaya baxmağa heç nəyi yoxdur, sonuncunun isə qabağa, ona görə də onlar NULL alır.

Nümunə: hadisələr arasındakı fərq

Bəzən bizə hadisələr arasında nə qədər vaxt keçdiyini bilmək lazımdır. Bunun üçün sadəcə bir vaxtı digərindən çıxırıq:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS time_since_last_event
FROM events;

Nəticə:

Nümunə: PARTITION BY istifadə edərək

Tutaq ki, bir neçə istifadəçimiz var və hər birinin öz hadisələri var. Biz hər istifadəçi üçün hadisələr arasındakı fərqi tapmaq istəyirik.

Cədvəli yeniləyirik və user_id sütunu əlavə edirik:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

İndi iki istifadəçimiz var. Hər qrup daxilində hesablama üçün PARTITION BY istifadə edirik:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS time_since_last_event
FROM events;

Nəticə:

Real tapşırıqlarda istifadə nümunələri

1. Event log-lar: istifadəçi login və çıxış kimi hadisələr arasında vaxtın analizi.
2. Time-tracking: müəyyən tapşırıqlara sərf olunan vaxtın hesablanması.
3. Davranış analitikası: onlayn mağazada müştərilərin ardıcıl hərəkətlərinin analizi.
4. Kumulativ metriklərin hesablanması: zaman sıraları ilə işləmək üçün pəncərə funksiyalarının istifadəsi.

Tipik səhvlər

LEAD() və LAG() ilə işləyərkən əsas problemlər bunlar ola bilər:

• OVER() içində ORDER BY unutmaq. Onsuz funksiya sətrlərin ardıcıllığını müəyyən edə bilmir.
• Zaman intervalları və ya data tipləri ilə bağlı problemlər (TIMESTAMP vs DATE).
• Pəncərə aralığının əvvəlində və sonunda yaranan NULL dəyərləri nəzərə almamaq.

Bu səhvlərdən qaçmaq üçün həmişə datanı yoxla və əməliyyatlar üçün düzgün pəncərə təyin etdiyinə əmin ol.