# 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

```sql
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>` קיים, כי הן לא טבלה אמיתית:

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

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

```csharp
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`:

```csharp
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, ובודקים כמו כל שיעור קודם:

```csharp
[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);
}
```

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

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

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

```sql
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 — השוואה לשורה הקודמת

```sql
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)](https://learn.microsoft.com/sql/t-sql/queries/select-over-clause-transact-sql)

[Microsoft Learn — Raw SQL queries (FromSqlRaw)](https://learn.microsoft.com/ef/core/querying/sql-queries)

---

## תרגילים

### תרגיל 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 בדוח אנליטי אחד.