AXV

HAVING va WHERE

HAVING mohiyati, WHERE va HAVING farqi, bajarilish tartibi, birga qo'llash

9-sinfBusiness Intelligence6-hafta · 18-dars

Bugun nimalarni o'rganamiz?

HAVING

Guruhlarni filtrlash

WHERE va HAVING

Farqi va tartib

Amaliy misollar

Hisobot va xatolar

Takrorlash

GROUP BY Shahar (3 ta shahar) nechta qator qaytaradi?

Har guruh uchun bitta qator.
01

HAVING

Guruhlarni filtrlash

HAVING g'oyasi

Guruhlartayyor Aggregatsiyahisoblandi HAVING shartnitekshiradi Mos guruhlarqoladi HAVING — guruh filtri

Katta shaharlarni tanlash


SELECT Shahar, SUM(Summa) AS Jami
FROM Sotuvlar
GROUP BY Shahar
HAVING SUM(Summa) > 4000000;
-- Samarqand | 9640000
-- Toshkent  | 5380000

SELECT Shahar, COUNT(*) AS SotuvSoni
FROM Sotuvlar
GROUP BY Shahar
HAVING COUNT(*) >= 4;
-- Toshkent | 4
HAVING da SELECT dagi alias ishlamaydi (T-SQL'da): HAVING Jami > 4000000 xato, funksiyani qayta yozing.

Mini-test

Aggregatsiya bo'yicha guruhlarni qaysi operator filtrlaydi?

HAVING guruhlangan natijani filtrlaydi.
02

WHERE va HAVING

Farqi va tartib

Mantiqiy tartib

FROM, WHERE GROUP BY HAVING SELECT,ORDER BY Avval qator filtri, keyin guruh filtri

WHERE va HAVING birga


SELECT Shahar, SUM(Summa) AS Jami
FROM Sotuvlar
WHERE Kategoriya <> N'Ekran'
GROUP BY Shahar
HAVING SUM(Summa) > 3500000
ORDER BY SUM(Summa) DESC;
-- Samarqand | 7740000

-- Qator sharti WHERE ga:
SELECT Shahar, SUM(Summa) AS Jami
FROM Sotuvlar
WHERE Shahar = N'Toshkent'
GROUP BY Shahar;
Eslab qoling: WHERE — «qaysi qatorlar?», HAVING — «qaysi guruhlar?».

Mini-test

SUM(Summa) > 4000000 sharti qayerda yoziladi?

Aggregatsiya sharti HAVING da.
03

Amaliy misollar

Hisobot va xatolar

Hisobot tuzish

Savolni o'qing Qator shartiniWHERE ga Guruhlang Guruh shartiniHAVING ga Savol → WHERE / HAVING taqsimoti

Juftliklar va o'rtacha


SELECT Shahar, Kategoriya, COUNT(*) AS Soni
FROM Sotuvlar
GROUP BY Shahar, Kategoriya
HAVING COUNT(*) > 1;
-- Buxoro  | Aksessuar | 2
-- Toshkent| Aksessuar | 2

SELECT Kategoriya, AVG(Summa) AS Ortacha
FROM Sotuvlar
GROUP BY Kategoriya
HAVING AVG(Summa) > 2000000;
-- Kompyuter | 4633333.33
Savol so'zlariga e'tibor bering: «har bir qator ...» → WHERE; «jami / o'rtacha / soni ... bo'lgan guruhlar» → HAVING.

Mini-test

HAVING COUNT(*) > 1 qaysi guruhlarni qoldiradi?

1 dan ko'p qatorli guruhlar.

WHERE va HAVING

WHERE

Qatorlarni guruhlashdan oldin filtrlaydi
VS

HAVING

Guruhlarni guruhlashdan keyin filtrlaydi

Aggregatsiya sharti faqat HAVING da.

Atamalar. Bosing

HAVING

bosing

Guruhlarni filtrlaydi

WHERE

bosing

Qatorlarni filtrlaydi

Bajarilish tartibi

bosing

FROM → WHERE → GROUP BY → HAVING → SELECT

Aggregatsiya sharti

bosing

SUM, COUNT, AVG bo'yicha shart

HAVING yozish

1. Shartni aniqlang

Qator yoki guruh?

2. Guruhlang

GROUP BY.

3. HAVING yozing

Aggregatsiya sharti.

WHERE va HAVING

XususiyatWHEREHAVING
Nimani filtrlaydiQatorlarniGuruhlarni
Qachon ishlaydiGROUP BY dan oldinGROUP BY dan keyin
AggregatsiyaIshlatib bo'lmaydiIshlatiladi
MisolShahar = N'Toshkent'SUM(Summa) > 4000000
GROUP BY sizIshlaydiOdatda kerak

Pastga bosing: farqlar

Birinchi HAVING

  1. Shaharlar bo'yicha jami summani GROUP BY bilan chiqaring.
  2. HAVING SUM(Summa) > 4000000 qo'shing va qaysi shahar tushib qolganini ko'ring.
  3. WHERE bilan Ekran toifasini olib tashlab, natijani solishtiring.

Aggregatsiya sharti


SELECT Shahar, SUM(Summa) AS Jami
FROM Sotuvlar
WHERE SUM(Summa) > 4000000
GROUP BY Shahar;

Aggregatsiya sharti


SELECT Shahar, SUM(Summa) AS Jami
FROM Sotuvlar
GROUP BY Shahar
HAVING SUM(Summa) > 4000000;

Natijalar

0
ta shahar guruhlandi
0
ta shahar HAVING dan o'tdi
0
bosqichli mantiqiy tartib

HAVING guruhlar sonini kamaytiradi.

Amaliy topshiriq 20 daqiqa

OsonKatta shaharlar: Jami summasi 4 000 000 dan oshgan shaharlarni chiqaring.
OsonKo'p sotuvli shaharlar: Kamida 4 ta sotuvi bo'lgan shaharni toping.
O'rtaQimmat toifa: O'rtacha summasi 2 000 000 dan oshgan toifalarni chiqaring.
O'rtaTakroriy juftliklar: Shahar va toifa juftligi bo'yicha 1 dan ko'p sotuvi bor guruhlarni toping.
QiyinWHERE va HAVING birga: Ekran sotuvlarisiz, jami 3 500 000 dan oshgan shaharlarni jami bo'yicha kamayish tartibida chiqaring.
BonusXatoni tuzating: WHERE SUM(Summa) > 4000000 so'rovini to'g'rilab, nima uchun xato ekanini yozing.

Dars xulosasi

HAVING

Guruhlarni filtrlaydi.

WHERE va HAVING

Qatorlar yoki guruhlar.

Tartib

FROM → WHERE → GROUP BY → HAVING.

Uyga vazifa: HAVING va WHERE bilan hisobotlar.

Keyingi dars: DISTINCT COUNT

Kompleks tahlil

7-hafta · 19-dars