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

فصل ۴ از ۹

پیشرفت ترم
۰٪

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

پرسش: «چند درصد از کتاب‌های این کتابخانه امتیازِ چهار و بالاتر دارند؟»

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

و قبل از آن دو، یک جوابِ سوم هم هست که هیچ‌کس دنبالش نبود: صفر. ساده‌ترین راهِ نوشتنِ همان درصد در 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 با سطل‌های پنجاه‌تایی چهار سطر می‌دهد که قله را داخلِ سطلِ اول قایم می‌کنند، و با سطل‌های بیست‌تایی یک قلهٔ ۳۰۸تایی کنارِ نُه سطلی که هیچ‌کدامشان به ۱۲۰ نمی‌رسند.

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

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