Window function'ni guruhlari

ChatGPT Image Aug 30, 2026, 12_33_32 AM.png

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:

OrderID	OrderDate	OrderNumber
1001	2025-01-05	1
1004	2025-01-12	2
1010	2025-01-28	3
1016	2025-02-16	4
1025	2025-03-12	5

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:

Customer	TotalSales	Rank
Ali	4400	1
Madina	3550	2
Vali	3350	3
Hasan	3350	3
Sardor	3350	3
Malika	2800	6

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:

Customer	Sales	RANK	DENSE_RANK
Ali	4400	1	1
Madina	3550	2	2
Vali	3350	3	3
Hasan	3350	3	3
Sardor	3350	3	3
Malika	2800	6	4

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:

OrderID	Date	Sales	PreviousSales
1001	Jan 5	1200	NULL
1004	Jan 12	500	1200
1010	Jan 28	800	500
1016	Feb 16	1200	800
1025	Mar 12	700	1200

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:

OrderID	Sales	NextSales
1001	1200	500
1004	500	800
1010	800	1200
1016	1200	700
1025	700	NULL

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:

OrderID	Sales	Previous	Next
1001	1200	NULL	500
1004	500	1200	800
1010	800	500	1200
1016	1200	800	700
1025	700	1200	NULL

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.

Tushuncha	Vazifasi
`OVER()`	Window Function ishlatish uchun
`PARTITION BY`	Ma'lumotni window ichida guruhlash
`ORDER BY`	Window ichida tartib belgilash
`SUM() OVER()`	Umumiy summani qatorlarni saqlagan holda hisoblash
`SUM() OVER(PARTITION BY...)`	Har bir guruh bo‘yicha summa
`ROW_NUMBER()`	Qatorlarga tartib raqami
`RANK()`	Reyting
`DENSE_RANK()`	Reyting, sakrashsiz
`LAG()`	Oldingi qator qiymati
`LEAD()`	Keyingi qator qiymati

SQL

Kurs dasturi

1. SQL va SQL Server bilan tanishuv

2. SQL asoslari — Ma’lumotlarni boshqarish

3. Jadvallar va ma’lumotlarni birlashtirish

4. SQL funksiyalari va shartli ifodalar

5. Ma’lumotlarni guruhlash va tahlil qilish

6. Murakkab SQL query'lar

7. SQL'ni amaliy avtomatlashtirish

8. AI yordamida SQL o‘rganish

9. Real loyiha