ليه window functions؟ المشكلة اللي GROUP BY مش بيحلها
عايز تعرض كل طلب ومعاه إجمالي طلبات العميل، أو ترتيب الطلب جوه شهر العميل، أو الفرق عن الطلب اللي قبله. GROUP BY بيضغط الصفوف لصف واحد لكل مجموعة فبتضيّع التفاصيل، والـ self-join أو الـ subquery لكل صف بطيء وصعب القراءة. الـ window function بتحسب على «نافذة» من الصفوف المرتبطة بالصف الحالي، وبتسيب كل الصفوف زي ما هي.
تشريح OVER(): PARTITION BY و ORDER BY والـ frame
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 |
الافتراضي مع 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 زمني لما فيه أيام ناقصة |
Ranking: ROW_NUMBER و RANK و DENSE_RANK و NTILE
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);| Name | Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| Mona | 98 | 1 | 1 | 1 |
| Ali | 95 | 2 | 2 | 2 |
| Omar | 95 | 3 | 2 | 2 |
| Sara | 90 | 4 | 4 | 3 |
| Hany | 80 | 5 | 5 | 4 |
إمتى أنهي واحد؟ ROW_NUMBER لما محتاج صف واحد بالظبط (dedup، top-1، pagination) — لكن اعمل ORDER BY حتمي (tie-breaker) وإلا النتيجة بتتغير بين التشغيلات. RANK للمسابقات («تعادل في المركز التاني والتالت يبقى رابع»). DENSE_RANK لـ «تاني أعلى مرتب» من غير ما التعادل يأكل مراكز.
LAG و LEAD و FIRST_VALUE و LAST_VALUE
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.
Running totals و moving averages ونسب من الإجمالي
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 بيتقارن بحد الخطة.
أنماط كلاسيكية: top-N per group و dedup و gaps & islands
Top-N per group
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 planDeduplication (احتفظ بأحدث نسخة)
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 ... USINGGaps & islands: فترات متصلة
«أطول سلسلة أيام متتالية استخدم فيها الـ tenant المنتج». الحيلة: لو الأيام متصلة، الفرق بين التاريخ والـ ROW_NUMBER ثابت جوه كل «جزيرة».
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;| Day | ROW_NUMBER | Day − rn | Island |
|---|---|---|---|
| Mar 1 | 1 | Feb 28 | A |
| Mar 2 | 2 | Feb 28 | A |
| Mar 3 | 3 | Feb 28 | A |
| Mar 7 | 4 | Mar 3 | B |
| Mar 8 | 5 | Mar 3 | B |
Cohort retention: التحليل اللي كل SaaS بيطلبه
«من العملاء اللي اشتركوا في يناير، كام في المية لسه بيستخدموا المنتج بعد شهر، اتنين، تلاتة؟» ده سؤال الـ product الأشهر، والـ window functions بتحله في query واحدة.
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.
Percentiles والـ median: PERCENTILE_CONT و PERCENTILE_DISC
-- 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 للتحليلات المؤقتة.
PostgreSQL: FILTER و WINDOW clause و RANGE بالوقت
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اللي بيعد صفوف.
الأداء: الـ index اللي بيلغي الـ sort
- الـ window بيحتاج الصفوف مرتبة بـ (PARTITION BY, ORDER BY). index بنفس الترتيب بالظبط بيخلي الـ plan يقرأ مرتب ويتجنب Sort operator (اللي ممكن يعمل spill لـ tempdb على بيانات كبيرة).
- كذا window بنفس الـ OVER في نفس الـ query بيتحسبوا في مرور واحد؛ OVER مختلفة = sorts إضافية. وحّد الـ OVER أو استخدم
WINDOWclause (PostgreSQL، و SQL Server 2022 كمان) للقراءة. - SQL Server: batch mode على rowstore (2019+ بشروط) أو columnstore بيسرّع الـ window aggregates جدًا في التقارير.
- فلتر بدري: WHERE على الـ partition (مثلًا tenant واحد وشهر واحد) قبل الـ window، مش بعده.
CREATE INDEX IX_DailyUsage_Tenant_Day ON dbo.DailyUsage (TenantId, Day) INCLUDE (Tokens);Window functions من EF Core
LINQ ما بيترجمش لـ window functions مباشرة في أغلب الحالات (بعض الأنماط زي GroupBy + Take بتتترجم لـ ROW_NUMBER داخليًا في EF Core الحديث، بس ما تعتمدش على ده للتقارير). الطريقة النضيفة: SQL صريح بيرجع type مخصص.
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 بنفس السلوك.
أخطاء شائعة
- فلترة نتيجة 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 بتاع النتيجة — الأول مش بيضمن ترتيب الإخراج.
اختبر فهمك
Scores: 100, 90, 90, 80. قيم DENSE_RANK() OVER (ORDER BY Score DESC)؟
running total بيقفز بقيمتين مرة واحدة عند طلبين بنفس التاريخ. السبب؟
ليه ما ينفعش WHERE ROW_NUMBER() OVER (...) = 1؟
تمارين إنترفيو محلولة
1: تاني أعلى مرتب في كل قسم (مع التعادل)
التفكير: «تاني أعلى» مع تعادل = DENSE_RANK (لو اتنين أخدوا الأعلى، التاني هو القيمة اللي بعدهم مش التالت في الترتيب).
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: نسبة كل منتج من مبيعات فئته، وترتيبه جوه الفئة
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 دقيقة
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).
تمارين إضافية محلولة
5: أول طلب لكل عميل وقيمة آخر طلب، في صف واحد لكل عميل
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 انحرافات معيارية»
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).
أسئلة إنترفيو
(Junior) الفرق بين GROUP BY والـ window function؟
(Mid) ROW_NUMBER مقابل RANK مقابل DENSE_RANK؟
(Senior) تقرير window functions على 200 مليون صف بطيء. تبص على إيه؟
المراجع والقراءة الإضافية
الدرس بيشرح الفكرة نفسها؛ الروابط دي للتحقق والتعمق والرجوع لتفاصيل الـ API. (محتاجة إنترنت.)
التوثيق الرسمي — Official documentation
خلصت الدرس وحليت الأسئلة؟ علّم عليه عشان يتحسب في تقدّمك.