קריטריון 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אתגר
קלטבלאות ועמודות זמינות:
newspapers:date,num_newspapers
באתגר הזה יש לנו נתונים על ייצור עיתונים. נרצה לדעת מהו מספר העיתונים הממוצע שהודפסו יומיים לפני השורה הנוכחית ויום אחד אחריה. כמו כן, נרצה לדעת מהו ההפרש בין המספר המרבי למספר המזערי של העיתונים שהודפסו מהתאריך הנוכחי ובשלושת הימים שלפניו. קראו לעמודות האלה avg_newspapers ו-diff_newspapers, בהתאמה.
נסו בעצמכם
השיעור הזה כולל חידון קצר. התחילו את השיעור כדי לענות עליו ולעקוב אחרי ההתקדמות.
כל השיעורים ביחידה יסודות
2תנאים
יסודות התנאיםמילת המפתח ANDמילת המפתח ORמילת המפתח NOTשילוב של תנאים מרוביםסוגרייםערכים בוליאניים3פורמט החזרה ספציפי
ערכי Nullמיון תוצאות – חלק 1מיון תוצאות – חלק 2סיכום – חברת אבטחת סייברהגבלת מספר הרשומותסיכום – מפעל כלי רכב6אתגרי מבוא
חזרה – בחירות לפרלמנטחזרה – מעצר חשוד בידי המשטרהחזרה – כלי משקה בברחזרה – מהנדסים עמודות חדשות9טבלאות מרובות
JOIN בסיסי חלק 1JOIN בסיסי חלק 2סיכום – JOINJOIN עצמיסיכום – JOIN עצמיאיחוד (UNION)פישוט שאילתות באמצעות מילת המפתח WITHסיכום – שאילתות WITHסיכום – קבלן נדל״ן12פונקציות חלון חלק 2
פונקציות RANK ו-DENSE_RANKסיכום – RANK ו-DENSE_RANKפונקציית NTILEפונקציות צבירהקריטריון ROWS ו-RANGEתרגלו בעצמכם: SQL אונליין