פונקציות אנליטיות הן נושא נהדר שכל כותב קוד SQL צריך להכיר. פונקציות אלו מאפשרות לנו לבצע חישובים מורכבים בתוך שאילתות, חישובים אשר ללא הפונקציות האנליטיות – היו אפשריים רק באמצעות PLSQL, תתי שאילתות או פקודות Join מורכבות ומסובכות.
באורקל קיימות פונקציות אנליטיות שונות, בפוסט זה אתמקד בפונקציות הבאות :
1. Ranking Functions – Rank, Dense_Rank, Ntile
2. Windows Functions
3. Lag / Lead Fuctions
מבנה פונקציה אנליטית
ניתן לאמר כי באופן כללי פונקציה אנליטית תראה בצורה הבאה
function_name () over ()
function_name () – היא הפונקציה אשר בה נשתמש כדי לבצע תחשיב מסויים כפי שנראה בדוגמאות הבאות.
over () – תאפשר לנו להגדיר מאפיינים נוספים לפיהם הפונקציה האנליטית תבצע את החישוב.
Ranking Functions
פונקציות ה Ranking נועדו לבצע פעולות דרוג שונות בטבלה לפי פרמטרים שונים, בחלק זה נתמקד בפונקציות Rank, Dense Rank ו Ntile
פונקציית RANK
בשאילתה הבאה, אנו משתמשים בפונקציית ה Rank כדי להציג דירוג מסויים, כאשר את מאפייני הדירוג אנו נגדיר באמצעות פקודת ה Over
HR >SELECT first_name, department_id, salary,
2 RANK() OVER (ORDER BY salary desc) sal_rank
3 FROM employees
4 WHERE department_id = 80 ;
FIRST_NAME DEPARTMENT_ID SALARY SAL_RANK
-------------------- ------------- ---------- ----------
John 80 14003 1
Karen 80 13503 2
Alberto 80 12003 3
Lisa 80 11503 4
Ellen 80 11003 5
Gerald 80 11003 5
Eleni 80 10503 7
Clara 80 10503 7
Harrison 80 10003 9
...
34 rows selected.
ניתן לראות כי העמודה האחרונה – SAL_RANK מציגה דירוגי שכר עבור העובדים השונים, כאשר העובד אשר מרוויח את השכר הגבוה ביותר מדורג ראשון.
כאשר קיימות שתי שורות עם אותו ערך (שני עובדים עם אותו שכר) הדירוג שלהם זהה והדירוג הבא = דירוג קודם + כמות המופעים של הדירוג הקודם.
פונקציית DENSE RANK
בשאילתה הבאה, אנו משתמשים בפונקציית ה Dense Rank כדי להציג דירוג מסויים, כאשר כמו מקודם – אנו מגדירים את תצורת הדירוג באמצעות פקודת ה Over
HR >SELECT first_name, department_id, salary,
2 DENSE_RANK() OVER (ORDER BY salary desc) sal_rank
3 FROM employees
4 WHERE department_id = 80 ;
FIRST_NAME DEPARTMENT_ID SALARY SAL_RANK
-------------------- ------------- ---------- ----------
John 80 14003 1
Karen 80 13503 2
Alberto 80 12003 3
Lisa 80 11503 4
Ellen 80 11003 5
Gerald 80 11003 5
Eleni 80 10503 6
Clara 80 10503 6
Harrison 80 10003 7
...
34 rows selected.
ניתן לראות כי כמו מקודם – העמודה האחרונה – SAL_RANK מציגה דירוגי שכר עבור העובדים השונים, כאשר העובד אשר מרוויח את השכר הגבוה ביותר מדורג ראשון.
אך הפעם כאשר קיימות שתי שורות עם אותו ערך (שני עובדים עם אותו שכר)
הדירוג הבא = דירוג קודם + 1. זהו ההבדל בין שתי הפונקציות.
שימוש ב Partitions
ניתן להרחיב את השימוש בפקודת ה Over ולציין באמצעותה חלוקה למקטעים. בדוגמא הבאה אנו מבקשים להציג דירוג שכר עבור כל מחלקה באופן נפרד.
SELECT first_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary desc) sal_rank
FROM employees ;
FIRST_NAME DEPARTMENT_ID SALARY SAL_RANK
-------------------- ------------- ---------- ----------
Jennifer 10 4403 1
Michael 20 13003 1
Pat 20 6003 2
Den 30 11003 1
Alexander 30 3103 2
Shelli 30 2903 3
Sigal 30 2803 4
Guy 30 2603 5
Karen 30 2503 6
Susan 40 6503 1
Adam 50 8203 1
...
107rows selected.
פונקציית NTILE
באמצעות פונקציית Ntile ניתן לחלק קבוצת שורות מסויימת לתתי קבוצות, לדוגמא, בשאילתא הבאה אנו מחלקים את כל העובדים בטבלה ל 2 קבוצות אשר נקבעות לפי שכרם
HR >SELECT first_name,department_id,salary,
2 NTILE(2) OVER(ORDER BY salary) "2_categories"
3 FROM employees;
FIRST_NAME DEPARTMENT_ID SALARY 2_categories
-------------------- ------------- ---------- ------------
Steven 90 4 1
TJ 50 2103 1
Hazel 50 2203 1
Steven 50 2203 1
James 50 2403 1
...
Charles 80 6203 2
Sundar 80 6403 2
Shanta 50 6503 2
...
107 rows selected.
Window Functions
כדי להבין כיצד פונקציות החלון עובדות, ראשית נביט בתוצאות השאילתה הבאה :
SELECT last_name , department_id, salary
FROM employees
WHERE department_id IN (10 , 20 , 30, 40, 60)
ORDER BY department_id , salary
LAST_NAME DEPARTMENT_ID SALARY
------------------------- ------------- ----------
Whalen 10 4403
Fay 20 6003
Hartstein 13003
Colmenares 30 2503
Himuro 2603
Tobias 2803
Baida 2903
Khoo 3103
Raphaely 11003
Mavris 40 6503
Lorentz 60 4203
Austin 4803
Pataballa 4803
Ernst 6003
Hunold 9003
15rows selected.
כעת, אם ננסה לבצע את השאילתה הבאה :
SELECT last_name , department_id , salary, SUM(salary)
FROM emps
WHERE department_id IN (10 , 20 , 30, 40, 60) ;
נקבל את השגיאה :
ERROR at line 1:
ORA-00937: not a single-group group function
מכיוון שזהו טבען של פונקציות הקבוצה – כל עמודה רגילה המופיעה במשפט ה SELECT יחד עם פונקצית קבוצה חייבת להופיע גם במשפט ה GROUP BY.
כאן נכנסות לתמונה פונקציות החלון, כפי שניתן לראות בדוגמא הבאה אנו נעזרים בפוקציות החלון על מנת לייצר עמודה נוספת (DEPT_SUM) – אשר מייצגת את הסך הכללי בטבלה, עבור כל שורה.
SELECT last_name, department_id , salary,
SUM(salary) OVER ( ) AS "DEPT_SUM"
FROM employees
WHERE department_id IN (10 , 20 , 30, 40, 60)
ORDER BY department_id ;
LAST_NAME DEPARTMENT_ID SALARY DEPT_SUM
------------------------- ------------- ---------- ----------
Whalen 10 4403 83645
Hartstein 20 13003 83645
Fay 6003 83645
Colmenares 30 2503 83645
Raphaely 11003 83645
Khoo 3103 83645
Baida 2903 83645
Tobias 2803 83645
Himuro 2603 83645
Mavris 40 6503 83645
Hunold 60 9003 83645
Ernst 6003 83645
Austin 4803 83645
Pataballa 4803 83645
Lorentz 4203 83645
15rows selected.
בדוגמא הבאה אנו נשתמש בפקודת ה Partition כדי להציג סך עבור כל מחלקה באופן נפרד :
SELECT last_name, department_id , salary,
SUM(salary) OVER ( PARTITION BY department_id ) AS "DEPT_SUM"
FROM employees
WHERE department_id IN (10 , 20 , 30, 40, 60) ;
LAST_NAME DEPARTMENT_ID SALARY DEPT_SUM
------------------------- ------------- ---------- ----------
Whalen 10 4403 4403
Hartstein 20 13003 19006
Fay 6003 19006
Colmenares 30 2503 24918
Raphaely 11003 24918
Khoo 3103 24918
Baida 2903 24918
Tobias 2803 24918
Himuro 2603 24918
Mavris 40 6503 6503
Hunold 60 9003 28815
Ernst 6003 28815
Austin 4803 28815
Pataballa 4803 28815
Lorentz 4203 28815
15rows selected.
Lag / Lead Functions
כאשר קיים צורך לבצע חישוב בין שתי שורות בטבלה (לדוגמא הנוכחית והקודמת, הנוכחית והבאה בתור) כותבי קוד SQL רבים נוטים להשתמש בתתי שאילתות מורכבות.
כפי שניתן לראות בדוגמאות הבאות, פונקציות ה Lag וה Lead מאפשרות לנו גישה פשוטה ונוחה לערכים אלו.
בדוגמא הראשונה אנו מייצרים שתי עמודות נוספות (LAG, LEAD) אשר כל אחת מהן מחזירה את הערך הקודם והבא בתור עבור השורה הנוכחית.
HR >SELECT employee_id,first_name,salary,
2 LAG(salary,1) OVER (ORDER BY salary) as lag,
3 LEAD(salary,1) OVER (ORDER BY salary) as lead
4 FROM employees
5 WHERE department_id = 80
6 ORDER BY salary;
EMPLOYEE_ID FIRST_NAME SALARY LAG LEAD
----------- -------------------- ---------- ---------- ----------
173 Sundita 6103 6203
179 Charles 6203 6103 6203
167 Amit 6203 6203 6403
166 Sundar 6403 6203 6803
165 David 6803 6403 7003
161 Sarath 7003 6803 7003
155 Oliver 7003 7003 7203
164 Mattea 7203 7003 7303
172 Elizabeth 7303 7203 7403
...
34 rows selected.
בדוגמא הבאה אנו מייצגים שנית את הערך הקודם והבא עבור השורה הנוכחית, אבל הפעם לכל קבוצה באופן נפרד :
SELECT department_id , employee_id , salary ,
LEAD(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ) NEXT_LOWER_SAL,
LAG(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ) PREV_HIGHER_SAL
FROM employees
WHERE department_id IN (10, 20, 30, 40, 60, 90)
ORDER BY department_id, salary DESC;
DEPARTMENT_ID EMPLOYEE_ID SALARY NEXT_LOWER_SAL PREV_HIGHER_SAL
------------- ----------- ---------- -------------- ---------------
10 200 4403
20 201 13003 6003
202 6003 13003
30 114 11003 3103
115 3103 2903 11003
116 2903 2803 3103
117 2803 2603 2903
118 2603 2503 2803
119 2503 2603
40 203 6503
60 103 9003 6003
104 6003 4803 9003
105 4803 4803 6003
106 4803 4203 4803
107 4203 4803
90 102 17003 17003
101 17003 4 17003
100 4 17003