פונקציות LEAD ו-LAG

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

הפונקציות LEAD ו־LAG מאפשרות לנו לגשת לערך של השורה הנוכחית בהיסט של n צעדים לאחור או n צעדים קדימה.

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

id	revenue	month
1	532	5
2	492	6
3	393	7
4	723	8

SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY id

פעולה זו תיצור את הטבלה הבאה:

id	revenue	prev_month_revenue
1	532
2	492	532
3	393	492
4	723	393

כך נוכל לחשב את היחס prev_month_revenue/revenue.

אם במקום זאת נשתמש בפונקציה LEAD, היא תיקח את ההכנסה של החודש הבא מכל שורה:

SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id

id	revenue	next_month_revenue
1	532	492
2	492	393
3	393	723
4	723

אתגר

קל

טבלאות ועמודות זמינות:
air_conditioners: id, efficiency, strength, month

השווה את היעילות של כל מזגן ליעילות של המזגן הקודם.

כתוב שאילתה שמציגה את היעילות של כל מזגן לצד היעילות של המזגן הקודם שהותקן (בהתבסס על id). חשב גם את ההפרש ביעילות בין המזגן הנוכחי לקודם. מיין את התוצאה לפי id ו-efficiency בסדר עולה.

עמודות הפלט הצפויות:

id
efficiency
previous_efficiency (באמצעות LAG)
efficiency_difference (היעילות הנוכחית - היעילות הקודמת)

לבסוף, עטוף את השאילתה וסנן את השורה הראשונה, שבה previous_efficiency (ולכן גם efficiency_difference) ריק, כלומר NULL:

SELECT * FROM (
    -- Your query here
)
WHERE previous_efficiency IS NOT NULL

נסו בעצמכם

בחנו את עצמכם

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

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

2תנאים

יסודות התנאים מילת המפתח AND מילת המפתח OR מילת המפתח NOT שילוב של תנאים מרובים סוגריים ערכים בוליאניים

5פעולות חשבון

אופרטורים מתמטיים עמודות מתמטיות פעולת מודולו הפונקציה ROUND()

8סטטיסטיקה

פונקציות צבירה מובנות חלק 1 פונקציות צבירה מובנות חלק 2 קיבוץ חלק 1 קיבוץ חלק 2 שאילתות משנה חלק 1 שאילתות משנה חלק 2 סיכום – חנות הרווח הכולל סיכום – חנות הקורקינטים סיכום – בית הקפה

11פונקציות חלון חלק 1

פונקציית ROW_NUMBER קריטריון ORDER BY קריטריון PARTITION BY PARTITION ו-ORDERפונקציות LEAD ו-LAGסיכום – LEAD ו-LAG סיכום – תמונות סיכום – תיבות

3פורמט החזרה ספציפי

ערכי Null מיון תוצאות – חלק 1 מיון תוצאות – חלק 2 סיכום – חברת אבטחת סייבר הגבלת מספר הרשומות סיכום – מפעל כלי רכב

6אתגרי מבוא

חזרה – בחירות לפרלמנט חזרה – מעצר חשוד בידי המשטרה חזרה – כלי משקה בבר חזרה – מהנדסים עמודות חדשות

9טבלאות מרובות

JOIN בסיסי חלק 1 JOIN בסיסי חלק 2 סיכום – JOIN JOIN עצמי סיכום – JOIN עצמי איחוד (UNION)פישוט שאילתות באמצעות מילת המפתח WITH סיכום – שאילתות WITH סיכום – קבלן נדל״ן

12פונקציות חלון חלק 2

פונקציות RANK ו-DENSE_RANK סיכום – RANK ו-DENSE_RANK פונקציית NTILE פונקציות צבירה קריטריון ROWS ו-RANGE

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