14-dars. Window functions (Aggregate, Rank, Values)

Window function'ni guruhlari

Ushbu funksiyalarning hammasi ham doimo kerak bo'lmaydi, shu sababli biz faqatgina eng ko'p kerak bo'ladiganlarini o'rganamiz. O'rganmaganlarimiz kelajakda kerak bo'lib qolsa uni qanday qilib mustaqil o'rganishni kursimizni oxirgi darsida o'rgatamiz.

Window Aggregate Functions

Window Aggregate Functions
        ├── COUNT()
        ├── SUM()
        ├── AVG()
        ├── MAX()
        └── MIN()

Hamma funksiyalarni bitta so'rovda ko'rib chiqamiz:

SELECT
    OrderID,
    CustomerName,
    Sales,
    COUNT(*) OVER(PARTITION BY CustomerID) AS CustomerOrderCount,
    SUM(Sales) OVER(PARTITION BY CustomerID) AS CustomerTotalSales,
    AVG(Sales) OVER(PARTITION BY CustomerID) AS CustomerAverageSales,
    MAX(Sales) OVER(PARTITION BY CustomerID) AS CustomerMaxSales,
    MIN(Sales) OVER(PARTITION BY CustomerID) AS CustomerMinSales
FROM Savdo
ORDER BY CustomerID, OrderDate;

Bu query orqali birdaniga:

• customer nechta order qilgani;

• jami qancha savdo qilgani;

• o‘rtacha orderi;

• eng katta orderi;

• eng kichik orderi ko‘rinadi.

Window Ranking Functions

Window Ranking Functions
        ├── ROW_NUMBER()
        ├── RANK()
        ├── DENSE_RANK()
        ├── NTILE()
        ├── CUME_DIST()
        └── PERCENT_RANK()

Bu yerda funksiyalar ko‘p, lekin hammasini to'liq o‘rganmaymiz.

ROW_NUMBER()

ROW_NUMBER() har bir qatorga unikal tartib raqami beradi.

Masalan:

Har bir customerning orderlarini sana bo‘yicha raqamlang.

SELECT
    OrderID,
    CustomerName,
    OrderDate,
    Sales,
    ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS OrderNumber
FROM Savdo;

Ali uchun:

Juda sodda ta'rif:

ROW_NUMBER() — qatorlarga tartib raqami beradi.

RANK()

Endi customerlarni jami savdosi bo‘yicha reyting qilamiz.

SELECT
    CustomerID,
    CustomerName,
    SUM(Sales) AS TotalSales,
    RANK() OVER(ORDER BY SUM(Sales) DESC) AS SalesRank
FROM Savdo
GROUP BY CustomerID, CustomerName;

Natijada:

Bu yerda teng qiymatlarga bir xil rank berildi.

DENSE_RANK()

Endi shu queryga DENSE_RANK()ni qo‘shamiz:

SELECT
    CustomerID,
    CustomerName,
    SUM(Sales) AS TotalSales,
    RANK() OVER(ORDER BY SUM(Sales) DESC) AS SalesRank,
    DENSE_RANK() OVER(ORDER BY SUM(Sales) DESC) AS DenseSalesRank
FROM Savdo
GROUP BY CustomerID, CustomerName;

Natija:

Mana shu yerda beginnerga eng muhim farqni ko‘rsatamiz:

RANK()
1
2
3
3
3
6

DENSE_RANK()
1
2
3
3
3
4

Eslab qolish:

RANK() — teng qiymatlardan keyin raqamda "sakrash" qiladi.

DENSE_RANK() — sakramaydi.

NTILE()

Endi NTILE()ni qisqa tanishtiramiz.

SELECT
    OrderID,
    Sales,
    NTILE(4) OVER(ORDER BY Sales DESC) AS SalesGroup
FROM Savdo;

NTILE(4):

Ma'lumotlarni 4 ta guruhga bo‘lishga yordam beradi.

Masalan:

1-guruh
2-guruh
3-guruh
4-guruh

CUME_DIST() va PERCENT_RANK()

Bu ikkalasini ham faqat tanishtirib o'tamiz.

CUME_DIST()

CUME_DIST() OVER(ORDER BY Sales)

Qiymatning tartibdagi nisbiy joylashuvini hisoblash uchun ishlatiladi.

PERCENT_RANK()

PERCENT_RANK() OVER(ORDER BY Sales)

Qiymatning reytingdagi nisbiy o‘rnini foizga yaqin ko‘rinishda ifodalash uchun ishlatiladi.

Window Value Functions

Window Value Functions
        ├── LAG()
        ├── LEAD()
        ├── FIRST_VALUE()
        └── LAST_VALUE()

Bu guruhda esa LAG() va LEAD()ni asosiy qilib o‘rganamiz.

FIRST_VALUE() va LAST_VALUE()ni tanishamiz.

Qaysi funksiya qachon kerak bo'lishi:

LAG()

LAG() - oldingi qatorning qiymatini olish uchun ishlatiladi.

Masalan:

Har bir customerning oldingi orderidagi Sales qiymatini ko‘rsat.

SELECT
    OrderID,
    CustomerName,
    OrderDate,
    Sales,
    LAG(Sales) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS PreviousSales
FROM Savdo;

Ali uchun:

Birinchi orderda oldingi order bo‘lmagani uchun:

NULL

chiqadi.

Eslab qolish:

LAG = oldingi

LEAD()

LEAD() — LAG()ning teskarisi. Keyingi qatorning qiymatini olish.

SELECT
    OrderID,
    CustomerName,
    OrderDate,
    Sales,
    LEAD(Sales) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS NextSales
FROM Savdo;

Natija:

Oxirgi orderda keyingi order yo‘q:

NULL

Eslab qolish:

LAG = oldingi

LEAD = keyingi

LAG + LEAD birgalikda

SELECT
    OrderID,
    CustomerName,
    OrderDate,
    Sales,
    LAG(Sales) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS PreviousSales,
    LEAD(Sales) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS NextSales
FROM Savdo;

Natija:

FIRST_VALUE()

FIRST_VALUE() - window'dagi birinchi qiymatni olish uchun ishlatiladi.

SELECT
    OrderID,
    CustomerName,
    OrderDate,
    Sales,
    FIRST_VALUE(Sales) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS FirstOrderSales
FROM Savdo;

LAST_VALUE()

LAST_VALUE() - window'dagi oxirgi qiymatni olish uchun ishlatiladi.

SELECT
    OrderID,
    CustomerName,
    OrderDate,
    Sales,
    LAST_VALUE(Sales) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS LastOrderSales
FROM Savdo;

Darsni umumlashtirish

Siz uchun quyida jadvalda berilgan funksiyalar eng keraklilari hisoblanadi, shu sababli ularni yaxshilab o'rganib oling va uyga vazifada berilgan vazifalarni bajarishda faol qo'llang.

Darsga tegishli SQL fayllar

⬇️ Darsda ishlatilgan .sql fayli: 📎 dars_14.sql (4.9 KB)

⬇️ Uyga vazifa .sql fayli: 📎 dars_14_uy_ishi.sql (7.6 KB)