در این فصل چه یاد میگیری#
دو قفسه، هرکدام پنج کتاب. میانگینِ امانتشان دقیقاً برابر است: هر دو ۸۰. میانهشان هم دقیقاً برابر است: هر دو ۸۰. و وقتی به خودِ ده سطر نگاه کنی، هیچ شباهتی به هم ندارند — در یکی همهٔ کتابها بینِ ۷۴ و ۸۶ امانت رفتهاند، در دیگری دو کتاب تقریباً هیچوقت بیرون نرفتهاند و دو کتاب بیش از ۱۵۰ بار.
بعد همین را روی جدولِ واقعی میبریم و به عددی میرسیم که در نگاهِ اول باورنکردنی است: میانگینِ میانگینهای همین جدول ۴۸٫۹۷ درمیآید، در حالی که میانگینِ واقعیِ همان ستون ۷۸٫۴۹ است. هیچکدام هم اشتباه محاسبه نشدهاند.

آخر این فصل میتوانی:
- نشان بدهی دو مجموعهٔ کاملاً متفاوت میتوانند میانگین و میانهٔ یکسان داشته باشند
- دامنه و پراکندگی را کنارِ هر میانگینی گزارش کنی
- میانه را در
SQLبگیری، بدونِ اینکه تابعی برایش وجود داشته باشد - بگویی چرا میانگینِ میانگینها معمولاً غلط است و با میانگینِ وزنی درستش کنی
- اثرِ یک مقدارِ پرت را روی میانگین و میانه با عدد نشان بدهی
قبل از شروع#
از فصلِ ۲: GROUP BY و HAVING، ترتیبِ منطقیِ اجرا، و اینکه یک سطرِ خروجی بعد از گروهبندی دیگر «یک کتاب» نیست.
از ترمِ ۱: تقسیمِ صحیح — 7/2 در SQL میشود ۳. در این فصل دو بار به کارمان میآید.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۶ | آنی |
shelves (همین فصل میسازدش) |
۱۰ | آنی |
۱. ده سطری که با انگشت میشود شمرد#
قبل از هر ادعایی روی ۱٬۲۰۰ سطر، همان پدیده را روی جدولی میسازیم که کلش جلوی چشمت جا میشود.
con.execute("DROP TABLE IF EXISTS shelves")
con.execute("""
CREATE TABLE shelves (
shelf TEXT NOT NULL,
title TEXT NOT NULL,
borrowed INTEGER NOT NULL
)
""")
con.executemany("INSERT INTO shelves VALUES (?, ?, ?)", [
("چپ", "کتاب ۱", 74), ("چپ", "کتاب ۲", 77), ("چپ", "کتاب ۳", 80),
("چپ", "کتاب ۴", 83), ("چپ", "کتاب ۵", 86),
("راست", "کتاب ۶", 2), ("راست", "کتاب ۷", 5), ("راست", "کتاب ۸", 80),
("راست", "کتاب ۹", 153), ("راست", "کتاب ۱۰", 160),
])
con.commit()
print(q("SELECT shelf, title, borrowed FROM shelves ORDER BY shelf DESC, borrowed"))
shelf title borrowed
0 چپ کتاب ۱ 74
1 چپ کتاب ۲ 77
2 چپ کتاب ۳ 80
3 چپ کتاب ۴ 83
4 چپ کتاب ۵ 86
5 راست کتاب ۶ 2
6 راست کتاب ۷ 5
7 راست کتاب ۸ 80
8 راست کتاب ۹ 153
9 راست کتاب ۱۰ 160
ده سطر، همهشان دیده میشوند. حالا خلاصهشان کنیم:
print(q("""
SELECT shelf,
COUNT(*) AS books,
SUM(borrowed) AS loans,
AVG(borrowed) AS mean
FROM shelves
GROUP BY shelf
"""))
shelf books loans mean
0 راست 5 400 80.0
1 چپ 5 400 80.0
یک جدولِ دوسطریِ کاملاً بیآزار: دو قفسه، هر دو پنج کتاب، هر دو ۴۰۰ امانت، هر دو میانگینِ ۸۰.
اگر گزارشت همین دو سطر باشد، خوانندهات نتیجه میگیرد که این دو قفسه مثلِ هماند. و همین حالا خودت ده سطرِ بالا را دیدهای و میدانی که نیستند.
«خب، میانه را هم اضافه کن.» میکنیم — و کمکی نمیکند:
for name in ("چپ", "راست"):
middle = int(q("""
SELECT borrowed FROM shelves WHERE shelf = ?
ORDER BY borrowed LIMIT 1 OFFSET 2
""", (name,)).iloc[0, 0])
print(f"قفسهٔ {name:<5} → میانه: {middle}")
قفسهٔ چپ → میانه: 80
قفسهٔ راست → میانه: 80
میانگین برابر، میانه هم برابر، و داده هیچ شباهتی ندارد. این تمامِ ادعای این فصل است، و ثابت شد — نه با استدلال، با ده سطر که خودت شمردی.
۲. چیزی که میانگین نگفت: بشمار#
راهِ دیدنِ تفاوت این است که مقدارها را دستهبندی کنی و هر دسته را بشماری. تقسیمِ صحیحِ ترمِ ۱ دقیقاً همین کار را میکند: borrowed / 50 اعشار را دور میریزد، پس هر عددِ بینِ ۰ و ۴۹ میشود صفر، بینِ ۵۰ و ۹۹ میشود یک، و همینطور. ضربش در ۵۰ فقط برچسبِ خواناتری میسازد.
print(q("""
SELECT shelf,
(borrowed / 50) * 50 AS bucket,
COUNT(*) AS n
FROM shelves
GROUP BY shelf, bucket
ORDER BY shelf DESC, bucket
"""))
shelf bucket n
0 چپ 50 5
1 راست 0 2
2 راست 50 1
3 راست 150 2
حالا تفاوت را میبینی. قفسهٔ چپ یک سطر دارد: هر پنج کتابش در یک دستهاند. قفسهٔ راست سه سطر دارد و کتابهایش در دو سرِ دور نشستهاند و وسط تقریباً خالی است.
همان دو عددِ خلاصه، و سه سطر بهجای یک سطر. این ارزانترین کاری است که میشود قبل از باور کردنِ یک میانگین انجام داد، و فصلِ ۵ همین نگاه را به یک ابزارِ کامل تبدیل میکند.
۳. دو عددی که همیشه باید کنارِ میانگین بروند#
اولی دامنه است: بیشینه منهای کمینه. رایگان است، چون MIN و MAX را از فصلِ ۱ داری.
دومی پراکندگی است: بهطورِ متوسط هر سطر چقدر از میانگین دور است. SQLite تابعِ آمادهای برایش ندارد، ولی یک راهِ یکمرحلهای هست که فقط از AVG استفاده میکند: میانگینِ مربعها منهای مربعِ میانگین. جوابش «واریانس» است و ریشهٔ دومش همان پراکندگی.
import math
spread = q("""
SELECT shelf,
MIN(borrowed) AS lo,
MAX(borrowed) AS hi,
MAX(borrowed) - MIN(borrowed) AS span,
AVG(borrowed * borrowed) - AVG(borrowed) * AVG(borrowed) AS variance
FROM shelves
GROUP BY shelf
""")
spread["spread"] = spread["variance"].apply(lambda v: round(math.sqrt(v), 2))
print(spread[["shelf", "lo", "hi", "span", "spread"]])
shelf lo hi span spread
0 راست 2 160 158 68.47
1 چپ 74 86 12 4.24
دامنه ۱۲ در برابرِ ۱۵۸، و پراکندگی ۴٫۲۴ در برابرِ ۶۸٫۴۷. حالا دو قفسه با یک نگاه از هم جدا میشوند، و هر دو عدد از همان پرسشی درآمدند که میانگین را داد.
آن «میانگینِ مربعها منهای مربعِ میانگین» عجیب به نظر میرسد، چون راهِ طبیعیِ فکر کردن به پراکندگی این نیست. راهِ طبیعی دو مرحله دارد: اول میانگین را حساب کن، بعد برگرد و ببین هر سطر چقدر از آن میانگین فاصله دارد. همان کار است و همان جواب؛ فقط راهِ بالا یک بار از داده رد میشود و راهِ طبیعی دو بار — و چون AVG بدونِ اینکه اول میانگین را بداند نمیتواند فاصلهها را حساب کند، در یک پرسشِ سادهٔ بدونِ زیرپرسش فقط راهِ یکمرحلهای جواب میدهد.
و ادعای «همان جواب» را باور نمیکنیم؛ میشماریمش. همان قانونِ دوم دوره، این بار روی خودِ فرمول:
for name in ("چپ", "راست"):
values = [int(v) for v in q(
"SELECT borrowed FROM shelves WHERE shelf = ? ORDER BY borrowed", (name,)
)["borrowed"]]
mean = sum(values) / len(values)
variance = sum((value - mean) ** 2 for value in values) / len(values)
print("قفسهٔ %-5s میانگین %5.1f | واریانس %8.2f | پراکندگی %5.2f" %
(name, mean, variance, variance ** 0.5))
قفسهٔ چپ میانگین 80.0 | واریانس 18.00 | پراکندگی 4.24
قفسهٔ راست میانگین 80.0 | واریانس 4687.60 | پراکندگی 68.47
۴٫۲۴ و ۶۸٫۴۷ — عیناً همان دو عددی که پرسشِ SQL داد. یک محاسبه که میانگین را میگیرد و بعد فاصلهها را مربع میکند، و یک محاسبه که هیچ فاصلهای حساب نمیکند — و به یک عدد میرسند.
🌱 ریشهاش کجاست: میانگین یک عددِ وسط است و بهتنهایی چیزی دربارهٔ پهنای داده نمیگوید؛ دو مجموعه با میانگینِ یکسان میتوانند یکی فشرده باشند و یکی پخش. ریشه ترمِ ۶ فصل ۶ با یک مثالِ حقوقِ دهنفره از اول بازش میکند، و پراکندگی را دقیقاً به همان راهِ دومرحلهای تعریف میکند که در کدِ بالا نوشتیم: فاصله از میانگین، مربع، میانگین، ریشه. اگر آنجا فرمولی دیدی که با پرسشِ
SQLاین فصل یکی نیست، همان دو عددِ بالا جوابت را دادهاند.
حالا همان نگاه روی جدولِ واقعی — و نتیجهاش یک هشدارِ صادقانه است:
real = q("""
SELECT branch,
COUNT(*) AS n,
ROUND(AVG(borrowed), 2) AS mean,
MIN(borrowed) AS lo,
MAX(borrowed) AS hi
FROM books
GROUP BY branch
ORDER BY mean DESC
""")
print(real)
branch n mean lo hi
0 نسیم 224 81.98 0 190
1 بهار 214 81.77 0 190
2 کوشا 200 77.71 0 190
3 پویا 195 77.55 0 190
4 مرکزی 180 76.54 0 188
5 دانش 187 74.26 0 185
دامنه اینجا تقریباً هیچچیز نمیگوید، چون در هر شش شعبه هم کتابی هست که هرگز امانت نرفته و هم کتابی که نزدیکِ ۱۹۰ بار رفته.
این یک نتیجهٔ منفی است و باید همانقدر صریح نوشته شود که یک نتیجهٔ مثبت: دامنه فقط به دو سطرِ لبه نگاه میکند، و دو سطرِ لبه در دادهٔ بزرگ تقریباً همیشه لبهاند. ابزارِ درست برای دادهٔ بزرگ همان شمارشِ دستهای بخشِ ۲ است.
۴. میانه، وقتی تابعی برایش وجود ندارد#
🔧 اگر کار نکرد: اولین کاری که هر کسی میکند این است که تابعِ آشنا را صدا بزند:
try:
q("SELECT MEDIAN(borrowed) FROM books")
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql 'SELECT MEDIAN(borrowed) FROM books': no such function: MEDIAN
SQLite تابعِ MEDIAN ندارد. راهِحل همان ابزارِ ترمِ ۱ فصلِ ۴ است: مرتب کن و سطرِ وسط را بردار. برای ۱٬۲۰۰ سطر، «وسط» بینِ سطرِ ۶۰۰ و ۶۰۱ است — یعنی OFFSET 599 و OFFSET 600.
two = q("SELECT borrowed FROM books ORDER BY borrowed LIMIT 2 OFFSET 599")
median = (int(two["borrowed"][0]) + int(two["borrowed"][1])) / 2
mean = float(q("SELECT AVG(borrowed) FROM books").iloc[0, 0])
print("دو سطر وسط:", list(two["borrowed"]))
print("میانه :", median)
print("میانگین:", mean)
print("فاصله :", round(mean - median, 4))
دو سطر وسط: [76, 77]
میانه : 76.5
میانگین: 78.4925
فاصله : 1.9925
میانگین کمی بالاتر از میانه است، و این چیزِ تازهای است که روی ده سطرِ بخشِ ۱ ندیدیم، چون آنجا میانگین و میانه برابر بودند: وقتی میانگین از میانه بالاتر میافتد، معمولاً یعنی دُمِ داده بهسمتِ بالا کشیده شده. فصلِ ۵ شکلِ این ستون را کامل نشان میدهد.
✅ چک کن: عددِ
OFFSETرا خودت حساب کن، حفظش نکن. برایnسطرِ زوج، دو سطرِ وسطOFFSET n/2 - 1وOFFSET n/2هستند؛ برایnفرد، یک سطر باOFFSET (n-1)/2. و حتماً یک ستونِ یکتا بهORDER BYاضافه کن وقتی میخواهی همیشه همان سطر را بگیری — ترمِ ۱ فصلِ ۴ نشان داد چرا.
۵. میانگینِ میانگینها#
حالا خطایی که در گزارشهای واقعی از همه رایجتر است. جدول را به دو گروه میشکنیم — کتابهای امتیازدار و بیامتیاز — و میانگینِ هر گروه را میگیریم:
groups = q("""
SELECT rating IS NULL AS unrated,
COUNT(*) AS n,
AVG(borrowed) AS mean
FROM books
GROUP BY unrated
""")
print(groups)
print()
print("میانگین این دو میانگین:", (float(groups["mean"][0]) + float(groups["mean"][1])) / 2)
print("میانگین روی کل جدول :", float(q("SELECT AVG(borrowed) FROM books").iloc[0, 0]))
unrated n mean
0 0 969 96.971104
1 1 231 0.978355
میانگین این دو میانگین: 48.97472960476056
میانگین روی کل جدول : 78.4925
۴۸٫۹۷ در برابرِ ۷۸٫۴۹ — سی واحد اختلاف، و هر دو عدد از همان ۱٬۲۰۰ سطر آمدند.
دلیلش این است که دو گروه هماندازه نیستند: یکی ۹۶۹ سطر دارد و دیگری ۲۳۱ سطر. وقتی دو میانگین را ساده جمع میکنی و بر دو تقسیم میکنی، به هر گروه وزنِ برابر دادهای — یعنی هر یک از آن ۲۳۱ کتاب را چهار برابرِ هر یک از آن ۹۶۹ کتاب مهم شمردهای، بدونِ اینکه چنین تصمیمی گرفته باشی.
🌱 ریشهاش کجاست: این همان قاعدهای است که در جمعِ کسرها یاد میگیری: دو کسر با مخرجهای نابرابر را نمیشود مستقیم جمع کرد، چون تکههایشان هماندازه نیستند. میانگین هم یک کسر است، و میانگینِ دو میانگین دقیقاً همان جمعِ ممنوع است. اگر این نگاه برایت تازه است، ریشه ترمِ ۲ فصل ۲ با دو پیتزای نابرابر از اول بازش میکند.
۶. میانگینِ وزنی: همان دو میانگین، جوابِ درست#
اگر فقط جدولِ گروهی را داشته باشی و دیگر به سطرهای اصلی دسترسی نداشته باشی — که در گزارشهای واقعی خیلی پیش میآید — باز هم میشود میانگینِ درست را بازساخت: هر میانگین را در تعدادِ سطرهایش ضرب کن، جمع بزن، و بر جمعِ تعدادها تقسیم کن.
top = float(groups["mean"][0]) * int(groups["n"][0])
bottom = float(groups["mean"][1]) * int(groups["n"][1])
total_rows = int(groups["n"][0]) + int(groups["n"][1])
print("جمع امانتهای گروه اول :", round(top, 4))
print("جمع امانتهای گروه دوم :", round(bottom, 4))
print("میانگین وزنی :", (top + bottom) / total_rows)
print("میانگین روی کل جدول :", float(q("SELECT AVG(borrowed) FROM books").iloc[0, 0]))
print("جمع سطرها :", total_rows)
جمع امانتهای گروه اول : 93965.0
جمع امانتهای گروه دوم : 226.0
میانگین وزنی : 78.4925
میانگین روی کل جدول : 78.4925
جمع سطرها : 1200
دقیقاً همان ۷۸٫۴۹۲۵ درآمد. میانگینِ وزنی یک ترفند نیست؛ فقط همان کاری است که AVG از اول میکرد — جمعِ کل تقسیم بر تعدادِ کل — که این بار از دو تکه بازسازی شد.
و همینجا یک قاعدهٔ عملی بردار: هر جدولِ گروهی که تحویل میدهی باید ستونِ COUNT(*) داشته باشد، حتی وقتی کسی نخواسته. بدونِ آن ستون، هیچکس نمیتواند میانگینهای تو را درست با هم ترکیب کند.
۷. یک سطر که میانگین را جابهجا میکند#
آخرین آزمایش. به قفسهٔ چپ — همان که همهٔ کتابهایش بینِ ۷۴ و ۸۶ بودند — یک کتابِ استثنایی اضافه میکنیم:
con.execute("INSERT INTO shelves VALUES (?, ?, ?)", ("چپ", "کتاب پرت", 900))
con.commit()
rows = list(q("SELECT borrowed FROM shelves WHERE shelf = ? ORDER BY borrowed", ("چپ",))["borrowed"])
mean_now = float(q("SELECT AVG(borrowed) FROM shelves WHERE shelf = ?", ("چپ",)).iloc[0, 0])
middle_two = q("""
SELECT borrowed FROM shelves WHERE shelf = ?
ORDER BY borrowed LIMIT 2 OFFSET 2
""", ("چپ",))
print("شش سطر قفسهٔ چپ:", rows)
print("میانگین: ۸۰ →", round(mean_now, 2))
print("میانه : ۸۰ →", (int(middle_two["borrowed"][0]) + int(middle_two["borrowed"][1])) / 2)
شش سطر قفسهٔ چپ: [74, 77, 80, 83, 86, 900]
میانگین: ۸۰ → 216.67
میانه : ۸۰ → 81.5
یک سطر از شش سطر، و میانگین بیش از دو برابر شد؛ میانه یکونیم واحد تکان خورد.
این تفاوتِ بنیادیِ آن دو است: میانگین مقدارِ هر سطر را میبیند، پس یک مقدارِ بزرگ کلش را میکشد. میانه فقط ترتیب را میبیند، پس برایش فرقی ندارد آن سطر ۹۰۰ باشد یا ۹٬۰۰۰٬۰۰۰.
هیچکدام «بهتر» نیست و انتخاب به پرسشت بستگی دارد. اگر میپرسی «کلِ بار چقدر بود؟» میانگین جوابِ درست است، چون آن یک کتابِ پرامانت واقعاً اتفاق افتاده. اگر میپرسی «یک کتابِ معمولیِ این قفسه چقدر امانت میرود؟» میانه جوابِ درست است.
📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ
shelvesیک سطر یک کتاب است، در جدولِ خلاصه یک قفسه، و در بخشِ ۵ یک گروه که هزار و دویست کتاب را به دو تکه کرده. سه دانهبندی در یک فصل. با کدام شمارشِ دوم سنجیدی؟ پنجتا: ده سطرِ قفسهها را با چشم شمردیم؛ میانه را کنارِ میانگین گذاشتیم؛ شمارشِ دستهای تفاوتی را نشان داد که هیچکدام از آن دو نشان نداده بودند؛ پراکندگیِ یکمرحلهایِSQLرا با محاسبهٔ دومرحلهای در پایتون سنجیدیم و هر دو راه به ۴٫۲۴ و ۶۸٫۴۷ رسیدند؛ و میانگینِ وزنی دقیقاً ۷۸٫۴۹۲۵ را بازساخت که همان عددِAVGروی کلِ جدول است. چه کسی از قلم افتاد؟ ۲۳۱ کتابی که در بخشِ ۵ یک گروهِ کاملاً جدا شدند و در هر میانگینی رویratingاصلاً شمرده نمیشوند؛ و همچنان ۲۴۰ سطرِarrivals.csv.
🤖 از دستیارت بپرس: «چه وقت میانه از میانگین گزارشِ بهتری است و چرا؟» بعد این را بپرس: «اگر فقط میانگینِ هر گروه و تعدادِ سطرهای هر گروه را داشته باشم، میتوانم میانگینِ کل را دقیق بازسازی کنم؟ و اگر فقط میانگینها را داشته باشم چطور؟» — جوابِ درست برای پرسشِ دوم «نه» است؛ اگر دستیارت گفت «بله، میانگینشان را بگیر»، همین حالا با عددهای بخشِ ۵ ردش کن.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| median | مدیان | میانه؛ مقدارِ وسطِ فهرستِ مرتبشده |
| range | رنج | دامنه؛ بیشینه منهای کمینه |
| spread | اسپرد | پراکندگی؛ اینکه مقدارها چقدر از میانگین دورند |
| outlier | اوتلایر | مقدارِ پرت؛ سطری که خیلی از بقیه فاصله دارد |
| weighted mean | ویتد مین | میانگینِ وزنی؛ میانگینی که اندازهٔ هر گروه را حساب میکند |
| skew | اسکیو | چولگی؛ کشیده شدنِ داده به یک سمت |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
حالا میدانی یک میانگین چه چیزی را پنهان میکند. فصلِ بعد سراغِ خطرناکترین شکلِ عددِ خلاصه میرود: درصد. همان یک شمارش را با دو مخرجِ کاملاً قابلِدفاع تقسیم میکنیم و دو عدد میگیریم — ۳۴٫۶۷ درصد و ۴۲٫۹۳ درصد — و بعد نشان میدهیم که سادهترین راهِ نوشتنِ همان درصد در SQL عددِ صفر برمیگرداند.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.