Salam, Bloguma xoş gəlmisiniz.

Bugünkü mövzumuz ORACLE SQL-in bir digər maraqlı bölməsi olan “aggregate” funksiyalardır. Keçən yazımızda xatırlıyırsınızsa scalar bir digər adı ilə “single-row ”funksiyalardan danışmışdıq, bu dəfəki mövzumuz isə “multirow” və ya aggregate funksiyalardır. Aggregate funksiyalar çoxlu sətir məlumatını qəbul edib bizə bir nəticə qaytarır. Bu yazımda əsasən aşağıdakı funksiyalardan danışacağam.

Aggregate funksiyaları sql sorğularda istifadə edəcəyimiz bəlli yerlər vardır.

• SELECT ifadəsi və ORDER BY-da
• GROUP BY
• HAVİNG

İndi isə ən çox istifadə olunan aggregate funksiyalardan bəhs edək.

COUNT

COUNT(e1)

e1 – istənilən verilən tipində ola bilən sütun adıdır.

Verilmiş sütunda olan bütün NOT NULL sətirlərin sayını qaytracaq bizə.

Məsələn VENDORS cədvəlinə baxaq.

SELECT VENDOR_NAME, STATUS, CATEGORY FROM VENDORS;

VENDOR_NAME         STATUS       CATEGORY

——————————   —————– ————–

Acme Steaks                17

Acme Poker

SELECT COUNT(VENDOR_NAME), COUNT(STATUS), COUNT(CATEGORY) FROM VENDORS;

COUNT(VENDOR_NAME)  COUNT(STATUS)  COUNT(CATEGORY)

————————————–  —————————  ———————-

2                                                  1                                0

Nəticədən də göründüyü kimi NULL olan dəyərlər COUNT-a daxil edilmədi. Ancaq COUNT vasitəsilə NULL olanlarıda nəzərə alıb sütundakı bütün sətirləri saymaq olar, bunun üçün e1 parametri yerinə asteriks(*) yazmaq kifayətdir.

SELECT COUNT(*) FROM VENDORS;

COUNT(*)

——————

2

Aggregate funksiyalar daxilində DİSTİNCT və ALL operatorlarını da istifadə etmək mümkündür. Məs:

SELECT COUNT(DISTINCT LAST_NAME), COUNT(ALL LAST_NAME) FROM EMPLOYEES;

COUNT(DISTINCTLAST_NAME)  COUNT(ALLLAST_NAME)

————————————————  ———————————

5                                                       7

SUM

SUM(e1)

e1 – sadəcə ədəd tipində olan məlumat sütunudur.

SELECT SUM(SUBTOTAL) FROM ORDERS;

SELECT SUM(SUBTOTAL) FROM ORDERS WHERE ORDER_DATE = SYSDATE;

MIN, MAX

MIN(e1), MAX(e2)

e1, e2 – tarix, ədəd və hərf(char və ya string)tiplərinin hər hansı birində olan məlumat sütunudur.

Ədəd tipi: Kiçik ədədlər MIN, böyük ədədlər MAX

Tarix tipi: Əvvəlki günlər MIN, sonrakı günlər MAX

Hərf tipi: ‘A’ < ‘Z’;  ‘Z’ < ‘a’;  ‘1’<’10’; ‘2’ > ‘100’

LAST_NAME

———————-

Hoddlestein

Smith

Worthington

Lindon

West

SELECT MIN(LAST_NAME), MAX(LAST_NAME) FROM EMPLOYEES;

MIN(LAST_NAME)           MAX(LAST_NAME)

————————————  ——————–

Hoddlestein                      Worthington

AVG

AVG(e1)

e1 – ədəd tipində olan məlumat sütunudur.

Verilmiş sütundakı ədədlərin ortalamasını hesablayır. NULL dəyərə sahib olanları nəzərə almır.

SELECT PAY_HISTORY_ID,SALARY FROM PAY_HISTORY;

PAY_HISTORY_ID  SALARY

————————–   ———————-

1                                  73922

2                                  47000

3                                  58000

4                                 37450

5                                 91379

6                                 45500

SELECT AVG(SALARY) FROM PAY_HISTORY;

AVG(SALARY)

———————-

58875.1666666666666666666666666666666667

Son nəticəni daha anlaşıqlı edək

SELECT TO_CHAR(ROUND(AVG(SALARY),2),'$999,999.99') FROM PAY_HISTORY;

TO_CHAR(ROUND(AVG(SALARY),2),’$999,999.99′)

——————————————-

$58,875.17

MEDIAN

MEDIAN(e1)

e1 – tarix və ya ədəd tipində olan məlumat sütunudur. NULL dəyərləri nəzərə almır.

Verilmis sütunu ASC(kiçikdən böyüyə doğru) formada sıralayır və ortadakı dəyəri bizə qaytarır bu qayda yalnız verilənlərin sayı tək olanda olur, cüt olduğu zaman isə ortada olan dəyərlərin ortalamasını bizə qaytarır.
CREATE TABLE TEST_MEDIAN(A NUMBER(3));

INSERT INTO TEST_MEDIAN VALUES (1);

INSERT INTO TEST_MEDIAN VALUES (10);

INSERT INTO TEST_MEDIAN VALUES (3);

SELECT MEDIAN(A) FROM TEST_MEDIAN;

MEDIAN(A)

—————–

3

Daha sonra 4 ədədini əlavə etdikdə isə nəticə belə olur.

INSERT INTO TEST_MEDIAN VALUES (4);

SELECT MEDIAN(A) FROM TEST_MEDIAN;

MEDIAN(A)

—————–

3.5

RANK

RANK(c1) WITHIN GROUP (ORDER BY e1)

c1, e1 – hərf və ədəd tiplərindən hər hansı biridirlər. Bir birlərinə ekvialent tip olmalıdırlar.

Sıralanmış(istər ASC istər DESC) e1 sütun dəyərləri içərisində c1 neçənci yerdə yerləşirsə bizə o qiyməti qaytarır. Bir nüansa diqqət etmək lazımdır əgər eyni ədəd və ya hərf düzülüşü olarsa(n eyni ədəd varsa) hər biri eyni rank qiymətini alacaq ancaq onlardan sonra gələcək dəyər n+1 rank dəyərini alacaq.

Məs:

CREATE TABLE RANK_TEST(VAL NUMBER);

INSERT INTO RANK_TEST VALUES(1);

INSERT INTO RANK_TEST VALUES(1);

INSERT INTO RANK_TEST VALUES(1);

INSERT INTO RANK_TEST VALUES(2);

SELECT RANK(1) WITHIN GROUP(ORDER BY VAL), RANK(2) WITHIN GROUP(ORDER BY VAL) FROM RANK_TEST;

RANK(1) WITHIN GROUP(ORDER BY VAL)  RANK(2) WITHIN GROUP(ORDER BY VAL)

————————————————————-  ——————————————————-

1                                                                                  4

FIRST, LAST

aggregate_function KEEP (DENSE_RANK FIRST ORDER BY e1)

aggregate_function KEEP (DENSE_RANK LAST ORDER BY e1)

Verilmiş cədvəli e1-ə görə sıralayır, FIRST birinci qiyməti LAST sonuncu qiyməti saxlayır və aggregate_function-a bu qiyməti ötürür.

CREATE TABLE TEST_FL(A NUMBER(3));

INSERT INTO TEST_FL VALUES (1);

INSERT INTO TEST_FL VALUES (10);

INSERT INTO TEST_FL VALUES (3);

SELECT MAX(A) KEEP (DENSE_RANK FIRST ORDER BY A) FROM TEST_FL;

MAX(A) KEEP(DENSE_RANKFIRSTORDERBYA)

———————————————————-

1

SELECT MAX(A) KEEP (DENSE_RANK LAST ORDER BY A) FROM TEST_FL;

MAX(A) KEEP(DENSE_RANKFIRSTORDERBYA)

———————————————————-

10

GROUP BY

Sql sorğularımız daxilində aggregate funksiya istifadə etdiyimiz zaman GROUP BY ifadəsini mütləq olaraq işlətməliyik. Tək başına aggregate funksiya istifadə etdikdə istifadə etməyimizə gərək yoxdur. GROUP BY bizə cədvəldəki məlumatları verilmiş sütuna görə qrup halında emal etməyə kömək edir.

Misallarla izah etməyə çalışacağam, əsasə aşağıdakı cədvəl(ship_cabins) üzərində sorğular yazacağam.

SHIP_CABIN_ID	ROOM_NUMBER	ROOM_STYLE	ROOM_TYPE	WINDOW	GUESTS	SQ_FT
1	102	Suite	Standard	Ocean	4	533
2	103	Stateroom	Standard	Ocean	2	160
3	104	Suite	Standard	None	4	533
4	105	Stateroom	Standard	Ocean	3	205
5	106	Suite	Standard	None	6	586
6	107	Suite	Royal	Ocean	5	1524
7	108	Stateroom	Large	None	2	211
8	109	Stateroom	Standard	None	2	180
9	110	Stateroom	Large	None	2	225
10	702	Suite	Presidential	None	5	1142
11	703	Suite	Royal	Ocean	5	1745
12	704	Suite	Skyloft	Ocean	8	722

SELECT                 ROOM_STYLE,

                               ROUND(AVG(SQ_FT),2) “Average SQ FT”,

                               MIN(GUESTS) “Minimum # of Guests”,

                               COUNT(SHIP_CABIN_ID) “Total # of cabins”

FROM                   SHIP_CABINS

WHERE                SHIP_ID = 1

GROUP BY          ROOM_STYLE;

ROOM_STYLE   Average SQ FT      Minimum # of Guests    Total # of cabins

———————-  —————————-  ————————————  ———————-

Suite                  969.29                     4                                          7

Stateroom        196.2                       2                                          5

SELECT                  ROOM_TYPE,

                                TO_CHAR(ROUND(AVG(SQ_FT),2),’999,999.99′) “Average SQ FT”,

                                MAX(GUESTS) “Maximum # of Guests”,

                                COUNT(SHIP_CABIN_ID) “Total # of cabins”

FROM                   SHIP_CABINS

WHERE                 SHIP_ID = 1

GROUP BY          ROOM_TYPE

ORDER BY            2 DESC;

ROOM_TYPE       Average SQ FT  Maximum # of Guests   Total # of cabins

———————–  ————————  ———————————-  ———————-

Royal                   1,634.50              5                                         2

Presidential        1,142.00              5                                         1

Skyloft                 722.00                 8                                         1

Standard             366.17                 6                                         6

Large                   218.00                 2                                          2

SELECT                  ROOM_STYLE,

                                 ROOM_TYPE,

                                 TO_CHAR(MIN(SQ_FT),’9,999′) “Min”,

                                 TO_CHAR(MAX(SQ_FT),’9,999′) “Max”,

                                 TO_CHAR(MIN(SQ_FT)-MAX(SQ_FT),’9,999′) “Diff”

FROM                     SHIP_CABINS

WHERE                  SHIP_ID = 1

GROUP BY            ROOM_STYLE, ROOM_TYPE

ORDER BY            3;

ROOM_STYLE  ROOM_TYPE      Min     Max     Diff

———————-  ———————– ——–  ——–  ——

Stateroom        Standard            160      205       -45

Stateroom        Large                   211      225      -14

Suite                  Standard             533      586      -53

Suite                  Skyloft                 722      722         0

Suite                  Presidential        1,142  1,142      0

Suite                  Royal                   1,524  1,745   -221

Qeyd: Aggregate funksiyaları ancaq 2 səviyəyə qədər iç-içə(nested) istifadə edə bilərsiniz əks halda xəta alacaqsınız. Məs:

1)

SELECT                 AVG(MAX(SQ_FT))

FROM                    SHIP_CABINS

WHERE                 SHIP_ID = 1

GROUP BY           ROOM_STYLE, ROOM_TYPE;

 

2)

SELECT               COUNT(AVG(MAX(SQ_FT)))

FROM                   SHIP_CABINS

WHERE                SHIP_ID = 1

GROUP BY          ROOM_STYLE, ROOM_TYPE;

 

1ci sorğu heç bir xəta qaytarmayacaq ancaq 2ci sorğu bizə belə bir xəta qaytaracaq.

Error report:

SQL Error: ORA-00935: group function is nested too deeply 

00935. 00000  – “group function is nested too deeply”

HAVING

Sorğularımızda GROUP BY istifadə etdiyimiz zaman gruplaşdırılmış məlumatlara hər hansı şərt vermək istəyiriksə bu zaman WHERE yox HAVING ifadəsini istifadə etməliyik. HAVING ancaq və ancaq GROUP BY istifadə olunduğu zaman işlədilir.

Aşağıdakı şəkildə sql SELECT sorğularımızda hansı ifadələrin məcburi hansıların əlavə olduğu verilib.

SELECT                 ROOM_STYLE, ROOM_TYPE,

                               TO_CHAR(MIN(SQ_FT),’9,999′) “Min”

FROM                   SHIP_CABINS

WHERE                SHIP_ID = 1

GROUP BY          ROOM_STYLE, ROOM_TYPE

HAVING               ROOM_TYPE IN (‘Standard’, ‘Large’)

OR MIN(SQ_FT) > 1200

ORDER BY            3;

ROOM_STYLE  ROOM_TYPE       Min

———————-  ————————  ——

Stateroom        Standard              160

Stateroom        Large                    211

Suite                  Standard              533

Suite                  Royal                 1,524

Bir yazımızın daha sonuna gəldik, ümid edirəm faydalı olacaq, fikirlərinizi şərh yazaraq bildirin dostlar J