SQL Window funksiyalar: ROW_NUMBER, RANK, LAG

Window funksiyalar agregat funksiyalardan farqli ravishda qatorlarni o'chirmaydi.

ROW_NUMBER

-- Har bir talabani raqamlash
SELECT 
    ROW_NUMBER() OVER (ORDER BY baho DESC) AS raqam,
    ism,
    baho
FROM talabalar;

-- Shahar bo'yicha raqamlash
SELECT 
    ROW_NUMBER() OVER (PARTITION BY shahar ORDER BY baho DESC) AS raqam,
    ism,
    shahar,
    baho
FROM talabalar;

RANK va DENSE_RANK

SELECT 
    ism,
    baho,
    RANK() OVER (ORDER BY baho DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY baho DESC) AS dense_rank
FROM talabalar;
-- RANK: 1,2,2,4 (3 o'tkazib yuboriladi)
-- DENSE_RANK: 1,2,2,3 (o'tkazib yuborilmaydi)

LAG va LEAD

-- Oldingi oylik savdo bilan solishtirish
SELECT 
    oy,
    savdo,
    LAG(savdo, 1) OVER (ORDER BY oy) AS oldingi_oy,
    savdo - LAG(savdo, 1) OVER (ORDER BY oy) AS farq
FROM oylik_savdo;

SUM va AVG window

-- Kumulyativ yig'indi
SELECT 
    sana,
    miqdor,
    SUM(miqdor) OVER (ORDER BY sana) AS kumulyativ
FROM savdolar;

-- Harakatlanuvchi o'rtacha (3 kunlik)
SELECT 
    sana,
    miqdor,
    AVG(miqdor) OVER (
        ORDER BY sana 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS harakatlanuvchi_ortacha
FROM savdolar;