در این فصل چه یاد میگیری#
تا اینجا هر تجمیعی یک معامله بود: عددِ گروهی میگرفتی و سطرها را از دست میدادی. ۱٬۲۰۰ سطر میرفت و ۸ سطر برمیگشت.
این فصل ابزاری میآورد که هر دو را با هم دارد. همان AVG و همان SUM، ولی این بار خروجی هنوز ۱٬۲۰۰ سطر دارد و کنارِ هر کتاب، میانگینِ دستهٔ خودش نشسته است. با همان یک پرسش میشود پرسید «این کتاب نسبت به دستهٔ خودش چطور است؟» — پرسشی که با GROUP BY اصلاً قابلِبیان نبود، چون بعد از گروهبندی دیگر کتابی وجود نداشت.
تنها چیزی که به پرسش اضافه میشود یک کلمه است: OVER.

آخر این فصل میتوانی:
- با
OVER ()یک عددِ کلی را کنارِ هر سطر بیاوری - با
PARTITION BYهمان کار را برای هر گروه جدا انجام بدهی - تفاوتِ بنیادیِ تابعِ پنجره و
GROUP BYرا با تعدادِ سطرها نشان بدهی - سهمِ هر سطر از کلِ گروهش را در همان سطر حساب کنی
- بگویی تابعِ پنجره کجای ترتیبِ منطقیِ اجرا مینشیند و چرا در
WHEREکار نمیکند
قبل از شروع#
از فصلِ ۲: ترتیبِ منطقیِ اجرا. کلِ رفتارِ عجیبِ این فصل از یک نکته بیرون میآید: تابعِ پنجره دیرتر از هر چیزی که تا امروز دیدهای اجرا میشود.
از فصلِ ۴: سهم از کل با زیرپرسشِ اسکالر. همان کار را این فصل سادهتر انجام میدهد.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۶ | آنی |
۱. پرسشی که با GROUP BY نمیشود پرسید#
پرسش: «این کتاب نسبت به میانگینِ دستهٔ خودش چقدر بالاتر یا پایینتر است؟»
برای جواب دادن دو چیز لازم داری که تا امروز نمیشد با هم داشت: borrowed این سطر، و AVG(borrowed) کلِ دسته. GROUP BY دومی را میدهد و اولی را میبلعد.
OVER همان دومی را بدونِ بلعیدنِ اولی میدهد:
print(q("""
SELECT id,
category,
borrowed,
ROUND(AVG(borrowed) OVER (), 4) AS all_avg
FROM books
ORDER BY id
LIMIT 4
"""))
print("تعداد سطرهای خروجی:",
len(q("SELECT id, AVG(borrowed) OVER () AS m FROM books")))
id category borrowed all_avg
0 1 رمان 33 78.4925
1 2 شعر 20 78.4925
2 3 علم 1 78.4925
3 4 تاریخ 86 78.4925
تعداد سطرهای خروجی: 1200
ستونِ آخر در هر چهار سطر همان ۷۸٫۴۹۲۵ است — همان عددی که فصلِ ۳ روی کلِ جدول گرفت — و خروجی هنوز ۱٬۲۰۰ سطر دارد.
OVER () یعنی «این تابع را روی یک پنجره حساب کن، و آن پنجره کلِ نتیجه است». پرانتزِ خالی همان «کل» است.
و این دقیقاً همان AVG است، نه یک تابعِ تازه. هر تابعِ تجمیعی که تا اینجا دیدی — COUNT و SUM و AVG و MIN و MAX — با اضافه شدنِ OVER به یک تابعِ پنجره تبدیل میشود.
۲. تفاوتِ بنیادی با GROUP BY، با عدد#
دو پرسش که دقیقاً یک عدد تولید میکنند و دو چیزِ کاملاً متفاوت برمیگردانند:
grouped = q("SELECT category, AVG(borrowed) AS m FROM books GROUP BY category")
windowed = q("SELECT id, category, AVG(borrowed) OVER (PARTITION BY category) AS m FROM books")
print("GROUP BY → سطرهای خروجی:", len(grouped))
print("PARTITION → سطرهای خروجی:", len(windowed))
print()
print("میانگینهای متفاوت در هر دو:",
len(set(round(float(v), 6) for v in grouped["m"])),
"و",
len(set(round(float(v), 6) for v in windowed["m"])))
GROUP BY → سطرهای خروجی: 8
PARTITION → سطرهای خروجی: 1200
میانگینهای متفاوت در هر دو: 8 و 8
هشت سطر در برابرِ ۱٬۲۰۰ سطر، و در هر دو دقیقاً هشت میانگینِ متفاوت.
این کوتاهترین توصیفِ تفاوت است: GROUP BY سطرها را در هم میریزد و بهجایشان یک سطر میگذارد؛ تابعِ پنجره سطرها را دست نمیزند و فقط یک ستون اضافه میکند. عددهای هر دو یکیاند؛ دانهبندیِ خروجی فرق میکند، و در این دوره دانهبندی همیشه مهمتر از عدد است.
پس کدام را کِی؟ اگر گزارشت قرار است یک سطر برای هر دسته داشته باشد، GROUP BY. اگر قرار است هر سطر بماند و کنارش یک عددِ زمینهای بنشیند، تابعِ پنجره.
۳. PARTITION BY: یک پنجره برای هر گروه#
PARTITION BY category یعنی «پنجره را نشکن روی کلِ جدول؛ برای هر دسته یک پنجرهٔ جدا بساز».
print(q("""
SELECT id,
category,
borrowed,
ROUND(AVG(borrowed) OVER (PARTITION BY category), 2) AS cat_avg,
COUNT(*) OVER (PARTITION BY category) AS cat_books,
borrowed - ROUND(AVG(borrowed) OVER (PARTITION BY category), 2) AS gap
FROM books
ORDER BY id
LIMIT 4
"""))
id category borrowed cat_avg cat_books gap
0 1 رمان 33 82.90 146 -49.90
1 2 شعر 20 73.82 164 -53.82
2 3 علم 1 71.88 144 -70.88
3 4 تاریخ 86 71.28 145 14.72
حالا هر سطر میداند دستهٔ خودش چه شکلی است. کتابِ اول ۳۳ بار امانت رفته در حالی که میانگینِ رمانها ۸۲٫۹۰ است، پس تقریباً پنجاه واحد زیرِ میانگینِ دستهٔ خودش نشسته.
و دقت کن که هر سه ستونِ پنجرهای، عددشان از سطرهایی میآید که در خروجی هم هستند. هیچ سطری حذف نشد؛ فقط برای هر سطر نگاهی به همسایههایش انداختیم.
✅ چک کن: ستونِ
cat_booksرا با جدولِ گروهیِ فصلِ ۲ بسنج. برای «رمان» باید ۱۴۶ باشد، برای «شعر» ۱۶۴. و یک کنترلِ قویتر: مقدارهای متفاوتِ ستونِcat_booksباید دقیقاً هشتتا باشند و جمعِ آن هشت مقدار ۱٬۲۰۰. اگر جمع ۱٬۲۰۰ نشد،PARTITION BYرا روی ستونی نوشتهای که فکر میکردی نوشتهای.
۴. سهم از کل، در همان سطر#
فصلِ ۴ سهمِ هر دسته از کل را با یک زیرپرسشِ اسکالر حساب کرد. حالا میشود سهمِ هر کتاب از دستهٔ خودش را گرفت، در همان سطر و بدونِ هیچ زیرپرسشی:
print(q("""
SELECT id,
borrowed,
SUM(borrowed) OVER (PARTITION BY category) AS cat_loans,
ROUND(borrowed * 100.0 / SUM(borrowed) OVER (PARTITION BY category), 3) AS share
FROM books
WHERE category = ?
ORDER BY borrowed DESC, id
LIMIT 4
""", ("زندگینامه",)))
id borrowed cat_loans share
0 601 190 11889 1.598
1 593 189 11889 1.590
2 942 189 11889 1.590
3 171 185 11889 1.556
پرامانتترین کتابِ این دسته ۱٫۶ درصدِ کلِ امانتهای دسته را به خودش گرفته.
دو نکتهٔ لازم دربارهٔ همین ستون. اول اینکه * 100.0 سرِ جایش است — بدونش همان تقسیمِ صحیحِ فصلِ ۴ صفر میداد. دوم اینکه مخرجِ این درصد کلِ امانتهای همان دسته است، نه کلِ کتابخانه، و این یک تصمیم است که باید در گزارش نوشته شود. اگر WHERE را هم برداری و PARTITION BY را هم، همان ستون به سهم از کلِ کتابخانه تبدیل میشود و عددها حدودِ هشت برابر کوچکتر. برداشتنِ PARTITION BY بهتنهایی هیچ عددی را عوض نمیکند، چون WHERE از قبل فقط همین یک دسته را نگه داشته؛ بخشِ ۵ میگوید چرا.
۵. پنجره کجای ترتیبِ اجرا مینشیند#
فصلِ ۲ ترتیبِ منطقیِ اجرا را داد. تابعِ پنجره یک ایستگاهِ تازه به آن اضافه میکند، و جایش دقیقاً همان چیزی است که بقیهٔ رفتارهایش را توضیح میدهد:
FROM → WHERE → GROUP BY → HAVING → پنجرهها → SELECT → ORDER BY → LIMIT
پنجره بعد از WHERE و GROUP BY و HAVING اجرا میشود. سه نتیجه دارد و هر سه در عمل به دردت میخورند:
- پنجره فقط سطرهایی را میبیند که از
WHEREرد شدهاند. یعنی «میانگینِ کل» در یک پرسشِ فیلتردار، میانگینِ کلِ فیلترشده است، نه میانگینِ جدول. - پنجره در
WHEREوHAVINGدر دسترس نیست، چون آنها زودتر تمام شدهاند. - پنجره روی نتیجهٔ
GROUP BYمینشیند، نه روی سطرهای اصلی — که بخشِ ۶ همین را به یک ابزارِ خیلی مفید تبدیل میکند.
نکتهٔ اول را همین حالا اندازه میگیریم:
print(q("""
SELECT COUNT(*) OVER () AS rows_in_window,
ROUND(AVG(borrowed) OVER (), 4) AS window_avg
FROM books
WHERE category = ?
LIMIT 1
""", ("شعر",)))
print()
print("میانگین کل جدول:", float(q("SELECT AVG(borrowed) FROM books").iloc[0, 0]))
rows_in_window window_avg
0 164 73.8171
میانگین کل جدول: 78.4925
OVER () اینجا «کلِ جدول» نیست، «کلِ ۱۶۴ سطری است که از WHERE رد شدند». اسمِ OVER () گمراهکننده است و این یکی از رایجترین سوءبرداشتهاست.
🔧 اگر کار نکرد: طبیعیترین کاری که هر کسی بعد از دیدنِ تابعِ پنجره میخواهد بکند این است که رویش فیلتر بگذارد. کار نمیکند:
try:
q("SELECT id FROM books WHERE AVG(borrowed) OVER () > ? LIMIT 3", (50,))
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql 'SELECT id FROM books WHERE AVG(borrowed) OVER () > ? LIMIT 3': misuse of window function AVG()
misuse of window function یعنی «این را جایی گذاشتهای که هنوز محاسبه نشده». دقیقاً همان شکلِ خطای misuse of aggregate در فصلِ ۲، یک ایستگاه جلوتر.
راهش این است که پرسش را دو مرحله کنی: اول پنجره را حساب کن، بعد روی نتیجهاش فیلتر بگذار. فصلِ ۷ همین کار را با یک زیرپرسش در FROM انجام میدهد و آنجا دقیقاً میگوید چه چیزی به ترمِ ۳ بدهکار میماند.
۶. پنجره روی نتیجهٔ گروهبندی#
چون پنجره بعد از GROUP BY اجرا میشود، میشود یک تابعِ پنجره را روی خروجیِ یک تابعِ تجمیعی گذاشت. در نگاهِ اول عجیب به نظر میرسد و در عمل پرکاربردترین الگوی این فصل است:
print(q("""
SELECT category,
COUNT(*) AS n,
SUM(COUNT(*)) OVER () AS all_books,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM books
GROUP BY category
ORDER BY n DESC
"""))
category n all_books pct
0 کودک 167 1200 13.92
1 شعر 164 1200 13.67
2 هنر 161 1200 13.42
3 رمان 146 1200 12.17
4 تاریخ 145 1200 12.08
5 علم 144 1200 12.00
6 فلسفه 137 1200 11.42
7 زندگینامه 136 1200 11.33
SUM(COUNT(*)) OVER () را از داخل به بیرون بخوان: اول GROUP BY هشت سطر میسازد و در هر سطر COUNT(*) را حساب میکند؛ بعد پنجره روی همان هشت سطر مینشیند و جمعشان را میگیرد. جوابش ۱٬۲۰۰ است — همان COUNT(*) کلِ جدول، ولی از راهی کاملاً متفاوت.
و همین دو مسیر، یک شمارشِ دومِ رایگان است: اگر SUM(COUNT(*)) OVER () با COUNT(*) کلِ جدول نخواند، یعنی WHERE یا GROUP BY سطری را انداخته بیرون.
ستونِ آخر همان درصدهای فصلِ ۴ است — ۱۳٫۹۲ و ۱۳٫۶۷ و بقیه — که آنجا با یک زیرپرسشِ اسکالر گرفتیمشان و اینجا بدونِ هیچ زیرپرسشی. دو مسیر، یک جواب.
📏 اندازه بگیر: یک سطر یعنی چه؟ در بخشهای ۱ تا ۵ یک سطر همچنان یک کتاب است — و این کلِ نکتهٔ تابعِ پنجره است. فقط در بخشِ ۶ که
GROUP BYهم در کار است، یک سطر یک دسته میشود. با کدام شمارشِ دوم سنجیدی؟ سهتا: تعدادِ سطرهای خروجیِ دو مسیر (۸ در برابرِ ۱٬۲۰۰) با تعدادِ مقدارهای متفاوتِ میانگین (۸ در هر دو)؛ ستونِcat_booksدر برابرِ جدولِ گروهیِ فصلِ ۲؛ وSUM(COUNT(*)) OVER ()که ۱٬۲۰۰ داد، همان عددِCOUNT(*)کلِ جدول. چه کسی از قلم افتاد؟ هر سطری کهWHEREانداخته باشدش بیرون، از خودِ پنجره هم بیرون است — در بخشِ ۵ دیدیم که میانگینِ «کل» آنجا میانگینِ ۱۶۴ سطر بود، نه ۱٬۲۰۰ سطر؛ و همچنان ۲۴۰ سطرِarrivals.csv.
🤖 از دستیارت بپرس: «تفاوتِ یک تابعِ تجمیعی و همان تابع با
OVERچیست؟» بعد این را بپرس: «اگر پرسشمWHEREداشته باشد،AVG(x) OVER ()میانگینِ کلِ جدول را میدهد یا میانگینِ سطرهای فیلترشده را؟» — جوابِ درست «سطرهای فیلترشده» است و باید به ترتیبِ اجرا اشاره کند؛ اگر جواب گفت «کلِ جدول»، همین حالا با عددهای بخشِ ۵ ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| window function | ویندو فانکشن | تابعی که روی گروهی از سطرها حساب میکند بدونِ اینکه سطرها را یکی کند |
| OVER | اوور | بندی که پنجره را تعریف میکند |
| PARTITION BY | پارتیشن بای | تقسیمِ سطرها به پنجرههای جدا |
| partition | پارتیشن | همان پنجره؛ گروهی که تابع رویش حساب میکند |
| running context | رانینگ کانتکست | عددِ زمینهای که کنارِ هر سطر مینشیند |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
پنجره تا اینجا بیترتیب بود: یک عدد برای کلِ گروه. فصلِ بعد داخلِ پنجره ترتیب میگذارد، و با همان یک تغییر، رتبهبندی و «N تای اولِ هر گروه» و مقایسهٔ دورهبهدوره ممکن میشوند. و با یک نمونهٔ آشنا شروع میکند: پنج کتابی که هر پنجتا دقیقاً ۱۹۰ بار امانت رفتهاند — یکی به آنها ۱ تا ۵ میدهد، یکی به هر پنجتا ۱ میدهد و سطرِ بعدی را ۶ میکند، و یکی به هر پنجتا ۱ میدهد و سطرِ بعدی را ۲.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.