آوند — داده: SQL، مدل‌سازی و تحلیل برای تصمیم

فصل ۸ از ۹

پیشرفت ترم
۰٪

در این فصل چه یاد می‌گیری#

فصلِ ۷ داخلِ پنجره ترتیب گذاشت. این فصل یک قدم جلوتر می‌رود: می‌گوید پنجره از کجا تا کجا باشد.

«از اولِ گروه تا همین سطر» یک جمعِ رونده می‌سازد. «سه سطرِ آخر» یک میانگینِ متحرک می‌سازد. و «همین سطر و یکی قبل و یکی بعد» یک نمایِ صاف‌شده.

و یک تله که تا اندازه‌اش نگیری باورت نمی‌شود: وقتی 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 آنباوندد پریسیدینگ از اولِ پنجره

تمرین‌ها

اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.

در فصل بعد#

هشت فصل ابزار جمع کردیم. فصلِ آخرِ این ترم هیچ چیزِ تازه‌ای یاد نمی‌دهد و به‌جایش یک گزارشِ کامل تحویل می‌دهد: یک جدولِ شش‌سطری با دانه‌بندیِ اعلام‌شده، یک ستونِ پنجره‌ای، و سه شمارشِ کنترلیِ متفاوت که اولی‌شان باید ۱٬۲۰۰ و ۹۴٬۱۹۱ بدهد. و همان‌جا معلوم می‌شود که دو شعبه‌ای که با جمعِ خام رتبهٔ پنجم و ششم دارند، با نرخِ هر کتاب جایشان عوض می‌شود.

به آخر این فصل رسیدی!

اگر ساختی و جواب داد، این دکمه مال توست.