Window Functions — ROW_NUMBER, RANK, LAG, LEAD

מה זה?#

“תן לי את הדירוג של כל ציון בתוך הציונים של אותו תלמיד” — GroupBy+OrderBy מה-LINQ שכבר מוכר לא עונים ישירות על זה, כי צריך “לספור” תוך כדי שממשיכים לראות כל שורה בנפרד (לא מקובצת). Window Functions הן תוספת SQL בדיוק לתרחישים כאלה — מחשבות ערך “יחסית לחלון” של שורות קשורות, בלי לאבד את פירוט השורה הבודדת כמו ש-GROUP BY רגיל היה עושה.

השיעור הזה שונה מהקודמים בנקודה אחת: עד עכשיו כל שאילתה עברה דרך LINQ (Where/Include/OrderBy). Window Functions אי-אפשר לבטא ב-LINQ רגיל — אז נלמד גם איך מריצים SQL גולמי דרך ה-DbContext שכבר בנינו, ומחברים אותו ל-endpoint אמיתי.

מילות מפתח שחשוב לזכור#

• Window Function — פונקציה שמחשבת ערך “יחסית לחלון” (Window) של שורות קשורות, תוך שמירה על כל שורה בנפרד בתוצאה

• OVER (PARTITION BY ... ORDER BY ...) — מגדיר את ה"חלון": PARTITION BY מחלק לקבוצות (כמו GROUP BY, אך בלי לאחד שורות), ORDER BY קובע סדר בתוך כל קבוצה

• ROW_NUMBER() — מספר סידורי רץ בתוך כל חלון, תמיד ייחודי (1, 2, 3…)

• RANK() — דירוג בתוך החלון; ערכים שווים מקבלים דירוג זהה, והבא “מדלג” (1, 1, 3)

• LAG/LEAD — גישה לערך בשורה הקודמת/הבאה בתוך אותו חלון, בלי JOIN עצמי

• FromSqlRaw — מתודה של EF Core שמריצה מחרוזת SQL גולמית (במקום לתרגם LINQ), למקרים ש-LINQ לא יודע לבטא

בונים הבנה שלב אחר שלב#

שלב 1: הבעיה — GROUP BY מאבד פירוט#

נניח שרוצים לדעת את הדירוג של כל שורה בתוך קבוצה שלה — לדוגמה, כל תלמיד, בתוך הכיתה שלו, לפי ציון. GROUP BY מאחד שורות לכדי סיכום אחד לקבוצה (כמו “ממוצע ציון לכל כיתה”) — הוא לא יכול להחזיר גם את השורה הבודדת (שם התלמיד) וגם מידע “יחסי לקבוצה” (הדירוג שלו) יחד, באותה שורה. בשביל זה בדיוק קיימות Window Functions.

שלב 2: הרעיון — ROW_NUMBER + PARTITION BY#

SELECT
    Name,
    ClassName,
    Score,
    ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankInClass
FROM Students;

-- Dana  | A | 95 | 1
-- Rotem | A | 88 | 2
-- Avi   | B | 92 | 1   -- starts from 1 again, because this is a different class (new PARTITION)

PARTITION BY ClassName מחלק את כל השורות לקבוצות לפי כיתה — בלי לאחד אותן (בניגוד ל-GROUP BY). ORDER BY Score DESC קובע את סדר הדירוג בתוך כל קבוצה. ROW_NUMBER() מספר כל שורה בתוך הקבוצה שלה, מחדש בכל קבוצה חדשה. שימו לב: כל שורה בתוצאה היא עדיין תלמיד בודד, עם כל הפרטים שלו — בדיוק מה ש-GROUP BY לא יכול לתת.

שלב 3: מריצים את זה על הנתונים האמיתיים שלנו#

הדוגמה למעלה היא כללית. נריץ רעיון דומה על Grades — דירוג כל ציון בתוך הציונים של אותו תלמיד (PARTITION BY StudentId), ישירות מול ה-DB שבנינו בשיעורים הקודמים.

קודם מגדירים מחלקה קטנה לצורת התוצאה — Window Functions לא מתאימות לאף DbSet<T> קיים, כי הן לא טבלה אמיתית:

public class GradeRanking
{
    public int StudentId { get; set; }
    public int Score { get; set; }
    public int RankForStudent { get; set; }
}

ורושמים אותה ב-SchoolDbContext כKeyless Entity — טיפוס תוצאה בלי Primary Key, כי הוא לא מגיע מטבלה אמיתית:

public class SchoolDbContext : DbContext
{
    public SchoolDbContext(DbContextOptions<SchoolDbContext> options) : base(options) { }

    public DbSet<Student> Students { get; set; }
    public DbSet<Grade> Grades { get; set; }
    public DbSet<GradeRanking> GradeRankings { get; set; }   // not a real table — just a shape for raw SQL results

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<GradeRanking>().HasNoKey();   // tells EF Core: don't expect a Primary Key here
    }
}

.HasNoKey() אומר ל-EF Core “אל תצפה ל-Primary Key במחלקה הזו, ואל תנסה למפות אותה לטבלה קיימת” — היא קיימת רק בשביל לתת צורה לתוצאה של שאילתה גולמית, לא בשביל CRUD רגיל.

עכשיו מריצים את ה-SQL הגולמי דרך FromSqlRaw:

List<GradeRanking> ranked = _context.GradeRankings.FromSqlRaw(@"
    SELECT StudentId, Score,
           ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY Score DESC) AS RankForStudent
    FROM Grades
").ToList();

FromSqlRaw מקבל מחרוזת SQL כמו שהיא, שולח אותה ישירות ל-DB, וממפה כל שורת תוצאה לאובייקט GradeRanking — לפי שמות העמודות שחייבים להתאים בדיוק לשמות ה-Properties. זה בדיוק אותו DbContext, אותו חיבור, אותו Docker — רק דרך שונה לשלוח שאילתה, לשימוש במקרים (כמו Window Functions) ש-LINQ לא יודע לבטא.

חושפים את זה דרך endpoint, ובודקים כמו כל שיעור קודם:

[HttpGet("grade-rankings")]
public IActionResult GetGradeRankings()
{
    var ranked = _context.GradeRankings.FromSqlRaw(@"
        SELECT StudentId, Score,
               ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY Score DESC) AS RankForStudent
        FROM Grades
    ").ToList();
    return Ok(ranked);
}

curl http://localhost:5123/api/students/grade-rankings

התוצאה: רשימת ציונים, כל אחד עם הדירוג שלו בתוך הציונים של אותו תלמיד בלבד — בדיוק מה ש-GROUP BY לא יכול להחזיר.

שלב 4: RANK — כשיש תיקו#

SELECT
    Name,
    Score,
    RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Students;

-- Dana  | 95 | 1
-- Rotem | 90 | 2
-- Avi   | 90 | 2   -- same score, same rank
-- Gal   | 85 | 4   -- skips 3!

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

שלב 5: LAG/LEAD — השוואה לשורה הקודמת#

SELECT
    Month,
    Revenue,
    LAG(Revenue) OVER (ORDER BY Month) AS PreviousMonthRevenue,
    Revenue - LAG(Revenue) OVER (ORDER BY Month) AS Growth
FROM MonthlyRevenue;
-- for each month: the previous month's revenue, and the difference — without writing a JOIN of the table with itself

בלי Window Functions, “השוואה לחודש הקודם” הייתה דורשת JOIN של הטבלה עם עצמה (self-join) עם תנאי מורכב על תאריכים — שביר וקשה לקריאה. LAG(Revenue) OVER (ORDER BY Month) עושה את זה בשורה אחת. גם זו מורצת באותו דפוס (FromSqlRaw) כשצריך לחשוף אותה ב-API.

הסבר עיקרי#

PARTITION BY מול GROUP BY — ההבדל הקריטי: GROUP BY מאחד שורות לקבוצה אחת (מאבד את הפירוט של כל שורה בנפרד — נשאר רק סיכום); PARTITION BY שומר על כל שורה בנפרד בתוצאה, רק “מודע” לקבוצה שהיא שייכת אליה בזמן החישוב. זו הסיבה שאפשר לראות גם את הפרט הבודד וגם את הדירוג בתוך הקבוצה שלו, באותה שורה בדיוק — משהו ש-GROUP BY פשוט לא יכול לבטא.

ROW_NUMBER מול RANK — ההבדל מתגלה רק כשיש תיקו (ערכים שווים): ROW_NUMBER תמיד נותן מספרים ייחודיים (גם לתיקו — שרירותי מי מקבל 2 ומי 3); RANK נותן דירוג זהה לתיקו, ו"מדלג" על המספרים הבאים (1, 1, 3 — לא 1, 1, 2). הבחירה תלויה בדרישה העסקית: רוצים “מיקום ברשימה” (ROW_NUMBER) או “דירוג אמיתי” (RANK)?

למה FromSqlRaw ולא LINQ — LINQ מתורגם ל-SQL באופן אוטומטי, אבל רק לתבניות שה-“מתרגם” של EF Core מכיר. Window Functions הן תחביר SQL ייחודי שאין לו מקבילה ישירה ב-LINQ הרגיל (נכון לגרסאות הנפוצות) — אז EF Core נותן “פתח מילוט” מכוון: אפשר לכתוב SQL בעצמכם, ועדיין לקבל את זה בחזרה כאובייקטים מוכרים, לא כטבלה גולמית. .HasNoKey() הוא התנאי הטכני שמאפשר את זה לתוצאה שלא מגיעה מטבלה אמיתית.

יתרונות#

Window Functions מבטאות שאילתות אנליטיות (דירוג, השוואה לשורה סמוכה) בשורה אחת קריאה, בלי JOIN עצמי מסובך; שומרות על כל שורה בנפרד, בניגוד ל-GROUP BY שמאחד; FromSqlRaw נותן גישה לכל יכולת SQL, גם כזו ש-LINQ לא מתרגם; שימושיות מאוד לדוחות עסקיים (דירוגים, מגמות לאורך זמן).

חסרונות#

תחביר OVER (PARTITION BY ... ORDER BY ...) דורש היכרות — לא אינטואיטיבי בהתחלה; Window Functions על טבלאות ענקיות ללא Index מתאים עלולות להיות איטיות משמעותית; FromSqlRaw מאבד חלק מהבטיחות של LINQ (שגיאת הקלדה בשם עמודה נתפסת רק בזמן ריצה, לא בקומפילציה).

נקודות חשובות#

• PARTITION BY מחלק לקבוצות בלי לאחד שורות (בניגוד ל-GROUP BY)

• ROW_NUMBER = מספרים ייחודיים תמיד; RANK = תיקו מקבל דירוג זהה, מדלג על הבא

• LAG/LEAD = גישה לשורה קודמת/הבאה בתוך החלון, בלי self-join

• FromSqlRaw + .HasNoKey() = הדרך להריץ SQL ש-LINQ לא מתרגם, ועדיין לקבל אובייקטים מוכרים בחזרה

טעויות נפוצות#

• לבלבל בין GROUP BY (מאחד שורות) ל-PARTITION BY (שומר על כל שורה) — תוצאות שונות מהותית

• לבחור ROW_NUMBER כשבפועל רוצים דירוג אמיתי עם תיקו זהה (RANK) — או להפך

• לשכוח ORDER BY בתוך ה-OVER(...) — בלעדיו ROW_NUMBER/RANK/LAG/LEAD לא יודעים לפי מה לסדר

• לשכוח .HasNoKey() על מחלקת תוצאה של FromSqlRaw — EF Core מצפה ל-Primary Key וזורק שגיאת הגדרה

סיכום#

Window Functions מחשבות ערך “יחסית לחלון” של שורות קשורות (PARTITION BY), תוך שמירה על כל שורה בנפרד — בשונה מ-GROUP BY שמאחד. ROW_NUMBER נותן מספור ייחודי; RANK נותן דירוג עם תיקו זהה ודילוג; LAG/LEAD נותנים גישה לשורה קודמת/הבאה בלי self-join. מכיוון ש-LINQ לא מתרגם אותן, הרצנו אותן דרך FromSqlRaw על ה-DbContext שכבר בנינו — מחלקת תוצאה פשוטה, .HasNoKey() כדי לסמן שהיא לא טבלה אמיתית, וחיבור ל-endpoint אמיתי כמו כל שיעור אחר ביחידה.

דוקומנטציה רשמית#

Microsoft Learn — Window functions (Transact-SQL)

Microsoft Learn — Raw SQL queries (FromSqlRaw)

תרגילים#

תרגיל 1 — ROW_NUMBER לפי PARTITION#

המשימה: כתבו שאילתה שמדרגת עובדים (ROW_NUMBER) לפי משכורת, בתוך כל מחלקה (PARTITION BY Department) — דרך FromSqlRaw, כמו בשלב 3.

בדיקה: כל מחלקה מתחילה דירוג מ-1 בנפרד, לא ממשיכה ברצף גלובלי.

תרגיל 2 — RANK מול ROW_NUMBER#

המשימה: על אותה טבלה, הריצו גם RANK() וגם ROW_NUMBER() על נתונים עם לפחות שני ערכים זהים, והשוו את התוצאות זו לצד זו.

בדיקה: RANK נותן אותו דירוג לערכים הזהים ומדלג על המספר הבא; ROW_NUMBER נותן מספרים רצופים וייחודיים גם לערכים זהים.

פרויקט מסכם#

המשימה: בנו endpoint GET api/students/revenue-trend עם Window Functions, דרך FromSqlRaw.

דרישות:

1. שאילתה שמציגה לכל חודש: הכנסה, הכנסת החודש הקודם (LAG), והפרש/צמיחה
2. הוסיפו ROW_NUMBER() OVER (ORDER BY Revenue DESC) כדי לדרג את החודשים החזקים ביותר
3. סננו (עם WHERE על תוצאת CTE/subquery, לא ישירות על Window Function) רק חודשים עם צמיחה חיובית
4. endpoint שמחזיר את התוצאה, נבדק עם curl

בדיקה: הדוח מציג לכל חודש את ההשוואה לחודש הקודם נכון; חודשי הצמיחה השלילית לא מופיעים בתוצאה הסופית המסוננת.

מה בפרק הבא#

בפרק הבא — פרויקט מסכם EF Core: פרויקט שמאחד את כל היחידה — עיצוב DB, EF Core Code First, SqlRepository, Queries עם Include, ו-Window Functions בדוח אנליטי אחד.