۱۴۰۴/۰۷/۰۴ Nebular

Window Functions

Window Function چیست؟

در SQL به طور معمول وقتی از GROUP BY استفاده می‌کنی، دیتات خلاصه (aggregate) می‌شه.
مثلا وقتی AVG(Salary) می‌گیری، کل ردیف‌ها به یک ردیف تبدیل می‌شن.
ولی Window Functions می‌ذارن بدون خلاصه کردن ردیف‌ها، همون محاسبات (جمع، میانگین، رتبه‌بندی و …) رو انجام بدی.
یعنی هر ردیف حفظ می‌شه اما یه ستون اضافه می‌کنی که مقدار محاسبه‌شده روی یک “پنجره (Window)” از داده‌هاست.
این “پنجره” رو با OVER(...) تعریف می‌کنیم.

✨ ساختار Window Function

<function>() OVER (
    PARTITION BY <ستون‌ها برای گروه‌بندی منطقی>
    ORDER BY <ستون‌ها برای ترتیب>
    ROWS/RANGE ... (اختیاری برای محدوده پنجره)
)

function() → توابعی مثل ROW_NUMBER(), RANK(), SUM(), AVG(), MAX(), MIN() و …
PARTITION BY → مشخص می‌کنه داده‌ها به چه بخش‌هایی تقسیم بشن. (مثل GROUP BY ولی فقط برای پنجره، نه برای کل کوئری)
ORDER BY → مشخص می‌کنه ترتیب داخل هر پنجره چه جوری باشه.
ROWS/RANGE → می‌گه پنجره از چه ردیف‌هایی تشکیل بشه (مثلا از ردیف قبل تا ردیف فعلی).

📌 انواع مهم Window Functions

1. Ranking Functions (رتبه‌بندی)

ROW_NUMBER() → شماره‌ی یکتا به هر ردیف بر اساس ترتیب می‌ده.
RANK() → مثل بالا، اما در صورت مساوی بودن مقدار، رتبه‌ی مساوی می‌ده و بعدی‌ها می‌پرن.
DENSE_RANK() → مثل RANK() ولی رتبه‌ها پرش ندارن.
NTILE(n) → داده‌ها رو به n بخش تقسیم می‌کنه.

2. Aggregate Functions (محاسبات تجمیعی روی پنجره)

SUM(), AVG(), MAX(), MIN(), COUNT()

3. Value Functions

LAG() → مقدار ردیف قبل رو می‌ده.
LEAD() → مقدار ردیف بعد رو می‌ده.
FIRST_VALUE() → اولین مقدار در پنجره.
LAST_VALUE() → آخرین مقدار در پنجره.

🔥 مثال‌ها با تریس

مثال ۱: پیدا کردن دو نفر اول با بالاترین حقوق در هر دپارتمان

WITH ranked AS (
  SELECT 
    Name, Dept, Salary,
    ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rn
  FROM Employees
)
SELECT * 
FROM ranked 
WHERE rn <= 2;

🔎 تریس:

PARTITION BY Dept → داده‌ها رو به بخش‌های مجزا برای هر دپارتمان تقسیم می‌کنه.
ORDER BY Salary DESC → داخل هر دپارتمان، بر اساس حقوق نزولی مرتب می‌کنه.
ROW_NUMBER() → شماره می‌زنه: 1، 2، 3، …
بعد در WHERE rn <= 2 → فقط نفر اول و دوم هر دپارتمان رو برمی‌گردونه.

مثال ۲: مقایسه حقوق کارمند با حقوق کارمند قبلی در همان دپارتمان

SELECT 
  Name, Dept, Salary,
  LAG(Salary, 1) OVER (PARTITION BY Dept ORDER BY Salary) AS PrevSalary
FROM Employees;

🔎 تریس:

LAG(Salary, 1) → حقوق ردیف قبل رو می‌ده.
مثلا اگر حقوق‌ها: 1000، 2000، 3000 باشن → خروجی می‌شه: NULL، 1000، 2000.

مثال ۳: پیدا کردن میانگین حقوق در هر دپارتمان

SELECT 
  Name, Dept, Salary,
  AVG(Salary) OVER (PARTITION BY Dept) AS AvgDeptSalary
FROM Employees;

🔎 تریس:

بدون اینکه ردیف‌ها جمع بشن، هر ردیف یه ستون اضافه داره که میانگین حقوق همون دپارتمانشه.

مثال ۴: تقسیم کارکنان هر دپارتمان به 4 گروه (کوارتیل)

SELECT 
  Name, Dept, Salary,
  NTILE(4) OVER (PARTITION BY Dept ORDER BY Salary DESC) AS Quartile
FROM Employees;

🔎 تریس:

NTILE(4) → داده‌ها رو به ۴ گروه تقسیم می‌کنه.
مثلا اگر 20 کارمند باشن → هر گروه 5 نفر می‌شه.

📝 سوالات تمرینی

حقوق هر کارمند رو به همراه میانگین حقوق دپارتمان خودش نشون بده.
بالاترین حقوق هر دپارتمان رو پیدا کن (بدون استفاده از ساب‌کوئری).
تفاوت حقوق هر کارمند با کارمند قبلی (بر اساس تاریخ استخدام) رو حساب کن.
نفر سوم با بالاترین حقوق در هر دپارتمان کیه؟
فروش روزانه هر فروشنده رو نشون بده، همراه با مجموع فروش تجمعی تا اون روز.

دادهٔ نمونه (CREATE/INSERT) — جدول Employees و DailySales

(این فقط برای شبیه‌سازیِ خروجی؛ می‌تونی همین داده‌ها رو در SQL Server خودت اجرا کنی.)

-- جدول کارمندان
CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name NVARCHAR(50),
  Dept NVARCHAR(20),
  Salary INT,
  HireDate DATE
);

INSERT INTO Employees (ID, Name, Dept, Salary, HireDate) VALUES
(1, 'Ali',     'Sales', 5000, '2020-01-10'),
(2, 'Sara',    'Sales', 7000, '2019-05-15'),
(3, 'Reza',    'Sales', 7000, '2021-02-20'),
(9, 'Dave',    'Sales', 4800, '2022-08-01'),
(4, 'Mina',    'HR',    6000, '2020-03-01'),
(5, 'Hossein', 'HR',    6000, '2018-07-12'),
(6, 'Layla',   'HR',    4500, '2022-06-05'),
(7, 'Paul',    'IT',    8000, '2017-09-30'),
(8, 'John',    'IT',    7500, '2019-11-25');

-- جدول فروش روزانه (برای سوال 5)
CREATE TABLE DailySales (
  SaleID INT PRIMARY KEY,
  SalesPersonID INT,
  SaleDate DATE,
  Amount INT
);

INSERT INTO DailySales (SaleID, SalesPersonID, SaleDate, Amount) VALUES
(1, 1, '2025-09-20', 100),
(2, 1, '2025-09-21', 150),
(3, 2, '2025-09-20', 200),
(4, 2, '2025-09-21', 100),
(5, 3, '2025-09-21', 50),
(6, 1, '2025-09-22', 80),
(7, 2, '2025-09-22', 120);

سؤال ۱ — حقوق هر کارمند همراه با میانگین حقوق دپارتمانِ او

هدف: برای هر ردیف (هر کارمند) میانگین حقوق همان دپارتمان را نشان بده، بدون اینکه ردیف‌ها aggregate شوند.

کوئری:

SELECT
  ID, Name, Dept, Salary,
  AVG(Salary) OVER (PARTITION BY Dept) AS AvgDeptSalary
FROM Employees
ORDER BY Dept, ID;

توضیح ساده: AVG(Salary) OVER (PARTITION BY Dept) برای هر ردیف، میانگینِ حقوقِ همهٔ ردیف‌های آن Dept را محاسبه می‌کند و نتیجه را به ستون جدید می‌افزاید. ردیف‌ها حذف نمی‌شوند — فقط یک ستون اضافی داریم.

دادهٔ نمونه (گروه‌بندی):

Sales: salaries = 7000, 7000, 5000, 4800 → جمع = 23800 ، تعداد = 4 → میانگین = 23800 / 4 = 5950
HR: 6000, 6000, 4500 → جمع = 16500 ، تعداد = 3 → میانگین = 5500
IT: 8000, 7500 → میانگین = 7750

خروجی (نمونه):

ID	Name	Dept	Salary	AvgDeptSalary
2	Sara	Sales	7000	5950
3	Reza	Sales	7000	5950
1	Ali	Sales	5000	5950
9	Dave	Sales	4800	5950
5	Hossein	HR	6000	5500
4	Mina	HR	6000	5500
6	Layla	HR	4500	5500
7	Paul	IT	8000	7750
8	John	IT	7500	7750

تریس (مثال برای یک ردیف):
برای ردیف Sara → Dept = Sales → میانگین Sales = 5950 (محاسبه شده روی تمام سطرهای Sales)، پس ستون اضافه = 5950.

سؤال ۲ — بالاترین حقوق هر دپارتمان (بدون GROUP BY)

هدف: مقدار بالاترین حقوقِ هر دپارتمان را بدست بیاوریم (و سپس روش گرفتن نامِ کارمندان صاحبِ بالاترین حقوق).

روش A — فقط مقدار بالاترین حقوق (بدون collapse کردن ردیف‌ها)

کوئری:

SELECT
  ID, Name, Dept, Salary,
  MAX(Salary) OVER (PARTITION BY Dept) AS MaxDeptSalary
FROM Employees
ORDER BY Dept, ID;

توضیح: MAX(...) OVER (PARTITION BY Dept) برای هر ردیف، مقدار بیشینهٔ Salary در همان Dept را می‌نویسد.

خروجی (نمونه):

Dept	Name	Salary	MaxDeptSalary
Sales	Sara	7000	7000
Sales	Reza	7000	7000
Sales	Ali	5000	7000
Sales	Dave	4800	7000
HR	Hossein	6000	6000
HR	Mina	6000	6000
HR	Layla	4500	6000
IT	Paul	8000	8000
IT	John	7500	8000

این کوئری به تو مقدار بیشینه را برای هر ردیف می‌دهد — اگر بخواهی فقط یک ردیف (یا ردیف‌(ها) از کارمندانِ صاحبِ بیشینه) را برگردانی باید فیلتر کنی.

روش B — نام (یا ردیف‌های) کارمند(ها) که بالاترین حقوق را دارند (با حفظ tiedها)

کوئری با RANK (برای شامل شدن tiedها):

WITH ranked AS (
  SELECT *, RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rnk
  FROM Employees
)
SELECT ID, Name, Dept, Salary
FROM ranked
WHERE rnk = 1
ORDER BY Dept, Name;

توضیح: RANK() به tiedها همان رتبه را می‌دهد. شرط rnk = 1 تمام کارمندانی را که در یک دپارتمان بیشترین حقوق را دارند (حتی اگر چند نفر tied باشند) برمی‌گرداند.

خروجی (نمونه):

برای Sales → Sara و Reza (هر دو 7000) بازگردانده می‌شوند.
برای HR → Hossein و Mina (هر دو 6000) بازگردانده می‌شوند.
برای IT → Paul (8000).

سؤال ۳ — تفاوت حقوق هر کارمند با کارمند قبلی (بر اساس تاریخ استخدام در همان دپارتمان)

هدف: برای هر کارمند مقدار Salary - PrevSalary را محاسبه کنیم، که PrevSalary حقوق کارمند قبلی در همان دپارتمان بر اساس HireDate است.

کوئری:

SELECT
  ID, Name, Dept, HireDate, Salary,
  LAG(Salary,1) OVER (PARTITION BY Dept ORDER BY HireDate) AS PrevSalary,
  Salary - LAG(Salary,1) OVER (PARTITION BY Dept ORDER BY HireDate) AS DiffFromPrev
FROM Employees
ORDER BY Dept, HireDate;

توضیح: LAG(Salary,1) مقدار salary ردیفِ قبلی داخل همان پارتیشن (همان Dept) را برمی‌گرداند. اگر ردیف قبلی وجود نداشته باشد، مقدار NULL است.

تریس و خروجی (به ترتیب HireDate در هر Dept):

Sales (ترتیب بر اساس HireDate):
1. Sara (2019-05-15) — Salary=7000 → PrevSalary = NULL → Diff = NULL
2. Ali (2020-01-10) — Salary=5000 → PrevSalary = 7000 → Diff = 5000 – 7000 = -2000
3. Reza (2021-02-20) — Salary=7000 → PrevSalary = 5000 → Diff = +2000
4. Dave (2022-08-01) — Salary=4800 → PrevSalary = 7000 → Diff = -2200
HR:
1. Hossein (2018-07-12) — Salary=6000 → Prev=NULL → Diff=NULL
2. Mina (2020-03-01) — Salary=6000 → Prev=6000 → Diff=0
3. Layla (2022-06-05) — Salary=4500 → Prev=6000 → Diff=-1500
IT:
1. Paul (2017-09-30) — Prev=NULL
2. John (2019-11-25) — Prev=8000 → Diff = 7500 – 8000 = -500

خروجی جدول: (نمایش چند ستون)

Dept	Name	HireDate	Salary	PrevSalary	DiffFromPrev
Sales	Sara	2019-05-15	7000	NULL	NULL
Sales	Ali	2020-01-10	5000	7000	-2000
Sales	Reza	2021-02-20	7000	5000	2000
Sales	Dave	2022-08-01	4800	7000	-2200
HR	Hossein	2018-07-12	6000	NULL	NULL
HR	Mina	2020-03-01	6000	6000	0
HR	Layla	2022-06-05	4500	6000	-1500
IT	Paul	2017-09-30	8000	NULL	NULL
IT	John	2019-11-25	7500	8000	-500

سؤال ۴ — نفر سوم با بالاترین حقوق در هر دپارتمان — (دو تعبیر و روش)

این سؤال معمولاً دو تعبیر دارد؛ هر دویشان را نشان می‌دهم و فرقشان را توضیح می‌دهم:

تعریف A (سومین ردیف براساس مرتب‌سازی Salary DESC) — «سومین شخص» (صرف‌نظر از tied) → از ROW_NUMBER() استفاده می‌کنیم.
تعریف B (سومین مقدار متمایزِ Salary) — «سومین سطح حقوق» (distinct salary) → از DENSE_RANK() استفاده می‌کنیم.
(RANK() هم وجود دارد که در صورت tied ممکن است رتبه‌ها پرش داشته باشند؛ فرق‌ها را پایین می‌بینیم.)

روش A — سومین شخص (ROW_NUMBER)

WITH rn AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC, ID ASC) AS rn
  FROM Employees
)
SELECT ID, Name, Dept, Salary
FROM rn
WHERE rn = 3
ORDER BY Dept;

توضیح: ROW_NUMBER() به هر ردیف یک شمارهٔ یکتا می‌دهد (حتی برای tiedها). شرط rn = 3 دقیقا سومین ردیف را انتخاب می‌کند.

در دادهٔ ما (Sales): ترتیب Salary DESC → Sara(7000, ID2) rn=1, Reza(7000, ID3) rn=2, Ali(5000, ID1) rn=3, Dave(4800) rn=4 → سومین شخص = Ali.

روش B — سومین مقدار متمایزِ Salary (DENSE_RANK)

WITH dr AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS drnk
  FROM Employees
)
SELECT ID, Name, Dept, Salary
FROM dr
WHERE drnk = 3
ORDER BY Dept, Name;

توضیح: DENSE_RANK() رتبهٔ مقدارِ salary را می‌دهد (مقادیر یکسان یک رتبه می‌گیرند و رتبه‌ها بدون پرش افزایش می‌یابند). شرط drnk = 3 یعنی سومین distinct salary. ممکن است یک یا چند نفر آن حقوق را داشته باشند.

مثال (Sales): distinct salaries = [7000 (rank1), 5000 (rank2), 4800 (rank3)] → drnk = 3 بازمی‌گرداند Dave (4800).

خلاصهٔ فرق‌ها با مثال:

ROW_NUMBER() rn=3 → سومین شخص (در مثال: Ali)
RANK() rnk=3 → کسانی که rank = 3 (با پرش در صورت tied). در مثال RANK() هم Ali خواهد بود چون پس از دو tied (rank1) نفر بعدی rank=3 است.
DENSE_RANK() drnk=3 → سومین distinct salary → Dave.

سؤال ۵ — فروش روزانه هر فروشنده همراه با مجموع فروش تجمعی تا آن روز (Running Total)

هدف: برای هر فروشنده، مجموع تجمعی (running total) تا هر تاریخ نمایش داده شود.

کوئری:

SELECT
  SaleID, SalesPersonID, SaleDate, Amount,
  SUM(Amount) OVER (
    PARTITION BY SalesPersonID
    ORDER BY SaleDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS RunningTotal
FROM DailySales
ORDER BY SalesPersonID, SaleDate, SaleID;

توضیح: SUM(...) OVER (PARTITION BY SalesPersonID ORDER BY SaleDate ROWS ... CURRENT ROW) مجموع مقدار Amount را برای همان SalesPersonID از ابتدای پارتیشن تا ردیفِ جاری (براساس ترتیب تاریخ) محاسبه می‌کند.

دادهٔ نمونه و محاسبه:

SalesPersonID = 1 (Ali):
- 2025-09-20 → Amount = 100 → RunningTotal = 100
- 2025-09-21 → Amount = 150 → RunningTotal = 100 + 150 = 250
- 2025-09-22 → Amount = 80 → RunningTotal = 250 + 80 = 330
SalesPersonID = 2 (Sara):
- 2025-09-20 → 200 → RT = 200
- 2025-09-21 → 100 → RT = 300
- 2025-09-22 → 120 → RT = 420
SalesPersonID = 3 (Reza):
- 2025-09-21 → 50 → RT = 50

خروجی (نمونه):

SaleID	SalesPersonID	SaleDate	Amount	RunningTotal
1	1	2025-09-20	100	100
2	1	2025-09-21	150	250
6	1	2025-09-22	80	330
3	2	2025-09-20	200	200
4	2	2025-09-21	100	300
7	2	2025-09-22	120	420
5	3	2025-09-21	50	50

نکته دربارهٔ ROWS vs RANGE:
در مثال بالا از ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW استفاده کردم تا صریحاً بگیم مجموع از ابتدای پارتیشن تا ردیف فعلی بر اساس ترتیب ردیف‌ها محاسبه بشه. اگر چند رکورد دقیقا روی یک SaleDate داشتی و نیاز به ترتیب قطعی داشتی، بهتره ORDER BY SaleDate, SaleID بگذاری تا deterministic باشه.

جمع‌بندی و نکات مهم

فرقِ اصلی بین توابع رتبه‌ای: ROW_NUMBER() یکتا می‌سازد؛ RANK() tiedها را مساوی رتبه می‌دهد و بعداً جاخالی می‌افتد؛ DENSE_RANK() tiedها را مساوی می‌دهد اما رتبه‌ها بی‌درز رشد می‌کنند.
Aggregate با OVER(PARTITION BY ...) = محاسبهٔ تجمیعی در پنجره بدون collapse کردن ردیف‌ها.
LAG/LEAD برای مقایسهٔ ردیف به ردیف بسیار کاربردی‌اند.
برای فیلتر کردن براساس مقدارِ تولید شده توسط window function باید از CTE یا derived table استفاده کنی (در SQL Server نمی‌توان window function را مستقیم در WHERE استفاده کرد).
در پرسش‌های Top-N per group، معمولاً ROW_NUMBER() برای «دقیقاً N ردیف» و RANK()/DENSE_RANK() برای «نگه‌داشتن tiedها» کاربرد دارد.

توضیح کاملِ خط‌به‌خطِ کوئری `TOP-N per group` با `ROW_NUMBER()`

عالی — بیا از صفر تا صد این کوئری را باز کنیم و هر کلمه/بخش را ساده و دقیق توضیح بدهم، بعد چند مثال و تریس خط‌به‌خط نشان دهم تا رفتارش کاملاً مشخص شود.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rn
  FROM Employees
)
SELECT * FROM ranked WHERE rn <= 2;

۱. هدفِ کلیِ کوئری (به زبان ساده)

هدف: «برای هر گروه (هر Dept) دو نفر با بالاترین حقوق (Salary) را انتخاب کن.»
یعنی خروجی باید برای هر دپارتمان جداگانه حداکثر ۲ ردیف با بیشترین حقوق نشان دهد.

۲. کلمه‌به‌کلمه (توضیح اجزاء)

WITH ranked AS (...)
یک CTE (Common Table Expression) می‌سازد به نام ranked. این مثل یک جدول موقت در همان کوئری است که می‌توانیم در SELECT بعدی به آن ارجاع دهیم. مزیت: خواناتر و سازمان‌یافته‌تر شدن کوئری نسبت به یک subquery طولانی.
داخل CTE:
SELECT *,
یعنی همه ستون‌های جدول Employees را بردار. ROW_NUMBER() OVER (...) AS rn
ROW_NUMBER() یک تابع پنجره‌ای است که به هر ردیف عدد ترتیبی یکتا (۱،۲،۳،…) نسبت می‌دهد در محدودهٔ پنجره‌ای که در OVER(...) تعریف شده. نام ستونی که تولید می‌شود rn است. OVER (PARTITION BY Dept ORDER BY Salary DESC)
این بخش مشخص می‌کند که پنجره چگونه ساخته شود:
- PARTITION BY Dept → داده‌ها را بر اساس مقدار Dept جدا می‌کنیم؛ یعنی برای هر دپارتمان جداگانه شمارش ROW_NUMBER() از اول شروع می‌شود.
- ORDER BY Salary DESC → داخل هر پارتیشن (هر دپارتمان)، ردیف‌ها بر اساس Salary به ترتیب نزولی مرتب می‌شوند؛ پس از بزرگترین حقوق به کوچکترین.
FROM Employees
منبع داده‌ها جدول Employees است.
خارجِ CTE:
SELECT * FROM ranked WHERE rn <= 2;
از نتیجهٔ CTE (که هر ردیف یک شماره rn دارد) فقط ردیف‌هایی را انتخاب می‌کنیم که شمارهٔ ترتیبی‌شان 1 یا 2 باشد — یعنی دو ردیف اولِ هر پارتیشن.

۳. چرا `ROW_NUMBER()` را استفاده کردیم؟ (دلایل و ویژگی‌ها)

هدفِ دقیق: «دقیقا N ردیف برای هر گروه» است. ROW_NUMBER() برای هر ردیف یک شمارهٔ یکتا می‌سازد (حتی اگر مقدار Salary تکراری باشد). بنابراین rn <= 2 دقیقا دو ردیف برمی‌گرداند (مگر اینکه گروه کمتر از 2 ردیف داشته باشد).
اگر بخواهیم «همه‌ی tiedها» در مرز شامل شوند (مثلاً اگر دو نفر در جای اول tie باشند و نفر سوم هم tied با نفر دوم)، ROW_NUMBER() مناسب نیست چون همیشه شماره‌ها را یکتا می‌کند و ممکن است بعضی tiedها حذف شوند. در آن حالت ممکن است بخواهیم از RANK() یا DENSE_RANK() استفاده کنیم.
ROW_NUMBER() سریع و ساده برای الگوی «Top N per group» است و بسیار رایج.

۴. مقایسه با `RANK()` و `DENSE_RANK()` (چرا فرق می‌کنند)

فرض داخل یک دپارتمان سه حقوق داریم: 7000, 7000, 5000

ROW_NUMBER() → به ترتیب: 1, 2, 3 (دو 7000 شماره‌های مختلف 1 و 2 می‌گیرند)
RANK() → به ترتیب: 1, 1, 3 (دو tied عدد 1 می‌گیرند، شمارهٔ بعدی می‌پرد — skip)
DENSE_RANK() → به ترتیب: 1, 1, 2 (بعد از tie، شمارهٔ بعدی افزایش بدون جاخالی)

نتیجهٔ شرط rn <= 2:

با ROW_NUMBER() دقیقاً ۲ ردیف انتخاب می‌شود (مثلاً هر دو 7000 ممکن است انتخاب شوند یا یکی از آن‌ها بسته به tie-breaker انتخاب شود اگر درخواست rn<=2).
با RANK() یا DENSE_RANK() شرط rank <= 2 می‌تواند تعداد متفاوتی بازگرداند (ممکن است >2 شود اگر tie وجود داشته باشد).

پس انتخاب تابع بستگی به این دارد که آیا می‌خواهیم «همه tiedها را نگه داریم» یا «دقیقاً N ردیف».

۵. تریسِ گام‌به‌گام با مثال واقعی

فرض جدول Employees به شکل زیر:

ID | Name    | Dept  | Salary
---+---------+-------+-------
1  | Ali     | Sales | 5000
2  | Sara    | Sales | 7000
3  | Reza    | Sales | 7000
4  | Mina    | HR    | 6000
5  | Hossein | HR    | 6000
6  | Layla   | HR    | 4500
7  | Paul    | IT    | 8000
8  | John    | IT    | 7500

گام A — اجرای بخش درون CTE (مرتب‌سازی و شماره‌گذاری)

برای هر Dept جدا:

برای Sales ترتیب بر اساس Salary DESC: Sara(7000), Reza(7000), Ali(5000)
به هر کدام ROW_NUMBER() اختصاص می‌گیرد: Sara → 1, Reza → 2, Ali → 3
برای HR: Mina(6000), Hossein(6000), Layla(4500) → Mina→1, Hossein→2, Layla→3
برای IT: Paul(8000), John(7500) → Paul→1, John→2

بنابراین CTE ranked شبیه این خواهد بود:

ID | Name    | Dept  | Salary | rn
---+---------+-------+--------+---
2  | Sara    | Sales | 7000   | 1
3  | Reza    | Sales | 7000   | 2
1  | Ali     | Sales | 5000   | 3
4  | Mina    | HR    | 6000   | 1
5  | Hossein | HR    | 6000   | 2
6  | Layla   | HR    | 4500   | 3
7  | Paul    | IT    | 8000   | 1
8  | John    | IT    | 7500   | 2

گام B — فیلتر `WHERE rn <= 2`

از جدول بالا فقط ردیف‌های rn برابر ۱ یا ۲ نگه داشته می‌شوند:

ID | Name    | Dept  | Salary | rn
---+---------+-------+--------+---
2  | Sara    | Sales | 7000   | 1
3  | Reza    | Sales | 7000   | 2
4  | Mina    | HR    | 6000   | 1
5  | Hossein | HR    | 6000   | 2
7  | Paul    | IT    | 8000   | 1
8  | John    | IT    | 7500   | 2

این دقیقاً «دو نفر برتر برای هر دپارتمان» است. (توجه: برای Sales چون دو نفر tied روی 7000 بودند، هر دو انتخاب شدند.)

۶. نکات مهم و edge-cases که باید بدانید

tie (مقادیر یکسان در ORDER BY)
اگر Salary تکراری باشد، ترتیب بین رکوردهای tied نامعلوم است مگر اینکه صراحتاً یک tiebreaker اضافه کنید. برای مثال: ORDER BY Salary DESC, ID ASC با این کار، زمانی که salary یکی است، بر اساس ID تعیین ترتیب می‌شود و رفتار ROW_NUMBER() تثبیت می‌شود (deterministic).
آیا WITH (CTE) لازم است؟
نه اجباری نیست؛ فقط خواناتر است. معادل بدون CTE: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rn FROM Employees ) AS ranked WHERE rn <= 2;
اگر بخواهیم همه tiedها را نگه داریم
از RANK() یا DENSE_RANK() استفاده کنید، مثلاً: SELECT * FROM ( SELECT *, RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rnk FROM Employees ) t WHERE rnk <= 2; این ممکن است برای یک دپارتمان بیش از 2 ردیف برگرداند اگر tiedها در مرز وجود داشته باشند.
عملکرد (Performance)
- تابع پنجره‌ای معمولاً نیاز به مرتب‌سازی (sort) داخل هر پارتیشن دارد. روی داده‌ی بزرگ، این کار هزینه‌بر است و ممکن است به tempdb spill کند.
- برای کمک به performance، ایندکس مناسب بسازید. برای این کوئری ایندکس پیشنهادی:
  CREATE INDEX IX_Employees_Dept_Salary ON Employees(Dept, Salary DESC);
  (در SQL Server می‌توانید ترتیب DESC را در index تعریف کنید.)
- گاهی روش‌های دیگر مثل CROSS APPLY با TOP (N) برای هر گروه عملکرد بهتری دارند، بسته به آمار و ایندکس‌ها: SELECT e.* FROM (SELECT DISTINCT Dept FROM Employees) d CROSS APPLY ( SELECT TOP (2) * FROM Employees e2 WHERE e2.Dept = d.Dept ORDER BY Salary DESC ) e; این روش می‌تواند در برخی سناریوها سریع‌تر باشد چون از TOP و ایندکس بهره می‌برد؛ اما خوانایی و قابلیت نگهداری متفاوت است.
ملاحظات همزمانی (consistency)
اگر داده‌ها در حال تغییر هستند و شما نتیجهٔ قطعی نیاز دارید، به سطح ایزولیشن و تراکنش توجه کنید.

۷. خلاصهٔ کاربردی — چه زمانی از این الگو استفاده کنیم؟

وقتی لازم است دقیقاً N ردیف برتر را برای هر گروه بگیرید → از ROW_NUMBER() و فیلتر rn <= N استفاده کنید.
وقتی می‌خواهید همه tiedها را نگه دارید → از RANK() یا DENSE_RANK() استفاده کنید.
برای تضمین ترتیب قطعی هنگام tiedها، یک ستون tiebreaker در ORDER BY اضافه کنید (مانند ID یا تاریخ استخدام).
برای performance بزرگ‌مقیاس، ایندکس و بررسی جایگزین‌هایی مثل CROSS APPLY را مد نظر قرار بده.

Window Functions، Subquery و GROUP BY هر کدوم کاربرد خودشون رو دارن و نمی‌تونن کاملاً جای همدیگه رو بگیرن. بیاییم قدم‌به‌قدم بررسی کنیم:

1️⃣ Window Functions

مثال: ROW_NUMBER(), RANK(), SUM() OVER(PARTITION BY ...)
مزیت اصلی: می‌تونی محاسبات Aggregate یا رتبه‌بندی رو روی ردیف‌ها انجام بدی بدون اینکه ردیف‌ها رو گروه‌بندی کنی.
یعنی هر ردیف همچنان حفظ می‌شه و اطلاعات گروه خودش رو هم داره.

مثال:

SELECT Name, Dept, Salary,
       AVG(Salary) OVER(PARTITION BY Dept) AS AvgSalaryDept
FROM Employees;

اینجا هر ردیف جدای از دیگران باقی می‌مونه و میانگین حقوق دپارتمان هم کنار اون نشون داده می‌شه.

2️⃣ Subqueries

وقتی بخوای یک مقدار خاص یا شرط پیچیده رو محاسبه کنی و استفاده کنی، Subquery خیلی راحت‌تره.
گاهی Window Functions پیچیده یا غیرممکنه.

مثال:

SELECT Name, Dept, Salary
FROM Employees E1
WHERE Salary = (SELECT MAX(Salary) FROM Employees E2 WHERE E2.Dept = E1.Dept);

پیدا کردن کارمندان با بالاترین حقوق در هر دپارتمان
می‌تونه با Window Function هم حل بشه، ولی Subquery ساده‌تره و SQL ساده‌تر می‌مونه.

3️⃣ GROUP BY

وقتی بخوای سطح جزئیات را از بین ببری و فقط Aggregate داشته باشی (مثل SUM, COUNT, AVG) استفاده می‌شه.
Window Function اطلاعات ردیف به ردیف رو نگه می‌داره، اما GROUP BY ردیف‌ها رو یکجا گروه‌بندی می‌کنه و فقط نتیجه Aggregated رو می‌ده.

مثال:

SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Dept;

فقط میانگین حقوق هر دپارتمان برمی‌گرده، بدون اینکه ردیف‌های فردی باقی بمونن.

🔑 جمع‌بندی

تکنیک	وقتی استفاده می‌کنیم	نکته
Window Functions	وقتی می‌خوای Aggregate یا رتبه‌بندی داشته باشی ولی ردیف‌ها حفظ بشن	نگه می‌داره جزئیات
Subquery	وقتی می‌خوای یک مقدار خاص محاسبه و استفاده بشه	ساده برای بعضی شرایط
GROUP BY	وقتی فقط نتایج Aggregate می‌خوای، ردیف‌ها اهمیتی ندارن	اطلاعات جزئی حذف می‌شه

💡 مثال ترکیبی:

پیدا کردن کارمندان با حقوق بیشتر از متوسط دپارتمان خودشون:

SELECT Name, Dept, Salary
FROM (
    SELECT Name, Dept, Salary,
           AVG(Salary) OVER(PARTITION BY Dept) AS AvgSalaryDept
    FROM Employees
) AS sub
WHERE Salary > AvgSalaryDept;

اینجا هم Window Function برای محاسبه میانگین استفاده شده، هم Subquery (Derived Table) برای فیلتر کردن.
بدون Window Function یا Subquery، نوشتنش خیلی پیچیده‌تر می‌شد.

توضیحات از منابع ماکروسافت

توابع پنجره (Window Functions) — کامل، دقیق و قابل‌فهم

عالی — می‌ریم سراغ توضیح مرحله‌به‌مرحله، با مثال و تریس (trace) تا کامل جا بیفته. من ابتدا مفهوم و ساختار کلی رو می‌گم، بعد انواع متداول و نکات فریم/رفتار، سپس چند مثال کاربردی همراه با جدول ورودی و خروجی و تریس خط به خط.

۱) مفهوم کلی — چه کاری انجام می‌دهند؟

توابع پنجره (window functions) این امکان را می‌دهند که برای هر ردیف یک مقدار محاسباتی را بر مبنای «یک مجموعه از ردیف‌های مربوط» (که آن را پنجره یا window می‌نامیم) حساب کنید، بدون اینکه ردیف‌ها را گروه‌بندی (collapse) کنید. تعریف پنجره با OVER(...) انجام می‌شود (می‌توانید PARTITION BY برای بخش‌بندی و ORDER BY برای ترتیب مشخص کنید و در صورت لازم فریم را با ROWS یا RANGE محدود کنید). Microsoft Learn+1

نکته مهم: اگر ORDER BY داخل OVER() باشد و فریم صریحاً مشخص نشده باشد، پیش‌فرض RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW است — یعنی تا مقدارِ فعلیِ ORDER BY (و شامل tieها) را در نظر می‌گیرد. این رفتار گاهی باعث نتایج غیرمنتظره با مقادیر تکراری ORDER BY می‌شود. Microsoft Learn

۲) ساختار کلی دستور

<تابع_پنجره> (expr) OVER (
    [ PARTITION BY col1, col2, ... ]
    [ ORDER BY col3 [ASC|DESC], ... ]
    [ ROWS | RANGE frame_clause ]
)

یا در SQL Server 2022+ می‌توانید نام پنجره‌ها را با WINDOW تعریف کنید (نیاز به compatibility level مناسب دارد). Microsoft Learn

۳) دسته‌بندی توابع متداول در SQL Server

توابع رتبه‌ای (Ranking): ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(n) — برای رتبه‌بندی/پارتیشن‌بندی. Microsoft Learn
توابع آفست / مقدار (Offset / Value): LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() — دسترسی به ردیف قبلی/بعدی یا اولین/آخرین مقدار در فریم. LAG/LEAD از SQL Server 2012 پشتیبانی می‌شود. Microsoft Learn
توابع تجمعی به صورت پنجره‌ای: SUM(), AVG(), COUNT(), MIN(), MAX(), STRING_AGG() و … با OVER() می‌توانند به صورت پنجره‌ای اجرا شوند. Microsoft Learn
توابع توزیع / آماری: PERCENT_RANK(), CUME_DIST(), PERCENTILE_CONT(), PERCENTILE_DISC() برای درصد و توزیع داده‌ها. Microsoft Learn

توجه: تابع استاندارد NTH_VALUE() در T-SQL (SQL Server) پشتیبانی نمی‌شود — اگر می‌خواهید «مقدار nام» را بیاورید باید با ROW_NUMBER() یا روش‌های دیگر شبیه‌سازی کنید. (مستندات مایکروسافت صراحتاً ذکر کرده‌اند که Transact-SQL این ویژگی را پشتیبانی نمی‌کند). Microsoft Learn

۴) قواعد اجرایی و محدودیت‌ها (Gotchas)

توابع پنجره تنها در SELECT و ORDER BY قابل استفاده‌اند (نمی‌توانید مستقیم در WHERE ازشان استفاده کنید). برای فیلتر براساس نتیجهٔ پنجره‌ای از CTE یا زیرپرس و سپس فیلتر استفاده کنید. Redgate Software
وقتی ORDER BY داخل OVER() دارید و فریم مشخص نکنید، پیش‌فرض RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW است — این مهم است چون با tieها (مقادیر یکسان ORDER BY) نتایج ممکن است شامل ردیف‌های بعدی هم بشوند. Microsoft Learn
ROWS فریم را بر اساس موقعیت فیزیکی ردیف می‌سازد (deterministic)؛ RANGE بر اساس مقدار ORDER BY عمل می‌کند (tieها را یکجا حساب می‌کند). اغلب برای حرکت پنجره‌ای که می‌خواهید به ردیف‌های تکراری تفاوت بدهید، از ROWS استفاده کنید.
توابع پنجره معمولاً نیاز به مرتب‌سازی (sort) و حافظه دارند — در موارد بزرگ می‌توانند هزینه زمانی/حافظه‌ای و انشعاب tempdb ایجاد کنند؛ طراحی ایندکس مناسب روی ستون‌های PARTITION BY و ORDER BY می‌تواند کمک کند، اما همیشه نیاز به sort وجود ندارد. (نکته عملی: برای عملیات روی داده‌های خیلی بزرگ، مراقب memory grant و spills باشید.)

۵) مثال‌های کاربردی همراه با تریس (مثال‌ها خط به خط)

مثال ۱ — مقایسه `ROW_NUMBER`, `RANK`, `DENSE_RANK`

داده ورودی (Employees):

ID | Name   | Dept  | Salary
---+--------+-------+-------
1  | Ali    | Sales | 5000
2  | Sara   | Sales | 7000
3  | Reza   | Sales | 7000
4  | Mina   | HR    | 6000
5  | Hossein| HR    | 6000
6  | Layla  | HR    | 4500

کوئری:

SELECT ID, Name, Dept, Salary,
  ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rn,
  RANK()       OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS drnk
FROM Employees
ORDER BY Dept, rn;

خروجی (توضیح/تریس):

برای Sales مرتب بر حسب Salary DESC: Sara(7000), Reza(7000), Ali(5000)
- ROW_NUMBER: Sara=1, Reza=2, Ali=3 (همیشه شمارشی)
- RANK: Sara=1, Reza=1, Ali=3 (دو تا 1 -> بعدی 3)
- DENSE_RANK: Sara=1, Reza=1, Ali=2 (پس از tie، شماره بعدی بدون جاخالی)
برای HR: Mina(6000), Hossein(6000), Layla(4500)
- ROW_NUMBER: 1,2,3
- RANK: 1,1,3
- DENSE_RANK:1,1,2

ترکیب خروجی (چند ردیف به صورت نمونه):

ID | Name  | Dept  | Salary | rn | rnk | drnk
---+-------+-------+--------+----+-----+-----
2  | Sara  | Sales | 7000   | 1  | 1   | 1
3  | Reza  | Sales | 7000   | 2  | 1   | 1
1  | Ali   | Sales | 5000   | 3  | 3   | 2
4  | Mina  | HR    | 6000   | 1  | 1   | 1
5  | Hossein| HR   | 6000   | 2  | 1   | 1
6  | Layla | HR    | 4500   | 3  | 3   | 2

مثال ۲ — جمع تجمعی (Running Total) و فرق `ROWS` vs `RANGE`

داده (Sales):

SaleDate     | Amount
-------------+-------
2025-09-20   | 100
2025-09-21   | 200   -- (A)
2025-09-21   | 50    -- (B) duplicate date
2025-09-22   | 150
2025-09-23   | 300

کوئری مقایسه‌ای:

SELECT SaleDate, Amount,
  SUM(Amount) OVER (ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS running_rows,
  SUM(Amount) OVER (ORDER BY SaleDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_range
FROM Sales
ORDER BY SaleDate, Amount;

تریس و نتیجه:

ترتیب فیزیکی (با ORDER BY SaleDate, Amount) ما را به این ترتیب می‌دهد:
1. 2025-09-20 — 100
2. 2025-09-21 — 50 (B) [پایین‌تر از 200 چون Amount هم گذاشتیم برای deterministic]
3. 2025-09-21 — 200 (A)
4. 2025-09-22 — 150
5. 2025-09-23 — 300

با ROWS (فریم بر اساس ردیف‌های قبلی تا ردیف فعلی):

ردیف1: running_rows = 100
ردیف2: running_rows = 100 + 50 = 150
ردیف3: running_rows = 150 + 200 = 350
ردیف4: running_rows = 350 + 150 = 500
ردیف5: running_rows = 500 + 300 = 800

با RANGE (پیش‌فرض وقتی ORDER BY باشد) — توجه: RANGE بر اساس مقدار SaleDate عمل می‌کند و برای هر ردیف با یک تاریخ مشخص، همه ردیف‌هایی که تاریخشان <= آن تاریخ را شامل می‌کند (بنابراین هر دو ردیف 2025-09-21 هر دو مجموع تا آن تاریخ را نشان خواهند داد):

ردیف1 (2025-09-20): running_range = 100
ردیف2 (2025-09-21, B): running_range = 100 + 50 + 200 = 350 (شامل هر دو ردیف 2025-09-21)
ردیف3 (2025-09-21, A): running_range = 350
ردیف4: running_range = 350 + 150 = 500
ردیف5: running_range = 800

خلاصه: اگر در داده‌تان duplicate در ORDER BY دارید و می‌خواهید رفتار «فقط ردیف‌های قبلی» را داشته باشید، از ROWS استفاده کنید؛ در غیر این‌صورت RANGE ممکن است گروهبندی بر اساس مقدار ORDER BY را انجام دهد. Microsoft Learn

مثال ۳ — `LAG` و محاسبهٔ تغییر نسبت به ردیف قبلی

داده (DailySales):

SaleDate   | Amount
-----------+-------
2025-09-20 | 100
2025-09-21 | 200
2025-09-22 | 150
2025-09-23 | 300

کوئری:

SELECT SaleDate, Amount,
  LAG(Amount, 1) OVER (ORDER BY SaleDate) AS prev_amount,
  Amount - LAG(Amount,1) OVER (ORDER BY SaleDate) AS diff_from_prev
FROM DailySales
ORDER BY SaleDate;

خروجی (تریس):

برای 2025-09-20: prev_amount = NULL, diff_from_prev = NULL
برای 2025-09-21: prev_amount = 100, diff_from_prev = 100
برای 2025-09-22: prev_amount = 200, diff_from_prev = -50
برای 2025-09-23: prev_amount = 150, diff_from_prev = 150

LAG/LEAD خیلی مفیدند برای تشخیص تغییرات ردیف‌به‌ردیف بدون join یا subquery. Microsoft Learn

مثال ۴ — گرفتن Top-N برای هر گروه (Top-N per group)

مسئله: برای هر بخش (Dept) دو کارمند با بیشترین حقوق را انتخاب کن.

روش (CTE + ROW_NUMBER):

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS rn
  FROM Employees
)
SELECT * FROM ranked WHERE rn <= 2;

این روش بسیار رایج و مؤثر است برای «Top N per group».

مثال ۵ — رتبه درصدی / توزیع (`PERCENT_RANK`, `CUME_DIST`)

فرض Sales در یک دپارتمان با حقوق: [7000, 7000, 5000] (count = 3)

PERCENT_RANK() معمولاً محاسبه می‌شود: (rank - 1) / (count - 1) → برای بالاترین مقدار (rank=1) مقدار 0 و برای پایین‌ترین مقدار 1 (بسته به تعداد ردیف‌ها).
CUME_DIST() = تعداد ردیف‌هایی که مقدارشان <= مقدار جاری تقسیم بر تعداد کل (مثال: برای 7000 که دو نفر دارند، CUME_DIST = 2/3 ≈ 0.6667). Microsoft Learn

۶) نکات عملی / بهترین شیوه‌ها

اگر می‌خواهید «nth value» را در SQL Server شبیه‌سازی کنید (چون NTH_VALUE استاندارد در T-SQL در دسترس نیست)، از ROW_NUMBER() در یک CTE یا subquery استفاده کنید و سپس مقدار موردنظر را انتخاب کنید. (مستندات مایکروسافت برای این ویژگی تفاوت قائل شده و اشاره دارد که T-SQL آن را پشتیبانی نمی‌کند). Microsoft Learn
برای جلوگیری از هزینه‌های sort سنگین: اگر ممکن است، ایندکس مرتب‌شده برای ستون‌های PARTITION BY/ORDER BY داشته باشید؛ اما باز هم بعضی محاسبات نیاز به sort در زمان اجرا دارند.
هنگام استفاده از توابع آماری و percentile، دقت کنید که NULLها چگونه محاسبه می‌شوند و آیا باید IGNORE NULLS یا معادل آن را پیاده کنید (بعضی توابع دارای گزینه‌ی IGNORE/RESPECT NULLS هستند).
برای به‌روزرسانی براساس نتایج پنجره‌ای (مثلاً حذف یا نگهداری یک رکورد در هر گروه)، معمولاً از CTE با ROW_NUMBER() استفاده کنید: WITH to_keep AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY keycol ORDER BY priority DESC) AS rn FROM MyTable ) DELETE FROM MyTable WHERE keycol IN (SELECT keycol FROM to_keep WHERE rn > 1); (این فقط الگو است؛ در عمل لازم است به PK و شرایط خاص توجه کنید.)

۷) خلاصهٔ سریع (cheat-sheet)

OVER() = تعیین پنجره؛ PARTITION BY = تقسیم، ORDER BY = ترتیب، ROWS|RANGE = فریم. Microsoft Learn+1
توابع مهم: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), SUM()/AVG() OVER(...), PERCENT_RANK(), CUME_DIST(), PERCENTILE_CONT/DISC. Microsoft Learn+1
NTH_VALUE() در T-SQL پشتیبانی نشده — برایش از ROW_NUMBER() استفاده کنید.

Window Functions

Window Function چیست؟

✨ ساختار Window Function

📌 انواع مهم Window Functions

1. Ranking Functions (رتبه‌بندی)

2. Aggregate Functions (محاسبات تجمیعی روی پنجره)

3. Value Functions

🔥 مثال‌ها با تریس

مثال ۱: پیدا کردن دو نفر اول با بالاترین حقوق در هر دپارتمان

مثال ۲: مقایسه حقوق کارمند با حقوق کارمند قبلی در همان دپارتمان

مثال ۳: پیدا کردن میانگین حقوق در هر دپارتمان

مثال ۴: تقسیم کارکنان هر دپارتمان به 4 گروه (کوارتیل)

📝 سوالات تمرینی

دادهٔ نمونه (CREATE/INSERT) — جدول Employees و DailySales

سؤال ۱ — حقوق هر کارمند همراه با میانگین حقوق دپارتمانِ او

سؤال ۲ — بالاترین حقوق هر دپارتمان (بدون GROUP BY)

روش A — فقط مقدار بالاترین حقوق (بدون collapse کردن ردیف‌ها)

روش B — نام (یا ردیف‌های) کارمند(ها) که بالاترین حقوق را دارند (با حفظ tiedها)

سؤال ۳ — تفاوت حقوق هر کارمند با کارمند قبلی (بر اساس تاریخ استخدام در همان دپارتمان)

سؤال ۴ — نفر سوم با بالاترین حقوق در هر دپارتمان — (دو تعبیر و روش)

روش A — سومین شخص (ROW_NUMBER)

روش B — سومین مقدار متمایزِ Salary (DENSE_RANK)

خلاصهٔ فرق‌ها با مثال:

سؤال ۵ — فروش روزانه هر فروشنده همراه با مجموع فروش تجمعی تا آن روز (Running Total)

جمع‌بندی و نکات مهم

توضیح کاملِ خط‌به‌خطِ کوئری TOP-N per group با ROW_NUMBER()

۱. هدفِ کلیِ کوئری (به زبان ساده)

۲. کلمه‌به‌کلمه (توضیح اجزاء)

۳. چرا ROW_NUMBER() را استفاده کردیم؟ (دلایل و ویژگی‌ها)

۴. مقایسه با RANK() و DENSE_RANK() (چرا فرق می‌کنند)

۵. تریسِ گام‌به‌گام با مثال واقعی

گام A — اجرای بخش درون CTE (مرتب‌سازی و شماره‌گذاری)

گام B — فیلتر WHERE rn <= 2

۶. نکات مهم و edge-cases که باید بدانید

۷. خلاصهٔ کاربردی — چه زمانی از این الگو استفاده کنیم؟

1️⃣ Window Functions

2️⃣ Subqueries

3️⃣ GROUP BY

🔑 جمع‌بندی

توابع پنجره (Window Functions) — کامل، دقیق و قابل‌فهم

۱) مفهوم کلی — چه کاری انجام می‌دهند؟

۲) ساختار کلی دستور

۳) دسته‌بندی توابع متداول در SQL Server

۴) قواعد اجرایی و محدودیت‌ها (Gotchas)

۵) مثال‌های کاربردی همراه با تریس (مثال‌ها خط به خط)

مثال ۱ — مقایسه ROW_NUMBER, RANK, DENSE_RANK

مثال ۲ — جمع تجمعی (Running Total) و فرق ROWS vs RANGE

مثال ۳ — LAG و محاسبهٔ تغییر نسبت به ردیف قبلی

مثال ۴ — گرفتن Top-N برای هر گروه (Top-N per group)

مثال ۵ — رتبه درصدی / توزیع (PERCENT_RANK, CUME_DIST)

۶) نکات عملی / بهترین شیوه‌ها

۷) خلاصهٔ سریع (cheat-sheet)

توضیح کاملِ خط‌به‌خطِ کوئری `TOP-N per group` با `ROW_NUMBER()`

۳. چرا `ROW_NUMBER()` را استفاده کردیم؟ (دلایل و ویژگی‌ها)

۴. مقایسه با `RANK()` و `DENSE_RANK()` (چرا فرق می‌کنند)

گام B — فیلتر `WHERE rn <= 2`

مثال ۱ — مقایسه `ROW_NUMBER`, `RANK`, `DENSE_RANK`

مثال ۲ — جمع تجمعی (Running Total) و فرق `ROWS` vs `RANGE`

مثال ۳ — `LAG` و محاسبهٔ تغییر نسبت به ردیف قبلی

مثال ۵ — رتبه درصدی / توزیع (`PERCENT_RANK`, `CUME_DIST`)