Зертханалық жұмыс №6. Oracle функциясының қосымша

43 
employee_id, last_name, hire_date 
FROM employees WHERE ranking <= 10 
Аталмыш код 10 қатардан артық қайтуы мүмкін. Мысалы, егер жасы 
бірдей екі адам бар болса, онда 11 тәулікте қайтады. 
2) Деректерді топтасытыру 
Қызмет мансабы, департаменттер бойынша жалақыны есептеңіз және 
топтастырыңыз, ортша жалақыны есептеңіз.
SELECT department_id, job_id, manager_id, SUM(salary) 
FROM employees 
GROUP BY department_id,
ROLLUP (job_id), 
CUBE (manager_id) 
3) Рекурсивті сұраулар. 
Қызметкерлердің қандай саны жыл сайын 2002 жыл мен 2010 жыл 
аралығында түсетінін есептеңіз
.
WITH period ( year ) AS (SELECT 2002 AS year FROM dual 
UNION ALL 
SELECT year + 1 AS year FROM period WHERE year < 2010) 
SELECT p.year, COUNT ( e.employee_id ) 
FROM employees e RIGHT OUTER JOIN period p 
ON p.year = EXTRACT ( YEAR FROM e.hire_date ) 
GROUP BY p.year ORDER BY p.year 
4) Ағаштәрізді сұраулар. 
Менеджерлер мен қызметкерлердің тізімін енгізіңіз әзірше 
employee_id=101. 
SELECT employee_id, last_name, job_id, manager_id 
FROM employees START WITH employee_id = 101 
CONNECT BY PRIOR manager_id = employee_id
 
Аналитикалық функциялар. Псевдобаған. 
Oracle-дағы псевдобағандар деп кестеде анық көрінбеген, алайда 
сұрауда қолданылуы мүмкін бағандар. 
 
ROWID
псевдобағаны — жолдың керемет идентификаторы болып 
табылады. Ол тек қана кесте аясында ған керемет емес, сондай-ақ деректер 
қорында да керемет болып табылады. ROWID физикалық көзғараста қордағы 
өзіндік координатты жазба болып табылады.
ROWID деректер қорымен жұмысты жеңілдетеді, кез-келген кесте 
бағанын бірденнен идентифицирлей алады, алғашқы кілтсіз кесте бағанын 
жояа және түзете алады. Сонымен қатар, ROWID қосымшаға ең тез әсер 
ететін және деректерді белсенде модифицирлейтді.
Алайда ROWID Oracle арнайы ерекшелігі болғандықтан, оны 
қосымшалар жасауда қолдануға болмайды.
SELECT ROWNID, Last_Name FROM hr.employees 

44 
 
ROWNUM псевдобағаны 
ORACLE SQL сұрауындағы деректерді 
шектеуге қолданады. Oracle нәтижелері қайталанытн жолдарды нөмерлей 
алады. Бұл үшін арнаы 
ROWNUM псевдобағаны 
қолданылады. Алайда бұл 
бағанды тура сұрауда қолдану мүмкін емес: мыслы, мынадай сұрау: 
SELECT ROWNUM, Last_Name FROM hr.employees 
Қатені қайтарады. 
Мұнымен бірге ROWNUM шығарылатын жазбалар санын шектеуге 
қолданылады: 
SELECT ROWNUM, Last_Name FROM employees WHERE ROWNUM < 10 
Мұндай жағдайда барлығы 9 жазба беріген. 
Мүндай мүмкіндік сұрау мәндері өте үлкен болған кезде жне серверді 
қатты жүктеген кезде сізге тек қана қалыпты жұмыс істеп жатқанына көз 
жеткізу үшін керек. аналитикалық функцияларды енгізудің техникалық 
мақсаты – лаконикалық формулировка беру және «аналитикалық сұрау» 
жылдамдығын арттыру.
Top-n Analysis орындау
. Top-N анализды подзапростра көмегімен және 
ішкі запростар арқылы орындауға болады.
Top-
n 
сұрау төменде сипатталған жағдайларда қолданылады: 
1)
Ішкісұраныс (inline view) сұрыпталған деректер тізімін генерациялау 
үшін. подзапрос деректерді қажетті ретпен қойюды қамтамасыз ету үшін
ORDER BY қосады. Нәтижелерді кему реті бойынша қою үшін DESC 
параметрі керек.
2)
Сыртқы сұрау нәтижелік жиынтықта баған санын шектейді. Сыртқы 
сұрау келесілерден тұрады: 
–
псевдобаған ROWNUM, кезектес мәнді көрсетеді, подзапростың 
әрбір қайталанытын бағаны 1ден басталады; 
–
WHERE, қайталану үшін н бағанын береді. Ішкі WHERE –де (<) 
немесе (<=) операторын қолдану керек. 
Top-N анализын жүргізудің жалпы көрінісі: 
SELECT [список_столбцов], ROWNUM
FROM (SELECT [список_столбцов] FROM таблица 
ORDER BY Top-N_столбец) WHERE ROWNUM <= N 
Агрегирлеудің қарапайым функциясымен салыстыру: 
Сұраныстың әрбір бағаны басқа бағанға бағынышсыз өңделеді. Алайда, 
іс жүзінде бағанды өңдеу барысында оны алдынғы немесе келесі бағанмен 
салытыру 
керек, 
бағандар 
тобын 
белгілеу 
керек, 
бір-біріне 
бағынышсыздарын өңдеу және т.б. ДҚБЖ SQL тілінде іш жүзінде 
қолданылуы сай келетін конструкция негізінде пайда болған, әдетте сап түзеу 
мен аналитикалық функциялар SQL:2003 тіркелген.
ДҚБЖ Oracle агрегатты функциялары (max(), min(), avg()) SQL 
талаптарына сай жұмыс жасайды. Агрегаттық және аналитикалық 
функциялардың жалпы саны Oracle11g-да 50ден астам. Аналитикалық 

45 
функциялар сап түзеуге (ranking), нәтиджелерді ауыстыруға (moving 
aggregates), деректерлі түрлі периол бойынша салыстыруға (period 
comparisons), қорытынды қатынасын көрсетуге (ratio of total), және т.б. 
әрекеттерді орындауға мүмкіндік береді.
Көптеген 
аналитикалық 
функциялар 
карапайым 
скалярлы 
агретирленген SUM, MAX функциялары сияқты әрекет етеді және GROUP 
BY арқылы қалыптасады.
Аналитикаық функция жалпы синтаксисте қолданылады және арнайы 
мүмкіндіктерге ие. Аналитикалық функция принциптерін түсіну үшін 
төмендегілерді түсіну керек: 
- деректерді қаай скенсирлеу керек; 
- деректерді қалай реттеу; 
- деректер терезесін қалай беру. 
Барлық функциялар келесі синтаксиске ие: 
ИМЯ_ФУНКЦИИ
(<аргумент>,< аргумент >, . . . ) 
OVER 
(<конструкция_фрагментации> <конструкция_упорядочения> <конструкция_окна>) 
OVER — бұл функцияны аналитикалық ретінде идентифицирлейтін 
кілттік сөз. Жоғарыда SUM агрегирлеуші функциясы көрсетілген: 
SUM(выражение 1) OVER([PARTITION BY выражение 2 [, выражение 3 [, …]]]) 
Егер де PARTITION BY көрсетілмесе, онда жалғыз топ есебінде толық 
мәтін алынады: 
SELECT ename, deptno, job, SUM(sal) OVER () sum_sal FROM emp 
Аналитикалық функциялар мүмкіндіктеріне қарай төрт түрлі сыныпқа 
бөлінеді: 
1)
Сап түзеу функциясы, «бірніш N» типті сұранысын құруға мүмкіндік 
береді. 
2)
Терезелік функциялар, түрлі агрегаттарды шығаруға мүмкіндік 
береді. 
3)
Қорытынды 
функциялар. 
Қорытынды 
функциядан 
терезелі 
функцияның кілттік айырмашылы – OVER операторныдағы ORDER BY 
құрылымының болмау. ORDER BY құрылымы болмаған кезде функция 
әрбір топ бағанында қолданылады. ORDER BY құрылымы бар кезде функция 
терезеде қолданылады.
4)
VAR_POP, 
VAR_SAMP, 
STDEVJPOP 
сияқты статистикалық 
функциялар, сызықтық регрессивті функциялар жиынтығы және т.б.бұлар 
кез келген ретсіз секцияның статистикалық көрсеткішін шығаруға мүмкіндік 
береді.
Бір аналитикалық функцияны басқасында қолдануға болмайды.
SELECT KEEP (DENSE_RANK FIRST ORDER BY [ NULLS ) 
OVER (PARTITION BY ) FROM GROUP BY

46 

жүктеу/скачать 0,98 Mb.

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