LAG, LEAD וסיכומים שוטפים

LAG ו-LEAD הן שתי פונקציות חלון מיוחדות המעניקות לך גישה אל שורות אחרות של אותה מחיצה: השורה הקודמת (LAG) או הבא (LEAD) לפי ה-ORDER BY של החלון. הם ה כלי go-to עבור השוואות שורה לשורה (דלתות, וריאציות, מרווחים) ו - בשילוב עם SUM/AVG/COUNT OVER - עבור סיכומים שוטפים.

LAG: השורה הקודמת

LAG(colonna)            OVER (PARTITION BY p ORDER BY o)
LAG(colonna, n)         OVER (PARTITION BY p ORDER BY o)   -- n righe indietro
LAG(colonna, n, default) OVER (PARTITION BY p ORDER BY o)  -- default se non c'è

דוגמה: עבור כל הזמנה, "ימים מאז ההזמנה הקודמת באותה מידה לקוח":

SELECT id,
       customer_id,
       ordered_at,
       ordered_at - LAG(ordered_at) OVER (
         PARTITION BY customer_id ORDER BY ordered_at
       ) AS since_previous_order
FROM   orders
ORDER BY customer_id, ordered_at;

בהזמנה הראשונה של כל לקוח יש since_previous_order = NULL בגלל אין "קודם" במחיצה.

LEAD: השורה הבאה

המראה של LAG. טיפוסי: "זמן עד לאירוע הבא", "הבדל עם הגרסה הבאה".

SELECT id, ordered_at,
       LEAD(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)
         - ordered_at AS to_next
FROM   orders;

סיכומים רצים: נצברים עם OVER

פונקציות מצטברות (SUM, AVG, COUNT, MAX, MIN) הופכות מצטבר בשילוב עם OVER (… ORDER BY …):

SELECT order_id,
       total,
       SUM(total) OVER (
         PARTITION BY customer_id
         ORDER BY     ordered_at
       ) AS running_total
FROM   …;

עבור כל שורה, SUM מחשב את הסכום של כל השורות במחיצה עד נקודה זו, לפי ה-ORDER BY של החלון. התוצאה היא ה "איזון ריצה" קלאסי.

נסה את זה

סקירת התרגיל