Window Function nima?

Window Function — jadvaldagi bir nechta qatorlar asosida hisob-kitob qiladi, lekin natijani har bir qator bilan birga saqlab qoladi.

Oddiyroq qilib:

GROUP BY natijada qatorlarni birlashtiradi.
Window Function esa qatorlarni saqlagan holda hisob-kitob qiladi.

GROUP BY vs Window Function

Tasavvur qilamiz, Sales.Orders jadvalida:

Biz har bir "ProductID bo‘yicha jami Sales qancha?" deb so‘raymiz.

GROUP BY bilan

SELECT
    ProductID,
    SUM(Sales) AS TotalSales
FROM Sales.Orders
GROUP BY ProductID;

Bu yerda 4 ta qator 2 ta guruhga aylandi.

Window Function bilan

SELECT
    OrderID,
    ProductID,
    Sales,
    SUM(Sales) OVER(PARTITION BY ProductID) AS TotalSalesByProduct
FROM Sales.Orders;

Bu safar asl qatorlarning hammasi saqlanib qoldi.

Nega Window Function kerak?

Mana bu holatni ko‘rib chiqamiz.

Biz "har bir mahsulot bo‘yicha jami savdoni hisoblang" desak, GROUP BY yetarli:

SELECT
    ProductID,
    SUM(Sales) AS TotalSales
FROM Sales.Orders
GROUP BY ProductID;

Lekin:

Har bir orderni ko‘rsating va shu order tegishli bo‘lgan mahsulotning jami savdosini ham ko‘rsating.

desak, GROUP BY bilan ishlash noqulay bo‘ladi. Chunki GROUP BY natijani guruhlab yuboradi. Window Function aynan shu muammoni hal qiladi.

OVER()

Window Function'ning asosiy belgisi:

OVER()

Masalan:

SUM(Sales) OVER()

Bu:

Sales qiymatlari ustida window hisoblash

kerakligini bildiradi.

PARTITION BY

PARTITION BY — ma'lumotlarni guruhlarga ajratib, Window Function'ni har bir guruh uchun alohida hisoblash imkonini beradi.

SUM(Sales) OVER(PARTITION BY ProductID)

Buni oddiy tilda:

ProductID bo‘yicha guruhla va har bir guruhning Sales summasini hisobla.

deb tushunish mumkin.

PARTITION BY ni qo'llanish holatlari

PARTITION BY'siz Window Function

PARTITION BY yozmasak, butun natija bitta guruh sifatida qaraladi.

SELECT
    OrderID,
    ProductID,
    Sales,
    SUM(Sales) OVER() AS TotalSales
FROM Sales.Orders;

Bu yerda TotalSales barcha orderlarning umumiy savdosini ko‘rsatadi.

Masalan:

Ya’ni har bir qator yonida umumiy summa ko‘rinadi.

PARTITION BY bilan

SELECT
    OrderID,
    ProductID,
    Sales,
    SUM(Sales) OVER(PARTITION BY ProductID) AS TotalSalesByProduct
FROM Sales.Orders;

Bu safar:

Har bir ProductID uchun alohida summa hisoblanadi.

Masalan sizning datasetdagi ko‘rinadigan ma’lumotlar bo‘yicha:

• ProductID = 101 → 140

• ProductID = 102 → 105

• ProductID = 104 → 75

• ProductID = 105 → 60

Shu qiymat tegishli har bir order qatorida ko‘rsatiladi.

Window Function'lar guruhlari

WINDOW funksiyalarini 3 guruhga bo'lib o'rganamiz:

WINDOW funksiyalari qanday ma'lumot turlarini qabul qilishi:

Window Function + Aggregate Function

Oldingi darsda o‘rgangan Aggregate Functions'ni Window Function bilan birga ishlatish mumkin:

SUM(Sales) OVER(...)
COUNT(*) OVER(...)
AVG(Sales) OVER(...)
MAX(Sales) OVER(...)
MIN(Sales) OVER(...)

Masalan:

SELECT
    OrderID,
    ProductID,
    Sales,
    AVG(Sales) OVER(PARTITION BY ProductID) AS AverageSalesByProduct
FROM Sales.Orders;

Bu:

Har bir mahsulot bo‘yicha o‘rtacha Sales'ni hisobla va uni har bir order qatorida ko‘rsat.

degan ma'noni anglatadi.

ORDER BY

Window Function ichida ORDER BY ham ishlatilishi mumkin.

Masalan, mijozlarni umumiy savdosi bo‘yicha reyting qilish:

SELECT
    CustomerID,
    SUM(Sales) AS TotalSales,
    RANK() OVER(ORDER BY SUM(Sales) DESC) AS CustomerRank
FROM Sales.Orders
GROUP BY CustomerID;

Bu sizning SQL draftingizdagi misolning o‘zi.

Bu query:

1. CustomerID bo‘yicha guruhlaydi;

2. har bir customer'ning jami Sales'ini hisoblaydi;

3. jami Sales bo‘yicha reyting beradi.

WINDOW funksiyalari bilan PARTITION BY va ORDER BY ni qo'llash shartlari:

GROUP BY + Window Function

SELECT
    CustomerID,
    SUM(Sales) AS TotalSales,
    RANK() OVER(ORDER BY SUM(Sales) DESC) AS CustomerRank
FROM Sales.Orders
GROUP BY CustomerID;

Bu yerda:

GROUP BY
     ↓
Customer bo‘yicha guruhla
     ↓
SUM()
     ↓
Har bir customer'ning jami savdosini hisobla
     ↓
RANK()
     ↓
Jami savdoga qarab reyting ber

Shu sababli Window Function'ni GROUP BY bilan birga ishlatish ham mumkin.

WINDOW funksiyalarni ishlatishdagi qoidalar:

1. Window funksiyalar faqat SELECT va ORDER BY bilan ishlaydi

select 
	OrderID,
	OrderDate,
	ProductID,
	sum(sales) over(partition by productid) TotalSalesByProducts
from Sales.Orders
order by sum(sales) over(partition by productid) desc
where sum(sales) over(partition by productid) > 100 -- ishlamaydi
group by sum(sales) over(partition by productid) -- ishlamaydi

2. Window funksiyalar nested (ichma-ich) qilinganda ishlamaydi

select 
	OrderID,
	OrderDate,
	ProductID,
	sum(sum(sales) over(partition by productid)) over(partition by productid) TotalSalesByProducts
from Sales.Orders;

3. Window funksiyalar WHERE'dan keyin ishlaydi

Faqat 101 va 102 id ga ega bo'lgan mahsulotlarning umumiy savdosini statuslari bo'yicha aniqlash:

select 
	OrderID,
	OrderDate,
	OrderStatus,
	ProductID,
	sum(sales) over(partition by OrderStatus) TotalSales
from Sales.Orders
where ProductID in (101, 102)

4. Window funksiya GROUP BY bilan faqatgina bir xil ustunlardagi so'rovda ishlaydi

Umumiy savdosiga ko'ra mijozlarni retingini tuzish:

select
	CustomerID,
	sum(sales) TotalSales,
	rank() over(order by sum(sales) desc) RankCustomers 
from Sales.Orders
group by CustomerID

GROUP BY va Window Function — eng muhim farq

Juda sodda qilib:

GROUP BY:

"Guruhla va natijani chiqar."

Window Function:

"Guruhla, hisobla, lekin asl qatorlarni ham qoldir."

Darsga tegishli SQL fayllar

⬇️ Darsda ishlatilgan .sql fayli: 📎 dars_13.sql (2.2 KB)

⬇️ Uyga vazifa .sql fayli: 📎 dars_13_uy_ishi.sql (4.2 KB)