פונקציות חלון ב־SQLExplained

27 of 27 rows

פונקציה / סעיף↕	מה היא מחזירה↕	דוגמה↕
דירוג
⤢ROW_NUMBER()	מספר שלם עוקב וייחודי לכל שורה בתוך החלון.	ROW_NUMBER() OVER (ORDER BY total DESC)
⤢RANK()	דירוג עם רווחים; שורות שוות חולקות דירוג והמספרים הבאים מדולגים.	RANK() OVER (ORDER BY total DESC)
⤢DENSE_RANK()	דירוג בלי רווחים; שורות שוות חולקות דירוג ושום דבר לא מדולג.	DENSE_RANK() OVER (ORDER BY total DESC)
⤢NTILE(n)	מפצל את החלון הממוין ל־n קבוצות שוות בערך.	NTILE(4) OVER (ORDER BY total DESC)
הצצה לשורות שכנות
⤢LAG(expr, n, default)	ערך מ־n שורות אחורה; ערך ברירת המחדל מחליף אותו כשאין שורה כזאת.	LAG(total, 1, 0) OVER (ORDER BY created_at)
⤢LEAD(expr, n, default)	ערך מ־n שורות קדימה; ערך ברירת המחדל מחליף אותו כשאין שורה כזאת.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
צבירה על פני חלון
⤢SUM() OVER	סכום רץ או לפי מחיצה בלי לכווץ שורות.	SUM(total) OVER (ORDER BY created_at)
⤢AVG() OVER	ממוצע רץ או לפי מחיצה.	AVG(total) OVER (PARTITION BY user_id)
⤢COUNT() OVER	ספירת שורות רצה או לפי מחיצה.	COUNT(*) OVER (PARTITION BY user_id)
⤢MIN() OVER	הערך הנמוך ביותר על פני החלון או המחיצה.	MIN(total) OVER (PARTITION BY user_id)
⤢MAX() OVER	הערך הגבוה ביותר על פני החלון או המחיצה.	MAX(total) OVER (PARTITION BY user_id)
⤢Running total	SUM עם ORDER BY ומסגרת פתוחה מההתחלה; כל שורה מסכמת את כל מה שעד אליה.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
⤢Moving average	AVG על מסגרת של n שורות לפני ואחרי השורה הנוכחית.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ראשון, אחרון ואחוזונים
⤢FIRST_VALUE()	הערך הראשון בסדר של החלון.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
⤢LAST_VALUE()	הערך האחרון במסגרת; בלי UNBOUNDED FOLLOWING המסגרת נעצרת בשורה הנוכחית.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
⤢NTH_VALUE(expr, n)	הערך בשורה ה־n של סדר החלון.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
⤢PERCENT_RANK()	הדירוג היחסי של שורה, מ־0 עד 1.	PERCENT_RANK() OVER (ORDER BY total)
⤢CUME_DIST()	חלק השורות שבשורה הנוכחית או לפניה, מ־1/n עד 1.	CUME_DIST() OVER (ORDER BY total)
סעיף ה־OVER
⤢OVER (...)	מגדיר את החלון שכל פונקציה קוראת.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
⤢PARTITION BY	מתחיל את החלון מחדש לכל קבוצת שורות.	PARTITION BY user_id
⤢ORDER BY	מסדר שורות בתוך כל מחיצה.	ORDER BY created_at
⤢ROWS BETWEEN	תוחם את המסגרת הנעה, למשל משורות קודמות לשורות באות.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
⤢RANGE BETWEEN	תוחם את המסגרת לפי ערכים ולא לפי מיקומי שורות; CURRENT ROW כולל שורות שוות ערך, וזו מסגרת ברירת המחדל תחת ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
⤢GROUPS BETWEEN	תוחם את המסגרת לפי קבוצות של שורות שוות ערך מה־ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
⤢EXCLUDE CURRENT ROW	מסיר את השורה הנוכחית מהמסגרת; EXCLUDE TIES מסיר במקום זאת את השווים לה.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
⤢WINDOW	נותן שם לחלון פעם אחת בשאילתה כדי שמספר סעיפי OVER יוכלו לעשות בו שימוש חוזר.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
⤢FILTER (WHERE ...)	מצמצם את השורות שצבירת חלון קוראת, לפני החלת החלון.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)