פונקציות אנליטיות הן נושא נהדר שכל כותב קוד SQL צריך להכיר. פונקציות אלו מאפשרות לנו לבצע חישובים מורכבים בתוך שאילתות, חישובים אשר ללא הפונקציות האנליטיות – היו אפשריים רק באמצעות PLSQL, תתי שאילתות או פקודות Join מורכבות ומסובכות.

באורקל קיימות פונקציות אנליטיות שונות, בפוסט זה אתמקד בפונקציות הבאות :

1.      Ranking FunctionsRank, 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