Software Engineer Encyclopedia
0%
Domain 04 · Database Engineering & Data Management · Pillar

SQL Window Functions بالتفصيل: OVER وPARTITION BY والـ frames

SQL Window Functions in Depth

Window functions بتحسب على «مجموعة صفوف مرتبطة» من غير ما تضيع الصفوف زي GROUP BY: ROW_NUMBER وRANK وDENSE_RANK، LAG/LEAD، running totals وmoving averages بالـ frames، top-N per group، gaps & islands، وإزاي تستخدمها من EF Core — مع تمارين إنترفيو محلولة على SQL Server وPostgreSQL.

  • Intermediate
  • ~11 min read
  • 16 sections
  • 17 code examples
  • 2 diagrams
  • 6 questions
  • Priority L2 · Essential
  • SQL Server 2022 / Azure SQL · PostgreSQL 17 · EF Core 10 (raw SQL / keyless types)

بعد الدرس ده هتقدر

  • تفرق بين GROUP BY والـ window functions وتعرف إمتى تستخدم كل واحد
  • تكتب OVER (PARTITION BY … ORDER BY … frame) وتفهم كل جزء بيعمل إيه
  • تستخدم ROW_NUMBER وRANK وDENSE_RANK وNTILE وتعرف الفرق في حالات التعادل
  • تستخدم LAG/LEAD وFIRST_VALUE/LAST_VALUE وتتفادى فخ الـ default frame
  • تحسب running totals وmoving averages ونسب من الإجمالي
  • تحل top-N per group وdeduplication وgaps & islands
  • تقرأ الأداء: indexes تدعم PARTITION BY + ORDER BY، وتستدعيها من EF Core

المتطلبات قبل ما تبدأ

01

ليه window functions؟ المشكلة اللي GROUP BY مش بيحلها

The problem window functions solve
Basics

عايز تعرض كل طلب ومعاه إجمالي طلبات العميل، أو ترتيب الطلب جوه شهر العميل، أو الفرق عن الطلب اللي قبله. GROUP BY بيضغط الصفوف لصف واحد لكل مجموعة فبتضيّع التفاصيل، والـ self-join أو الـ subquery لكل صف بطيء وصعب القراءة. الـ window function بتحسب على «نافذة» من الصفوف المرتبطة بالصف الحالي، وبتسيب كل الصفوف زي ما هي.

GROUP BY CustomerIdC1 | total 350C2 | total 90rows gone: you lost each orderSUM(Total) OVER (PARTITION BY CustomerId)O1 | C1 | 100 | 350O2 | C1 | 250 | 350O3 | C2 | 90 | 90every order kept + the customer's total on each rowLogical order: FROM → WHERE → GROUP BY → HAVING → WINDOW functions → SELECT → ORDER BY⇒ you cannot filter on a window result in WHERE; wrap it in a CTE/subquery.
GROUP BY collapses rows; a window function keeps every row and adds a computed column
02

تشريح OVER(): PARTITION BY و ORDER BY والـ frame

Anatomy of the OVER clause
Deep dive
SQLthe three parts
SELECT  OrderId, CustomerId, OrderDate, Total,
        SUM(Total) OVER (
            PARTITION BY CustomerId                  -- 1. split rows into groups (optional)
            ORDER BY OrderDate, OrderId              -- 2. order inside each group (needed for ranking/running)
            ROWS BETWEEN UNBOUNDED PRECEDING         -- 3. frame: which rows around the current one
                 AND CURRENT ROW
        ) AS RunningTotal
FROM    dbo.Orders;
الجزءبيعمل إيهلو اتشال
PARTITION BYبيقسم الصفوف؛ الحساب بيتعاد من الأول في كل partitionالجدول كله partition واحدة
ORDER BYترتيب جوه الـ partition (مش ترتيب النتيجة النهائية!)ranking مش مسموح، والـ aggregates بتبقى على الـ partition كلها
Frame (ROWS/RANGE/GROUPS)أنهي صفوف تتحسب لكل صفمع ORDER BY: الافتراضي RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
فخ الـ default frame

الافتراضي مع ORDER BY هو RANGE ... CURRENT ROW، والـ RANGE بيعامل الصفوف المتساوية في الـ ORDER BY كـ «peers» ويدخلهم كلهم. لو عميل عنده طلبين في نفس التاريخ، الـ running total هيقفز بالاتنين مع بعض في الصفين. عشان كده: (1) خلي الـ ORDER BY unique (ضيف OrderId)، و(2) اكتب ROWS صراحة. وفي SQL Server، ROWS غالبًا أسرع لأن RANGE بيستخدم worktable على القرص.

Frameالمعنىالاستخدام
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWمن أول الـ partition للصف الحاليrunning total
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWآخر 7 صفوفmoving average 7 أيام (لو صف لكل يوم)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGالـ partition كلهاLAST_VALUE صح، نسبة من الإجمالي
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROWبالقيمة مش بالعدد (PostgreSQL)moving window زمني لما فيه أيام ناقصة
03

Ranking: ROW_NUMBER و RANK و DENSE_RANK و NTILE

Ranking functions
Practical
SQLsame data, four functions
SELECT  Name, Score,
        ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum,     -- 1,2,3,4,5  (ties broken arbitrarily!)
        RANK()       OVER (ORDER BY Score DESC) AS Rnk,        -- 1,2,2,4,5  (gaps after ties)
        DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRnk,   -- 1,2,2,3,4  (no gaps)
        NTILE(4)     OVER (ORDER BY Score DESC) AS Quartile    -- split into 4 roughly equal buckets
FROM    (VALUES ('Mona',98),('Ali',95),('Omar',95),('Sara',90),('Hany',80)) s(Name, Score);
NameScoreROW_NUMBERRANKDENSE_RANK
Mona98111
Ali95222
Omar95322
Sara90443
Hany80554

إمتى أنهي واحد؟ ROW_NUMBER لما محتاج صف واحد بالظبط (dedup، top-1، pagination) — لكن اعمل ORDER BY حتمي (tie-breaker) وإلا النتيجة بتتغير بين التشغيلات. RANK للمسابقات («تعادل في المركز التاني والتالت يبقى رابع»). DENSE_RANK لـ «تاني أعلى مرتب» من غير ما التعادل يأكل مراكز.

04

LAG و LEAD و FIRST_VALUE و LAST_VALUE

Offset and value functions
Practical
SQLmonth-over-month change per tenant
WITH monthly AS (
    SELECT TenantId,
           DATEFROMPARTS(YEAR(CreatedAt), MONTH(CreatedAt), 1) AS Month,
           SUM(AmountCents) / 100.0 AS Revenue
    FROM   dbo.Invoices
    GROUP BY TenantId, DATEFROMPARTS(YEAR(CreatedAt), MONTH(CreatedAt), 1)
)
SELECT TenantId, Month, Revenue,
       LAG(Revenue) OVER (PARTITION BY TenantId ORDER BY Month)                 AS PrevMonth,
       Revenue - LAG(Revenue, 1, 0) OVER (PARTITION BY TenantId ORDER BY Month) AS Delta,      -- default 0 for the first month
       100.0 * (Revenue - LAG(Revenue) OVER (PARTITION BY TenantId ORDER BY Month))
             / NULLIF(LAG(Revenue) OVER (PARTITION BY TenantId ORDER BY Month), 0) AS GrowthPct, -- NULLIF avoids divide-by-zero
       FIRST_VALUE(Revenue) OVER (PARTITION BY TenantId ORDER BY Month
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirstMonthRevenue
FROM monthly
ORDER BY TenantId, Month;

فخ LAST_VALUE: LAST_VALUE(x) OVER (ORDER BY d) من غير frame بيرجع قيمة الصف الحالي، لأن الـ default frame بيقف عند CURRENT ROW. لازم ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING، أو استخدم FIRST_VALUE مع ORDER BY معكوس.

شهور ناقصة: LAG بيجيب «الصف اللي قبله» مش «الشهر اللي قبله». لو tenant ما عندوش فواتير في مارس، LAG في أبريل هيرجع فبراير. الحل: calendar table (أو generate_series في PostgreSQL) و LEFT JOIN قبل الـ window.

05

Running totals و moving averages ونسب من الإجمالي

Window aggregates
Practical
SQLdaily active usage: running total, 7-day moving average, share of the month
SELECT  Day, Tokens,
        SUM(Tokens) OVER (ORDER BY Day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS RunningTokens,
        AVG(Tokens * 1.0) OVER (ORDER BY Day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)   AS MovingAvg7,
        100.0 * Tokens / SUM(Tokens) OVER (PARTITION BY DATETRUNC(month, Day))           AS PctOfMonth
FROM    dbo.DailyUsage
WHERE   TenantId = @tenantId
ORDER BY Day;
-- DATETRUNC exists in SQL Server 2022+; use DATEFROMPARTS on older versions, date_trunc in PostgreSQL.

ده بالظبط نوع الـ query اللي بيغذي dashboard «استهلاك الـ AI tokens» في Billing: سطر واحد لكل يوم، والـ running total بيتقارن بحد الخطة.

06

أنماط كلاسيكية: top-N per group و dedup و gaps & islands

Classic patterns
Deep dive

Top-N per group

SQLlatest 3 orders per customer
WITH ranked AS (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC, OrderId DESC) AS rn
    FROM dbo.Orders o
)
SELECT * FROM ranked WHERE rn <= 3;          -- filter in the outer query: windows run after WHERE
-- Supporting index: (CustomerId, OrderDate DESC, OrderId DESC) INCLUDE (Total) -> no sort in the plan

Deduplication (احتفظ بأحدث نسخة)

SQLdelete duplicates, keep the newest per email
WITH d AS (
    SELECT Id, ROW_NUMBER() OVER (PARTITION BY LOWER(Email) ORDER BY UpdatedAt DESC, Id DESC) AS rn
    FROM dbo.Contacts
)
DELETE FROM d WHERE rn > 1;                 -- SQL Server allows DELETE through a CTE; in PostgreSQL use DELETE ... USING

Gaps & islands: فترات متصلة

«أطول سلسلة أيام متتالية استخدم فيها الـ tenant المنتج». الحيلة: لو الأيام متصلة، الفرق بين التاريخ والـ ROW_NUMBER ثابت جوه كل «جزيرة».

SQLconsecutive active days per tenant
WITH days AS (
    SELECT DISTINCT TenantId, CAST(UsedAt AS date) AS Day FROM dbo.UsageEvents
),
grp AS (
    SELECT TenantId, Day,
           DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY TenantId ORDER BY Day), Day) AS IslandKey
    FROM days
)
SELECT TenantId, MIN(Day) AS StartDay, MAX(Day) AS EndDay, COUNT(*) AS Streak
FROM grp
GROUP BY TenantId, IslandKey
ORDER BY Streak DESC;
DayROW_NUMBERDay − rnIsland
Mar 11Feb 28A
Mar 22Feb 28A
Mar 33Feb 28A
Mar 74Mar 3B
Mar 85Mar 3B
07

Cohort retention: التحليل اللي كل SaaS بيطلبه

Cohort retention with window functions
Deep dive

«من العملاء اللي اشتركوا في يناير، كام في المية لسه بيستخدموا المنتج بعد شهر، اتنين، تلاتة؟» ده سؤال الـ product الأشهر، والـ window functions بتحله في query واحدة.

cohortM0M1M2M32026-01100%62%48%41%2026-02100%68%55%2026-03100%71%Read diagonally: did theAI feature launched in Febimprove M1 retention?(illustrative numbers)
Cohort table: rows are signup months, columns are months since signup, cells are % still active
SQLcohort_retention.sql (PostgreSQL)
WITH activity AS (                                   -- one row per tenant per active month
    SELECT DISTINCT tenant_id, date_trunc('month', used_at)::date AS month
    FROM usage_events
),
cohort AS (
    SELECT tenant_id, month,
           MIN(month) OVER (PARTITION BY tenant_id) AS cohort_month
    FROM activity
),
counts AS (
    SELECT cohort_month,
           (EXTRACT(YEAR FROM age(month, cohort_month)) * 12 + EXTRACT(MONTH FROM age(month, cohort_month)))::int AS months_since,
           COUNT(*) AS active
    FROM cohort
    GROUP BY 1, 2
)
SELECT cohort_month, months_since, active,
       ROUND(100.0 * active / FIRST_VALUE(active) OVER (PARTITION BY cohort_month ORDER BY months_since), 1) AS retention_pct
FROM counts
ORDER BY cohort_month, months_since;

تلات windows بتشتغل مع بعض: MIN() OVER بيحدد شهر الانضمام لكل tenant من غير subquery، الـ GROUP BY بيعد، و FIRST_VALUE بيجيب حجم الـ cohort في الشهر 0 عشان النسبة. الربط بالمنتج في Product Engineering.

08

Percentiles والـ median: PERCENTILE_CONT و PERCENTILE_DISC

Percentiles as window or ordered-set aggregates
Practical
SQLp50/p95 latency per endpoint
-- SQL Server: analytic form (repeats the value on every row of the partition)
SELECT DISTINCT Endpoint,
       PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY DurationMs) OVER (PARTITION BY Endpoint) AS P50,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY DurationMs) OVER (PARTITION BY Endpoint) AS P95
FROM dbo.RequestLog
WHERE At >= DATEADD(hour, -1, SYSUTCDATETIME());

-- PostgreSQL: ordered-set aggregate (one row per group)
SELECT endpoint,
       percentile_cont(0.5)  WITHIN GROUP (ORDER BY duration_ms) AS p50,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95
FROM request_log
WHERE at >= now() - interval '1 hour'
GROUP BY endpoint;

PERCENTILE_CONT بيعمل interpolation بين قيمتين (ممكن يطلع رقم مش موجود في البيانات)، و PERCENTILE_DISC بيرجع قيمة فعلية من البيانات. للـ latency الـ CONT مقبول. لكن على ملايين الصفوف ده مكلف (sort كامل) — في الإنتاج الـ percentiles بتتحسب من histograms في نظام الـ metrics (Observability)، والـ SQL للتحليلات المؤقتة.

09

PostgreSQL: FILTER و WINDOW clause و RANGE بالوقت

PostgreSQL extras
Practical
SQLcleaner window SQL in PostgreSQL
SELECT day, tenant_id, tokens,
       sum(tokens) OVER w_running                                  AS running_tokens,
       avg(tokens) OVER w_7d                                       AS avg_7d,
       count(*) FILTER (WHERE tokens > 100000) OVER w_running      AS heavy_days_so_far
FROM daily_usage
WINDOW w_running AS (PARTITION BY tenant_id ORDER BY day ROWS UNBOUNDED PRECEDING),
       w_7d      AS (PARTITION BY tenant_id ORDER BY day RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW);
  • WINDOW clause بيسمّي تعريف النافذة مرة واحدة (SQL Server 2022 بيدعمه برضه).
  • FILTER بيعمل aggregate شرطي من غير CASE.
  • RANGE مع INTERVAL بيحسب «آخر 7 أيام» بالقيمة الزمنية، فالأيام الناقصة ما بتلخبطش النافذة — بعكس ROWS 6 PRECEDING اللي بيعد صفوف.
10

الأداء: الـ index اللي بيلغي الـ sort

Performance and indexing
Deep dive
  • الـ window بيحتاج الصفوف مرتبة بـ (PARTITION BY, ORDER BY). index بنفس الترتيب بالظبط بيخلي الـ plan يقرأ مرتب ويتجنب Sort operator (اللي ممكن يعمل spill لـ tempdb على بيانات كبيرة).
  • كذا window بنفس الـ OVER في نفس الـ query بيتحسبوا في مرور واحد؛ OVER مختلفة = sorts إضافية. وحّد الـ OVER أو استخدم WINDOW clause (PostgreSQL، و SQL Server 2022 كمان) للقراءة.
  • SQL Server: batch mode على rowstore (2019+ بشروط) أو columnstore بيسرّع الـ window aggregates جدًا في التقارير.
  • فلتر بدري: WHERE على الـ partition (مثلًا tenant واحد وشهر واحد) قبل الـ window، مش بعده.
SQLindex that serves PARTITION BY TenantId ORDER BY Day
CREATE INDEX IX_DailyUsage_Tenant_Day ON dbo.DailyUsage (TenantId, Day) INCLUDE (Tokens);
11

Window functions من EF Core

Using window functions from .NET
Practical

LINQ ما بيترجمش لـ window functions مباشرة في أغلب الحالات (بعض الأنماط زي GroupBy + Take بتتترجم لـ ROW_NUMBER داخليًا في EF Core الحديث، بس ما تعتمدش على ده للتقارير). الطريقة النضيفة: SQL صريح بيرجع type مخصص.

C#keyless result type + SqlQuery
public sealed record TenantMonthlyRevenue(Guid TenantId, DateTime Month, decimal Revenue, decimal? PrevMonth, decimal? GrowthPct);

var rows = await db.Database.SqlQuery<TenantMonthlyRevenue>($"""
    WITH monthly AS (
        SELECT TenantId, DATEFROMPARTS(YEAR(CreatedAt), MONTH(CreatedAt), 1) AS Month, SUM(AmountCents) / 100.0 AS Revenue
        FROM dbo.Invoices WHERE CreatedAt >= {from} GROUP BY TenantId, DATEFROMPARTS(YEAR(CreatedAt), MONTH(CreatedAt), 1)
    )
    SELECT TenantId, Month, Revenue,
           LAG(Revenue) OVER (PARTITION BY TenantId ORDER BY Month) AS PrevMonth,
           100.0 * (Revenue - LAG(Revenue) OVER (PARTITION BY TenantId ORDER BY Month))
                 / NULLIF(LAG(Revenue) OVER (PARTITION BY TenantId ORDER BY Month), 0) AS GrowthPct
    FROM monthly
    """).ToListAsync(ct);                     // interpolated values become parameters (no SQL injection)

القيم المدمجة في الـ interpolated string بتتحول parameters تلقائيًا. وخلي الـ queries دي في query objects أو read-side (CQRS) مع integration tests على database حقيقية (Testcontainers) — الـ SQLite in-memory ما بيدعمش كل الـ functions بنفس السلوك.

12

أخطاء شائعة

Common mistakes
Basics
  • فلترة نتيجة window في WHERE (ممنوع) بدل CTE خارجي.
  • ORDER BY مش unique في ROW_NUMBER ← نتايج مختلفة كل مرة (pagination بتكرر/تفوّت صفوف).
  • الاعتماد على الـ default RANGE frame في running totals مع تكرار في قيم الـ ORDER BY.
  • LAST_VALUE من غير frame صريح.
  • LAG على بيانات فيها فترات ناقصة من غير calendar table.
  • الخلط بين ORDER BY جوه OVER و ORDER BY بتاع النتيجة — الأول مش بيضمن ترتيب الإخراج.
13

اختبر فهمك

Knowledge check
Basics

Scores: 100, 90, 90, 80. قيم DENSE_RANK() OVER (ORDER BY Score DESC)؟

running total بيقفز بقيمتين مرة واحدة عند طلبين بنفس التاريخ. السبب؟

ليه ما ينفعش WHERE ROW_NUMBER() OVER (...) = 1؟

14

تمارين إنترفيو محلولة

Interview exercises with solutions
Practical
1: تاني أعلى مرتب في كل قسم (مع التعادل)

التفكير: «تاني أعلى» مع تعادل = DENSE_RANK (لو اتنين أخدوا الأعلى، التاني هو القيمة اللي بعدهم مش التالت في الترتيب).

SQLsolution
WITH r AS (SELECT DeptId, Name, Salary, DENSE_RANK() OVER (PARTITION BY DeptId ORDER BY Salary DESC) AS dr FROM Employees)
SELECT DeptId, Name, Salary FROM r WHERE dr = 2;
2: نسبة كل منتج من مبيعات فئته، وترتيبه جوه الفئة
SQLsolution
SELECT Category, Product, Sales,
       100.0 * Sales / SUM(Sales) OVER (PARTITION BY Category) AS PctOfCategory,
       RANK() OVER (PARTITION BY Category ORDER BY Sales DESC)  AS RankInCategory
FROM ProductSales;

مفيش ORDER BY في الـ SUM، فالـ frame = الـ partition كلها (اللي احنا عايزينه هنا).

3: المستخدمين اللي سجلوا دخول 3 أيام متتالية على الأقل

التفكير: gaps & islands: DISTINCT للأيام، ثم Day − ROW_NUMBER كـ island key، ثم GROUP BY مع HAVING COUNT(*) ≥ 3. بديل: LAG(Day, 2) = Day − 2 days يعني فيه 3 أيام متتالية تنتهي عند Day (أبسط لو المطلوب «موجود ولا لأ» بس).

4: Session-ization — قسّم events المستخدم لـ sessions لو الفجوة أكتر من 30 دقيقة
SQLsolution (PostgreSQL)
WITH e AS (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) > interval '30 minutes'
                   OR LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL THEN 1 ELSE 0 END AS is_new
  FROM events
)
SELECT user_id, ts,
       SUM(is_new) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING) AS session_no
FROM e;

نمطين مع بعض: LAG لاكتشاف «بداية جديدة»، و running SUM للـ flags عشان تدّي رقم لكل مجموعة. ده أساس تحليلات الـ product (مدة الـ session، عدد الأسئلة لكل session في chat).

15

تمارين إضافية محلولة

More solved exercises
Practical
5: أول طلب لكل عميل وقيمة آخر طلب، في صف واحد لكل عميل
SQLsolution
SELECT DISTINCT CustomerId,
       FIRST_VALUE(OrderDate) OVER w AS FirstOrder,
       LAST_VALUE(Total)      OVER w AS LastOrderTotal
FROM dbo.Orders
WINDOW w AS (PARTITION BY CustomerId ORDER BY OrderDate, OrderId
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

الـ frame الكامل ضروري لـ LAST_VALUE. بديل أسرع غالبًا: ROW_NUMBER مرتين (asc و desc) وجوين، أو GROUP BY مع MIN وقيمة آخر طلب عبر APPLY.

6: Pagination ثابتة بـ ROW_NUMBER — ليه keyset أحسن؟

الحل: WHERE rn BETWEEN 1001 AND 1020 بيحتاج يحسب ويرتب كل الصفوف قبل 1001 — بيبطأ مع الصفحات العميقة، وبيكرر/يفوّت صفوف لو البيانات اتغيرت بين الطلبات. Keyset: WHERE (CreatedAt, Id) < (@lastCreatedAt, @lastId) ORDER BY CreatedAt DESC, Id DESC مع index مناسب ← O(log n) لأي صفحة. الـ window مناسب للتقارير، والـ keyset للـ APIs (REST: Pagination).

7: «الفرق بين كل قراءة sensor والمتوسط المتحرك لآخر 5 قراءات، وعلّم القراءات اللي بتبعد أكتر من 3 انحرافات معيارية»
SQLsolution (anomaly flag)
WITH s AS (
  SELECT SensorId, At, Value,
         AVG(Value)   OVER (PARTITION BY SensorId ORDER BY At ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS Mu,
         STDEV(Value) OVER (PARTITION BY SensorId ORDER BY At ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS Sigma
  FROM dbo.Readings
)
SELECT *, CASE WHEN Sigma > 0 AND ABS(Value - Mu) > 3 * Sigma THEN 1 ELSE 0 END AS IsAnomaly FROM s;

النافذة بتنتهي عند 1 PRECEDING عشان القراءة الحالية ما تدخلش في المتوسط اللي بتتقارن بيه. ده baseline بسيط لـ anomaly detection قبل أي ML (Time Series).

16

أسئلة إنترفيو

Interview questions
Basics
(Junior) الفرق بين GROUP BY والـ window function؟
GROUP BY بيرجع صف لكل مجموعة؛ الـ window function بترجع قيمة لكل صف محسوبة على مجموعة صفوف مرتبطة بيه، من غير ما تقلل عدد الصفوف.
(Mid) ROW_NUMBER مقابل RANK مقابل DENSE_RANK؟
ROW_NUMBER رقم فريد حتى مع التعادل (محتاج tie-breaker حتمي)، RANK بيدّي نفس الرقم للمتعادلين ويسيب فراغ بعدهم، DENSE_RANK نفس الرقم من غير فراغ.
(Senior) تقرير window functions على 200 مليون صف بطيء. تبص على إيه؟
الـ plan: هل فيه Sort ضخم أو spill لـ tempdb؟ index بترتيب (PARTITION BY, ORDER BY) مع INCLUDE للأعمدة، فلترة بدري، توحيد الـ OVER clauses، ROWS بدل RANGE، batch mode/columnstore للتقارير، أو pre-aggregation يومي في جدول ملخص بدل الحساب كل مرة.
↗

المراجع والقراءة الإضافية

References & further reading

الدرس بيشرح الفكرة نفسها؛ الروابط دي للتحقق والتعمق والرجوع لتفاصيل الـ API. (محتاجة إنترنت.)

خلصت الدرس وحليت الأسئلة؟ علّم عليه عشان يتحسب في تقدّمك.

محتويات الدرس