Menu

פונקציות חלון ב-PostgreSQL: OVER, PARTITION BY, ROW_NUMBER

פונקציות חלון (window functions) מחשבות ערך לכל שורה מתוך קבוצת שורות קשורות בלי לכווץ אותן: דירוגים, סכומים מצטברים, ממוצעים נעים והשוואות לשורה הקודמת.

בדף הזה יש עורכים שאפשר להריץ. לערוך, להריץ ולראות את הפלט מיד.

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

כל שורה נשארת בתוצאה ונושאת את הסכום של האזור שלה: 1000 ל-north, ‏500 ל-south. החלק של כל נציג הוא אז ביטוי אחד (ל-ana יש 50 אחוז מ-north). כל פונקציית צבירה (sum, count, avg, min, max) הופכת לפונקציית חלון כשמוסיפים OVER. OVER () ריק הופך את כל התוצאה לחלון אחד.

למספר שורות עם ROW_NUMBER, ‏RANK ו-DENSE_RANK

שלוש פונקציות הדירוג שונות רק כששורות שוות בערך של ORDER BY:

ל-ben ול-cy יש 85. ‏row_number עדיין נותן להם 2 ו-3; מי מקבל 2 לא מוגדר, אלא אם ה-ORDER BY שובר את השוויון. rank נותן לשניהם 2 וקופץ ל-4 עבור dee. ‏dense_rank נותן לשניהם 2 וממשיך עם 3.

פונקציהשוויוןפערים אחרי שוויון
row_number()מספרים שוניםלא
rank()אותו מספרכן
dense_rank()אותו מספרלא

לקבל את N השורות הראשונות בכל קבוצה

פונקציות חלון מחושבות אחרי WHERE, ולכן אי אפשר לסנן לפיהן ישירות. מספרו את השורות בתת שאילתה או ב-CTE, וסננו בחוץ:

השאילתה מחזירה את שני הנציגים הטובים ביותר בכל אזור. השתמשו ב-rank() במקום ב-row_number() כדי לשמור כל שורה ששווה במקום השני. לשורה הראשונה בלבד בכל קבוצה, DISTINCT ON קצר יותר.

לחשב סכום מצטבר או ממוצע נע

עם ORDER BY בתוך OVER, פונקציית צבירה רצה על מסגרת. כברירת מחדל המסגרת מתחילה בשורה הראשונה של ה-partition ומסתיימת בשורה הנוכחית, וזה נותן סכום מצטבר. פסוקית ROWS BETWEEN קובעת מסגרת אחרת, כמו השורה הנוכחית ושתי השורות שלפניה:

הסכום המצטבר מגיע ל-130 ביום 5, והממוצע של שלושה ימים ביום 5 הוא (30 + 20 + 50) / 3 = 33.3.

מסגרת ברירת המחדל היא RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. היא כוללת כל שורה ששווה לנוכחית בערך של ORDER BY, כך ששתי שורות עם אותו יום מקבלות את אותו סכום מצטבר. כתבו ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW כדי להוסיף אותן אחת אחת.

להשוות לשורה הקודמת: LAG ו-LEAD

lag(col) קורא ערך מהשורה שלפני הנוכחית בסדר החלון, ו-lead(col) מהשורה שאחריה. שתיהן מחזירות NULL מעבר לקצה, אלא אם מעבירים ערך ברירת מחדל כארגומנט שלישי.

ליום 1 אין שורה קודמת, ולכן previous ו-change הם NULL. פסוקית WINDOW מגדירה חלון פעם אחת, בשם, כדי שכמה פונקציות ישתמשו בו.

first_value עובד עם מסגרת ברירת המחדל, אבל last_value לא: מסגרת ברירת המחדל מסתיימת בשורה הנוכחית, ולכן הוא מחזיר את הערך של השורה הנוכחית. הוסיפו ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING כדי שהמסגרת תהיה כל ה-partition. פונקציות נוספות שקיימות רק כפונקציות חלון הן ntile(n), percent_rank(), cume_dist() ו-nth_value(); המדריך של PostgreSQL לפונקציות חלון מפרט את כולן.

לסנן אילו שורות פונקציית צבירה סופרת

FILTER (WHERE ...) עובד גם על פונקציות צבירה בחלון, לסכומים מותנים בכל שורה:

SELECT day,
       count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;

שאלות נפוצות

מה ההבדל בין GROUP BY ל-PARTITION BY?

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

אפשר להשתמש בפונקציית חלון ב-WHERE?

לא. פונקציות חלון רצות אחרי WHERE, ‏GROUP BY ו-HAVING, ולכן הן מותרות רק ברשימת ה-select וב-ORDER BY. חשבו את הערך בתת שאילתה או ב-CTE וסננו בשאילתה החיצונית.

למה LAST_VALUE מחזיר את הערך של השורה הנוכחית?

עם ORDER BY בתוך OVER, מסגרת ברירת המחדל מסתיימת בשורה הנוכחית (ובשורות השוות לה), כך שהשורה האחרונה במסגרת היא הנוכחית. הוסיפו ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING כדי שהמסגרת תהיה כל ה-partition.

מה ההבדל בין ROWS ל-RANGE במסגרת?

ROWS סופר שורות פיזיות: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW הוא השורה הנוכחית ושתי השורות שלפניה. RANGE עובד לפי הערך של ORDER BY, כך ששורות עם ערכים שווים (peers) תמיד נמצאות יחד במסגרת.

איור של שפות התכנות ב-Coddy

ללמוד תכנות עם Coddy

להתחיל