שימוש בטבלאות של חתכי אוכלוסיה בבניית דו"חות

במקרים רבים יש צורך בבנית דו"חות המסכמים נתונים לפי כל מיני קריטריונים שונים ומשונים כך שבכל שורה בדו"ח היינו רוצים לקבל סיכום נתונים של אוכלוסיה אחרת של רשומות. שיטה נפוצה לבניית דו"חות מסוג זה היא שימוש בטבלאות אכלוס.

לצורך ההדגמה, נביא דוגמה מעולם ה-HR שבאופן מסורתי משמש בלימוד בסיסי נתונים.

נניח שיש לנו טבלת עובדים במבנה הבא (טיפוסי הנתונים הם של SQL Server אבל בקלות אפשר להמיר אותם לטיפוסים של אורקל או כל בסיס נתונים אחר):

EmpId int

EmpName varchar

DeptId int

Salary float

Bonus float

נבנה טבלת חתכי אוכלוסיה דינאמית במבנה הבא:

PopulationId int

PopulationDesc varchar

DeptId_Ind bit

DeptId_values varchar

Salary_Ind bit

Salary_From float

Salary_To float

נכניס ערכים לדוגמה בטבלה:

Salary_ To	Salary_ From	Salary_ Ind	DeptId_ values	DeptId_ Ind	Population Desc	Population Id
		Null	1,2,5	True	Important employees	1
999999	20000	False	1,5	False	Nice employees	2
5000	0	True	10,11,12	True	Underpaid employees	3
999999	30000	True		NULL	Overpaid employees	4

הגדרנו 4 חתכי אוכלוסיה. כעת נוכל ליצור דו"ח שיציג לנו את נתונים סיכומיים בכל חתך. מה בעצם מגדיר כל חתך:

• החתך "Importance employees" מגדיר עובדים העובדים במחלקות 1 או 2 או 5.
• החתך "Nice employees" מגדיר עובדים שלא עובדים מחלקות 1 או 5 (לא עובדים בגלל ש-DeptId_Ind הוא False) ואשר לא מרוויחים בין 20000 ו- 999999 ש"ח (לא מרוויחים בגלל ש- SalaryInd הוא False).
• החתך "Underpaid employees" מגדיר עובדים העובדים במחלקות 10 או 11 או 12 ומשכורתם בין 0 ל- 5000 ש"ח
• החתך "Overpaid employees" אינו מתייחס כלל למחלקות (DeptId_Ind הוא NULL) ומתייחס לעובדים המרויחים מעל 30000 ש"ח. 

לאחר שהוגדרו האוכלוסיות, צריך להציג את הנתונים. ההצגה נעשית ע"י מכפלה קרטזית של הרשומות מטבלת הנתונים employees וטבלת חיתוכי האוכלוסיה emp_population.

Select  PopulationDesc, sum(salary) "Total Sal", avg(salary) "Avg. sal", sum(bonus) "Total Bonus" 

From employees e cross join emp_population ep

 Where ((ep.DeptId IN (select word from fn_split(e.deptId, ',') f) AND ISNULL(DeptId_Ind, 1) = 1)

         OR                  

       (ep.DeptId NOT IN (select word from fn_split(e.deptId, ',') f) AND ISNULL(DeptId_Ind, 0) = 0)     

   AND 

       ((e.Salary between ep.Salary_From and ep.Salary_To AND ISNULL(Salary_Ind, 1) = 1) 

        OR

        (e.Salary NOT between ep.Salary_From and ep.Salary_To AND ISNULL(Salary_Ind, 0) = 0)        

       (( 

זהו SQL שלקוח מ-SQL Server. באותו אופן אפשר להתאימו לאורקל ולהחליף את הפונקציה ISNULL ב-NVL.

שימו לב לפונקציה fn_split שאמורה להפוך מחרוזת מופרדת בפסיקים (או תו מפריד אחר) לטבלת ערכים. פונקציה כזו אפשר להוריד ממגוון אתרים כמו למשל:http://www.sqlservercentral.com/scripts/Miscellaneous/31913

באורקל מדובר ב-pipelined function ולכן הקריאה לפונקציה תהיה יחד עם שימוש בפונקציה table. דוגמה לפונקציה שכזו באורקל אפשר להוריד מהבלוג הזה:http://edgewaters.blogspot.com/2008/10/oracle-plsql-split-function-boosts.html

 דוגמה לפלט אפשרי:

Population Desc	Total Sal	Avg. Sal	Total Bonus
Important Employees	100000	22000	280000
Nice Employees	120000	15000	100000
Under Paid employees	150000	3000	0
Over Paid employees	1000000	42000	700000

אפשר גם לעשות טבלת חיתוכי אוכלוסיה עם הרבה יותר חיתוכים (ויותר עמודות) ופשוט להרכיב עוד תנאים על המכפלה הקרטזית של הטבלאות.