Window Functions ב-SQLite: OVER, PARTITION BY ומסגרות

Window Function מוסיפה עמודה בלי לכווץ שורות

GROUP BY מצמצם שורות רבות לאחת. Window function עושה משהו אחר: היא מחשבת ערך על פני קבוצת שורות קשורות, אבל שומרת כל שורת קלט בפלט. מקבלים את הפירוט שורה אחר שורה ואת הצבירה, זה לצד זה.

הצורה תמיד זהה: פונקציה, ואז OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

העמודה total_all מציגה את הסכום הכולל של כל השורות, חוזר על עצמו בכל שורה. השורות המקוריות לא נפגעות. השוו את זה ל-SELECT SUM(amount) FROM sales: אותו מספר, אבל רק שורה אחת חוזרת. Window functions נותנות את שתי התצוגות בבת אחת.

PARTITION BY: צבירה בתוך קבוצות

OVER () ריק צובר על פני כל הטבלה. הוסיפו PARTITION BY כדי לצבור בתוך קבוצות, בדומה ל-GROUP BY, אבל שוב, בלי לכווץ שורות.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

כל שורה מקבלת את הסכום של האזור שלה ואת החלק שלה מהסכום הזה. עם GROUP BY רגיל הייתם מאבדים את הפירוט לכל עובד. זה היתרון המרכזי של window functions: פירוט וגם צבירה בשאילתה אחת.

דירוג: ROW_NUMBER, RANK, DENSE_RANK

משפחת הדירוג ממספרת שורות לפי ORDER BY בתוך OVER. שלושת הסוגים שונים באופן שבו הם מטפלים בתיקו.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

קריאת הפלט:

• ROW_NUMBER() תמיד ייחודי: תיקו נשבר באופן שרירותי. השתמשו בו כשצריך מספר יציב ושונה לכל שורה.
• RANK() נותן לשורות בתיקו את אותו דירוג, ואז מדלג על המספרים הבאים. אחרי שני שחקנים בתיקו במקום 1 בא דירוג 3.
• DENSE_RANK() גם הוא נותן תיקו, אבל לא מדלג. הדירוג הבא הוא 2.

ל"N המובילים בכל קבוצה", שלבו דירוג עם PARTITION BY וסננו בשאילתה חיצונית: WHERE לא יכולה להתייחס ל-window functions ישירות:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

שני בעלי השכר הגבוה ביותר בכל אזור.

LAG ו-LEAD: להסתכל על שורות שכנות

LAG(col) מחזירה את הערך של col מהשורה הקודמת בחלון. LEAD(col) מסתכלת קדימה. שתיהן מושלמות לשאלות על שינוי לאורך זמן.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

ה-yesterday של השורה הראשונה הוא NULL: אין שום דבר לפניה. אפשר לספק ערך ברירת מחדל: LAG(celsius, 1, celsius) OVER (ORDER BY day) ישתמש בערך של היום כשאין שורה קודמת.

LEAD היא תמונת הראי. שלבו את שתיהן עם PARTITION BY כדי לקבל רצפים לכל קבוצה, למשל השוואה של המכירות החודש לחודש הקודם בתוך כל אזור.

סכומים מצטברים עם מסגרות חלון

הוסיפו ORDER BY בתוך OVER, ופונקציות צבירה כמו SUM, AVG, COUNT מתחילות לחשב באופן מצטבר:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

שני דברים שכדאי לשים לב אליהם:

• SUM(amount) OVER (ORDER BY day) הוא סכום מצטבר. מסגרת ברירת המחדל כשכותבים ORDER BY בלי מסגרת מפורשת היא "מתחילת החלון ועד השורה הנוכחית".
• העמודה השנייה משתמשת במסגרת מפורשת: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. זה חלון נע של 3 שורות: ממוצע נע.

המודל המנטלי למסגרות: כל window function מחושבת על פני מסגרת של שורות, שמוגדרת ביחס לשורה הנוכחית. מסגרות נפוצות:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: סכום מצטבר (ברירת המחדל המובלעת).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW: חלון נגרר.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: כל המחיצה.

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

FIRST_VALUE, LAST_VALUE, NTILE

עוד כמה window functions שכדאי להכיר:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE ו-LAST_VALUE מחזירות את הערך הראשון או האחרון בתוך המסגרת. עם LAST_VALUE, שימו לב למסגרת: מסגרת ברירת המחדל מסתיימת ב-CURRENT ROW, ולכן בדרך כלל תרצו ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING כדי לקבל את הערך האחרון האמיתי של המחיצה.
• NTILE(n) מחלקת את השורות ל-n דליים שווים בערך: שימושי לרבעונים, לאחוזונים ולחלוקות בסגנון A/B.

מתן שם לחלון עם WINDOW

כשכמה עמודות חולקות את אותה פסוקית OVER (...), החזרה נהיית מייגעת. SQLite מאפשר לתת שם לחלון פעם אחת ולהשתמש בו שוב:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

אותה שאילתה, פחות רעש. פסוקית WINDOW באה אחרי WHERE/GROUP BY/HAVING ולפני ORDER BY.

Window Functions מול GROUP BY

שתיהן כוללות צבירה, אבל הן עונות על שאלות שונות:

• GROUP BY מצמצם. שורה אחת לכל קבוצה. השתמשו בו כשרוצים רק את הסיכום.
• Window functions משמרות. כל שורת קלט שורדת, עם עמודות מחושבות נוספות לצידה.

אם אי פעם תמצאו את עצמכם עושים GROUP BY ואז מצרפים את הצבירות בחזרה לטבלה המקורית, זה סימן חזק ש-window function הייתה עושה את העבודה בשאילתה אחת.

כמה מלכודות

• WHERE לא יכולה להתייחס ל-window functions. הסינון קורה לפני שהחלונות מחושבים. עטפו את השאילתה בתת שאילתה או ב-CTE וסננו ברמה החיצונית.
• מסגרות מובלעות נושכות. SUM(x) OVER (ORDER BY y) הוא סכום מצטבר כי מסגרת ברירת המחדל היא RANGE UNBOUNDED PRECEDING. אם רציתם את הסכום של כל המחיצה, כתבו OVER (PARTITION BY ...) בלי ORDER BY, או ציינו את המסגרת במפורש.
• LAST_VALUE מפתיעה את כולם בפעם הראשונה. כשמסגרת ברירת המחדל מסתיימת בשורה הנוכחית, היא מחזירה את הערך הנוכחי, ולא את האחרון במחיצה. דרסו את המסגרת.
• Window functions דורשות SQLite 3.25 ומעלה (שוחרר ב-2018). בכל התקנה מודרנית באופן סביר הן קיימות, אבל חלק מהסביבות המשובצות מפגרות מאחור.

הבא בתור: עמודות מחושבות

Window functions הן חישוב בזמן השאילתה. הדף הבא מכסה חישוב בזמן האחסון: עמודות מחושבות (generated columns), שבהן הערך של העמודה מוגדר על ידי ביטוי ומתעדכן אוטומטית כשהנתונים שמתחתיו משתנים.