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

فصل ۶ از ۹

پیشرفت ترم
۰٪

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

تا اینجا هر تجمیعی یک معامله بود: عددِ گروهی می‌گرفتی و سطرها را از دست می‌دادی. ۱٬۲۰۰ سطر می‌رفت و ۸ سطر برمی‌گشت.

این فصل ابزاری می‌آورد که هر دو را با هم دارد. همان 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 اجرا می‌شود. سه نتیجه دارد و هر سه در عمل به دردت می‌خورند:

  1. پنجره فقط سطرهایی را می‌بیند که از WHERE رد شده‌اند. یعنی «میانگینِ کل» در یک پرسشِ فیلتردار، میانگینِ کلِ فیلترشده است، نه میانگینِ جدول.
  2. پنجره در WHERE و HAVING در دسترس نیست، چون آن‌ها زودتر تمام شده‌اند.
  3. پنجره روی نتیجهٔ 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 تای اولِ هر گروه» و مقایسهٔ دوره‌به‌دوره ممکن می‌شوند. و با یک نمونهٔ آشنا شروع می‌کند: پنج کتابی که هر پنج‌تا دقیقاً ۱۹۰ بار امانت رفته‌اند — یکی به آن‌ها ۱ تا ۵ می‌دهد، یکی به هر پنج‌تا ۱ می‌دهد و سطرِ بعدی را ۶ می‌کند، و یکی به هر پنج‌تا ۱ می‌دهد و سطرِ بعدی را ۲.

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

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