- Yig'ish funktsiyalari SELECT bayonotida maydon nomlari kabi ishlatiladi, faqat bitta istisno: ular maydon nomini argument sifatida qabul qiladilar. Funktsiyalar bilan JUM va AVG faqat raqamli maydonlardan foydalanish mumkin. Funktsiyalar bilan COUNT, MAX va MIN ikkala raqamli va belgilar maydonlaridan foydalanish mumkin. Belgilar maydonlari bilan foydalanilganda MAX va MIN ularni ASCII ekvivalentiga aylantiradi va ularni alifbo tartibida qayta ishlaydi. Ba'zi DBMS-lar ichki o'rnatilgan agregatlardan foydalanishga ruxsat beradi, ammo bu ANSI standartidan og'ish, natijada yuzaga keladigan barcha oqibatlarga olib keladi.
Masalan, har bir fan bo'yicha imtihon topshirgan talabalar sonini hisoblashingiz mumkin. Buning uchun siz "Intizom" maydoni bo'yicha guruhlash bilan so'rovni bajarishingiz va natijada ushbu fan nomini va ushbu fan bo'yicha guruhdagi qatorlar sonini ko'rsatishingiz kerak. * Funktsiyasini COUNT funktsiyasining argumenti sifatida ishlatish guruhdagi barcha satrlarni hisoblashni anglatadi.
R1 ni tanlang. Intizom, COUNT (*)
GROUP BY R1.Tartib-intizom;
Natija:
R1 intizomini tanlang, COUNT (*)
Qaerda R1. NULL ball emas
GROUP BY R1.Tartib-intizom;
Natija:
guruhlashdan oldin stendlar to'plamiga kiritilmaydi, shuning uchun "Axborot nazariyasi" fani bo'yicha guruhdagi stendlar soni 1 taga kam bo'ladi.
Agar so'rovni quyidagi tarzda yozsangiz, xuddi shunday natijaga erishish mumkin:
R1 ni tanlang. Intizom, COUNT (R1. Baholash)
R1 tomonidan guruh. Intizom;
Funktsiya COUNT (QO'ShIMChA NOM) funktsiyadan farqli o'laroq, guruhdagi belgilangan qiymatlar sonini hisoblaydi COUNT (*), guruhdagi satrlar sonini hisoblaydigan. Darhaqiqat, "Axborot nazariyasi" faniga ega guruhda 4 ta satr bo'ladi, ammo "Baholash" atributining atigi 3 ta o'ziga xos qiymati.
Yagona funktsiyalarda nol qiymatlarni boshqarish qoidalari
Agar ustundagi biron bir qiymat teng bo'lsa NULL funktsiya natijasini hisoblashda ular chiqarib tashlanadi.
Agar ustundagi barcha qiymatlar teng bo'lsa NULL keyin O'rtacha maksimal min \u003d NULL, hisoblash \u003d 0 (nol).
Agar stol bo'sh bo'lsa, hisoblash (*) \u003d 0 .
Aggregat funktsiyalaridan ham dastlabki guruhlash amalisiz foydalanish mumkin, bu holda butun munosabat bitta guruh deb hisoblanadi va ushbu guruh uchun bitta guruhga bitta qiymatni hisoblash mumkin.
Agregat funktsiyalarini talqin qilish qoidalari
Yig'ish funktsiyalari chiqish ro'yxatiga kiritilishi mumkin va keyin ular butun jadvalga qo'llaniladi.
R1 dan MAX (Skor) ni tanlang sessiyada maksimal ballni beradi;
R1 dan SUM (Score) ni tanlang har bir mashg'ulot uchun barcha baholarning yig'indisini beradi;
R1 dan AVG (Skor) ni tanlang butun sessiya uchun o'rtacha ball beradi.
2; Natija: "width \u003d" 640 " Yana sessiya ma'lumotlar bazasiga murojaat qilib (jadvallar R1) biz muvaffaqiyatli o'tgan imtihonlar sonini topamiz:
COUNT (*) ni tanlang Ijaraga olingan _ imtihonlar
Qaerda 2-sinf;
Natija:
Umumiy funktsiyalar argumenti jadvallarning alohida ustunlari bo'lishi mumkin. Masalan, guruhdagi ma'lum bir ustunning aniq qiymatlari sonini hisoblash uchun ustun nomi bilan birga DISTINCT kalit so'zidan foydalanish kerak. Keling, har bir fan uchun olingan har xil baholarning sonini hisoblab chiqamiz:
SELECT R1 Intizom, COUNT (DISTINCT R1 baholash)
Qaerda R1. NULL ball emas
GROUP BY R1.Tartib-intizom;
Natija:
Agar WHERE qismidagi aniq shartni chiqarib tashlasangiz, xuddi shu natija olinadi, bu holda so'rov quyidagicha ko'rinadi:
R1 ni tanlang. Intizom, COUNT (DISTINCT R1. Baholash)
R1 tomonidan guruh. Intizom;
Funktsiya COUNT (R1-ni ajratib oling. Baholash) faqat aniq deb hisoblaydi turli xil qiymatlar.
Bu holatda ham kerakli natijani olish uchun "Skor" ustunining ma'lumotlar turini oldindan o'zgartirishni, uni haqiqiy turga keltirishni amalga oshirish kerak, keyin o'rtacha qiymatni hisoblash natijasi butun son bo'lmaydi. Bunday holda, so'rov quyidagicha ko'rinadi:
2 R2 bo'yicha guruh. Guruh, R1. Intizom; Bu erda CAST () funktsiyasi "Skor" ustunini ma'lumotlarning yaroqli turiga o'zgartiradi. "width \u003d" 640 " R2.Group, R1.Intizom, Count (*) ni jami sifatida, AVG (quyma (o'nlik sifatida bal (3,1))) ni O'rtacha_ nuqta sifatida tanlang
R1, R2 dan
qaerda R1. To'liq ism \u003d R2. Ism va R1. ball nolga teng emas
va R1. 2-sinf
R2 bo'yicha guruh. Guruh, R1. Intizom;
Bu erda funktsiya CAST () "Skor" ustunini ma'lumotlarning yaroqli turiga o'zgartiradi.
Siz WHERE bandida agregat funktsiyalaridan foydalana olmaysiz, chunki ushbu bo'limdagi shartlar bitta satr bo'yicha va agregat funktsiyalar qatorlar guruhlari bo'yicha baholanadi.
GROUP BY bandi ma'lum bir sohadagi qiymatlar to'plamini boshqa maydon nuqtai nazaridan aniqlashga imkon beradi va yig'indiga agregat funktsiyasini qo'llaydi. Bu bitta SELECT bandida maydonlarni va funktsiyalarni birlashtirishga imkon beradi. Yig'ish funktsiyalari ham SELECT qatorining natijalarini chiqarish uchun, ham HAVING guruhlarini qayta ishlash uchun ishlatilishi mumkin. Bunday holda, har bir yig'ilgan funktsiya har bir tanlangan guruh uchun hisoblanadi. Yig'ish funktsiyalarini hisoblashda olingan qiymatlar tegishli natijalarni ko'rsatish yoki guruhlarni tanlash sharti uchun ishlatilishi mumkin.
Imtihonlarda bitta intizomda bir nechta deklaratsiya olingan guruhlarni aks ettiruvchi so'rov tuzamiz:
1; Natija: "width \u003d" 640 " R2 ni tanlang. Guruh
R1, R2 dan
Qaerda R1. To'liq ism \u003d R2. Ism VA
R1 bal \u003d 2
GRUP BY R2. Guruh, R1. Intizom
HAVING soni (*) 1;
Natija:
Bizda banklar filiallari hisobvaraqlari to'g'risidagi ma'lumotlarni o'z ichiga olgan F munosabati saqlanadigan bitta F jadvalidan iborat "Bank" ma'lumotlar bazasi mavjud:
Filiallardagi umumiy hisob balansini toping. Har bir filial uchun jadvaldan SUM ni tanlab, ularning har biri uchun alohida so'rov qilish mumkin, ammo GROUP BY operatsiyasi ularning barchasini bitta buyruqqa joylashtiradi:
SELECT Filial , SUM ( Qolganlari )
GROUP BY filiali;
GROUP BY agregat funktsiyalarini Filial maydonining qiymati bilan aniqlangan har bir guruh uchun mustaqil ravishda qo'llaydi. Guruh bir xil maydon qiymatiga ega chiziqlar va funktsiyadan iborat JUM u har bir bunday guruh uchun alohida qo'llaniladi, ya'ni har bir filial uchun umumiy hisob qoldig'i alohida hisoblanadi. U tegishli bo'lgan maydon qiymati GROUP BY , ta'rifi bo'yicha, birlashtirilgan funktsiya natijasi kabi har bir chiqish guruhi uchun faqat bitta qiymatga ega.
5000; HAVING bandidagi argumentlar GROUP BY ishlatadigan SELECT bandidagi kabi qoidalarga amal qiladi. Ular har bir chiqish guruhi uchun bitta qiymatga ega bo'lishi kerak. "width \u003d" 640 " Deylik, siz faqat hisob raqamidagi qoldiq miqdori 5000 dollardan oshadigan filiallarni tanladingiz, shuningdek tanlangan filiallar uchun jami qoldiqlar. Umumiy balansi 5000 dollardan yuqori bo'lgan filiallarni namoyish qilish uchun siz HAVING bandini ishlatishingiz kerak. HAVING bandi ma'lum qatorlarni chiqindidan olib tashlash uchun ishlatiladigan mezonlarni belgilaydi, xuddi WHERE bandi alohida qatorlar uchun.
To'g'ri buyruq shunday bo'ladi:
SELECT filiali, SUM (qoldiq)
GROUP BY Filial
SUM (bor Qolganlari ) 5 000;
Gapdagi tortishuvlar YO'Q gapdagi kabi qoidalarga rioya qiling SELECT qaerda ishlatiladi GROUP BY ... Ular har bir chiqish guruhi uchun bitta qiymatga ega bo'lishi kerak.
Quyidagi buyruq taqiqlanadi:
SELECT filiali, SUM (qoldiq)
GROUP BY filiali
OCHILGAN Sana \u003d 27.12.2004 ;
Maydon Ochilish sanasi gapda ishlatib bolmaydi YO'Q chunki u har bir chiqish guruhi uchun bir nechta qiymatga ega bo'lishi mumkin. Bunday vaziyatdan qochish uchun taklif YO'Q faqat tanlangan agregatlar va maydonlarga murojaat qilishlari kerak GROUP BY ... Yuqoridagi so'rovni bajarishning to'g'ri usuli mavjud:
SELECT filiali, SUM (qoldiq)
Qaerda ochilish sanasi \u003d '27 / 12/2004 '
GROUP BY filiali;
Ushbu so'rovning ma'nosi quyidagicha: 2004 yil 27 dekabrda ochilgan har bir hisobvaraq filiali uchun qoldiq miqdorini toping.
Yuqorida aytib o'tilganidek, HAVING faqat bitta chiqish guruhi uchun bir xil qiymatga ega bo'lgan argumentlardan foydalanishi mumkin. Amalda, agregat funktsiyalariga havolalar eng keng tarqalgan, ammo GROUP BY bilan tanlangan maydonlar ham amal qiladi. Masalan, biz Sankt-Peterburg, Pskov va Uryupinskdagi filiallarning umumiy hisobraqamlarini ko'rishni istaymiz:
SELECT filiali, SUM (qoldiq)
F, Q dan
Qaerda F. Filial \u003d Q. Filial
GROUP BY filiali
BO'LISHDA ("Sankt-Peterburg", "Pskov", "Uryupinsk");
100000; Agar umumiy qoldiq $ 100,000 dan ortiq bo'lsa, unda biz uni natijada ko'ramiz, aks holda biz bo'sh nisbatni olamiz. "width \u003d" 640 "
Shuning uchun HAVING bandining tanlash shartiga kiritilgan predikatlarning arifmetik ifodalarida siz to'g'ridan-to'g'ri GROUP BY bandida guruhlash ustunlari sifatida ko'rsatilgan ustunlarning xususiyatlaridan foydalanishingiz mumkin. Qolgan ustunlar faqat COUNT, SUM, AVG, MIN va MAX yig'indisi funktsiyalari spetsifikatsiyalari doirasida belgilanishi mumkin, bu holda barcha qatorlar guruhi uchun ba'zi bir yig'indilar qiymati hisoblanadi. HAVING bandining natijasi faqat HAVING qismidagi tanlov shartini baholash natijasi HAQI bo'lgan satr guruhlarini o'z ichiga olgan guruhlangan jadvaldir. Xususan, agar GRUP BY o'z ichiga olmagan so'rovda HAVING bandi mavjud bo'lsa, u holda uning bajarilishi natijasi yoki bo'sh jadval bo'ladi, yoki ustunlar guruhlanmagan holda bitta guruh sifatida ko'rib chiqilgan jadval ifodasining oldingi bo'limlari natijasi bo'ladi. Keling, bir misolni ko'rib chiqaylik. Aytaylik, biz barcha filiallar uchun jami qoldiqlarni namoyish qilmoqchimiz, lekin agar u 100000 dollardan ortiq bo'lsa, bu holda bizning so'rovimiz guruhlash operatsiyalarini o'z ichiga olmaydi, lekin HAVING bo'limini o'z ichiga oladi va quyidagicha ko'rinadi:
SUM SUM ( Qolganlari )
SUM (bor Qolganlari ) 100 000;
Agar umumiy qoldiq $ 100,000 dan ortiq bo'lsa, unda biz uni natijada ko'ramiz, aks holda biz bo'sh nisbatni olamiz.
GROUP BY bandi (SELECT iborasi) ma'lumotlar (qatorlar) ni ustun yoki bir nechta ustunlar yoki iboralar qiymati bo'yicha guruhlash imkonini beradi. Natijada xulosa qatorlari to'plami bo'ladi.
Tanlovlar ro'yxatidagi har bir ustun GROUP BY bandida bo'lishi kerak, faqat istisnolar doimiy va ustunlar - agregat funktsiyalarining operandlari.
Jadvalni har qanday ustunlar kombinatsiyasi bo'yicha guruhlash mumkin.
Yig'ish funktsiyalari qatorlar guruhidan bitta umumiy qiymat olish uchun ishlatiladi. Barcha agregat funktsiyalari ustun yoki ifoda bo'lishi mumkin bo'lgan bitta argument bo'yicha hisob-kitoblarni amalga oshiradi. Har qanday agregat funktsiyasi natijada alohida ustunda ko'rsatiladigan doimiy qiymatga baho beradi.
Yig'ish funktsiyalari SELECT bayonotining ustunlar ro'yxatida ko'rsatilgan bo'lib, unda GROUP BY bandi ham bo'lishi mumkin. Agar SELECT buyrug'ida GROUP BY bandi bo'lmasa va tanlangan ustunlar ro'yxatida kamida bitta agregat funktsiyasi bo'lsa, unda unda oddiy ustunlar bo'lmasligi kerak. Boshqa tomondan, tanlangan ustunlar ro'yxati, agar bu ustunlar GROUP BY bandiga argument sifatida ishlatilsa, yig'ish funktsiyasi uchun argument bo'lmagan ustun nomlarini o'z ichiga olishi mumkin.
Agar so'rovda WHERE bandi bo'lsa, unda agregat funktsiyalari tanlov natijalari uchun qiymatni hisoblab chiqadi.
MIN va MAX funktsiyalarini yig'ish navbati bilan eng kichik va eng katta ustun qiymatlari hisoblanadi. Argumentlar raqamlar, satrlar va sanalar bo'lishi mumkin. Barcha NULL qiymatlari hisoblashdan oldin o'chiriladi (ya'ni, hisobga olinmaydi).
SUM agregat funktsiyasi ustun qiymatlarining umumiy yig'indisini hisoblab chiqadi. Faqat raqamlar argument bo'lishi mumkin. DISTINCT parametridan foydalanish SUM funktsiyasini ishlatishdan oldin ustundagi har qanday takrorlanadigan qiymatlarni olib tashlaydi. Xuddi shunday, ushbu agregat funktsiyasini qo'llashdan oldin barcha NULL qiymatlarini olib tashlang.
Aggregat funktsiyasi AVG ustundagi barcha qiymatlarning o'rtacha qiymatini qaytaradi. Argumentlar faqat raqamlar bo'lishi mumkin va barcha NULL qiymatlari baholashdan oldin o'chiriladi.
Yig'ish funktsiyasi COUNT ikki xil shaklga ega:
- COUNT (col_name) - col_name ustunidagi qiymatlar sonini hisoblaydi, NULL qiymatlari hisoblanmaydi
- COUNT (*) - jadvaldagi qatorlar sonini hisoblaydi, NULL qiymatlari ham hisobga olinadi
Agar so'rovda DISTINCT kalit so'zi ishlatilsa, barcha takrorlangan ustun qiymatlari COUNT funktsiyasidan oldin o'chiriladi.
COUNT_BIG funktsiya COUNT bilan bir xil funktsiya. Ikkala orasidagi farq faqat ular qaytaradigan natija turida: COUNT_BIG funktsiyasi har doim BIGINT qiymatlarini qaytaradi, COUNT funktsiyasi esa INTEGER turidagi ma'lumotlar qiymatlarini qaytaradi.
IN taklif bor qatorlar guruhiga qo'llaniladigan shartni belgilaydi. Bu satr guruhlari uchun tegishli jadval tarkibidagi WHERE bandi bilan bir xil ma'noga ega (WHERE guruhlashdan oldin amal qiladi, HAVING keyin).
Muayyan sotuvchi tomonidan ishlab chiqarilgan kompyuter modellarining sonini qanday topishim mumkin? Xuddi shu texnik xususiyatlarga ega kompyuterlar uchun o'rtacha narxni qanday aniqlash mumkin? Statistik ma'lumotlarga tegishli bo'lgan ushbu va boshqa ko'plab savollarga javoban javob berish mumkin xulosa (agregat) funktsiyalari... Standart quyidagi umumiy funktsiyalarni ta'minlaydi:
Ushbu funktsiyalarning barchasi bitta qiymatni qaytaradi. Vazifalar COUNT, MIN va MAX ma'lumotlar har qanday turiga nisbatan qo'llaniladi JUM va AVG faqat raqamli maydonlar uchun ishlatiladi. Funktsiya o'rtasidagi farq COUNT (*) va COUNT (<имя поля>) ikkinchisi hisoblashda NULL qiymatlarini hisobga olmasligi.
Misol. Shaxsiy kompyuterlar uchun minimal va maksimal narxni toping:
Misol. A ishlab chiqaruvchisidan mavjud bo'lgan kompyuterlar sonini toping:
Misol. Agar biz A ishlab chiqaruvchisi tomonidan ishlab chiqarilgan turli xil modellarning sonini qiziqtiradigan bo'lsak, unda so'rov quyidagicha shakllantirilishi mumkin (har bir model bir marta mahsulot jadvalida qayd etilganligi sababli):
Misol. A ishlab chiqaruvchisidan mavjud bo'lgan turli xil modellar sonini toping. So'rov avvalgisiga o'xshash bo'lib, unda ishlab chiqaruvchi A tomonidan ishlab chiqarilgan modellarning umumiy sonini aniqlash talab qilingan edi, bu erda siz kompyuter jadvalidagi turli xil modellar sonini ham topishingiz kerak (ya'ni sotish uchun mavjud).
Statistik ko'rsatkichlarni qachon faqat noyob qiymatlardan foydalanilishini ta'minlash uchun agregat funktsiyalarining argumenti foydalanish mumkin dISTINCT parametri... Boshqalar parametr ALL sukut bo'yicha va ustundagi barcha qaytish qiymatlari hisoblangan deb hisoblaydi. Operator,
Agar biz ishlab chiqarilgan kompyuter modellarining sonini olishimiz kerak bo'lsa har bir ishlab chiqaruvchisi, siz foydalanishingiz kerak bo'ladi gROUP BY bandisintaktik ravishda quyidagi qaerda bandlar.
GROUP BY bandi
GROUP BY bandi qo'llanilishi mumkin bo'lgan chiqish chiziqlari guruhlarini aniqlash uchun ishlatiladi umumiy funktsiyalar (COUNT, MIN, MAX, AVG va SUM)... Agar ushbu band etishmayotgan bo'lsa va agregat funktsiyalaridan foydalanilsa, unda nomlari ko'rsatilgan barcha ustunlar SELECTkiritilishi kerak umumiy funktsiyalarva ushbu funktsiyalar so'rov predikatsiyasini qondiradigan barcha qatorlar to'plamiga qo'llaniladi. Aks holda, SELECT ro'yxatining barcha ustunlari, kiritilmagan umumiy funktsiyalarda, ko'rsatilishi kerak gROUP BY bandida... Natijada, so'rovning barcha chiqish satrlari ushbu ustunlardagi qiymatlarning bir xil kombinatsiyasi bilan tavsiflangan guruhlarga bo'linadi. Shundan so'ng, har bir guruhga umumiy funktsiyalar qo'llaniladi. GROUP BY uchun barcha NULL qiymatlari teng deb hisoblanadi, ya'ni. NULL qiymatlarini o'z ichiga olgan maydon bo'yicha guruhlanganida, bunday satrlarning barchasi bitta guruhga kiradi.Agar a gROUP BY bandi bilan, SELECT bandida umumiy funktsiyalar yo'q, keyin so'rov shunchaki har bir guruhdan bitta qatorni qaytaradi. Ushbu funktsiya DISTINCT kalit so'zi bilan birga natijalar to'plamidagi takrorlangan qatorlarni yo'q qilish uchun ishlatilishi mumkin.
Keling, oddiy misolni ko'rib chiqaylik:
| SELECT model, COUNT (model) AS Qty_model, AVG (narx) AS Avg_price Kompyuterdan GROUP BY modeli; |
Ushbu so'rovda har bir kompyuter modeli uchun ularning soni va o'rtacha narxi aniqlanadi. Model qiymatlari bir xil bo'lgan barcha qatorlar guruhni tashkil qiladi va SELECT chiqishi har bir guruh uchun qiymatlar sonini va o'rtacha narx qiymatlarini hisoblab chiqadi. So'rov quyidagi jadvalga olib keladi:
| model | Miqdor_model | Avg_price |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Agar SELECT-da sana ko'rsatilgan ustun mavjud bo'lsa, unda har bir aniq sana uchun ushbu ko'rsatkichlarni hisoblash mumkin bo'ladi. Buni amalga oshirish uchun sanani guruhlash ustuni sifatida qo'shing, so'ngra yig'indisi funktsiyalari qiymatlarning har bir kombinatsiyasi uchun hisoblab chiqiladi (model-sana).
Bir nechta o'ziga xos xususiyatlar mavjud agregat funktsiyalarini bajarish qoidalari:
- Agar so'rov natijasida bo'lsa chiziqlar olinmadi (yoki ushbu guruh uchun bir nechta satrlar) bo'lsa, unda har qanday agregat funktsiyalarini hisoblash uchun dastlabki ma'lumotlar yo'q. Bunday holda, COUNT funktsiyalarining natijasi nolga teng bo'ladi va boshqa barcha funktsiyalarning natijalari NULL bo'ladi.
- Dalil agregat funktsiyasi o'zida jamlangan funktsiyalarni o'z ichiga olmaydi (funktsiyadan funktsiya). O'sha. bitta so'rovda siz maksimal o'rtacha qiymatlarni ololmaysiz.
- COUNT funktsiyasining natijasi tamsayı (INTEGER). Boshqa umumiy funktsiyalar qayta ishlanadigan qiymatlarning ma'lumotlar turlarini meros qilib oladi.
- Agar SUM funktsiyasini bajarishda foydalanilgan ma'lumotlar turining maksimal qiymatidan oshadigan natija olingan bo'lsa, xato.
Agar so'rovda bo'lmasa gROUP BY bandlarikeyin umumiy funktsiyalarkiritilgan bandini tanlang, barcha olingan so'rovlar satrlarida bajariladi. Agar so'rovda bo'lsa gROUP BY bandi, ko'rsatilgan ustunlar yoki ustunlar guruhi qiymatlari bir xil bo'lgan har bir qator to'plami gROUP BY bandi, guruhni tashkil qiladi va umumiy funktsiyalar har bir guruh uchun alohida-alohida amalga oshiriladi.
QOIDA QO'YISh
Agar a qaerda band qatorlarni filtrlash uchun predikatni belgilaydi, keyin taklif bor qo'llaniladi guruhlashdan keyin shunga o'xshash predikat filtrlash guruhlarini qiymatlari bo'yicha aniqlash umumiy funktsiyalar... Ushbu band bilan olingan qiymatlarni sinash uchun kerak agregat funktsiyasi da belgilangan yozuv manbasining alohida satrlaridan emas ushbu banddanva bunday chiziqlar guruhlari... Shuning uchun bunday tekshiruv tarkibida bo'lishi mumkin emas qaerda band.
Salom! Bugun biz SQL-dagi agregat funktsiyalari bilan tanishamiz, ularning ishlarini oldingi darslarda tuzgan jadvallarimiz ma'lumotlari bilan batafsil tahlil qilamiz.
Umumiy tushuncha
So'nggi qo'llanmada biz ma'lumotlar so'rovlari qanday tuzilganligi bilan tanishdik. Yig'ish funktsiyalari olingan ma'lumotlarni qandaydir tarzda umumlashtira olish, ya'ni ularni biz xohlagan tarzda boshqarish uchun mavjuddir.
Ushbu funktsiyalar SELECT so'roviga kiritilgan kalit so'zlar yordamida amalga oshiriladi va ularning yozilishi keyinroq tavsiflanadi. Tushunarli bo'lish uchun SQL-dagi agregat funktsiyalarining ba'zi imkoniyatlari:
- Tanlangan qiymatlarni yig'ing
- Qiymatlarning o'rtacha arifmetikasini toping
- Minimal va maksimal qiymatlarni toping
SQL Aggregate funktsiyalariga misollar
Biz eng ko'p ishlatiladigan funktsiyalarni ko'rib chiqamiz va bir nechta misollarni keltiramiz.
SUM funktsiyasi
Ushbu funktsiya SELECT so'rovidagi maydon qiymatlarini yig'ishga imkon beradi. SQL-dagi barcha boshqa funktsiyalar singari sintaksis juda sodda bo'lgan juda foydali funktsiya. Tushunish uchun darhol bir misol bilan boshlaylik:
2016 yilda yakunlangan Buyurtmalar jadvalidan barcha buyurtmalar summasini oling.
Buyurtmalar miqdorini shunchaki qaytarib olish mumkin edi, ammo bu menga juda oddiy tuyuladi. Jadvalimizning tuzilishini eslaylik:
| onum | amt | odate | cnum | qaqshatqich |
|---|---|---|---|---|
| 1001 | 128 | 2016-01-01 | 9 | 4 |
| 1002 | 1800 | 2016-04-10 | 10 | 7 |
| 1003 | 348 | 2017-04-08 | 2 | 1 |
| 1004 | 500 | 2016-06-07 | 3 | 3 |
| 1005 | 499 | 2017-12-04 | 5 | 4 |
| 1006 | 320 | 2016-03-03 | 5 | 4 |
| 1007 | 80 | 2017-09-02 | 7 | 1 |
| 1008 | 780 | 2016-03-07 | 1 | 3 |
| 1009 | 560 | 2017-10-07 | 3 | 7 |
| 1010 | 900 | 2016-01-08 | 6 | 8 |
Quyidagi kod kerakli tanlovni amalga oshiradi:
"2016-01-01" va "2016-12-31" QAYSI QAYERDA BO'LADI, SUM (amt) ni tanlang;Natijada biz quyidagilarni olamiz:
| SUM (amt) |
|---|
| 4428 |
Ushbu so'rovda biz foydalanganmiz sUM funktsiyasi, shundan so'ng qavsda siz yig'ish uchun maydonni ko'rsatishingiz kerak. Keyin biz shartni WHERE-da aniqladik, bu satrlarni faqat 2016 yildan tanladi. Aslida, bu shart boshqacha yozilishi mumkin, ammo endi SQL-dagi agregat yig'indisi funktsiyasi muhimroq.
AVG funktsiyasi
Keyingi funktsiya ma'lumotlar maydonining o'rtacha arifmetikasini hisoblab chiqadi, biz uni parametr sifatida belgilaymiz. Ushbu funktsiya uchun sintaksis yig'indisi funktsiyasi bilan bir xil. Shuning uchun, to'g'ridan-to'g'ri eng oddiy vazifaga o'tamiz:
Buyurtmalar jadvalidan o'rtacha buyurtma qiymatini ko'rsating.
Va darhol so'rov:
Buyurtmalardan AVG (amt) ni tanlang;Natijada biz quyidagilarni olamiz:
Shuni ham ta'kidlash joizki, avvalgi funktsiyalardan farqli o'laroq, bu 2 ta belgi parametrlari bilan ishlashi mumkin, ya'ni siz quyidagi kabi so'rov yozishingiz mumkin. MIN (odate) (bu holda bizda ramziy sana bor), keyin 2016-01-01 bizga qaytariladi.
Haqiqat shundaki, ushbu funktsiyalar belgilarni ASCII kodiga aylantirish mexanizmiga ega va ular keyinchalik ularni taqqoslashadi.
Yana bir muhim jihat shundaki, biz SELECT so'rovida ba'zi oddiy matematik operatsiyalarni bajarishimiz mumkin, masalan quyidagi so'rov:
Buyurtmalardan "farq" sifatida SELECT (MAX (amt) - MIN (amt));Quyidagi javobni qaytaradi:
Shubhasiz, buyurtmalar soni 10 tani tashkil etadi, ammo agar siz to'satdan katta stolga ega bo'lsangiz, unda bu funktsiya juda qulay bo'ladi. Noyob sotuvchilar uchun DISTINCT bu erda ishlatilishi kerak, chunki bitta sotuvchi bir nechta buyurtmalarni bajarishi mumkin.
GROUP BY bayonoti
Keling, SQL so'rovlarimizning funksiyasini kengaytirishga yordam beradigan 2 ta muhim operatorni ko'rib chiqamiz. Ulardan birinchisi, GROUP BY bandi bo'lib, u har qanday soha bo'yicha guruhlanadi, bu ba'zan zarur. Va allaqachon ushbu guruh uchun u belgilangan harakatni amalga oshiradi. Masalan:
Har bir sotuvchi uchun barcha buyurtmalar summasini alohida-alohida ko'rsatish.
Ya'ni, endi biz buyurtmalar jadvalidagi har bir sotuvchi uchun buyurtma narxi ko'rsatilgan maydonlarni tanlashimiz va ularni sarhisob qilishimiz kerak. Bularning barchasi SQL-dagi GROUP BY bandini juda osonlashtiradi:
Snum, SUM (amt) AS ni tanlang "Barcha buyurtmalar summasi" Buyurtmalar GROUP BY snum dan;Va oxirida biz quyidagilarni olamiz:
| qaqshatqich | Barcha buyurtmalar summasi |
|---|---|
| 1 | 428 |
| 3 | 1280 |
| 4 | 947 |
| 7 | 2360 |
| 8 | 900 |
Ko'rib turganingizdek, SQL har bir sotuvchi uchun guruh ajratdi va ularning barcha buyurtmalarining summasini hisoblab chiqdi.
Operatori bor
Ushbu operator oldingisiga qo'shimcha sifatida ishlatiladi. Guruhlash paytida ma'lumotlarni tanlash uchun shartlarni belgilash uchun kerak. Agar shart bajarilsa, u holda guruh tanlanadi, agar bo'lmasa, unda hech narsa bo'lmaydi. Quyidagi kodni ko'rib chiqing:
Snum, SUM (amt) AS ni tanlang "Barcha buyurtmalar summasi" Buyurtmalardan GROUP BY snum HAVING MAX (amt)\u003e 1000;Qaysi biri sotuvchi uchun guruh yaratadi va ushbu guruhdagi buyurtmalar miqdorini hisoblab chiqadi, faqat buyurtmaning maksimal miqdori 1000 dan oshgan bo'lsa. Shubhasiz, bunday sotuvchi faqat bitta bo'lsa, u uchun guruh tanlanadi va barcha buyurtmalar summasi hisoblanadi:
| qaqshatqich | Barcha buyurtmalar summasi |
|---|---|
| 7 | 2360 |
Nima uchun bu erda WHERE bandini ishlatmaslik kerak, ammo SQL shunday tuzilganki, bu holda u xatoga yo'l qo'yishi mumkin va shuning uchun SQLda HAVING operatori mavjud.
SQL-dagi agregat funktsiyalariga misollar
1. 2016 yil 1 yanvarda bajarilgan barcha buyurtmalarni hisoblaydigan so'rovni yozing.
SUM (amt) TO WHERE Buyurtmalardan tanlang odate \u003d "2016-01-01";2. Mijozlar jadvalida shahar maydoni uchun aniq nol bo'lmagan qiymatlar sonini hisoblaydigan so'rovni yozing.
Mijozlardan COUNT (DISTINCT city) ni tanlang;3. Har bir mijoz uchun eng kichik miqdorni tanlaydigan so'rov yozing.
SELECT cnum, MIN (amt) FROM GROUP BY cnum buyurtmalaridan;4. Ismlari D harfi bilan boshlanadigan mijozlarni tanlaydigan so'rov yozing.
Mijozlardan "G%" LIKE kabi cname ni tanlang.5. Har bir shaharda eng yuqori reytingni tanlaydigan so'rovni yozing.
SELECT city, MAX (reyting) mijozlardan GROUP BY city;Xulosa
Shu bilan tugaymiz. Ushbu maqolada biz SQL-dagi agregat funktsiyalar haqida bilib oldik. Keyinchalik foydali bo'lishi mumkin bo'lgan asosiy tushunchalar va asosiy misollarni tahlil qildik.
Agar siz hali ham savollaringiz bo'lsa, ularni sharhlaringizda so'rang.
Quyidagi bo'limlarda so'rovlarda ishlatilishi mumkin bo'lgan boshqa SELECT bandlari, shuningdek agregat funktsiyalari va operatorlar to'plamlari tasvirlangan. Eslatib o'taman, biz hozirgacha "WHERE" bandidan foydalanishni ko'rib chiqdik va ushbu maqolada "GROUP BY", "ORDER BY" va "HAVING" bandlarini ko'rib chiqamiz va ushbu bandlarni Transact-SQL-da qo'llab-quvvatlanadigan agregat funktsiyalari bilan birgalikda ishlatishga ba'zi misollar keltiramiz.
GROUP BY bandi
Hukm GROUP BY bir yoki bir nechta ustunlar yoki ifodalarning qiymatlari asosida xulosa qatorini olish uchun tanlangan qatorlarni guruhlarga ajratadi. GROUP BY bandi uchun oddiy foydalanish holati quyidagi misolda keltirilgan:
SampleDb-dan foydalanish; Job tomonidan Works_On GROUP dan ish tanlash;
Ushbu misolda xodimlarning lavozimlari namuna olinadi va guruhlanadi.
Yuqoridagi misolda GROUP BY bandi "Job" ustuni uchun barcha mumkin bo'lgan qiymatlar (shu jumladan NULL) uchun alohida guruh yaratadi.
GROUP BY bandidagi ustunlardan foydalanish ma'lum shartlarga javob berishi kerak. Xususan, so'rovni olish ro'yxatidagi har bir ustun GROUP BY bandida ham bo'lishi kerak. Ushbu talab agregat funktsiyasining bir qismi bo'lgan doimiy va ustunlarga taalluqli emas. (Agregat funktsiyalari keyingi kichik bo'limda muhokama qilinadi.) Bu mantiqan to'g'ri keladi, chunki faqat GROUP BY bandidagi ustunlar har bir guruh uchun bitta qiymatga ega bo'lishiga kafolat beradi.
Jadvalni har qanday ustunlar kombinatsiyasi bo'yicha guruhlash mumkin. Quyidagi misol Works_on jadvalidagi satrlarni ikkita ustun bo'yicha guruhlashni namoyish etadi:
SampleDb-dan foydalanish; Works_On GROUP BY ProjectNumber, Job'dan FROM ProjectNumber, Job ni tanlang;
Ushbu so'rov natijasi:
So'rov natijalariga ko'ra siz loyiha raqami va sarlavhasining turli xil kombinatsiyalariga ega to'qqizta guruh mavjudligini ko'rishingiz mumkin. GROUP BY bandidagi ustunlar nomlari ketma-ketligi SELECT ustunlar ro'yxatidagi kabi bo'lishi shart emas.
Yig'ish funktsiyalari
Summa qiymatlarini olish uchun agregat funktsiyalaridan foydalaniladi. Barcha funktsiyalarni quyidagi toifalarga bo'lish mumkin:
oddiy agregat funktsiyalari;
statistik yig'ma funktsiyalar;
foydalanuvchi tomonidan belgilangan yig'ma funktsiyalar;
analitik agregat funktsiyalari.
Bu erda biz agregat funktsiyalarining dastlabki uch turini ko'rib chiqamiz.
Muntazam yig'iladigan funktsiyalar
Transact-SQL quyidagi oltita umumiy funktsiyalarni qo'llab-quvvatlaydi: MIN, MAX, JUM, AVG, COUNT, COUNT_BIG.
Barcha agregat funktsiyalari ustun yoki ifoda bo'lishi mumkin bo'lgan bitta argument bo'yicha hisob-kitoblarni amalga oshiradi. (Faqatgina istisno - bu COUNT va COUNT_BIG ikkita funktsiyalarning ikkinchi shakli, ya'ni COUNT (*) va COUNT_BIG (*)). Har qanday agregat funktsiyasi alohida natija ustunida paydo bo'ladigan doimiy qiymatga baho beradi.
Yig'ish funktsiyalari SELECT bayonotining ustunlar ro'yxatida ko'rsatilgan bo'lib, unda GROUP BY bandi ham bo'lishi mumkin. Agar SELECT buyrug'ida GROUP BY bandi bo'lmasa va tanlangan ustunlar ro'yxatida kamida bitta agregat funktsiyasi bo'lsa, unda unda oddiy ustunlar bo'lmasligi kerak (yig'ish funktsiyasi uchun argument sifatida ishlatiladigan ustunlardan tashqari). Shuning uchun quyidagi misoldagi kod noto'g'ri:
SampleDb-dan foydalanish; Xodimdan familiyani, MIN (Id) ni tanlang;
Bu erda "Employee" jadvalining LastName ustuni tanlangan ustunlar ro'yxatida bo'lmasligi kerak, chunki bu agregat funktsiyasi uchun argument emas. Boshqa tomondan, tanlangan ustunlar ro'yxati, agar bu ustunlar GROUP BY bandiga argument sifatida ishlatilsa, agregat funktsiyasi uchun argument bo'lmagan ustun nomlarini o'z ichiga olishi mumkin.
Agregate function argumenti oldida ikkita mumkin bo'lgan kalit so'zlardan biri bo'lishi mumkin:
HAMMAHisob-kitoblar ustundagi barcha qiymatlar bo'yicha amalga oshirilishini bildiradi. Bu sukut bo'yicha.
BILISHHisoblash uchun faqat noyob ustun qiymatlari ishlatilishini belgilaydi.
MIN va MAX funktsiyalarini yig'ish
MIN va MAX umumiy funktsiyalari navbati bilan ustundagi eng kichik va eng katta qiymatlarni hisoblab chiqadi. Agar so'rovda WHERE bandi bo'lsa, MIN va MAX funktsiyalari belgilangan shartlarga javob beradigan qatorlar uchun eng kichik va eng katta qiymatlarni qaytaradi. Quyidagi misol MIN agregat funktsiyasidan foydalanishni ko'rsatadi:
SampleDb-dan foydalanish; - Xodimdan 2581 SELECT MIN (Id) "Minimum Id value" AS;
Yuqoridagi misolda qaytarilgan natija unchalik ma'lumotli emas. Masalan, ushbu raqamga ega bo'lgan xodimning familiyasi noma'lum. Ammo siz ushbu familiyani odatdagi tarzda ololmaysiz, chunki avval aytib o'tganimizdek, familiya ustunini aniq ko'rsatolmaysiz. Ushbu xodimning familiyasini xodimning eng past soni bilan birga olish uchun, so'rovnomadan foydalaniladi. Quyidagi misolda pastki so'rovda oldingi misoldan SELECT iborasi mavjud bo'lgan bunday pastki so'rovdan foydalanish ko'rsatilgan:
So'rovni bajarish natijasi:
MAX agregat funktsiyasidan foydalanish quyidagi misolda keltirilgan:
MIN va MAX funktsiyalari qatorlar va sanalarni argument sifatida qabul qilishi mumkin. Satr argumenti bo'lsa, qiymatlar haqiqiy saralash tartibi yordamida taqqoslanadi. Barcha vaqtinchalik sana argumentlari uchun eng past ustun qiymati eng erta sana, eng yuqori darajasi esa so'nggi hisoblanadi.
DISTINCT kalit so'zidan MIN va MAX funktsiyalari bilan foydalanish mumkin. Barcha NULL qiymatlari argument ustunlaridan MIN va MAX yig'ma funktsiyalari qo'llanilishidan oldin o'chiriladi.
SUM agregat funktsiyasi
Umumiy sUM funktsiyasi ustun qiymatlarining umumiy yig'indisini hisoblaydi. Ushbu agregat funktsiyasining argumenti har doim raqamli ma'lumotlar turiga ega bo'lishi kerak. SUM agregat funktsiyasidan foydalanish quyidagi misolda keltirilgan:
SampleDb-dan foydalanish; SELECT SUM (byudjet) loyiha "Umumiy byudjet";
Bu, masalan, barcha loyihalar uchun umumiy byudjetlar uchun hisoblangan. So'rovni bajarish natijasi:
Ushbu misolda yig'ilgan funktsiya loyihaning barcha byudjet qiymatlarini guruhlaydi va ularning umumiy miqdorini aniqlaydi. Shu sababli, so'rovda yashirin guruhlash funktsiyasi mavjud (shunga o'xshash barcha so'rovlar singari). Yuqoridagi misoldan yashirin guruhlash funktsiyasi quyidagi misolda ko'rsatilgandek aniq ko'rsatilishi mumkin:
SampleDb-dan foydalanish; LOYIHA GROUP BY () dan "Jami byudjet" SUM (byudjet) ni tanlang;
DISTINCT parametridan foydalanish SUM funktsiyasini qo'llashdan oldin ustundagi har qanday takrorlanadigan qiymatlarni olib tashlaydi. Xuddi shunday, ushbu funktsiyani ishlatishdan oldin barcha NULL qiymatlarini olib tashlang.
Aggregat funktsiyasi AVG
Umumiy aVG funktsiyasi ustundagi barcha qiymatlarning o'rtacha arifmetikasini qaytaradi. Ushbu agregat funktsiyasining argumenti har doim raqamli ma'lumotlar turiga ega bo'lishi kerak. AVG funktsiyasi qo'llanilishidan oldin argumentdan barcha NULL qiymatlari olib tashlanadi.
AVG agregati funktsiyasidan foydalanish quyidagi misolda keltirilgan:
SampleDb-dan foydalanish; - qaytaradi 133833 SELECT AVG (Budget) "Loyiha uchun o'rtacha byudjet" FROM Project;
Bu erda byudjetning o'rtacha arifmetikasi barcha byudjetlar uchun hisoblanadi.
COUNT va COUNT_BIG funktsiyalarini yig'ish
Umumiy cOUNT funktsiyasi ikki xil shaklga ega:
COUNT (col_name) COUNT (*)
Funktsiyaning birinchi shakli col_name ustunidagi qiymatlar sonini hisoblaydi. Agar so'rovda DISTINCT kalit so'zi ishlatilsa, barcha takrorlangan ustun qiymatlari COUNT funktsiyasidan oldin o'chiriladi. COUNT funktsiyasining ushbu shakli ustun qiymatlari sonini hisoblashda NULL qiymatlarini hisobga olmaydi.
COUNT agregat funktsiyasining birinchi shaklidan foydalanish quyidagi misolda keltirilgan:
SampleDb-dan foydalanish; Works_on GROUP BY ProjectNumber-dan ProjectNumber, COUNT (DISTINCT Job) "Project in Jobs" ni tanlang;
Bu erda har bir loyiha uchun turli pozitsiyalar soni hisoblanadi. Ushbu so'rov natijasi:
Misolda ko'rsatilgan so'rovni bajarish natijasidan ko'rinib turibdiki, COUNT funktsiyasi NULL qiymatlarini hisobga olmadi. (Ish ustunidagi barcha qiymatlarning yig'indisi kerak bo'lganda 11 emas, 7 ga teng.)
COUNT funktsiyasining ikkinchi shakli, ya'ni. COUNT (*) funktsiyasi jadvaldagi qatorlar sonini hisoblaydi. Va COUNT (*) funktsiyasi bilan so'rovning SELECT iborasi sharti bo'lgan WHERE bandini o'z ichiga olgan bo'lsa, funktsiya belgilangan shartni qondiradigan qatorlar sonini qaytaradi. COUNT funktsiyasining birinchi versiyasidan farqli o'laroq, ikkinchi shakl NULL qiymatlarini e'tiborsiz qoldirmaydi, chunki bu funktsiya ustunlarda emas, balki satrlarda ishlaydi. Quyidagi misol COUNT (*) funktsiyasidan foydalanishni namoyish etadi:
SampleDb-dan foydalanish; Work_on GROUP BY Jobs tomonidan "Ish turi", COUNT (*) "Kerakli ishchilar" kabi ishlarni tanlang;
Bu erda barcha loyihalardagi postlar soni hisoblanadi. So'rovni bajarish natijasi:
COUNT_BIG funktsiya COUNT bilan bir xil funktsiya. Ikkala orasidagi farq faqat ular qaytaradigan natija turida: COUNT_BIG funktsiyasi har doim BIGINT qiymatlarini qaytaradi, COUNT funktsiyasi esa INTEGER turidagi ma'lumotlar qiymatlarini qaytaradi.
Statistik yig'ma funktsiyalar
Statistik yig'ma funktsiyalar guruhini quyidagi funktsiyalar tashkil etadi:
VARUstun yoki ifodadagi barcha qiymatlarning statistik dispersiyasini hisoblab chiqadi.
VARPUstundagi yoki ifodadagi barcha qiymatlar to'plamining statistik farqini hisoblab chiqadi.
STDEVUstundagi yoki ifodadagi barcha qiymatlarning standart og'ishini (tegishli dispersiyaning kvadrat ildizi sifatida hisoblanadi) hisoblab chiqadi.
STDEVPUstundagi yoki ifodadagi barcha qiymatlar yig'indisining standart og'ishini hisoblaydi.
Foydalanuvchi tomonidan belgilangan umumiy funktsiyalar
Ma'lumotlar bazasi mexanizmi foydalanuvchi tomonidan belgilangan funktsiyalarni amalga oshirishni ham qo'llab-quvvatlaydi. Ushbu imkoniyat foydalanuvchilarga tizimning umumiy funktsiyalarini o'zlari amalga oshiradigan va o'rnatadigan funktsiyalar bilan to'ldirishga imkon beradi. Ushbu funktsiyalar foydalanuvchi tomonidan aniqlangan funktsiyalarning maxsus sinfini anglatadi va keyinchalik batafsil muhokama qilinadi.
QOIDA QO'YISh
Gapda YO'Q qatorlar guruhiga qo'llaniladigan shartni belgilaydi. Shunday qilib, ushbu band satr guruhlari uchun tegishli jadval tarkibidagi WHERE bandi bilan bir xil ma'noga ega. HAVING bandi uchun sintaksis quyidagicha:
Ahvoli bor
Bu erda shart parametri shartni ifodalaydi va tarkibiga agregat funktsiyalari yoki doimiylari kiradi.
COUNT (*) yig'ish funktsiyasi bilan birgalikda HAVING bandini ishlatish quyidagi misolda keltirilgan:
SampleDb-dan foydalanish; - ProjectNumber HOWING COUNT (*) guruhidan Works_on GROUP guruhidan SELECT ProjectNumber raqamini "p3" ga qaytaradi.
Ushbu misolda tizim barcha qatorlarni ProjectNumber ustunidagi qiymatlar bo'yicha guruhlash uchun GROUP BY bandidan foydalanadi. Shundan so'ng, har bir guruhdagi qatorlar soni hisoblanadi va to'rt qatordan kam (uchta yoki undan kam) bo'lgan guruhlar tanlanadi.
HAVING bandi quyida keltirilgan misolda ko'rsatilganidek, umumiy funktsiyalarsiz ham ishlatilishi mumkin:
SampleDb-dan foydalanish; - "K%" kabi ish joyiga ega bo'lgan ishchi tomonidan GROUP-dan ISHLAB CHIQARIShdagi "maslahatchi" qaytib keladi;
Ushbu misol Works_on jadvalidagi qatorlarni ish nomi bo'yicha guruhlaydi va "K" harfi bilan boshlanmaydigan ishlarni yo'q qiladi.
HAVING bandi GROUP BY bandisiz ham ishlatilishi mumkin, ammo bu odatiy hol emas. Bunday holda, jadvalning barcha satrlari bir xil guruhga qaytariladi.
Buyurtma bo'yicha
Hukm Buyurtma bo'yicha so'rov bilan qaytarilgan natija to'plamidagi qatorlarning tartib tartibini belgilaydi. Ushbu jumla quyidagi sintaksisga ega:
Saralash tartibi col_name parametrida ko'rsatilgan. Col_number - bu ustunlarni SELECT iborasining tanlangan ro'yxatida paydo bo'lgan tartibda aniqlaydigan tartiblashtirishning navbatdagi ko'rsatkichi (1 - birinchi ustun, 2 - ikkinchi ustun va hk). ASC parametri tartiblashni o'sish tartibida belgilaydi va dESC parametri - quyi oqimda. Asl qiymati ASC.
"ORDER BY" bandidagi ustunlar nomlari tanlangan ustunlar ro'yxatida bo'lishi shart emas. Ammo bu SELECT DISTINCT kabi so'rovlarga taalluqli emas, chunki bunday so'rovlarda, ORDER BY bandida ko'rsatilgan ustun nomlari tanlangan ustunlar ro'yxatida ham ko'rsatilishi kerak. Bundan tashqari, ushbu band FROM bandida ko'rsatilmagan jadvallarning ustun nomlarini o'z ichiga olmaydi.
"ORDER BY" bandining sintaksisidan ko'rinib turibdiki, natijalar to'plamini saralash bir nechta ustunlarda amalga oshirilishi mumkin. Ushbu saralash quyidagi misolda keltirilgan:
Ushbu misol xodimlar soni 20000 dan kam bo'lgan xodimlar uchun bo'lim raqamlari va xodimlarning familiyalari va ismlarini tanlaydi va familiyasi va ismi bo'yicha saralanadi. Ushbu so'rov natijasi:
"ORDER BY" bandidagi ustunlar ularning nomlari bilan emas, balki tanlov ro'yxatidagi buyurtma bilan belgilanishi mumkin. Shunga ko'ra, yuqoridagi misolda keltirilgan jumla quyidagi tarzda qayta yozilishi mumkin:
Ustunlarni nomlar o'rniga ularning pozitsiyalari bo'yicha belgilashning ushbu muqobil usuli, agar buyurtma mezonida agregat funktsiyasi bo'lsa ishlatiladi. (Yana bir usul - bu "ORDER BY" bandida paydo bo'ladigan ustun nomlarini ishlatishdir.) Ammo, "ORDER BY" bandida, so'rovni yangilashni osonlashtirish uchun ustunlarni raqamlar bilan emas, balki ularning nomlari bilan belgilash tavsiya etiladi, agar tanlanganlar ro'yxatiga ustunlarni qo'shish yoki olib tashlash kerak bo'lsa. "ORDER BY" bandidagi ustunlarni ularning raqamlari bo'yicha ko'rsatish quyidagi misolda keltirilgan:
SampleDb-dan foydalanish; Select_NET, COUNT (*) "Xodimlar soni" dan Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
Bu erda har bir loyiha uchun loyiha raqami va unda qatnashadigan xodimlar soni tanlanadi, natijada xodimlar soniga qarab kamayib boruvchi tartibda buyurtma beriladi.
Transact-SQL o'sish tartibida saralashda ro'yxatning yuqori qismida va kamayish tartibida saralashda ro'yxatning oxiriga NULL qiymatlarini joylashtiradi.
Natijalarni sahifalash uchun ORDER BY Clause-dan foydalanish
So'rov natijalarini joriy sahifada aks ettirish maxsus dasturda amalga oshirilishi yoki ma'lumotlar bazasi serveri tomonidan bajarilishi mumkin. Birinchi holda, ma'lumotlar bazasining barcha qatorlari dasturga yuboriladi, ularning vazifasi kerakli qatorlarni tanlash va ularni ko'rsatishdir. Ikkinchi holda, faqat joriy sahifa uchun zarur bo'lgan qatorlar olinadi va server tomondan ko'rsatiladi. Siz kutganingizdek, server tomonida sahifalar yaratish odatda eng yaxshi ishlashni ta'minlaydi. faqat namoyish qilish uchun zarur bo'lgan satrlar mijozga yuboriladi.
Server tomonida sahifa yaratishni qo'llab-quvvatlash uchun SQL Server 2012 ikkita yangi SELECT bandini taqdim etadi: OFFSET va FETCH. Ushbu ikkita jumlaning qo'llanilishi quyidagi misolda keltirilgan. Bu erda AdventureWorks2012 ma'lumotlar bazasidan (manbada topishingiz mumkin), u ishbilarmonlik identifikatori, lavozimi nomi va barcha ayol ishchilarning tug'ilgan kuni, lavozim nomi bo'yicha o'sish tartibida saralanadi. Olingan qatorlar qatori 10 qatorli sahifalarga bo'linadi va uchinchi sahifa ko'rsatiladi:
Gapda OFFSET ko'rsatilgan natijada o'tkazib yuboriladigan natija qatorlari sonini belgilaydi. Ushbu miqdor qatorlarni "ORDER BY" bandi bilan tartiblanganidan keyin hisoblanadi. Gapda KEYINGI OLISH qayerda QAYER va tartiblangan qatorlar sonini belgilaydi. Ushbu bandning parametri doimiy, ifoda yoki boshqa so'rov natijasi bo'lishi mumkin. FETCH NEXT jumlasi gapga o'xshaydi BIRINChI FETCH.
Server tomonida sahifalar yaratishda asosiy maqsad o'zgaruvchilardan foydalangan holda umumiy sahifa shakllarini amalga oshirish imkoniyatiga ega bo'lishdir. Ushbu vazifani SQL Server to'plami orqali amalga oshirish mumkin.
Bayonnomani va identifikator xususiyatini tanlang
IDENTITY mulk avtomatik ravishda qo'shimcha hisoblagich shaklida jadvalning ma'lum bir ustuni uchun qiymatlarni aniqlashga imkon beradi. TINYINT, SMALLINT, INT va BIGINT kabi raqamli ma'lumotlar turidagi ustunlar ushbu xususiyatga ega bo'lishi mumkin. Bunday jadval ustuni uchun ma'lumotlar bazasi mexanizmi avtomatik ravishda belgilangan boshlang'ich qiymatidan boshlab ketma-ket qiymatlarni hosil qiladi. Shuning uchun, IDENTITY xususiyati tanlangan ustun uchun aniq raqamli qiymatlarni yaratish uchun ishlatilishi mumkin.
Jadvalda IDENTITY xususiyatiga ega bo'lgan bitta ustun bo'lishi mumkin. Jadval egasi quyidagi misolda ko'rsatilgandek boshlang'ich qiymatini va o'sishini belgilash imkoniyatiga ega:
SampleDb-dan foydalanish; Jadvalni yarating (Id INT IDENT (10000, 1) NULL emas, NVARCHAR (30) NULL emas, narx PUL) Mahsulotga (ism, narx) QIMMATLARNI KIRISH ("Product1", 10), ("Product2", 15) , ("Product3", 8), ("Product4", 15), ("Product5", 40); - 10004 SELECT IDENTITYCOL-ni mahsulotni QAYERDA qaytaradi Name \u003d "Product5"; - Oldingi bayonotga o'xshash narsa SELECT $ identifikatorni FROM Product WHERE Name \u003d "Product5";
Ushbu misol avval identifikator xususiyatiga ega Id ustunini o'z ichiga olgan Mahsulotlar jadvalini yaratadi. Id ustunidagi qiymatlar tizim tomonidan avtomatik ravishda yaratiladi, 10000 dan boshlab va har bir keyingi qiymat uchun bir qadam ortib boradi: 10,000, 10,001, 10,002 va boshqalar.
IDENTITY xususiyati bilan bir nechta tizim funktsiyalari va o'zgaruvchilari bog'liqdir. Masalan, misol kodidan foydalaniladi tizim o'zgaruvchisi $ identifikatori... Ushbu kodni ishlatish natijalaridan ko'rinib turibdiki, ushbu o'zgaruvchiga avtomatik ravishda IDENTITY xususiyatiga murojaat qilinadi. Buning o'rniga tizim funktsiyasidan foydalanishingiz mumkin IDENTITYCOL.
IDENTITY xususiyatiga ega ustunning dastlabki qiymati va qadamini funktsiyalar yordamida topish mumkin IDENT_SEED va IDENT_INCR navbati bilan. Ushbu funktsiyalar quyidagicha qo'llaniladi:
SampleDb-dan foydalanish; SELECT IDENT_SEED ("Mahsulot"), IDENT_INCR ("Mahsulot")
Yuqorida aytib o'tilganidek, IDENTITY qiymatlari tizim tomonidan avtomatik ravishda o'rnatiladi. Ammo foydalanuvchi parametrni belgilash orqali ma'lum satrlar uchun ularning qiymatlarini aniq belgilashi mumkin IDENTITY_INSERT aniq qiymatni kiritmasdan oldin ON qiymati:
SET IDENTSET INSERT jadvalining nomi ON
IDENTITY_INSERT parametri yordamida IDENTITY xususiyati bilan ustunni istalgan qiymatga, shu jumladan dublikatiga o'rnatishda foydalanish mumkinligi sababli, IDENTITY xususiyati odatda ustun qiymatlarining o'ziga xosligini ta'minlamaydi. Shuning uchun ustun qiymatlarining o'ziga xosligini ta'minlash uchun UNIQUE yoki PRIMARY KEY cheklovlari qo'llanilishi kerak.
IDENTITY_INSERT-ni yoqgandan so'ng jadvalga qiymatlarni qo'shsangiz, tizim IDENTITY ustunida navbatdagi qiymatni yaratadi va shu ustunning eng yuqori qiymatini oshiradi.
SEQUENCE bayonotini yarating
IDENTITY xususiyatidan foydalanish bir nechta muhim kamchiliklarga ega, ulardan eng muhimi:
mulk belgilangan jadval bilan cheklangan;
yangi ustun qiymatini uni qo'llashdan boshqa usul bilan olish mumkin emas;
iDENTITY xususiyati faqat ustun yaratishda ko'rsatilishi mumkin.
Shu sabablarga ko'ra SQL Server 2012 IDENTITY xususiyati bilan bir xil semantikaga ega bo'lgan, ammo ilgari ro'yxatda ko'rsatilgan kamchiliklarsiz ketma-ketliklarni taqdim etadi. Shu nuqtai nazardan, ketma-ketlik ma'lumotlar bazasining turli xil ob'ektlari, masalan, ustunlar va o'zgaruvchilar uchun hisoblagich qiymatlarini belgilashga imkon beradigan funksionalligini anglatadi.
Ketma-ketliklar ko'rsatma yordamida yaratiladi TARMOQNI YARATISH... CREATE SEQUENCE iborasi SQL standartida aniqlangan va IBM DB2 va Oracle kabi boshqa relyatsion ma'lumotlar bazalari tomonidan qo'llab-quvvatlanadi.
Quyidagi misol SQL Serverda ketma-ketlikni qanday yaratishni ko'rsatadi:
SampleDb-dan foydalanish; 5 MINVALUE 1 MAXVALUE 256 ÇEVRİMİNE 1 artımlarla bilan sekans dbo.Sequence1 AS INT START yaratish;
Yuqoridagi misolda Sequence1 qiymatlari tizim tomonidan avtomatik ravishda hosil bo'lib, har bir keyingi qiymat uchun 1 dan va 5 o'simtadan boshlanadi. Shunday qilib, ichida sTART taklifi boshlang'ich qiymati ko'rsatilgan va iNCREMENT taklifi - qadam. (Qadam ijobiy yoki salbiy bo'lishi mumkin.)
Keyingi ikkita ixtiyoriy jumlada MINVALUE va MAXVALUE ketma-ketlik ob'ektining minimal va maksimal qiymatini belgilaydi. (MINVALUE boshlang'ich qiymatdan kichik yoki unga teng bo'lishi kerak va MAXVALUE ketma-ketlik uchun belgilangan ma'lumotlar turining yuqori chegarasidan katta bo'lmasligini unutmang.) Gapda VAQT maksimal (yoki manfiy qadam bilan ketma-ketlik uchun minimal) qiymatdan oshganda ketma-ketlik boshidan takrorlanishini bildiradi. Odatiy bo'lib, ushbu band NO CYCLE-ga o'rnatildi, ya'ni maksimal yoki minimal ketma-ketlik qiymatidan oshib ketish istisnoga olib keladi.
Ketma-ketlikning asosiy xususiyati ularning jadvallardan mustaqilligi, ya'ni. ular jadval ustunlari yoki o'zgaruvchilar kabi har qanday ma'lumotlar bazasi ob'ekti bilan ishlatilishi mumkin. (Ushbu xususiyat saqlashga va shuning uchun ishlashga ijobiy ta'sir ko'rsatadi. Muayyan ketma-ketlikni saqlash kerak emas, faqat uning oxirgi qiymati saqlanadi.)
Yangi ketma-ketlik qiymatlari yaratilgan ifodalar uchun keyingi qiymat, uning qo'llanilishi quyidagi misolda keltirilgan:
SampleDb-dan foydalanish; - qaytaradi 1 dbo.sequence1 UCHUN KEYINGI VALUE ni tanlang; - 6-ni qaytaradi (keyingi qadam) dbo.sequence1 UCHUN KEYINGI VALUE ni tanlang;
NEXT VALUE FOR ifodasi ketma-ketlik natijasini o'zgaruvchiga yoki ustunli katakka beradi. Quyidagi misol natijalarni ustunga belgilash uchun ushbu ifodadan foydalanilishini ko'rsatadi:
SampleDb-dan foydalanish; Jadvalni yarating (Id INT NULL emas, NVARCHAR nomi (30) NULL emas, narxi PUL) Mahsulot qiymatlariga Kiritish (dbo.sequence1 uchun keyingi qiymat, "Product1", 10); Mahsulot qiymatlariga qo'shish (dbo.sequence1 uchun keyingi qiymat, "Product2", 15); - ...
Yuqoridagi misol birinchi navbatda to'rtta ustunli Mahsulot deb nomlangan jadval yaratadi. So'ngra ikkita INSERT bayonoti ushbu jadvalga ikkita qator kiritadi. Birinchi ustunning dastlabki ikkita katagi 11 va 16 bo'ladi.
Quyidagi misol katalog ko'rinishidan foydalanishni ko'rsatadi sys. oqibatlari ketma-ketlikning joriy qiymatini ishlatmasdan ko'rish uchun:
Odatda, NEXT VALUE FOR ifodasi tizimni hosil qilingan qiymatlarni kiritishga majbur qilish uchun INSERT operatorida ishlatiladi. Ushbu ibora OVER bandi yordamida ko'p satrli so'rovning bir qismi sifatida ham ishlatilishi mumkin.
Mavjud ketma-ketlikning xususiyatini o'zgartirish uchun murojaat qiling aLTER SEQUENCE bayonoti... Ushbu bayonot uchun eng muhim usullardan biri, ushbu ketma-ketlikni qayta tiklaydigan WITH RESTART variantiga nisbatan. Quyidagi misol Sequence1 ning deyarli barcha xususiyatlarini qayta tiklash uchun ALTER SEQUENCE-dan foydalanishni namoyish etadi:
SampleDb-dan foydalanish; O'zgarishlar ketma-ketligi dbo.sequence1 50 MINVALUE 50 MAXVALUE 200 TARAFIDAN YO'Q 100 BERISh BILAN QAYTARILADI;
Ko'rsatma yordamida ketma-ketlik o'chiriladi YO'Q QO'YISh.
Operatorlarni o'rnating
Avvalroq muhokama qilingan operatorlardan tashqari Transact-SQL yana uchta o'rnatilgan operatorlarni qo'llab-quvvatlaydi: UNION, INTERSECT va EXCEPT.
UNION operatori
UNION operatori ikki yoki undan ortiq so'rov natijalarini birlashma tarkibidagi barcha so'rovlarga tegishli barcha qatorlarni o'z ichiga olgan bitta natija to'plamiga birlashtiradi. Binobarin, ikkita jadvalni birlashtirish natijasi asl jadvallarning biridagi yoki ikkalasidagi barcha qatorlarni o'z ichiga olgan yangi jadvaldir.
UNION operatorining umumiy shakli quyidagicha:
select_1 UNION select_2 (select_3]) ...
Select_1, select_2, ... variantlari bu qo'shilishni yaratadigan SELECT so'zlari. Agar ALL parametri ishlatilsa, barcha satrlar, shu jumladan dublikatlar ko'rsatiladi. UNION bayonotida ALL parametri SELECT bilan bir xil ma'noga ega, ammo bitta farq bilan: SELECT uchun bu parametr standart hisoblanadi, lekin UNION uchun u aniq ko'rsatilishi kerak.
Asl shaklida, SampleDb UNION operatoridan foydalanishni namoyish qilish uchun mos emas. Shuning uchun, ushbu bo'lim mavjud EmployeeEnh jadvalini yaratadi, u mavjud Employee jadvali bilan bir xil, ammo qo'shimcha shahar ustuniga ega. Ushbu ustunda xodimlarning yashash joyi ko'rsatilgan.
EmployeeEnh jadvalini yaratish bizga ushbu banddan foydalanishni namoyish etish imkoniyatini beradi ICHIGA SELECT bayonotida. SELECT INTO bayonoti ikkita amalni bajaradi. Birinchidan, SELECT ro'yxatida berilgan ustunlar bilan yangi jadval tuziladi. Keyin asl jadvaldagi qatorlar yangi jadvalga kiritiladi. INTO bandida yangi jadval nomi, FROM bandida manba jadvalining nomi ko'rsatilgan.
Quyidagi misolda EmployeeEnh jadvalining Employee jadvalidan yaratilishi ko'rsatilgan:
SampleDb-dan foydalanish; SELECT * INTO EmployeeEnh FROM xodimi; ALTER JADVALI EmployeeEnh ADD City NCHAR (40) NULL;
Ushbu misolda SELECT INTO operatori EmployeeEnh jadvalini yaratadi, unga Employee manba jadvalidagi barcha qatorlarni kiritadi va keyin ALTER TABLE operatori yangi jadvalga City ustunini qo'shadi. Ammo qo'shilgan shahar ustunida hech qanday qiymat mavjud emas. Ushbu ustunga qiymatlarni Management Studio orqali yoki quyidagi kod yordamida kiritish mumkin:
SampleDb-dan foydalanish; Yangilash EmployeeEnh SET City \u003d "Qozon" Qaerda Id \u003d 2581; Yangilash EmployeeEnh SET City \u003d "Moskva" Qaerda Id \u003d 9031; Yangilash EmployeeEnh SET City \u003d "Yekaterinburg" Qaerda Id \u003d 10102; Yangilash EmployeeEnh SET City \u003d "Sankt-Peterburg" Qaerda Id \u003d 18316; Yangilash EmployeeEnh SET City \u003d "Krasnodar" Qaerda Id \u003d 25348; Yangilash EmployeeEnh SET City \u003d "Qozon" Qaerda Id \u003d 28559; Yangilanish EmployeeEnh SET City \u003d "Perm" Qaerda Id \u003d 29346;
Endi biz UNION bayonotidan foydalanishni namoyish etishga tayyormiz. Quyidagi misolda ushbu bayonot yordamida EmployeeEnh va Department jadvallari o'rtasida qo'shilish yaratish uchun so'rov ko'rsatilgan:
SampleDb-dan foydalanish; Xodimlar ittifoqidan shaharni "shahar" deb tanlang, bo'limdan joyni tanlang;
Ushbu so'rov natijasi:
UNION bayonoti yordamida faqat mos jadvallarni birlashtirish mumkin. Mos keluvchi jadvallar deganda, shuni anglatadiki, tanlovdagi har ikkala ustunlar ro'yxati bir xil sonli ustunlardan iborat bo'lishi kerak va tegishli ustunlar mos keladigan ma'lumotlar turlariga ega bo'lishi kerak. (Moslik nuqtai nazaridan INT va SMALLINT ma'lumotlar turlari mos emas.)
Birlashtirish natijasini faqat quyidagi misolda ko'rsatilgandek, oxirgi SELECT bayonotidagi ORDER BY bandi yordamida buyurtma qilish mumkin. GROUP BY va HAVING gaplari alohida SELECT so'zlari bilan ishlatilishi mumkin, lekin qo'shilishning o'zida emas.
Ushbu misoldagi so'rov d1 bo'limida ishlaydigan yoki 2008 yil 1 yanvardan oldin loyihada ish boshlagan xodimlarni tanlaydi.
UNION operatori ALL opsiyasini qo'llab-quvvatlaydi. Ushbu parametr natija to'plamidan dublikatlarni olib tashlamaydi. Agar bir yoki bir nechta UNION operatorlari qo'shilgan barcha SELECT operatorlari bir xil jadvalga murojaat qilsa, UN operatori o'rniga OR operatoridan foydalanish mumkin. Bunday holda, SELECT bayonotlari to'plami OR bayonotlari to'plami bo'lgan bitta SELECT bayonoti bilan almashtiriladi.
INTERSECT va operatorlar bundan mustasno
To'plam bilan ishlash uchun yana ikkita operator, INTERSECT va Istisno, mos ravishda kesishma va farqni aniqlang. Kesishma ostida, ushbu kontekstda, ikkala jadvalga tegishli qatorlar to'plami mavjud. Va ikkita jadval o'rtasidagi farq birinchi jadvalga tegishli bo'lgan va ikkinchisida mavjud bo'lmagan barcha qiymatlar sifatida aniqlanadi. Quyidagi misol INTERSECT operatoridan foydalanishni ko'rsatadi:
Transact-SQL ALL parametridan INTERSECT operatori yoki EXCEPT operatori bilan foydalanishni qo'llab-quvvatlamaydi. EXCEPT operatoridan foydalanish quyidagi misolda keltirilgan:
Shuni esda tutingki, ushbu uchta o'rnatilgan operatorlar bajarilishning har xil ustunligiga ega: INTERSECT eng yuqori ustunlikka ega, undan keyin EXCEPT va UNION eng past ustunlikka ega. Bir nechta turli xil operatorlardan foydalanishda bajarilish ustuvorligiga e'tibor bermaslik kutilmagan natijalarga olib kelishi mumkin.
CASE iboralari
Ma'lumotlar bazasini dasturiy dasturlash sohasida ba'zida ma'lumotlar taqdimotini o'zgartirish kerak bo'ladi. Masalan, odamlarni o'zlarining ijtimoiy sinfiga qarab ajratish mumkin, ular 1, 2 va 3 qiymatlaridan foydalangan holda, mos ravishda erkaklar, ayollar va bolalarni bildiradi. Ushbu dasturlash texnikasi dasturni amalga oshirish uchun zarur bo'lgan vaqtni qisqartirishi mumkin. CASE ifodasi Transact-SQL sizga ushbu turdagi kodlashni osonlikcha amalga oshirishga imkon beradi.
Ko'pgina dasturlash tillaridan farqli o'laroq, CASE bu bayonot emas, balki ifoda. Shuning uchun CASE ifodasi deyarli har qanday Transact-SQL-ning iboralarga imkon beradigan har qanday joyda ishlatilishi mumkin. CASE ifodasi ikki shaklga ega:
oddiy CASE ifodasi;
qidiruv iborasi CASE.
Oddiy CASE ifodasi uchun sintaksis quyidagicha:
Oddiy CASE ifodasi bo'lgan bayonot avval barcha ifodalar ro'yxatini qidiradi qAChON band birinchi ifodaga mos keladigan birinchi ifoda_1 va keyin mos keladiganni bajaradi keyin taklif... Agar WHEN ro'yxatida mos keladigan ifoda bo'lmasa, u holda bOShQA band.
CASE qidiruv ifodasi sintaksisiga quyidagilar kiradi:
Bunday holda, u birinchi saralash shartini qidiradi va keyin tegishli THEN bandini bajaradi. Agar shartlarning hech biri talablarga javob bermasa, ELSE bandi bajariladi. CASE qidiruv iborasidan foydalanish quyidagi misolda keltirilgan:
SampleDb-dan foydalanish; Loyiha nomini tanlang, byudjet\u003e 0 va byudjet 100000 va byudjet 150000 va byudjetni tanlang
Ushbu so'rov natijasi:
Ushbu misol barcha loyihalar byudjetlarini tortadi va hisoblangan og'irliklarni tegishli loyiha nomlari bilan birga ko'rsatadi.
Quyidagi misol CASE ifodasini ishlatishning yana bir usulini ko'rsatadi, bu erda WHEN bandida iboraning bir qismi bo'lgan pastki so'rovlar mavjud:
SampleDb-dan foydalanish; SELECT ProjectName, Case CASE WH p1.Budget (SELECT AVG (p2.Budget) FROM Project from p2) THEN "o'rtacha" END "byudjet kategoriyasi" Project p1;
Ushbu so'rov natijasi quyidagicha:
