קריטריון ROWS ו-RANGE

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

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

ואפשר לציין את האפשרויות הבאות:

• CURRENT ROW - השורה הנוכחית
• n PRECEDING - שורות לפני השורה הנוכחית
• n FOLLOWING - שורות אחרי השורה הנוכחית 

ההבדל בין ROWS ל־RANGE הוא שהקריטריון ROWS אינו מתחשב בערכים, אלא רק במיקומים, ואילו RANGE מגדיר את החלון במונחים של טווחי ערכים ולא של מיקומי שורות.

עבור RANGE אנחנו חייבים לציין ORDER BY --column_name--, כי אחרת הוא לא ידע איך לבחור את החלון.

לדוגמה:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

כאן היא יוצרת חלון שכולל את השורה הנוכחית, את השורה שלפניה ואת השורה שאחריה.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

כאן נוצרת מסגרת חלון שכוללת עבור כל רמה (ממוינת בסדר עולה) את הרמה הנוכחית, רמה אחת לפניה ורמה אחת אחריה. אם הרמה הנוכחית היא 5, היא תכלול את הרמות 4, 5 ו-6.

הערה: השימוש ב־RANGE BETWEEN עשוי לכלול יותר שורות בחלון שלך, מכיוון שהוא כולל את כל השורות שחולקות את אותם הערכים כמו אלה שבטווח, בעוד ש־ROWS BETWEEN תמיד יכלול את אותו מספר שורות (כל עוד הן זמינות בקבוצת הנתונים). 

בנוסף, RANGE אינו תומך בעמודות תאריך.

הנה דוגמה פשוטה להמחשת ההבדל בין ROWS ל-RANGE:

בשימוש ב-ROWS:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

שימוש ב־RANGE:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range