در این فصل چه یاد میگیری#
فصلِ ۷ داخلِ پنجره ترتیب گذاشت. این فصل یک قدم جلوتر میرود: میگوید پنجره از کجا تا کجا باشد.
«از اولِ گروه تا همین سطر» یک جمعِ رونده میسازد. «سه سطرِ آخر» یک میانگینِ متحرک میسازد. و «همین سطر و یکی قبل و یکی بعد» یک نمایِ صافشده.
و یک تله که تا اندازهاش نگیری باورت نمیشود: وقتی ORDER BY داخلِ OVER مینویسی، پنجرهات دیگر کلِ گروه نیست — یک قابِ پیشفرض میگیرد که خودت انتخابش نکردهای. همان پنج کتابی که هر پنجتا دقیقاً ۱۹۰ بار امانت رفتهاند، با قابِ پیشفرض هر پنجتا شمارهٔ ۵ میگیرند و با قابِ سطری ۱ تا ۵.

آخر این فصل میتوانی:
- با
ROWS BETWEENبگویی پنجره از کجا تا کجاست - جمعِ تجمعی بسازی و از خودش یک شمارشِ کنترلی بگیری
- میانگینِ متحرکِ
kتایی بسازی و بگویی سطرهای اولش چرا قابلِاعتماد نیستند - تفاوتِ
ROWSوRANGEرا روی تساوی با عدد نشان بدهی - بگویی صاف کردن چه چیزی را نشان میدهد و چه چیزی را پنهان میکند
قبل از شروع#
از فصلِ ۷: ORDER BY داخلِ OVER، و اینکه بدونِ یک ستونِ یکتا ترتیب تضمینشده نیست.
از فصلِ ۶: پنجره بعد از GROUP BY اجرا میشود، پس میشود SUM(COUNT(*)) OVER (...) نوشت. کلِ این فصل روی همین الگو سوار است.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۶ | آنی |
۱. یک سریِ زمانی برای کار کردن#
فصلِ ۷ سالبهسال کار کرد و فقط هفت سطر داشت. برای دیدنِ قاب به سطرهای بیشتری نیاز داریم، پس همان تاریخ را به فصلِ سهماهه میشکنیم.
عبارتش طولانی است و در هر پرسش تکرار میشود، پس یک بار در پایتون میسازیمش. دقت کن که این متنِ خودِ پرسش است، نه یک مقدار — مقدارها همچنان با ? میروند.
QUARTER = ("strftime('%Y', added_at) || '-Q' || "
"((CAST(strftime('%m', added_at) AS INTEGER) + 2) / 3)")
series = q(f"""
SELECT {QUARTER} AS quarter,
COUNT(*) AS n
FROM books
GROUP BY quarter
ORDER BY quarter
""")
print(series.head(5))
print("سطرها:", len(series), "| جمع:", int(series["n"].sum()))
quarter n
0 2019-Q1 43
1 2019-Q2 46
2 2019-Q3 51
3 2019-Q4 53
4 2020-Q1 51
سطرها: 26 | جمع: 1200
بیستوشش فصلِ سهماهه، و جمعشان ۱٬۲۰۰. یک سطر از این جدول یعنی «یک فصلِ سهماهه» — نه یک کتاب.
و یک خبرِ خوب که در فصلِ ۷ نداشتیم: اینجا هیچ دورهٔ ناقصی نیست. داده در آخرین روزِ یک فصلِ سهماهه تمام میشود، پس هر ۲۶ سطر پوششِ کاملِ سه ماه دارند. این را باید چک میکردیم و کردیم؛ اگر داده وسطِ یک فصل تمام شده بود، هر عددی که در ادامه میسازیم روی سطرِ آخر خراب میشد.
۲. جمعِ تجمعی: از اول تا همین سطر#
ORDER BY داخلِ OVER بگذار و ببین چه اتفاقی میافتد:
running = q(f"""
SELECT {QUARTER} AS quarter,
COUNT(*) AS n,
SUM(COUNT(*)) OVER (ORDER BY {QUARTER}) AS running
FROM books
GROUP BY quarter
ORDER BY quarter
""")
print(running.head(5))
print("...")
print(running.tail(2))
quarter n running
0 2019-Q1 43 43
1 2019-Q2 46 89
2 2019-Q3 51 140
3 2019-Q4 53 193
4 2020-Q1 51 244
...
quarter n running
24 2025-Q1 49 1145
25 2025-Q2 55 1200
ستونِ running در هر سطر جمعِ همهٔ سطرها از اول تا همانجاست، و سطرِ آخرش دقیقاً ۱٬۲۰۰ است.
در فصلِ ۶ همین SUM(COUNT(*)) OVER () بدونِ ORDER BY در هر سطر ۱٬۲۰۰ میداد. اضافه شدنِ ORDER BY پنجره را از «کلِ گروه» به «از اول تا همین سطر» عوض کرد — و این تغییر خودش موضوعِ بخشِ ۴ است.
و همان سطرِ آخر یک شمارشِ کنترلیِ رایگان است: اگر جمعِ تجمعی در سطرِ آخر با COUNT(*) کلِ جدول نخواند، یعنی یا WHERE سطری را انداخته یا GROUP BY گروهی را جا انداخته. هر جا جمعِ تجمعی میسازی، سطرِ آخرش را با کل بسنج.
۳. میانگینِ متحرک، و سطرهایی که میانگینشان kتایی نیست#
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW یعنی «قاب را از دو سطر قبل تا همین سطر بگیر» — سه سطر روی هم.
moving = q(f"""
SELECT {QUARTER} AS quarter,
COUNT(*) AS n,
ROUND(AVG(COUNT(*)) OVER (
ORDER BY {QUARTER}
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_avg,
COUNT(*) OVER (
ORDER BY {QUARTER}
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rows_in_frame
FROM books
GROUP BY quarter
ORDER BY quarter
""")
print(moving.head(5))
quarter n moving_avg rows_in_frame
0 2019-Q1 43 43.00 1
1 2019-Q2 46 44.50 2
2 2019-Q3 51 46.67 3
3 2019-Q4 53 50.00 3
4 2020-Q1 51 51.67 3
به ستونِ آخر نگاه کن: در سطرِ اول قاب فقط یک سطر دارد و در سطرِ دوم دو سطر.
یعنی moving_avg در سطرِ اول اصلاً میانگین نیست — خودِ عدد است. در سطرِ دوم میانگینِ دوتاست. از سطرِ سوم به بعد است که واقعاً یک میانگینِ سهفصلی داری.
SQLite این را خطا نمیدهد و در خروجی هم علامت نمیزند. پس هر بار که میانگینِ متحرک میسازی، ستونِ «چند سطر در قاب بود» را هم بساز — یک COUNT(*) روی همان قاب، رایگان — و در گزارش یا k − 1 سطرِ اول را کنار بگذار یا صریح علامتشان بزن.
این دقیقاً همان تلهٔ «دورهٔ ناقص» فصلِ ۷ است، این بار در ابتدای سری بهجای انتهایش.
✅ چک کن: ستونِ
rows_in_frameباید ۱ و ۲ و بعد همهاش ۳ باشد. اگر همهاش ۱ بود،ROWSرا باRANGEعوض کردهای. اگر از همان سطرِ اول ۳ بود، قاب را رو به آینده نوشتهای، مثلاًCURRENT ROW AND 2 FOLLOWING. در هر دو حالت عددهایت میانگینِ سهفصلیِ گذشته نیستند. جا انداختنِORDER BYداخلِOVERرا این ستون نمیگیرد: باز هم ۱ و ۲ و ۳ میدهد، ولی دیگر چیزی تضمین نمیکند که «دو سطرِ قبل» همان دو فصلِ قبل باشند، پس آن را با چشم چک کن.
۴. قابِ پیشفرض، و تلهٔ آن#
حالا مهمترین نکتهٔ فصل. وقتی ORDER BY داخلِ OVER مینویسی و قاب را صریح نگویی، SQLite یک قابِ پیشفرض میگذارد — و آن پیشفرض RANGE است، نه ROWS.
تفاوتشان فقط سرِ تساوی خودش را نشان میدهد:
ROWSسطرها را میشمارد: «دو سطر قبل» یعنی دقیقاً دو سطر.RANGEمقدارها را میبیند: همهٔ سطرهایی که مقدارِORDER BYشان با سطرِ فعلی برابر است، یکجا داخلِ قاب میآیند.
روی همان پنج کتابِ ۱۹۰تایی:
print(q("""
SELECT id,
borrowed,
COUNT(*) OVER (ORDER BY borrowed DESC) AS default_frame,
COUNT(*) OVER (
ORDER BY borrowed DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS row_frame
FROM books
ORDER BY borrowed DESC, id
LIMIT 7
"""))
id borrowed default_frame row_frame
0 46 190 5 1
1 448 190 5 2
2 601 190 5 3
3 1056 190 5 4
4 1169 190 5 5
5 138 189 10 6
6 392 189 10 7
ستونِ پیشفرض به هر پنج سطرِ مساوی عددِ ۵ میدهد؛ ستونِ سطری ۱ تا ۵.
منطقِ RANGE کاملاً باحساب است: سطرهای مساوی از نظرِ ORDER BY همرتبهاند، پس هیچ دلیلی نیست یکیشان زودتر از دیگری داخلِ قاب بیاید. ولی اگر تو دنبالِ «شمارهٔ سطر» بودی، این عدد دقیقاً همان چیزی نیست که میخواستی.
و دقت کن که سطرِ ششم در ستونِ پیشفرض ۱۰ شد، نه ۶ — چون هر پنج کتابِ ۱۸۹تایی با هم داخلِ قاب آمدند، با اینکه LIMIT 7 فقط دوتایشان را نشان میدهد.
قاعدهٔ عملی این فصل: هر جا ORDER BY داخلِ OVER مینویسی، قاب را هم صریح بنویس. اگر مقدارهای ستونِ ترتیب یکتا باشند، ROWS و RANGE یک جواب میدهند و صریح نوشتن هزینهای ندارد؛ اگر یکتا نباشند، همین یک کلمه جوابت را عوض میکند.
🔧 اگر کار نکرد: دو سرِ قاب باید به ترتیب نوشته شوند — اول ابتدای قاب، بعد انتهایش. برعکسش خطا میدهد:
try:
q("""
SELECT id, SUM(borrowed) OVER (
ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 PRECEDING
) AS bad
FROM books
LIMIT 1
""")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql '
SELECT id, SUM(borrowed) OVER (
ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 PRECEDING
) AS bad
FROM books
LIMIT 1
': unsupported frame specification
unsupported frame specification یعنی «انتهای قاب قبل از ابتدایش افتاده». ترتیبِ درست همیشه از گذشته به آینده است: 2 PRECEDING بعد CURRENT ROW بعد 1 FOLLOWING.
۵. قابی که آینده را هم میبیند#
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING قاب را دورِ سطرِ فعلی وسطچین میکند: یکی قبل، خودش، یکی بعد.
centred = q(f"""
SELECT {QUARTER} AS quarter,
COUNT(*) AS n,
ROUND(AVG(COUNT(*)) OVER (
ORDER BY {QUARTER}
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
), 2) AS centred_avg
FROM books
GROUP BY quarter
ORDER BY quarter
""")
print(centred.head(4))
quarter n centred_avg
0 2019-Q1 43 44.50
1 2019-Q2 46 46.67
2 2019-Q3 51 50.00
3 2019-Q4 53 51.67
عددهای این ستون همان عددهای میانگینِ متحرکِ بخشِ ۳ هستند، یک سطر بالاتر: ۴۴٫۵۰ و ۴۶٫۶۷ و ۵۰٫۰۰. پس میانگینِ وسطچین صافتر نیست؛ فقط قلهها را از جای زمانیشان عقب نمیاندازد. و یک شرطِ سنگین دارد: به سطرِ بعدی نگاه میکند.
اگر دادهات یک سریِ زمانی باشد و بخواهی از این عدد برای تصمیم در همان لحظه استفاده کنی، آن سطرِ بعدی هنوز اتفاق نیفتاده. یعنی این ستون برای مرورِ گذشته درست است و برای تصمیم در لحظه نه.
این تفاوت را همیشه در گزارش بنویس، چون از روی خودِ عدد اصلاً پیدا نیست.
۶. صاف کردن چه چیزی را پنهان میکند#
میانگینِ متحرک نوسان را کم میکند و این دقیقاً کاری است که ازش میخواهی. ولی همان کار، چیزهایی را هم پاک میکند که شاید مهم بودهاند.
compare = q(f"""
SELECT {QUARTER} AS quarter,
COUNT(*) AS n,
ROUND(AVG(COUNT(*)) OVER (
ORDER BY {QUARTER}
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_avg
FROM books
GROUP BY quarter
ORDER BY quarter
""")
print("دامنهٔ ستون خام :", int(compare["n"].min()), "تا", int(compare["n"].max()))
print("دامنهٔ میانگین متحرک :", round(float(compare["moving_avg"].min()), 2),
"تا", round(float(compare["moving_avg"].max()), 2))
print("بزرگترین جهش خام :",
int(max(abs(compare["n"][i] - compare["n"][i - 1]) for i in range(1, len(compare)))))
print("بزرگترین جهش صافشده :",
round(max(abs(compare["moving_avg"][i] - compare["moving_avg"][i - 1])
for i in range(1, len(compare))), 2))
دامنهٔ ستون خام : 34 تا 63
دامنهٔ میانگین متحرک : 37.67 تا 54.33
بزرگترین جهش خام : 20
بزرگترین جهش صافشده : 6.67
دامنه از یک بازهٔ پهن به یک بازهٔ باریک آمد، و بزرگترین جهش تقریباً یکسوم شد.
اگر پرسشت «روندِ کلی چیست؟» باشد، این خوب است. اگر پرسشت «آیا اتفاقِ غیرعادیای افتاد؟» باشد، میانگینِ متحرک همان اتفاق را پاک کرده.
و این دقیقاً همان درسِ فصلِ ۵ است با ابزارِ دیگری: عرضِ سطل شکلِ توزیع را عوض میکرد، طولِ قاب شکلِ روند را. هر دو تصمیمِ توأند، هیچکدام از داده نمیآیند، و هر دو باید در گزارش اعلام شوند.
📏 اندازه بگیر: یک سطر یعنی چه؟ در بخشهای ۱ و ۲ و ۳ و ۵ و ۶ یک سطر یک فصلِ سهماهه است، نه یک کتاب. فقط در بخشِ ۴ برگشتیم به سطحِ کتاب. با کدام شمارشِ دوم سنجیدی؟ سهتا: جمعِ ستونِ
nروی ۲۶ سطر که ۱٬۲۰۰ شد؛ سطرِ آخرِ جمعِ تجمعی که مستقل از آن هم ۱٬۲۰۰ داد؛ و ستونِrows_in_frameکه ثابت کرد قاب واقعاً سه سطری است — مگر در دو سطرِ اول. چه کسی از قلم افتاد؟ دو سطرِ اولِ ستونِ میانگینِ متحرک که میانگینِ سهفصلی نیستند و اگر جدا علامتشان نزنی مثلِ بقیه خوانده میشوند؛ نوسانهایی که صاف کردن پاکشان کرد؛ و همچنان ۲۴۰ سطرِarrivals.csv.
🤖 از دستیارت بپرس: «تفاوتِ
ROWSوRANGEدر قابِ تابعِ پنجره چیست؟» بعد این را بپرس: «اگرORDER BYرا داخلِOVERبنویسم ولی قاب را ننویسم، پیشفرض چیست و روی ستونی با مقدارهای تکراری چه فرقی میکند؟» — جوابِ درست باید بگوید پیشفرضRANGEاست و سطرهای هممقدار را یکجا میآورد؛ اگر جواب گفت «فرقی نمیکند»، همین حالا با پنج کتابِ ۱۹۰تایی ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| frame | فریم | قاب؛ بخشی از پنجره که تابع رویش حساب میکند |
| ROWS BETWEEN | رُوز بیتوین | قاب بر اساسِ شمارشِ سطر |
| RANGE | رنج | قاب بر اساسِ مقدارِ ستونِ ترتیب |
| running total | رانینگ توتال | جمعِ تجمعی از اول تا سطرِ فعلی |
| moving average | موینگ اوریج | میانگینِ متحرکِ kتایی |
| UNBOUNDED PRECEDING | آنباوندد پریسیدینگ | از اولِ پنجره |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
هشت فصل ابزار جمع کردیم. فصلِ آخرِ این ترم هیچ چیزِ تازهای یاد نمیدهد و بهجایش یک گزارشِ کامل تحویل میدهد: یک جدولِ ششسطری با دانهبندیِ اعلامشده، یک ستونِ پنجرهای، و سه شمارشِ کنترلیِ متفاوت که اولیشان باید ۱٬۲۰۰ و ۹۴٬۱۹۱ بدهد. و همانجا معلوم میشود که دو شعبهای که با جمعِ خام رتبهٔ پنجم و ششم دارند، با نرخِ هر کتاب جایشان عوض میشود.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.