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

فصل ۵ از ۹

پیشرفت ترم
۰٪

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

همان ستونِ borrowed را دو بار دسته‌بندی می‌کنیم. با سطل‌های پنجاه‌تایی چهار سطر می‌گیریم که یک سطلِ بزرگ دارند و سه سطلِ تقریباً هم‌اندازه — قله کاملاً داخلِ سطلِ اول قایم است و دیده نمی‌شود. با سطل‌های بیست‌تایی، یک قلهٔ ۳۰۸تایی ظاهر می‌شود کنارِ نُه سطلی که هیچ‌کدامشان به ۱۲۰ نمی‌رسند.

داده عوض نشد. تصمیمِ ما دربارهٔ عرضِ سطل عوض شد.

و یک سکوتِ بدتر هم هست: سطلی که هیچ سطری ندارد، هیچ سطری هم تولید نمی‌کند. در دسته‌بندیِ امتیازها، سطلِ «زیر ۲» اصلاً در خروجی پیدا نمی‌شود — نه با شمارشِ صفر، بلکه اصلاً — و اگر از روی همان خروجی نموداری بکشی، محورش از جایی شروع می‌شود که خودت انتخابش نکرده‌ای.

دو ردیف قفسهٔ دانه‌بندی روی یک تودهٔ یکسان، یکی با چند خانهٔ پهن و دیگری با خانه‌های باریکِ زیاد، و دو پروفیل پرشدنِ کاملاً متفاوت

آخر این فصل می‌توانی:

  • با CASE سطل بسازی و برایش برچسبِ خوانا بگذاری
  • هیستوگرام را با GROUP BY بگیری و با یک نوارِ متنی ببینی‌اش
  • نشان بدهی عرضِ سطل شکلِ توزیع را عوض می‌کند
  • سطل‌های خالی را پیدا کنی، حتی وقتی در خروجی نیستند
  • صدک را به‌شکلِ تقریبی بگیری، بدونِ اینکه تابعی برایش وجود داشته باشد

قبل از شروع#

از فصلِ ۳: میانگین و میانه و پراکندگی، و شمارشِ دسته‌ای روی ده سطرِ قفسه‌ها — این فصل همان کار را به یک ابزارِ کامل تبدیل می‌کند.

از ترمِ ۱: CASE WHEN و تقسیمِ صحیح. هر دو ابزارِ اصلیِ سطل‌بندی‌اند.

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

جدول تعدادِ سطر زمانِ ساخت
books ۱٬۲۰۰ کمتر از یک ثانیه
branches ۶ آنی

۱. سطل‌بندی با CASE: برچسب به‌جای عدد#

CASE WHEN را از ترمِ ۱ داری: یک زنجیرهٔ شرط که به‌جای عدد، برچسب می‌سازد. حالا همان برچسب را در GROUP BY می‌گذاریم.

print(q("""
    SELECT CASE
             WHEN borrowed = 0   THEN 'هرگز'
             WHEN borrowed < 20  THEN 'کم'
             WHEN borrowed < 100 THEN 'متوسط'
             ELSE 'پرامانت'
           END      AS band,
           COUNT(*) AS n
    FROM books
    GROUP BY band
    ORDER BY n DESC
"""))
      band    n
0  پرامانت  473
1    متوسط  419
2       کم  228
3     هرگز   80

چهار سطر گرفتیم، و ترتیبشان از روی شمارش است نه از روی معنیِ سطل.

اینجا اتفاقاً دقیقاً برعکسِ ترتیبِ طبیعی درآمده و همین گمراه‌کننده است: به نظر مرتب می‌آید، ولی مرتب نیست — با شمارشِ متفاوت هر ترتیبی ممکن بود. و ORDER BY band هم درستش نمی‌کند: آن‌وقت ترتیب، ترتیبِ نویسه‌های فارسی می‌شود که هیچ ربطی به بزرگ و کوچکِ عدد ندارد — همان دامِ ترمِ ۱ فصلِ ۴.

راهِ‌حل این است که کنارِ برچسب یک شمارهٔ مرتب‌سازی هم بسازی:

print(q("""
    SELECT CASE
             WHEN borrowed = 0   THEN 1
             WHEN borrowed < 20  THEN 2
             WHEN borrowed < 100 THEN 3
             ELSE 4
           END      AS band_id,
           CASE
             WHEN borrowed = 0   THEN 'هرگز'
             WHEN borrowed < 20  THEN 'کم'
             WHEN borrowed < 100 THEN 'متوسط'
             ELSE 'پرامانت'
           END      AS band,
           COUNT(*) AS n
    FROM books
    GROUP BY band_id, band
    ORDER BY band_id
"""))
   band_id     band    n
0        1     هرگز   80
1        2       کم  228
2        3    متوسط  419
3        4  پرامانت  473

حالا سطرها به ترتیبِ خودِ مقدارند و جدول خواندنی است.

✅ چک کن: جمعِ ستونِ n باید ۱٬۲۰۰ شود. ولی این جمع چون ELSE داریم همیشه ۱٬۲۰۰ می‌شود، حتی اگر borrowed جایی NULL باشد: چنین سطری به هیچ WHENی نمی‌خورد و بی‌صدا در ELSE، یعنی «پرامانت»، می‌نشیند. اینجا borrowed هیچ‌جا NULL نیست، ولی روی هر ستونی که NULL می‌پذیرد همیشه یک شرطِ IS NULL جدا بگذار؛ جمعِ ۱٬۲۰۰ این خطا را نمی‌گیرد.

۲. هیستوگرام با تقسیمِ صحیح، و نواری که دیده می‌شود#

برچسب برای چهار دسته خوب است. برای دیدنِ شکل باید سطل‌های هم‌عرض و زیاد داشته باشی، و نوشتنِ بیست WHEN کارِ عاقلانه‌ای نیست. تقسیمِ صحیح همان را در یک عبارت می‌دهد.

def show(frame, width):
    biggest = int(frame["n"].max())
    for row in frame.itertuples(index=False):
        bar = "█" * round(int(row.n) * 40 / biggest)
        print(f"  {int(row.bucket):>3}–{int(row.bucket) + width - 1:<3} {int(row.n):>4}  {bar}")
    print("  جمع:", int(frame["n"].sum()))


twenty = q("""
    SELECT (borrowed / 20) * 20 AS bucket, COUNT(*) AS n
    FROM books
    GROUP BY bucket
    ORDER BY bucket
""")
show(twenty, 20)
    0–19   308  ████████████████████████████████████████
   20–39   103  █████████████
   40–59   107  ██████████████
   60–79    96  ████████████
   80–99   113  ███████████████
  100–119  104  ██████████████
  120–139  110  ██████████████
  140–159  107  ██████████████
  160–179  109  ██████████████
  180–199   43  ██████
  جمع: 1200

حالا شکل را می‌بینی، و شکل یک قلهٔ تنها در سرِ چپ است.

سطلِ اول ۳۰۸ سطر دارد، هشت سطلِ میانی همه بینِ ۹۶ و ۱۱۳، و سطلِ آخر ۴۳ — که بخشِ ۴ سراغش می‌رود. این توزیع یک تپه نیست؛ دو جمعیتِ متفاوت است که در یک ستون ذخیره شده‌اند — و از ترمِ ۱ می‌دانیم آن دو کدام‌اند: ۲۳۱ کتابِ بی‌امتیاز که بیشترین امانتشان ۲ است، و ۹۶۹ کتابِ دیگر.

میانگینِ ۷۸٫۴۹ که فصلِ ۳ گرفت، عددی است بینِ آن دو جمعیت، و به هیچ‌کدامشان شبیه نیست.

۳. عرضِ سطل شکل را عوض می‌کند#

همان ستون، همان پرسش، فقط یک عدد فرق دارد:

fifty = q("""
    SELECT (borrowed / 50) * 50 AS bucket, COUNT(*) AS n
    FROM books
    GROUP BY bucket
    ORDER BY bucket
""")
show(fifty, 50)
print()
five = q("""
    SELECT (borrowed / 5) * 5 AS bucket, COUNT(*) AS n
    FROM books
    GROUP BY bucket
    ORDER BY bucket
""")
show(five.head(6), 5)
print("  (فقط شش سطل اول از", len(five), "سطل)")
    0–49   472  ████████████████████████████████████████
   50–99   255  ██████████████████████
  100–149  264  ██████████████████████
  150–199  209  ██████████████████
  جمع: 1200

    0–4    243  ████████████████████████████████████████
    5–9     20  ███
   10–14    23  ████
   15–19    22  ████
   20–24    29  █████
   25–29    24  ████
  جمع: 361
  (فقط شش سطل اول از 39 سطل)

سه نمایش از یک ستون، و سه داستانِ متفاوت.

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

هیچ‌کدام از این سه دروغ نمی‌گویند و هیچ‌کدام هم «نمایشِ درست» نیستند. عرضِ سطل یک تصمیمِ توست، و مثلِ مخرجِ فصلِ ۴ باید در گزارش اعلام شود.

قاعدهٔ عملی: هر توزیعی را با دستِ‌کم دو عرضِ متفاوت نگاه کن. اگر شکلی با هر دو دیده شد، واقعی است؛ اگر فقط با یک عرضِ خاص دیده شد، به آن شک کن.

🌱 ریشه‌اش کجاست: هیستوگرام و همین نکته که پهنای دسته شکل را عوض می‌کند، درسِ ریشه ترمِ ۶ فصل ۷ است — آنجا فرقِ هیستوگرام با نمودارِ ستونی هم دقیق می‌شود: در نمودارِ ستونی ترتیبِ ستون‌ها دلبخواهی است، در هیستوگرام محورِ افقی یک عددِ پیوسته است و جابه‌جا کردنِ دو ستون خرابش می‌کند.

۴. سطلِ آخر که پهنایش کمتر است#

به سطلِ آخرِ نمایشِ بیست‌تایی برگرد: عددش ۴۳ است، در حالی که هشت سطلِ میانی همه بینِ ۹۶ و ۱۱۳‌اند. آیا کتاب‌های خیلی پرامانت واقعاً این‌قدر کم‌اند؟

print(q("""
    SELECT MAX(borrowed) AS most,
           SUM(borrowed >= 180) AS in_last_bucket,
           SUM(borrowed >= 160 AND borrowed < 180) AS in_previous_bucket
    FROM books
"""))
   most  in_last_bucket  in_previous_bucket
0   190              43                 109

بیشترین مقدارِ ستون ۱۹۰ است، پس سطلِ آخر به‌جای بیست عدد، فقط یازده عددِ ممکن دارد: ۱۸۰ تا ۱۹۰.

یعنی آن ۴۳ کوتاه است چون سطل کوتاه است، نه چون داده آنجا کم است. سطلِ قبلی‌اش پهنای کاملِ بیست را دارد و ۱۰۹ سطر.

این یکی از پرتکرارترین خواندن‌های غلطِ هیستوگرام است، و راهِ جلوگیری‌اش یک جمله است: کنارِ هر هیستوگرام، کمینه و بیشینهٔ واقعیِ ستون را هم بنویس. آن‌وقت هر کسی می‌تواند خودش بفهمد کدام سطل ناقص است.

۵. سطلی که هیچ سطری ندارد#

حالا خطرناک‌ترین سکوتِ این فصل. امتیازها را به بازه‌های یکی‌یکی می‌شکنیم:

print(q("""
    SELECT CASE
             WHEN rating IS NULL THEN 'بی‌امتیاز'
             WHEN rating < 2.0   THEN 'زیر ۲'
             WHEN rating < 3.0   THEN '۲ تا ۳'
             WHEN rating < 4.0   THEN '۳ تا ۴'
             ELSE                     '۴ و بالاتر'
           END      AS band,
           COUNT(*) AS n
    FROM books
    GROUP BY band
"""))
print()
print("کمترین امتیاز در جدول:", float(q("SELECT MIN(rating) FROM books").iloc[0, 0]))
         band    n
0   بی‌امتیاز  231
1      ۲ تا ۳  148
2      ۳ تا ۴  405
3  ۴ و بالاتر  416

کمترین امتیاز در جدول: 2.6

پنج برچسب نوشتیم و چهار سطر گرفتیم. «زیر ۲» در خروجی نیست — نه با شمارشِ صفر، بلکه اصلاً.

GROUP BY فقط از سطرهایی که وجود دارند گروه می‌سازد. برچسبی که به هیچ سطری نخورد، هیچ‌وقت ساخته نمی‌شود. پس یک شمارشِ صفر و یک بازهٔ ناموجود، در خروجی دقیقاً یک شکل دارند: هیچ.

و همین روی سطل‌های عددی هم اتفاق می‌افتد، حتی وسطِ توزیع:

poetry = q("""
    SELECT (borrowed / 5) * 5 AS bucket, COUNT(*) AS n
    FROM books
    WHERE category = ?
    GROUP BY bucket
    ORDER BY bucket
""", ("شعر",))

expected = list(range(0, 191, 5))
present = set(int(v) for v in poetry["bucket"])
missing = [b for b in expected if b not in present]

print("سطل‌های ممکن :", len(expected))
print("سطل‌های موجود:", len(poetry))
print("سطل‌های غایب :", missing)
print("جمع سطرها    :", int(poetry["n"].sum()))
سطل‌های ممکن : 39
سطل‌های موجود: 36
سطل‌های غایب : [55, 75, 190]
جمع سطرها    : 164

سه سطل از ۳۹ سطلِ ممکن اصلاً سطری ندارند، و دو تایشان وسطِ توزیع‌اند.

نموداری که از این خروجی بکشی، آن دو شکاف را نشان نمی‌دهد؛ دو ستونِ همسایه را به هم می‌چسباند و توزیع را از چیزی که هست پیوسته‌تر نشان می‌دهد.

راهِ درستش این است که فهرستِ سطل‌های ممکن را جدا بسازی و نتیجه را رویش بنشانی — دقیقاً همان کاری که چند خطِ پایتونیِ بالا کرد. در SQL هم می‌شود، با ساختنِ یک جدولِ کمکی از همهٔ سطل‌ها؛ ابزارش CROSS JOIN است که ترمِ ۳ فصلِ ۴ می‌آورد. تا آنجا، همین شمارشِ «چند سطل انتظار داشتم و چندتا گرفتم» کافی است و باید عادت شود.

🔧 اگر کار نکرد: پرتکرارترین خطای این فصل، جا انداختنِ END در CASE است، و پیامش گیج‌کننده به نظر می‌رسد:

try:
    q("SELECT CASE WHEN borrowed > 100 THEN 1 ELSE 0 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 CASE WHEN borrowed > 100 THEN 1 ELSE 0 FROM books LIMIT 1': near "FROM": syntax error

پیام به FROM اشاره می‌کند، ولی مشکل یک کلمه قبل‌تر است: SQLite هنوز منتظرِ END بود و به‌جایش FROM دید. در خطای نحوی همیشه به کلمهٔ قبل از جایی که پیام نشان می‌دهد نگاه کن.

۶. صدک، به‌شکلِ تقریبی#

میانه سطرِ وسط بود. صدک همان ایده است در هر نقطهٔ دیگری: صدکِ ۹۰ یعنی مقداری که ۹۰ درصدِ سطرها از آن کمترند. و مثلِ میانه، SQLite تابعی برایش ندارد — همان ORDER BY ... LIMIT 1 OFFSET n کار را می‌کند.

total = int(q("SELECT COUNT(*) FROM books").iloc[0, 0])
for percent in (10, 25, 50, 75, 90):
    offset = percent * total // 100 - 1
    value = int(q("SELECT borrowed FROM books ORDER BY borrowed, id LIMIT 1 OFFSET ?",
                  (offset,)).iloc[0, 0])
    print(f"  صدک {percent:>2} → OFFSET {offset:>4} → {value:>3}")
  صدک 10 → OFFSET  119 →   1
  صدک 25 → OFFSET  299 →  16
  صدک 50 → OFFSET  599 →  76
  صدک 75 → OFFSET  899 → 132
  صدک 90 → OFFSET 1079 → 166

پنج عدد که شکلِ توزیع را در یک خط خلاصه می‌کنند — و صدکِ ۱۰ برابرِ ۱ است، که همان دُمِ چپِ پرجمعیت را دوباره نشان می‌دهد.

چرا «تقریبی»؟ دو دلیل، و هر دو را باید در گزارش بنویسی. اول اینکه فرمولِ OFFSET یک انتخاب است، نه یک قانون: راه‌های متفاوتی برای گرد کردنِ جایگاهِ صدک وجود دارد و هرکدام ممکن است یک سطر جلوتر یا عقب‌تر بیفتند. دوم اینکه این روش برای هر صدک یک بار کلِ ستون را مرتب می‌کند؛ روی جدولِ بزرگ گران است و ترمِ ۵ نشان می‌دهد چقدر.

📏 اندازه بگیر: یک سطر یعنی چه؟ در هر جدولِ این فصل یک سطر یک سطل است — نه یک کتاب — و عددش تعدادِ کتاب‌های داخلِ آن سطل. با کدام شمارشِ دوم سنجیدی؟ سه‌تا: جمعِ ستونِ شمارش در نمایشِ بیست‌تایی و پنجاه‌تایی هر دو ۱٬۲۰۰ ماند و نشان داد عرضِ سطل هیچ سطری را گم نمی‌کند (در نمایشِ پنج‌تایی فقط شش سطلِ اول چاپ شد، پس جمعِ آن تکه ۳۶۱ است)؛ بیشینهٔ ستون (۱۹۰) توضیح داد چرا سطلِ آخر کوتاه است؛ و شمارشِ «چند سطل انتظار داشتم» در برابرِ «چندتا گرفتم» سه سطلِ غایب را پیدا کرد. چه کسی از قلم افتاد؟ سطل‌هایی که هیچ سطری ندارند و هیچ سطری هم در خروجی نمی‌سازند — از جمله کلِ بازهٔ «زیر ۲» در امتیازها؛ و همچنان ۲۴۰ سطرِ arrivals.csv.

🤖 از دستیارت بپرس: «چطور عرضِ مناسبِ سطل را برای یک هیستوگرام انتخاب کنم؟» بعد این را بپرس: «اگر یک سطل هیچ سطری نداشته باشد، در خروجیِ GROUP BY چه اتفاقی می‌افتد و چطور می‌توانم مجبورش کنم با شمارشِ صفر ظاهر شود؟» — جوابِ درست به ساختنِ فهرستِ سطل‌ها به‌عنوان یک منبعِ جدا اشاره می‌کند؛ اگر جواب فقط گفت «COALESCE بگذار»، مسئله را نفهمیده است.

واژه‌های تازهٔ این فصل#

کلمه تلفظ به حروف فارسی یعنی چه
bucket / bin باکت / بین سطل؛ بازه‌ای که مقدارها در آن شمرده می‌شوند
histogram هیستوگرام نمودارِ شمارشِ سطل‌ها روی یک محورِ عددی
bucket width باکت ویدث عرضِ سطل
distribution دیستریبیوشن توزیع؛ اینکه مقدارها چطور پخش شده‌اند
percentile پرسنتایل صدک؛ مقداری که سهمِ مشخصی از داده از آن کمترند
bimodal بای‌مودال دوقله‌ای؛ توزیعی که دو تجمعِ جدا دارد

تمرین‌ها

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

در فصل بعد#

تا اینجا هر بار که تجمیع گرفتیم، سطرهای اصلی را از دست دادیم: ۱٬۲۰۰ سطر می‌رفت و ۶ یا ۸ سطر برمی‌گشت. فصلِ بعد ابزاری می‌آورد که هر دو را با هم دارد — هر ۱٬۲۰۰ سطر سرِ جایشان می‌مانند و کنارِ هرکدام یک عددِ گروهی می‌نشیند. مثلاً کنارِ هر کتاب، میانگینِ دستهٔ خودش، و سهمش از کلِ امانت‌های آن دسته — در یک پرسش و بدونِ اینکه سطری گم شود.

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

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