|
23-Mavzu: Agregat funksiyalar. Kesishgan so‘rov.
Reja:
1. Taklif bo'yicha GURUHLASH
2. SELECT ProjectNumber
3. SELECT Id, LastName
Quyidagi kichik bo'limlar so'rovlarda ishlatilishi mumkin bo'lgan SELECT iborasining boshqa bandlarini, shuningdek, jamlash funktsiyalari va bayonotlar to'plamini tavsiflaydi. Eslatib o‘tamiz, hozirgacha biz WHERE bandidan foydalanishni ko‘rib chiqdik va ushbu maqolada biz GROUP BY, ORDER BY va HAVING bandlarini ko‘rib chiqamiz va bu bandlarni jamlovchi bilan birgalikda ishlatishga misollar keltiramiz. Transact-SQL da qo'llab-quvvatlanadigan funktsiyalar.
Taklif bo'yicha GURUHLASH
GROUP BY bandi bir yoki bir nechta ustunlar yoki iboralar qiymatlari asosida jamlama qatorlar to‘plamini yaratish uchun tanlangan qatorlar to‘plamini guruhlaydi . GROUP BY bandidan oddiy foydalanish quyidagi misolda ko'rsatilgan:
USE SampleDb;
SELECT Job
FROM Works_On
GROUP BY Job;
Ushbu misolda xodimlarning lavozimlarini tanlash va guruhlash amalga oshiriladi. Ushbu so'rovning natijasi:
Yuqoridagi misolda GROUP BY bandi Ish ustunining barcha mumkin bo'lgan qiymatlari (jumladan, NULL) uchun alohida guruh yaratadi.
GROUP BY bandidagi ustunlardan foydalanish muayyan shartlarga javob berishi kerak. Xususan, so'rovning tanlash ro'yxatidagi har bir ustun GROUP BY bandida ham paydo bo'lishi kerak. Bu talab agregat funksiyaning bir qismi bo'lgan doimiylar va ustunlarga taalluqli emas. (Yig'ish funktsiyalari keyingi kichik bo'limda muhokama qilinadi.) Bu mantiqiy, chunki faqat GROUP BY bandidagi ustunlarga har bir guruh uchun bitta qiymat kafolatlanadi.
Jadval ustunlarining har qanday kombinatsiyasi bo'yicha guruhlanishi mumkin. Quyidagi misol Works_on jadvalining qatorlarini ikkita ustunga guruhlashni ko'rsatadi:
USE SampleDb;
SELECT ProjectNumber, Job
FROM Works_On
GROUP BY ProjectNumber, Job;
Ushbu so'rovning natijasi:
So'rov natijalariga ko'ra, siz loyiha raqami va pozitsiyasining turli kombinatsiyalariga ega bo'lgan to'qqizta guruh mavjudligini ko'rishingiz mumkin. GROUP BY bandidagi ustun nomlari ketma-ketligi SELECT ustunlari roʻyxatidagi kabi boʻlishi shart emas.
Agregat funktsiyalari
Umumiy qiymatlarni olish uchun agregat funktsiyalardan foydalaniladi. Barcha agregat funktsiyalarni quyidagi toifalarga bo'lish mumkin:
oddiy agregat funktsiyalar;
statistik agregat funktsiyalari;
foydalanuvchi tomonidan belgilangan funktsiyalarni yig'ish;
analitik agregat funktsiyalari.
Bu erda biz agregat funktsiyalarning dastlabki uchta turini ko'rib chiqamiz.
Oddiy agregat funktsiyalari
Transact-SQL tili quyidagi oltita jami funksiyalarni qo'llab-quvvatlaydi: MIN , MAX , SUM , AVG , COUNT , COUNT_BIG .
Barcha agregat funktsiyalar ustun yoki ifoda bo'lishi mumkin bo'lgan bitta argument bo'yicha hisob-kitoblarni amalga oshiradi. (Yagona istisno COUNT va COUNT_BIG ikkita funksiyaning ikkinchi shakli, yaʼni mos ravishda COUNT(*) va COUNT_BIG(*) hisoblanadi.) Har qanday yigʻma funksiyaning natijasi alohida natija ustunida koʻrsatiladigan doimiy qiymatdir.
Agregat funktsiyalar SELECT iborasining ustunlar ro'yxatida ko'rsatilgan, ular GROUP BY bandini ham o'z ichiga olishi mumkin. Agar SELECT iborasida GROUP BY bandi boʻlmasa va tanlash ustunlari roʻyxatida kamida bitta agregat funksiya boʻlsa, unda oddiy ustunlar boʻlmasligi kerak (agregat funksiyasiga argument boʻlib xizmat qiluvchi ustunlardan tashqari). Shuning uchun quyidagi misoldagi kod noto'g'ri:
USE SampleDb;
SELECT LastName, MIN(Id)
FROM Employee;
Bu erda Xodimlar jadvalining Familiyasi ustuni ustunlarni tanlash ro'yxatida bo'lmasligi kerak, chunki u agregat funktsiya argumenti emas. Boshqa tomondan, ustunlarni tanlash ro'yxati, agar bu ustunlar GROUP BY bandining argumentlari bo'lsa, yig'ish funksiyasi uchun argument bo'lmagan ustun nomlarini o'z ichiga olishi mumkin.
Agregat funktsiya argumentidan oldin ikkita mumkin bo'lgan kalit so'zlardan biri bo'lishi mumkin:
HAMMA
Ustundagi barcha qiymatlar bo'yicha hisob-kitoblar bajarilishini bildiradi. Bu standart qiymat.
AJROQ
Hisoblash uchun faqat noyob ustun qiymatlari ishlatilishini bildiradi.
MIN va MAX funktsiyalarini jamlash
MIN va MAX agregat funktsiyalari mos ravishda ustunning eng kichik va eng katta qiymatini hisoblab chiqadi. Agar so'rovda WHERE bandi bo'lsa, MIN va MAX funktsiyalari belgilangan mezonlarga javob beradigan satrlarning eng kichik va eng katta qiymatini qaytaradi. Quyidagi misolda MIN agregat funktsiyasidan foydalanish ko'rsatilgan:
USE SampleDb;
-- Вернет 2581
SELECT MIN(Id) AS 'Минимальное значение Id'
FROM Employee;
Yuqoridagi misolda qaytarilgan natija unchalik ma'lumotli emas. Masalan, ushbu raqamga ega bo'lgan xodimning ismi noma'lum. Biroq, bu familiyani odatiy tarzda olish mumkin emas, chunki yuqorida aytib o'tilganidek, Familiya ustunini aniq ko'rsatishga yo'l qo'yilmaydi. Ushbu xodimning familiyasini va eng kam sonli xodimni olish uchun pastki so'rov qo'llaniladi. Quyidagi misolda bunday quyi so'rovdan foydalanish ko'rsatilgan, bunda quyi so'rov oldingi misoldagi SELECT iborasini o'z ichiga oladi:
USE SampleDb;
SELECT Id, LastName
FROM Employee
WHERE Id = (SELECT MIN(Id)
FROM Employee);
So'rovni bajarish natijasi:
MAX agregat funktsiyasidan foydalanish quyidagi misolda ko'rsatilgan:
USE SampleDb;
-- 29346
SELECT Id, LastName
FROM Employee
WHERE Id = (SELECT MAX(Id)
FROM Employee);
MIN va MAX funksiyalari argument sifatida qatorlar va sanalarni ham olishi mumkin. Satr argumenti bo'lsa, qiymatlar haqiqiy tartiblash tartibidan foydalangan holda taqqoslanadi. Barcha vaqtinchalik sana argumentlari uchun eng kichik ustun qiymati eng erta sana, eng katta ustun qiymati esa oxirgi sana hisoblanadi.
Siz MIN va MAX funksiyalari bilan DISTINCT kalit so'zidan foydalanishingiz mumkin. MIN va MAX agregat funktsiyalaridan foydalanishdan oldin barcha NULL qiymatlar argument ustunlaridan chiqarib tashlanadi.
SUM yig'indisi funksiyasi
SUM agregat funktsiyasi ustundagi qiymatlarning umumiy yig'indisini hisoblab chiqadi. Ushbu agregat funktsiyaning argumenti har doim raqamli ma'lumotlar turiga ega bo'lishi kerak. SUM agregat funktsiyasidan foydalanish quyidagi misolda ko'rsatilgan:
USE SampleDb;
SELECT SUM (Budget) 'Суммарный бюджет'
FROM Project;
Ushbu misol barcha loyihalar byudjetlarining umumiy summasini hisoblab chiqadi. So'rovni bajarish natijasi:
Ushbu misolda agregat funktsiyasi loyihaning barcha byudjet qiymatlarini guruhlaydi va ularning umumiy miqdorini aniqlaydi. Shu sababli, so'rovda yashirin guruhlash funktsiyasi mavjud (barcha shunga o'xshash so'rovlar kabi). Yuqoridagi misoldagi yashirin guruhlash funktsiyasi quyidagi misolda ko'rsatilganidek, aniq ko'rsatilishi mumkin:
Do'stlaringiz bilan baham: |
