פונקציות חלון ב Firebird 3

פונקציות חלון במסדי נתונים (גם מוכרים כפונקציות אנליטיות), הן פונקציות בתוך שאילתא בחלק ה select המאפשרות לבצע אגריגציה על מידע ללא סינון של המידע עצמו בפעולות count, max, avg וכו'.

גרסה 3 של firebird מספקת תמיכה בסיסית בתכונות האלו על ידי שימוש ב OVER.

בואו נקח לדוגמא את הטבלה הבאה: יש לי טבלת עובד. היא מכילה שדות של  מזהה, שם, ומשכורת. נגיד ואני רוצה להציג את המשכורת של כל עובד בייחד עם הצגת האחוז של המשכורת מול כל המשכורות בייחד.

שאילתא "רגילה" תראה בצורה הבאה:

select
    id,
    name,
    salary,
    salary / (select sum(salary) from employee) percentage
from employee;

אבל עם פונקציות חלון, השאילתא תראה כך:

select
    id,
    name,
    salary,
    salary / sum(salary) over () percentage
from employee;

ובהתחלה זה רק נראה נקי יותר. הבעיה בשאילתא הראשונה היא שעל כל שאילתא אחת, נעבור על כל הטבלה של העובדים ונחשב כל פעם מחדש. זה לא יעיל ומאוד איטי. יש הרבה דרכים להתמודד עם זה, אבל עדיין אין שום דרך שהיא באמת יעילה. אפשר להשתמש בפרמטר שהוא משתנה ואז לעשות פעם אחת את השאילתא של ה sum ולהעביר לשאילתא המקורית שלנו את המשתנה, אבל אז אנחנו זקוקים למשהו באמצע, ואנחנו כבר יוצאים מגישת הSQL הנקי.

השאילתא השנייה לא רק יותר נקייה, אלא גם מהירה יותר. היות וsalary / sum(salary) over () percentage מחושב, נשמר במתמון ולא עובר הרבה פעמים על הטבלה כל פעם מחדש. ובעצם זה מה ש OVER מבצע על פונקציות החלון.

העניין הוא שOVER הוא לא רק זה. ניתן באמצעות אפשרות בשם PARTITION לבצע פעולת GROUP BY  ובכך שיצרנו קבוצה אנחנו יכולים להתמודד איתה בשאילתא הגדולה יותר.

החוקיות של PARTITION BY נראה כך:

<window function>([<expr>]) OVER ([PARTITION BY <expr>] [, <expr> ...])

במידה ונוסיף שדה של תפקיד בטבלה שיצרנו ונרצה לראות את אחוז המשכורת בייחס לשאר העוסקים באותו תפקיד במקום בייחס לשאר העובדים בכללי, נעשה את זה כך:

select
    id,
    name,
    role,
    salary,
    salary / sum(salary) over (partition by role)
from employee;

אבל גם זה לא כל התמיכה של OVER. ב OVER יש תמיכה גם לאפשרות של ORDER BY אשר יכולה להשתמש (לפי בחירה) ב PARTITION .

אם נחשוב על הטבלה הנוכחית שלנו בצורה הבאה:

id salary
1 10.00
2 12.00
3 8.00
4 9.00
5 10.00

ונבצע את השאילתא בצורה הבאה:

select
    id,
    salary,
    sum(salary) over (order by salary) cum_salary
from employee
  order by salary;

אז נקבל טבלה כזו:

id salary cum_salary
3 8.00 8.00
4 9.00 17.00
1 10.00 37.00
5 10.00 37.00
2 12.00 49.00

אפשר לראות ש cum_salary מכיל בעצם אגריגציה של sum(salary) . בטח אתם מתפלאים למה 37 חוזר פעמיים, אבל למרות שיש כאן קבוצה, אנחנו בעצם עשינו סידור של המידע ולא איגוד של התוצאות מה ש group by נקי היה עושה.

אפשר להשתמש ביותר משורת OVER אחת בשאילתא, ואפשר גם להשתמש ב ASEC ו DESC בתוך הORDER BY. למעשה פרט ל LIST, אפשר להשתמש ב ORDER BY על כל פונקציות החלון. הסיבה שאי אפשר להשתמש בה על LIST היא מכיוון ש LIST מחזיר שדה מסוג BLOB .

רוב הפוסט הזה מבוסס על 2 כתבות שנעשו על ידי המפתח שהוסיף את התמיכה של OVER ב FIREBIRD. הנה המאמר הראשון על פונקציות חלון והמאמר השני על פונקציות חלון.

select
id,
salary,
sum(salary) over (order by salary) cum_salary
from employee
order by salary;

9 מחשבות על “פונקציות חלון ב Firebird 3

  1. Shai

    בעניין הדוגמה הראשונה: חפש על With. היא עושה את העבודה הזו בצורה לא פחות נקיה ויעילה, ולטעמי הרבה יותר ברורה (היא נותנת פתרון SQL־י טהור להצעה שלך להשתמש במשתנה).

    הדוגמה השניה היא self-join פשוט, ולא ברורה לי התועלת הגדולה כאן.

    דוגמה שלישית: הסיבה ש־37 מופיע פעמיים בתוצאה היא ש־10 מופיע פעמיים במקור, ולכן הרשומות 1 ו־5 נמצאות שתיהן במקום השלישי בסדר; המערכת בוחרת שלא להעדיף אחת מהן באופן שרירותי, וכל הכבוד לה על כך — למרות שיהיה מי שיגיד שזה לא אינטואיטיבי.

    תודה על פוסט מעניין ומשכיל,

    שי.

  2. ik_5 מאת

    שי, הדוגמא הראשונה מראה איך לא צריך לעשות את זה. cross join שהיית צריך לעשות במקרה של הראשון לא בדיוק עושה את אותה העבודה כמו כאן. ב join הוא עדיין עובר על הטבלה יותר מפעם אחת. אותו המקרה ל with , שגם שם הוא היה עובר אין ספור פעמים זה הרעיון של CTE בכללי. ההבדלהוא שבשימוש OVER הוא עושה cache למעבר הראשון וכל שאר המידע נמשך מה cache שזה בעצם זהה לרעיון של משתנה, רק שזה מתבצע בשבילך ללא משתנה אמיתי.

    לדעתי Self Join לא היה עושה את אותה העבודה שנעשת כאן, אבל אני צריך לנסות את זה בשביל שאני אהיה בטוח.

  3. Shai

    עידו,

    כבר בדוגמה הראשונה שלך, אם מתבצע מעבר על הטבלה יותר מפעמיים אז צריך לתפוס את ה־query optimizer ולהעיף לו סטירה עם הרגל. SQL היא לא פסקל; המנוע יכול וצריך להתייחס לשאילתא כהגדרה לוגית בלבד, כלומר "מה צריך לעשות" ולא "איך"; ברוב המנועים הבוגרים, explain על שאילתא כזו יראה לך שעוברים על הטבלה בדיוק את שתי הפעמים שצריך ולא אף אחת יותר. כנ"ל לגבי ה־join בדוגמה השניה.

    ה־join מתבצע מול שליפת הסיכומים:


    select id,
    name,
    role,
    salary,
    salary / sumsalary
    from employee
    join (select sum(salary) sumsalary, role sumrole from employee group by role) t
    on role=sumrole

  4. ik_5 מאת

    אז לא קראת את מה שכתבתי בפוסט, או את הקישורים שהעברתי בסוף.

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

    השתמשתי במסד הנתונים employee שמגיע עם FB בתור דוגמא.
    מתוך 89 רשומות בטבלת ה employee שם, זה מה שיצא מהשאילתא שלך:

    180 fetches, 0 marks, 0 reads, 0 writes.
    0 inserts, 0 updates, 0 deletes, 0 index, 84 seq.
    Delta memory: 3736 bytes.
    Total execution time: 0.068s
    Script execution finished.

    הוא ניגש 180 פעם לטבלה. זה לא יעיל ! אם היו 180 רשומות זה היה מכפיל את עצמו במקרה הטוב. בדיוק זה מה ש OVER מנסה למנוע כאן.

  5. Shai

    קראתי מצויין את מה שכתבת בפוסט. עכשיו קראתי גם את הקישורים, והם לא הוסיפו לי דבר מעבר למה שכתבת.

    הבנתי מה השאילתא הראשונה ומה השניה; אני לא חושב שפונקציות החלון מוסיפות שם משהו.

    והנקודה שניסיתי להבהיר: אם יש הבדל משמעותי בביצועים בין ניסוחי השאילתות השונים — גם בין שתי השאילתות הראשונות שלך, וגם בין שאילתת החלון השניה שלך ל־join שלי, זה לא אומר שפונקציות חלון הן דבר יעיל ונפלא; זה רק אומר שה־query optimizer של Firebird מפשל.

  6. ik_5 מאת

    לקבלת המידע לקח פחות ממילי שנייה אחת להגיע, העניין הוא שהוא היה צריך לשלוף 180 פעמים את המידע וזה לא יעיל לא משנה כמה תנסה להציג שכן.
    במקום זה, הציגו לך כלי (שקיים לך גם ב PostreSQL וב MS SQL, ואורקל ועוד כמה) שהתפקיד שלו זה למנוע את ה180 שליפות ובמקום זה לעשות 2 שליפות וזה דורש ממך פחות קוד, ופחות הסתבכות ולא יותר קוד ויותר הסתבכות.

    פרט ל natural join, כל פעולת join שתבצע היא לא ידידותית למתכנת ! היא דורשת כתיבת קוד שלפעמים לא קריא (אם יש לך יותר מפעולה אחת בשאילתא), שלרוב דורש ממך להשקיע הרבה מחשבה באיך לממש אותו נכון (כי לפעמים מה שכתבת ומה שהתכבנת שונים לגמרי), וכו'.
    כל הדבר הזה נחסך לך במקרה הזה. אבל אם לך נוח לעבוד עם join מכל סוג, תהנה.

  7. Shai

    אני אנסה להבהיר שוב את מה שאני טוען:

    מי שצריך למנוע את ה־180 זה ה־query optimizer. מאחר ושתי השאילתות שקולות, וקל לראות את זה, הוא צריך להגיע לאותה תוכנית לביצוע.

    אתה מתעקש לא להבין את מה שאני כותב, ולהתייחס לשאילתת SQL כאילו היא קוד בפסקל שאומר ל־database בדיוק מה לעשות. יכול להיות ש־Firebird מתנהג ככה. אם כן, הוא דפוק. לא PostgreSQL ולא אף אחד מהמסחריים לא מתנהגים ככה. שאילתת SQL אמורה להגדיר *רק* אילו רשומות אמורות לחזור. בשביל להחליט איך למצוא אותן, יש למנוע חלקים שנקראים Query Optimizer ו־Query Planner. הם לא יכולים לעשות תמיד את הדבר הנכון, אבל הם בהחלט צריכים להתאמץ.

    אני אגיד את זה בצורה בוטה, כי אחרת אתה לא מבין: אם explain על שתי השאילתות הראשונות שלך לא מציג בדיוק את אותה תוכנית, זה באג ב־Firebird. כי הוא *כן* אמור לעשות שם בדיוק את אותו דבר.

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

  8. ik_5 מאת

    שי בוא אני אתרגם לך את הדברים שלך:

    אתה אומר "לא מעניין אותי שמסדי נתונים ירוצו 1,000 פעם על משהו, התפקיד שלהם לדעת שהם יכולים לקצר את זה". התשובה היא שהם לא יודעים את זה. במקרה הטוב אם התוצאה חוזרת היא תשמר בסוג של cache, אבל מראש לא מבטיחים לך את זה *בשום* מסד נתונים.

    באים ונותנים לך כלי שמגדיר מתי אתה כן לפעולה מסויימת צריך cache, אומרים לך שבמקרים 1 2 3 4 אתה צריך להשתמש בהם בשביל *שלך* יהיה מהר יותר.

    אתה אומר "שמע, אני מחליט ואני קובע וזה לא מרשים אותי" סבבה אל תשתמש בזה, זה כלי אחד מיני רבים לעשות את אותה העבודה.

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

    אז בהצלחה בדרך שלך, אני מעדיף את הדרך של OVER בכל מסד נתונים שתומך בו כי אני מעדיף לשים בורג בקיר אם מברג ולא ציפורניים ארוכות (שאין לי).

  9. שי

    תרגמת קרוב, אבל לא בדיוק:

    בהחלט מעניין אותי שמסדי הנתונים לא ירוצו 1000 פעם על משהו. אני גם בהחלט מודע לכך שלא מובטחת לי תכנות ביצוע אופטימלית (הבעיה הכללית של מציאת תוכנית כזאת היא בודאי NP-hard, אם לא קשה עוד יותר). עדיין, אני מצפה מהם להסתדר במקרים הקלים, כמו הדוגמה הראשונה שלך. שוב: אם יש שם תוכנית ביצוע שונה, זה באג ראוי לדיווח.

    "זה לא מרשים אותי": לא אמרתי. להיפך, התרשמתי לטובה. לגבי over/order-by אפילו ציינתי את זה. התרשמתי לרעה מהצגת הנושא מכיוון הביצועים.

    לגבי ה"תרגום לשפת תכנות רגילה": זו במידה רבה הנקודה. SQL היא לא שפת תכנות רגילה, ומימושים שלה מחוייבים להשתדל לעבוד בהתאם. הטענה שלך על "עוד דרך" היא פשוט מופרכת מיסודה: הפקודה עם over פשוט לא אמורה להגיד למנוע שום דבר על איך לבצע את השאילתה. אם היא אומרת לו לבצע cache בצורה מסויימת, זה באג (חשבת, למשל, מה יקרה כאשר הטבלה לא תכנס לזיכרון הפיסי? האם תגלה פתאום שהפקודה היעילה שלך תוקעת את כל המחשב ב-thrash בגלל שכמות הנתונים גדלה, או שאז תצפה פתאום מהמנוע לגלות בדרך פלא את הדבר הנכון לעשות?)

כתיבת תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s