Menu
CoddyTech

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

חלק מהיחידה יסודות במסלול ה-SQL של Coddy. שיעור 69 מתוך 72.

נכון לעכשיו, אין לנו גמישות בבחירת מספר השורות שלפני או אחרי שיש להביא בחשבון. כעת הדבר אפשרי באמצעות הקריטריונים 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

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

ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

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

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

בנוסף, בתאריכים המאוחסנים כטקסט (כמו '2024-01-05'), היסט של RANGE אינו מבצע חישובי תאריכים: עם 1 PRECEDING החלון מכיל רק את השורות עם אותו תאריך. כדי לספור ימים, יש למיין לפי מספר במקום זאת, למשל julianday(date).

הנה דוגמה פשוטה להמחשת ההבדל בין 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
challenge icon

אתגר

קל

טבלאות ועמודות זמינות:

  • newspapers: date, num_newspapers

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

נסו בעצמכם

quiz iconבחנו את עצמכם

השיעור הזה כולל חידון קצר. התחילו את השיעור כדי לענות עליו ולעקוב אחרי ההתקדמות.

כל השיעורים ביחידה יסודות

תרגלו בעצמכם: SQL אונליין