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

آخر این فصل میتوانی:
- نسبتِ دو شمارش را در
SQLبگیری بدونِ اینکه تقسیمِ صحیح صفرش کند - برای هر درصدی که مینویسی مخرجش را صریح اعلام کنی
- سهمِ هر گروه از کل را در همان پرسشِ گروهی حساب کنی
- نرخ بهازای واحد بسازی و ببینی چطور رتبهها را جابهجا میکند
- تغییرِ درصدی را درست بگیری و بگویی نسبت به چه چیزی
قبل از شروع#
از فصلِ ۳: میانگین و میانه و پراکندگی، میانگینِ وزنی، و اینکه هر جدولِ گروهی باید ستونِ شمارش داشته باشد.
از ترمِ ۱: تقسیمِ صحیح — 7/2 در SQL میشود ۳ — و CAST. این فصل تمامش روی همین یک نکته سوار است.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۶ | آنی |
۱. اولین تقسیم، و صفری که هیچکس نمیخواست#
«میانگینِ امانت بهازای هر کتاب» را دو جور بنویسیم: یک بار با AVG و یک بار با تقسیمِ دستیِ جمع بر شمارش.
print(q("""
SELECT AVG(borrowed) AS with_avg,
SUM(borrowed) / COUNT(*) AS plain_division,
SUM(borrowed) * 1.0 / COUNT(*) AS times_one_point_zero,
CAST(SUM(borrowed) AS REAL) / COUNT(*) AS with_cast
FROM books
"""))
with_avg plain_division times_one_point_zero with_cast
0 78.4925 78 78.4925 78.4925
ستونِ دوم ۷۸ داد و بقیه ۷۸٫۴۹۲۵. اینجا اعشار فقط بریده شد و هنوز عددِ قابلِاستفادهای مانده.
حالا همان کار روی یک درصد، جایی که صورت از مخرج کوچکتر است:
print(q("""
SELECT SUM(rating >= 4.0) AS good_books,
COUNT(*) AS all_books,
SUM(rating >= 4.0) / COUNT(*) AS the_ratio
FROM books
"""))
good_books all_books the_ratio
0 416 1200 0
۴۱۶ تقسیم بر ۱٬۲۰۰ شد صفر.
هر دو عدد صحیحاند، پس SQLite تقسیمِ صحیح انجام میدهد: جوابِ واقعی ۰٫۳۴۶ است، اعشار دور ریخته میشود، و صفر میماند. نه خطا، نه هشدار — فقط یک صفر که در یک گزارشِ خودکار میتواند ماهها بماند.
دو راهِ درست کردنش، و هر دو یک کار میکنند: یک طرف را اعشاری کن.
print(q("""
SELECT ROUND(SUM(rating >= 4.0) * 1.0 / COUNT(*), 6) AS times_one,
ROUND(CAST(SUM(rating >= 4.0) AS REAL) / COUNT(*), 6) AS with_cast,
ROUND(SUM(rating >= 4.0) * 100.0 / COUNT(*), 2) AS as_percent
FROM books
"""))
times_one with_cast as_percent
0 0.346667 0.346667 34.67
* 1.0 کوتاهتر است و CAST(... AS REAL) گویاتر؛ هر دو مجازند و در این دوره هر دو را میبینی. قاعدهٔ عملی: هر جا در SQL علامتِ تقسیم مینویسی، همان لحظه از خودت بپرس هر دو طرف صحیحاند یا نه.
و ضربِ در ۱۰۰ فقط یک تغییرِ واحد است، نه یک محاسبهٔ تازه.
🌱 ریشهاش کجاست: درصد چیزِ تازهای نیست؛ فقط یک کسر است که مخرجش را همیشه ۱۰۰ گرفتهای تا دو نسبتِ نابرابر قابلِمقایسه شوند. اگر میخواهی این را از پایه ببینی، ریشه ترمِ ۲ فصل ۵ با دو نوارِ صدتایی بازش میکند.
۲. همان شمارش، دو مخرج، دو جواب#
حالا پرسشِ اصلی. صورت روشن است: ۴۱۶ کتاب امتیازِ چهار و بالاتر دارند. مخرج چیست؟
print(q("""
SELECT SUM(rating >= 4.0) AS good,
COUNT(*) AS all_books,
COUNT(rating) AS rated_books,
ROUND(SUM(rating >= 4.0) * 100.0 / COUNT(*), 2) AS pct_of_all,
ROUND(SUM(rating >= 4.0) * 100.0 / COUNT(rating), 2) AS pct_of_rated
FROM books
"""))
good all_books rated_books pct_of_all pct_of_rated
0 416 1200 969 34.67 42.93
۳۴٫۶۷ و ۴۲٫۹۳ — هشت واحدِ درصد فاصله، از یک صورتِ مشترک.
و حالا مهمترین کارِ این فصل: بگو هرکدام جوابِ چه پرسشی است.
- ۳۴٫۶۷ درصد جوابِ این است: «اگر یک کتاب را تصادفی از فهرست بردارم، چقدر احتمال دارد کتابی باشد که امتیازِ چهار و بالاتر خورده؟» مخرجش کلِ فهرست است، و ۲۳۱ کتابِ بیامتیاز را کتابهایی که آن امتیاز را ندارند حساب میکند.
- ۴۲٫۹۳ درصد جوابِ این است: «از کتابهایی که اصلاً قضاوت شدهاند، چه سهمی خوب بوده؟» مخرجش فقط کتابهای امتیازدار است، و ۲۳۱ کتابِ بیامتیاز را از هر دو طرفِ کسر بیرون میگذارد.
هیچکدام غلط نیست و انتخاب به پرسش بستگی دارد. ولی یک چیز قطعاً غلط است: نوشتنِ «۴۲٫۹ درصدِ کتابها امتیازِ خوبی دارند» بدونِ اینکه بگویی مخرج کدام است. آن جمله دربارهٔ ۹۶۹ کتاب حرف میزند و وانمود میکند دربارهٔ ۱٬۲۰۰ کتاب است.
قاعدهٔ سختِ این فصل: یک درصد تا وقتی مخرجش را نگفتهای، عدد نیست.
۳. سهم از کل، در همان پرسشِ گروهی#
سهمِ هر دسته از کلِ فهرست را میخواهیم. مشکل این است که COUNT(*) داخلِ یک GROUP BY فقط سطرهای همان گروه را میشمارد؛ برای مخرج به عددی از بیرونِ گروه نیاز داریم.
راهش این است که یک پرسشِ کامل را داخلِ پرانتز بگذاری، جایی که یک عدد لازم است:
share = q("""
SELECT category,
COUNT(*) AS n,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM books), 2) AS pct
FROM books
GROUP BY category
ORDER BY pct DESC
""")
print(share)
print("جمع درصدها:", round(share["pct"].sum(), 4))
print("جمع شمارشها:", int(share["n"].sum()))
category n pct
0 کودک 167 13.92
1 شعر 164 13.67
2 هنر 161 13.42
3 رمان 146 12.17
4 تاریخ 145 12.08
5 علم 144 12.00
6 فلسفه 137 11.42
7 زندگینامه 136 11.33
جمع درصدها: 100.01
جمع شمارشها: 1200
(SELECT COUNT(*) FROM books) یک زیرپرسشِ اسکالر است: یک پرسشِ کامل که دقیقاً یک عدد برمیگرداند و همانجا جای یک مقدار مینشیند. فعلاً همینقدر بدان؛ ترمِ ۳ فصلِ ۶ کلِ خانوادهٔ زیرپرسشها را باز میکند و میگوید کِی گران میشوند و کِی جای بهتری دارند.
✅ چک کن: جمعِ ستونِ
nباید دقیقاً ۱٬۲۰۰ باشد و هست. ولی جمعِ درصدها ۱۰۰٫۰۱ شد، نه ۱۰۰. این اشتباه نیست و نباید «درستش کنی» — هشت عدد را جدا جدا گرد کردهای و خطاهای گِرد کردن روی هم جمع شدهاند. کارِ درست این است که در گزارش بنویسی درصدها گرد شدهاند، نه اینکه یکی از آنها را دستی کم کنی تا جمع صد شود.
۴. نرخ بهازای واحد: وقتی رتبهها جابهجا میشوند#
پرسشِ گزارشی: «کدام دسته بیشترین امانت را دارد؟» یک پرسشِ کاملاً معمولی که دو جوابِ کاملاً متفاوت دارد.
print(q("""
SELECT category,
SUM(borrowed) AS loans,
COUNT(*) AS books,
ROUND(SUM(borrowed) * 1.0 / COUNT(*), 2) AS loans_per_book
FROM books
GROUP BY category
ORDER BY loans DESC
"""))
category loans books loans_per_book
0 کودک 13170 167 78.86
1 هنر 12682 161 78.77
2 شعر 12106 164 73.82
3 رمان 12104 146 82.90
4 زندگینامه 11889 136 87.42
5 فلسفه 11554 137 84.34
6 علم 10351 144 71.88
7 تاریخ 10335 145 71.28
ستونِ دوم مرتب است و ستونِ آخر نیست — و همینجا نگاه کن که چه اتفاقی افتاده.
«کودک» با ۱۳٬۱۷۰ امانت بالای جدول است، ولی نرخِ هر کتابش ۷۸٫۸۶ است. «زندگینامه» پنجمینِ جدول است با ۱۱٬۸۸۹ امانت، ولی نرخِ هر کتابش ۸۷٫۴۲ است — بالاترینِ کلِ جدول.
دلیلش این است که دستهها هماندازه نیستند. «کودک» ۱۶۷ کتاب دارد و «زندگینامه» ۱۳۶ کتاب. جمعِ خام هم اندازهٔ دسته را اندازه میگیرد و هم محبوبیتش را، و این دو را از هم جدا نمیکند. نرخ همان جدا کردن است: مخرج را روی یک واحد ثابت میکنی — اینجا «یک کتاب» — و بعد دو دسته قابلِمقایسه میشوند.
🌱 ریشهاش کجاست: این دقیقاً همان کاری است که برای مقایسهٔ دو بستهٔ نابرابرِ برنج میکنی: قیمتِ هر بسته را به قیمتِ یک کیلو تبدیل میکنی و تازه آنوقت میفهمی کدام ارزانتر است. ریشه ترمِ ۲ فصل ۸ همین را با دو بستهٔ واقعی باز میکند.
و یک هشدارِ لازم: نرخ فقط وقتی مقایسهپذیر است که مخرجش واقعاً واحدِ درستی باشد. «امانت بهازای هر کتاب» فرض میگیرد همهٔ کتابها مدتِ برابری در فهرست بودهاند، و ستونِ added_at نشان میدهد که نبودهاند. گزارشِ صادقانه عدد را میدهد و همین جمله را هم میدهد.
۵. تغییرِ درصدی، و اینکه بالا و پایین قرینه نیستند#
برای هر سال میشماریم چند کتاب به فهرست اضافه شده، بعد تغییرِ سالبهسال را حساب میکنیم. در SQL ابزارِ مخصوصِ این کار LAG است که فصلِ ۷ میآورد؛ فعلاً همان جدولِ گروهی را میگیریم و تغییر را کنارش حساب میکنیم.
(نامِ مستعار عمداً added_year است و نه year — فصلِ ۲ نشان داد که year نامِ یک ستونِ واقعیِ همین جدول است و GROUP BY آن را میگیرد.)
years = q("""
SELECT strftime('%Y', added_at) AS added_year, COUNT(*) AS n
FROM books
GROUP BY added_year
ORDER BY added_year
""")
counts = list(years["n"])
labels = list(years["added_year"])
for i in range(1, len(counts)):
change = (counts[i] - counts[i - 1]) * 100.0 / counts[i - 1]
print(f" {labels[i-1]} → {labels[i]} : {counts[i-1]:>4} → {counts[i]:>4}"
f" تغییر: {change:+7.2f}٪")
2019 → 2020 : 193 → 206 تغییر: +6.74٪
2020 → 2021 : 206 → 166 تغییر: -19.42٪
2021 → 2022 : 166 → 165 تغییر: -0.60٪
2022 → 2023 : 165 → 188 تغییر: +13.94٪
2023 → 2024 : 188 → 178 تغییر: -5.32٪
2024 → 2025 : 178 → 104 تغییر: -41.57٪
سطرِ آخر یک کاهشِ بزرگ نشان میدهد و آن کاهش واقعی نیست: همان دورهٔ ناقصی است که فصلِ ۲ نشانش داد. این پایگاه داده تا اواسطِ سالِ آخر بیشتر ندارد، پس آن سال با شش سالِ کامل قابلِمقایسه نیست. یک عددِ درصدیِ درستمحاسبهشده روی دو دورهٔ نابرابر، همچنان یک ادعای غلط است.
و حالا تلهٔ دومِ درصد. سطرِ دوم را برمیداریم — همان ۲۰۶ و ۱۶۶ — و تغییر را یک بار در هر جهت حساب میکنیم:
a, b = counts[1], counts[2]
print("از", a, "به", b, ":", round((b - a) * 100.0 / a, 2), "درصد")
print("از", b, "به", a, ":", round((a - b) * 100.0 / b, 2), "درصد")
print("جمع دو تغییر :", round((b - a) * 100.0 / a + (a - b) * 100.0 / b, 2), "درصد")
از 206 به 166 : -19.42 درصد
از 166 به 206 : 24.1 درصد
جمع دو تغییر : 4.68 درصد
همان دو عدد، و دو درصدِ کاملاً متفاوت — و جمعشان صفر نمیشود.
دلیلش این است که مخرجِ این دو کسر یکی نیست: صورتِ هر دو ۴۰ است، ولی اولی بر ۲۰۶ تقسیم شده و دومی بر ۱۶۶. پس «۱۹ درصد کم شد» و «۲۴ درصد زیاد شد» میتوانند دربارهٔ یک جفت عددِ واحد باشند، و هیچکدام هم دروغ نباشند.
🌱 ریشهاش کجاست: همین مکانیزم است که باعث میشود ۲۰٪ گران شدن و بعد ۲۰٪ تخفیف، قیمت را به جای اولش برنگرداند. ریشه ترمِ ۲ فصل ۶ کوتاه است و فقط همین یک عادت را جا میاندازد: بپرس درصدی از چه چیزی.
۶. مخرجی که صفر است، و سکوتِ SQLite#
برای هر دسته یک نسبت میخواهیم: کلِ امانتهای دسته تقسیم بر تعدادِ کتابهای پنجستارهٔ آن. دقت کن که صورت، امانتِ همهٔ کتابهای دسته است، نه فقط پنجستارهها؛ برای همین عددها از ۱۹۰، یعنی بیشترین امانتِ یک کتاب، خیلی بزرگترند و «امانتِ هر کتابِ پنجستاره» نیستند. این نسبت فقط برای دیدنِ رفتارِ مخرج ساخته شده است، و بعضی دستهها هیچ کتابِ پنجستارهای ندارند.
print(q("""
SELECT category,
SUM(rating = 5.0) AS five_star,
ROUND(SUM(borrowed) * 1.0 / SUM(rating = 5.0), 1) AS raw_rate,
ROUND(SUM(borrowed) * 1.0 / NULLIF(SUM(rating = 5.0), 0), 1) AS safe_rate
FROM books
GROUP BY category
ORDER BY category
"""))
category five_star raw_rate safe_rate
0 تاریخ 1 10335.0 10335.0
1 رمان 1 12104.0 12104.0
2 زندگینامه 0 NaN NaN
3 شعر 3 4035.3 4035.3
4 علم 0 NaN NaN
5 فلسفه 1 11554.0 11554.0
6 هنر 0 NaN NaN
7 کودک 3 4390.0 4390.0
دو ستونِ آخر دقیقاً یکیاند، و این خودش درسِ اصلی است.
در SQLite تقسیم بر صفر خطا نمیدهد؛ NULL میدهد. پس NULLIF اینجا هیچچیزی را «نجات» نداد — کاری که کرد این بود که تصمیم را در متنِ پرسش دیدنی کند: هر کسی این پرسش را بخواند میفهمد نویسندهاش میدانسته مخرج ممکن است صفر شود.
و دو دلیلِ عملی که همیشه بنویسیاش: در بیشترِ پایگاه دادههای دیگر تقسیم بر صفر خطا میدهد و همان پرسش آنجا میشکند؛ و NULLIF جلوی مخرجهای صفرِ غیرِواقعی را هم میگیرد — مثلِ ستونی که مقدارش را نمیدانی و بهاشتباه صفر ذخیره شده.
و حالا خطرِ واقعی، که هیچ ربطی به NULLIF ندارد: آن سه NULL در ستونِ نرخ، در هر محاسبهٔ بعدی بیصدا کنار میروند.
rates = q("""
SELECT category,
SUM(borrowed) * 1.0 / NULLIF(SUM(rating = 5.0), 0) AS rate
FROM books
GROUP BY category
""")
print("تعداد دستهها :", len(rates))
print("دستههای نرخدار :", int(rates["rate"].notna().sum()))
print("میانگین ستون نرخ :", round(float(rates["rate"].mean()), 2))
print("همان میانگین اگر آن سه دسته صفر بودند:",
round(float(rates["rate"].fillna(0).mean()), 2))
تعداد دستهها : 8
دستههای نرخدار : 5
میانگین ستون نرخ : 8483.67
همان میانگین اگر آن سه دسته صفر بودند: 5302.29
میانگینی که بر ۵ تقسیم کرده در برابرِ میانگینی که بر ۸ تقسیم کرده. همان تلهٔ AVG و مخرج از ترمِ ۱ فصلِ ۶، این بار در ستونی که خودمان ساختیم.
🔧 اگر کار نکرد: وقتی درصدها را پارامتری میکنی، پرتکرارترین خطا این است که تعدادِ
?ها با تعدادِ مقدارها نخواند:
try:
q("SELECT COUNT(*) FROM books WHERE borrowed > ? AND rating >= ?", (100,))
except Exception as error:
print(type(error).__module__ + "." + type(error).__name__)
print(error)
pandas.errors.DatabaseError
Execution failed on sql 'SELECT COUNT(*) FROM books WHERE borrowed > ? AND rating >= ?': Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.
پیام دقیقاً میگوید دو مقدار انتظار داشت و یکی گرفت. ولی خطای واقعاً گرانقیمتِ این فصل هیچ پیامی ندارد: همان SUM(...) / COUNT(*) که صفر داد، و همان / 0 که NULL داد و بعد از یک AVG ناپدید شد.
📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ بخشِ ۳ و ۴ یک سطر یک دسته است و درصدش نسبت به کلِ فهرست حساب شده، نه نسبت به گروهِ خودش. در بخشِ ۵ یک سطر یک سال است. با کدام شمارشِ دوم سنجیدی؟ سهتا: جمعِ شمارشِ هشت دسته که ۱٬۲۰۰ شد؛ جمعِ درصدها که ۱۰۰٫۰۱ شد و همان اختلافِ گِرد کردن را لو داد؛ و نرخِ هر کتاب که رتبهبندیِ جمعِ خام را جابهجا کرد و نشان داد آن رتبهبندی چه چیزی را اندازه میگرفته. چه کسی از قلم افتاد؟ ۲۳۱ کتابِ بیامتیاز، که بسته به انتخابِ مخرج یا در جواب هستند یا نیستند — و همین انتخاب ۳۴٫۶۷ را به ۴۲٫۹۳ تبدیل کرد؛ سه دستهای که نرخشان
NULLشد و از میانگینِ نرخها افتادند؛ نیمهٔ دومِ سالِ آخر؛ و همچنان ۲۴۰ سطرِarrivals.csv.
🤖 از دستیارت بپرس: «چرا در
SQLتقسیمِ دو عددِ صحیح، عددِ صحیح میدهد و چطور جلویش را بگیرم؟» بعد این را بپرس: «اگر بگویم ۴۲ درصدِ کتابها امتیازِ خوبی دارند، خواننده چه فرضی دربارهٔ مخرج میکند و آن فرض کجا میتواند غلط باشد؟» — جوابِ کامل باید به سطرهایی اشاره کند که اصلاً امتیاز ندارند؛ اگر جواب فقط دربارهٔ گِرد کردن حرف زد، پرسش را نفهمیده است.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| denominator | دنامینیتور | مخرج؛ عددی که بر آن تقسیم میکنی |
| rate | ریت | نرخ؛ نسبتی که مخرجش یک واحدِ ثابت است |
| share of total | شر آو توتال | سهم از کل |
| scalar subquery | اسکالر سابکوئری | پرسشی داخلِ پرسش که یک عدد برمیگرداند |
| percent change | پرسنت چنج | تغییرِ درصدی نسبت به یک مبنا |
| rounding error | راندینگ ارور | خطای گِرد کردن که در جمع دیده میشود |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
این فصل داده را به یک عددِ نسبی خلاصه کرد. فصلِ بعد برعکسش را میکند: بهجای یک عدد، شکلِ کلِ داده را نشان میدهد. با CASE سطل میسازیم و میشماریم، و بلافاصله میبینیم که عرضِ سطل خودش شکل را عوض میکند — همان ستونِ borrowed با سطلهای پنجاهتایی چهار سطر میدهد که قله را داخلِ سطلِ اول قایم میکنند، و با سطلهای بیستتایی یک قلهٔ ۳۰۸تایی کنارِ نُه سطلی که هیچکدامشان به ۱۲۰ نمیرسند.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.