4 зертханалық сабағы (2-сағат). Тақырыбы: реляционды және булев операторларының одан да көп предикаттарды шығаруда қолдану

Тақырыбы: СКАЛЯРЛЫ ӨРНЕКТЕ САЛЫНҒАН АГРЕГАТТАР

жүктеу 84,48 Kb.

бет	4/6
Дата	11.11.2022
өлшемі	84,48 Kb.
	#40128

1 2 3 4 5 6

Документ Microsoft Word

8 зертханалық сабақтарына арналған тапсырмалар
7 зертханалық сабағы (2-сағат).

Тақырыбы: СКАЛЯРЛЫ ӨРНЕКТЕ САЛЫНҒАН АГРЕГАТТАР
Әдістемелік нұсқау.
Осыған дейін сіз агрегатттық функцияларды жалғыз өрістермен бірге аргумент ретінде қолдандыңыз. Енді сіз агрегатттық функцияларды бір немесе бірнеше өрістерден тұратын скалярлы өрнектерден құралған аргументтермен қолдан аласыз.
( Егер де сіз бұны істесеңіз, DISTINCT рұқсат етілмейді. ) Реттелу кестесінде әрбір тапсырыс берушіге алдыңғы төленбеген баланс (поле blnc) сақтайтын тағы бір жолға ие болады деп есептейік. Сіз осы қосымша суммаға ие болып, алдыңғы балансқа қосылатын ағынды балансты табуыңыз керек.
Сіз ең үлкен төленбеген балансты келесі түрде таба аласыз:
SELECT MAX ( blnc + (amt) ) FROM Orders;
Кестенің әрбір жолына тапсырыс беруші үшін бұл сұраныс blnc және amt –ға салынып отырады және ол табылатын ең үлкен мәнді таңдау керек. Әрине, әзірге тапсырыс берушілер көп мәнді реттер ала алады, олардың төленбеген баланстары әр ретке бөлек бағаланып отырады. Кейінгі мерзімді рет ең үлкен төленбеген балансқа ие болуы мүмкін. Онда, ескі баланс жоғарыдағы сұраныстағыдай таңдалуы керек.
GROUP BY ҰСЫНЫСЫ
GROUP BY Ұсынысы басқа өріс терминіндегі ерекше өрісте мәндердің ішкі жиынын анықтауға және агрегаттың функциясын ішкі жиынға қолдануға мүмкіндік береді.
Бұл сізге бір ғана SELECT ұсынысында өрістер мен агрегатты функцияларды біріктіруге мүмкіндік береді.Мысалы, сізге әрбір сатушыдан алынған ең көп сумманы табу керек болсын.
Сіз snum өрісінің әрбір мәніне Реттелу кестесінен MAX (amt) таңдай отырып бөлек-бөлек сұраныс жасай аласыз.
Алайда, GROUP BY Сізге олардың барлығын бір-ақ командаға орналастыруға мүмкіндік береді:
SELECT snum, MAX (amt) FROM Orders GROUP BY snum;
Бұл сұраныстың нәтижесі 6.5. суретте көрсетілген.
=============== SQL Execution Log ==============
| SELECT snum, MAX (amt) |
| FROM Orders |
| GROUP BY snum; |
| =============================================== |
| snum |
| ------ -------- |
| 1001 767.19 |
| 1002 1713.23 |
| 1003 75.75 |
| 1014 1309.95 |
| 1007 1098.16 |
================================================
Сурет 6.5: Әрбір сатушыдан максималды сумманың табылуы.
GROUP BY толықтай өріс мәні арқылы табылатын агрегаттық функцияның барлық сериясы қолданылады. Бұл кезде, әрбір топтағы барлық жол сол snum өріс мәнінен тұрады, және MAX функциясы әрбір топқа жекелей қолданылады. Бұл GROUP BY қолданылатын өріс мәні, ол еңгізу тобында бір ғана мән қабылдайды, агрегаттық функцияға ұқсас. Нәтижесінде агрегат пен өрістерді біріктіреді.
Сол сияқты көпсандық GROUP BY өрісін қолдануға болады.
Мысалы, егер сіз әрбір сатушының күніне алатын жалақысын есептеу керек болсын. Ол үшін сіз сатушының әр күнде алатын жалақысын біріктіріп, осылардың әрбіріне MAX функциясын қолданамыз.(төмендегідей):
SELECT snum, odate, MAX ((amt)) FROM Orders GROUP BY snum, odate;
Шыққан нәтиже 6.6 суретте көрсетілген.
=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) FROM Orders
| GROUP BY snum, odate; |
| =============================================== |
| snum odate |
| ------ ---------- -------- |
| 1001 10/03/1990 767.19 |
| 1001 10/05/1990 4723.00 |
| 1001 10/06/1990 9891.88 |
| 1002 10/03/1990 5160.45 |
| 1002 10/04/1990 75.75 |
| 1002 10/06/1990 1309.95 |
| 1003 10/04/1990 1713.23 |
| 1014 10/03/1990 1900.10 |
| 1007 10/03/1990 1098.16 |
================================================
Сурет 6.6: Әрбір күнде алынғанжоғарғы қосынды
Әрине, бос топтар өйткені сатушы күніне тізім бойынша жасамаған, сондықтан нәтиже көрінбейді.
HAVING қосымшасы.
Айталық, алдыңғы мысалда, сіз $3000.00 аспайтын максималды сумманы алдық . Сіз агрегаттық функцияны WHERE қосымшасында қолдана алмаймыз ( егер сіз келесі сұранысты қолдансаңыз), өйткені предикаттар бір жолдық терминде бағаланады, агрегаттық функция жолдық топ терминімен бағаланады. Бұл дегеніміз сіз төмендегіге ештеңе істей алмайсыз:
SELECT snum, odate, MAX (amt) FROM Oreders WHERE MAX ((amt)) > 3000.00
GROUP BY snum, odate;
Бұл қолайсыз интерпретация ANSI-дан ауытқу болы табылады. $3000.00 асқан максималдық бағасын көру үшін, сіз HAVING қосымшасын қолданамыз.
HAVING қосымшасы қолданылатын енгізілген топты жою үшін критерииді қолданады.
Төмендегі дұрыс команда болып табылады:
SELECT snum, odate, MAX ((amt)) FROM Orders GROUP BY snum, odate
HAVING MAX ((amt)) > 3000.00;
Осы сұраныстың нәтижесі 6.7 суретте көрсетілген.

=============== SQL Execution Log ==============

| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate |
| HAVING MAX (amt) > 3000.00; |
| =============================================== |
| snum odate |
| ------ ---------- -------- |
| 1001 10/05/1990 4723.00 |
| 1001 10/06/1990 9891.88 |
| 1002 10/03/1990 5160.45 |
================================================
Сурет 6. 7: Агрегаттық мән тобын жою
HAVING сөйлеміндегі аргументтер GROUP BY командасында қолданылатын SELECT сөйлеміндегі ережелермен бағынады.Олар шығару тобына бір мағына беру керек.Келесі командаға тыйым салынады.
SELECT snum, MAX (amt) FROM Orders GROUP BY snum
HAVING odate = 10/03/1988;
HAVING сөйлемі бір топқа бір мәннен көп мән беретіндіктен, HAVING сөйлемі арқылы оdate өрісін шақыру мүмкін емес.Мұндай жағдайға тап болмас үшін HAVING сөйлемі таңдап алынған GROUP BY өріс және агрегаттарына сілтеме жасауы тиіс.Жоғарғыда айтылған сұраныс жасаудың дұрыс әдісі бар.( шығарылуы 6.8 суретінде көрсетілген ):
SELECT snum, MAX (amt) FROM Orders WHEREodate = 10/03/1990
GROUP BY snum;
=============== SQL Execution Log ==============
| SELECT snum, odate, MAX (amt) |
| FROM Orders |
| GROUP BY snum, odate; |
| =============================================== |
| snum |
| ------ -------- |
| 1001 767.19 |
| 1002 5160.45 |
| 1014 1900.10 |
| 1007 1098.16 |
================================================
6.8 сурет: Әр сатушының 3 қазанға ең жоғарғы табысының соммасы.
odate өрісі жоқ болғандықтан, бұл мәліметтердің мәні басқа мысалдарға қарағанда төмен,сондықтан таңдалған өрістер де болмауы керек.Қорытынды «бұл-3 қазандағы ең үлкен рет» - деген сияқты сөйлемдер қосу керек.7 тарауда қорытындыңызға мәтінді қалай қою керек екендігін көрсетеміз. Жоғарыда айтылғандай, HAVING шығару тобында бір мәнді қамтитын аргументтер қолданылады. Тәжірибе жүзінде агрегаттық функцияларға сілтемелер – ортақ,бірақ GROUP BY көмегімен таңдап алынған өріс мүмкін.Мысалы,біз Serres және Rifkin ретін көргіміз келеді:
SELECT snum, MAX (amt) FROM Orders GROUP BY snum
HAVING snum B (1002,1007);
=============== SQL Execution Log ==============
| SELECT snum, MAX (amt) |
| FROM Orders |
| GROUP BY snum |
| HAVING snum IN ( 1002, 1007 ); |
| =============================================== |
| snum |
| ------ -------- |
| 1002 5160.45 |
| 1007 1098.16 |
================================================
6. 9 сурет: HAVING GROUP BY өрісімен қолданылуы
КІРІСТІРІЛГЕН АГРЕГАТТАР ЖАСАМАҢДАР
ANSI SQL қатал интерпритациясында, сіз агрегаттың агрегатын қолдана алмайсыз. Мысалы, сіздің қай күні ең жоғарғы соммада пайда түскенін білгіңіз келеді делік.Егер сіз бұны жүзеге асырып көрсеңіз,онда сіздің
SELECT odate, MAX ( SUM (amt) ) FROM Orders
GROUP BY odate;
Командасының ықтималдығы ауытқиды. ( Қолайлы болып табылатын кейбір іске асырулар, тіпті олар қиын болса да, салынған (вложенные) агрегаттар өте пайдалы болуы мүмкін,бұл шектеуді жазбайды).Жоғарғыдағы командада,мысалы, SUM odate өрісіндегі әрбір топта қолданылуы керек, ал MAX барлық топқа жалғыз мән тудыратын, барлық топта қолданылуы керек.Бірақ GROUP BY сөйлемі odate өрісінің әрбір тобы үшін жалғыз шығару жолы болу керектігін түсіндіреді.

№ 8 зертханалық сабақтарына арналған тапсырмалар:
1. 3 қазандағы барлық табыс соммасын санайтын сұраныс жазыңыз.
2. Тапсырушы кестесінде city өрісіндегі мән NULL әртүрлі санын санайтын сұраныс құрыңыз.
3. Әрбір тапсырушы үшін ең төмен сомманы таңдайтын сұраныс құрыңыз.
4. Тапсырушының аты-жөні G әріпінен басталатындай етіп алфавиттік ретпен таңдайтын сұраныс құрыңыз.
5. Әр қаланың жоғарғы бағасын таңдайтын сұраныс құрыңыз.
6. Әр күн сайын өз ретімен тапсырушы санын тіркейтін сұраныс құрыңыз.(Егер сатушы сол күні бірден көп ретті қамтыса, онда ол бір рет қана есепке алынуы керек).

№ 7 зертханалық сабағы (2-сағат).

жүктеу 84,48 Kb.

Достарыңызбен бөлісу:

1 2 3 4 5 6