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

فصل ۳ از ۸

پیشرفت ترم
۰٪

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

این فصل دلیلِ وجودِ کلِ این ترم است.

جمعِ صفحاتِ این کتابخانه ۵۸۸٬۰۴۹ است. یک JOIN به جدولِ امانت‌ها اضافه می‌کنیم — فقط برای اینکه یک ستونِ دیگر هم در گزارش باشد — و همان SUM می‌شود ۱٬۹۴۶٬۱۰۳. هیچ خطایی، هیچ هشداری. عدد ۳٫۳ برابر شد.

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

یک دستهٔ سه‌شاخه که یک جریانِ ورودی را به سه جریانِ خروجیِ هم‌اندازه تبدیل می‌کند و پیمانهٔ پایین را لبریز می‌کند

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

  • بگویی یک‌به‌یک و یک‌به‌چند و چندبه‌چند یعنی چه، و کدامشان سطر می‌سازد
  • تورمِ یک SUM را با دو عدد نشان بدهی
  • نشانه‌ای را که بدونِ دانستنِ اسکیما هم کار می‌کند تشخیص بدهی
  • با تجمیع قبل از پیوند، عددِ درست را برگردانی و ثابت کنی که درست است

قبل از شروع#

از فصلِ ۱ و ۲: INNER JOIN و LEFT JOIN و بندِ ON. از ترمِ ۲: GROUP BY و SUM و COUNT(DISTINCT ...).

دانه‌بندی: یک سطر از books یک کتاب است؛ یک سطر از loans یک بار امانت رفتن. یک کتاب می‌تواند ده‌ها سطر در loans داشته باشد — و کلِ این فصل دربارهٔ نتیجهٔ همین جمله است.

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

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

۱. دو کتاب، سه امانت#

قبل از هر عددِ بزرگی، چیزی که با انگشت شمردنی است:

con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("DROP TABLE IF EXISTS tiny_loans")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT, pages INTEGER)")
con.execute("CREATE TABLE tiny_loans (id INTEGER PRIMARY KEY, book_id INTEGER)")
con.executemany("INSERT INTO tiny_books VALUES (?, ?, ?)", [(1, "الف", 100), (2, "ب", 200)])
con.executemany("INSERT INTO tiny_loans VALUES (?, ?)", [(1, 1), (2, 2), (3, 2)])
con.commit()

print("جمع صفحه‌ها، روی یک جدول:",
      int(q("SELECT SUM(pages) AS s FROM tiny_books").iloc[0, 0]))
print()
print(q("""
    SELECT t.title, t.pages
    FROM tiny_books AS t
    JOIN tiny_loans AS n ON n.book_id = t.id
    ORDER BY t.id, n.id
"""))
print()
print("جمع صفحه‌ها، بعد از JOIN :",
      int(q("""
          SELECT SUM(t.pages) AS s
          FROM tiny_books AS t
          JOIN tiny_loans AS n ON n.book_id = t.id
      """).iloc[0, 0]))
جمع صفحه‌ها، روی یک جدول: 300

  title  pages
0   الف    100
1     ب    200
2     ب    200

جمع صفحه‌ها، بعد از JOIN : 500

⁦۱۰۰ + ۲۰۰ + ۲۰۰ = ۵۰۰⁩. با انگشت شمردنی، و کاملاً غلط.

کتابِ «ب» دو بار امانت رفته، پس در loans دو سطر دارد، پس JOIN دو نسخه از آن ساخت — و SUM هر دو نسخه را جمع زد. صفحه‌های «ب» دو بار شمرده شدند، نه به این خاطر که دو کتاب است، بلکه به این خاطر که دو امانت است.

اسمِ رابطهٔ بینِ این دو جدول یک‌به‌چند است: یک کتاب، چند امانت. سه حالت ممکن است و فقط یکی‌شان بی‌خطر است:

  • یک‌به‌یک — هر سطر دقیقاً یک جفت دارد. تعدادِ سطرها عوض نمی‌شود. (books به branches در فصلِ ۱، از سمتِ کتاب: هر کتاب یک شعبه دارد، هرچند یک شعبه چند کتاب دارد و این پیوند از سمتِ شعبه یک‌به‌چند است)
  • یک‌به‌چند — یک سطر چند جفت دارد. تعدادِ سطرها زیاد می‌شود. (books به loans)
  • چندبه‌چند — هر دو طرف چند جفت دارند. تعدادِ سطرها ضرب می‌شود.

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

۲. همان اتفاق، روی دادهٔ واقعی#

before_rows = int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0])
before_sum = int(q("SELECT SUM(pages) AS s FROM books").iloc[0, 0])
after = q("""
    SELECT COUNT(*) AS rows_now, SUM(b.pages) AS pages_now
    FROM books AS b
    JOIN loans AS l ON l.book_id = b.id
""")

print("قبل از JOIN — سطر:", before_rows, "| جمع صفحه‌ها:", before_sum)
print("بعد از JOIN — سطر:", int(after["rows_now"][0]),
      "| جمع صفحه‌ها:", int(after["pages_now"][0]))
print("نسبت جمع‌ها      :", round(int(after["pages_now"][0]) / before_sum, 2))
قبل از JOIN — سطر: 1200 | جمع صفحه‌ها: 588049
بعد از JOIN — سطر: 3998 | جمع صفحه‌ها: 1946103
نسبت جمع‌ها      : 3.31

عدد ۳٫۳۱ برابر شد، و هیچ‌کس چیزی نگفت.

۳. نشانه‌ای که بدونِ اسکیما هم کار می‌کند#

اگر فقط یک جمله از این فصل قرار است در ذهنت بماند، همین است:

تعدادِ سطرها عوض شد، در حالی که تو فقط یک ستون اضافه کرده بودی.

این نشانه هیچ چیزی از اسکیما نمی‌خواهد. لازم نیست بدانی loans چیست یا رابطه‌اش چند به چند است. قبل از JOIN یک COUNT(*) بگیر، بعدش یک COUNT(*) دیگر. اگر عوض شد، هر تجمیعی که در آن پرسش داری مشکوک است.

و برعکسش هم درست است: در فصلِ ۱ همین آزمون را زدیم و عدد ۱۲۰۰ ماند ۱۲۰۰. آن JOIN بی‌خطر بود و ثابتش کردیم، نه اینکه فرضش کنیم.

۴. بدتر از عددِ غلط: برندهٔ غلط#

حالا همان دو پرسش، این بار به‌تفکیکِ شعبه — دقیقاً همان کاری که یک گزارشِ واقعی می‌کند:

print("درست — فقط از books:")
print(q("""
    SELECT br.name, SUM(b.pages) AS pages
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    GROUP BY br.id, br.name
    ORDER BY pages DESC
"""))
print()
print("غلط — همان، به‌علاوهٔ ستون امانت‌ها:")
print(q("""
    SELECT br.name, SUM(b.pages) AS pages, COUNT(*) AS loans
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    JOIN loans AS l ON l.book_id = b.id
    GROUP BY br.id, br.name
    ORDER BY pages DESC
"""))
درست — فقط از books:
    name   pages
0   نسیم  108608
1   کوشا  106918
2   پویا   97273
3  مرکزی   94868
4   دانش   93673
5   بهار   86709

غلط — همان، به‌علاوهٔ ستون امانت‌ها:
    name   pages  loans
0  مرکزی  526045   1113
1   نسیم  437875    837
2   دانش  331396    676
3   کوشا  240599    506
4   بهار  231523    488
5   پویا  178665    378

مرکزی از رتبهٔ چهارم به اول پرید و پویا از سوم به آخر افتاد.

دلیلش این است که تورم برای همهٔ شعبه‌ها یکسان نیست: کتاب‌های مرکزی بیشتر امانت می‌روند، پس بیشتر تکثیر می‌شوند. ستونِ pages در جدولِ دوم عملاً دارد محبوبیت را اندازه می‌گیرد، نه صفحه را — و عنوانِ ستون همچنان pages است.

اگر این گزارش مبنای تصمیمِ «کدام شعبه به قفسهٔ بیشتری نیاز دارد» باشد، جواب کاملاً عوض می‌شود.

۵. DISTINCT مسکّن است، نه درمان#

اولین چیزی که به ذهنِ همه می‌رسد DISTINCT است. برای شمردن جواب می‌دهد؛ برای جمع زدن نه.

joined = q("""
    SELECT COUNT(*)                AS naive,
           COUNT(DISTINCT b.id)    AS distinct_books,
           SUM(DISTINCT b.pages)   AS distinct_pages,
           COUNT(DISTINCT b.pages) AS page_values
    FROM books AS b
    JOIN loans AS l ON l.book_id = b.id
""")
never = q("""
    SELECT COUNT(*) AS books, SUM(b.pages) AS pages
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id
    WHERE l.id IS NULL
""")
all_pages = int(q("SELECT SUM(pages) AS s FROM books").iloc[0, 0])

print("COUNT(*) بعد از JOIN      :", int(joined["naive"][0]))
print("COUNT(DISTINCT b.id)      :", int(joined["distinct_books"][0]))
print("کتاب‌های بی‌امانت (فصل ۲)  :", int(never["books"][0]))
print()
print("SUM(DISTINCT b.pages)     :", int(joined["distinct_pages"][0]))
print("جمع واقعی همان کتاب‌ها    :", all_pages - int(never["pages"][0]))
print("چند کتاب، چند مقدار متمایز:", int(joined["distinct_books"][0]), "کتاب،",
      int(joined["page_values"][0]), "مقدار")
COUNT(*) بعد از JOIN      : 3998
COUNT(DISTINCT b.id)      : 1000
کتاب‌های بی‌امانت (فصل ۲)  : 200

SUM(DISTINCT b.pages)     : 290121
جمع واقعی همان کتاب‌ها    : 485911
چند کتاب، چند مقدار متمایز: 1000 کتاب، 598 مقدار

COUNT(DISTINCT b.id) عددِ درست را برگرداند: ۱٬۰۰۰ کتابِ متمایز. و شمارشِ دومش هم آماده است — ۱٬۰۰۰ به‌علاوهٔ ۲۰۰ کتابِ بی‌امانتِ فصلِ ۲ می‌شود ۱٬۲۰۰. درست است چون id یکتاست، پس «متمایز» دقیقاً همان «کتاب» است.

ولی SUM(DISTINCT b.pages) فاجعه است: ۲۹۰٬۱۲۱ در برابرِ ۴۸۵٬۹۱۱. حالا کمتر از واقعیت است، نه بیشتر. دلیلِ این فاصله یکی است: ۱٬۰۰۰ کتاب فقط ۵۹۸ مقدارِ متمایزِ صفحه دارند، یعنی ۴۰۲ کتاب کاملاً حذف شدند چون کتابِ دیگری هم‌اندازه‌شان بود. فاصله تا جمعِ کلِ کتابخانه از این هم بیشتر است: ۲۰۰ کتابِ بی‌امانت از اول با INNER JOIN رفته بودند و جمعِ ۴۸۵٬۹۱۱ هم آن‌ها را ندارد؛ با آن‌ها می‌شود ۵۸۸٬۰۴۹.

DISTINCT مقدارهای تکراری را حذف می‌کند، و مسئلهٔ ما سطرهای تکراری بود، نه مقدارهای تکراری. برای COUNT روی یک کلیدِ یکتا این دو یکی می‌شوند؛ برای SUM روی هر ستونِ دیگری نه.

۶. درمان: تجمیع قبل از پیوند#

راهِ درست این است که جدولِ یک‌به‌چند را قبل از پیوند به یک سطر برای هر کتاب تبدیل کنیم. آن‌وقت رابطه یک‌به‌یک می‌شود و دیگر چیزی تکثیر نمی‌شود.

یک چیزِ تازه در پرسشِ زیر هست: به‌جای نامِ جدول، یک SELECT داخلِ پرانتز آمده. آن SELECT مثلِ یک جدول رفتار می‌کند که همان‌جا ساخته می‌شود. فعلاً همین‌قدر بدان که هر پرسشی می‌تواند جای یک جدول بنشیند؛ فصلِ ۶ کاملاً بازش می‌کند و نامش را می‌گوید.

print(q("""
    SELECT br.name,
           SUM(b.pages)             AS pages,
           SUM(COALESCE(x.n, 0))    AS loans
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
           ON x.book_id = b.id
    GROUP BY br.id, br.name
    ORDER BY pages DESC
"""))
print()
print("جمع ستون pages:",
      int(q("""
          SELECT SUM(b.pages) AS s
          FROM books AS b
          LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
                 ON x.book_id = b.id
      """).iloc[0, 0]), "| باید برابر 588049 باشد")
print("جمع ستون loans:",
      int(q("""
          SELECT SUM(COALESCE(x.n, 0)) AS s
          FROM books AS b
          LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id) AS x
                 ON x.book_id = b.id
      """).iloc[0, 0]), "| باید برابر 3998 باشد")
    name   pages  loans
0   نسیم  108608    837
1   کوشا  106918    506
2   پویا   97273    378
3  مرکزی   94868   1113
4   دانش   93673    676
5   بهار   86709    488

جمع ستون pages: 588049 | باید برابر 588049 باشد
جمع ستون loans: 3998 | باید برابر 3998 باشد

هر دو ستون درست‌اند و رتبه‌بندی به همان چیزی برگشت که واقعاً هست.

و شمارشِ دوم را همان‌جا چاپ کردیم: جمعِ ستونِ pages دقیقاً ۵۸۸٬۰۴۹ شد — یعنی هیچ کتابی دو بار شمرده نشده — و جمعِ ستونِ loans دقیقاً ۳٬۹۹۸ — یعنی هیچ امانتی گم نشده. این دو خط تفاوتِ یک گزارش با یک ادعا هستند.

LEFT JOIN و COALESCE هم تصادفی نیستند: بدونِ آن‌ها ۲۰۰ کتابِ بی‌امانت دوباره می‌افتادند بیرون و جمعِ صفحه‌ها کمتر از ۵۸۸٬۰۴۹ می‌شد. درمانِ fan-out نباید خودش سطر بیندازد.

✅ چک کن: دو عددِ خطِ آخر باید دقیقاً با عددهای جدولِ بالا بخوانند: جمعِ ستونِ pages برابرِ جمعِ همان ستون در شش سطرِ جدول، و همین‌طور loans. اگر جمعِ pages کمتر درآمد، LEFT JOIN را JOIN نوشته‌ای.

۷. دانه‌بندی، چیزی است که اعلام می‌شود#

هر سه جدولِ این فصل ستونی به نامِ pages داشتند و سه عددِ متفاوت دادند. تفاوتشان در محاسبه نبود، در این بود که یک سطر یعنی چه:

  • جدولِ اول: یک سطر = یک شعبه، و pages جمعِ صفحاتِ کتاب‌های آن شعبه.
  • جدولِ دوم: یک سطر = یک شعبه، ولی pages جمع روی امانت‌های آن شعبه است — عددی که هیچ اسمِ درستی ندارد.
  • جدولِ سوم: مثلِ اولی، به‌علاوهٔ یک ستونِ سالمِ امانت.

پس قبل از هر JOIN بپرس: بعد از این پیوند، یک سطر یعنی چه؟ اگر جوابت عوض شد، هر تجمیعی که از قبل داشتی باید بازبینی شود.

🔧 اگر کار نکرد: اگر داخلِ پرانتزِ بخشِ ۶ GROUP BY را جا بیندازی، خطا نمی‌گیری — SQLite سکوت می‌کند و کلِ جدول را یک سطر می‌کند. ولی اگر نامِ مستعارِ آن جدولِ موقت را جا بیندازی، صریح اعتراض می‌کند:

try:
    q("""
        SELECT b.id, x.n
        FROM books AS b
        LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id)
               ON x.book_id = b.id
        LIMIT 1
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT b.id, x.n
        FROM books AS b
        LEFT JOIN (SELECT book_id, COUNT(*) AS n FROM loans GROUP BY book_id)
               ON x.book_id = b.id
        LIMIT 1
    ': no such column: x.n

ولی خطای واقعیِ این فصل همان ۱٬۹۴۶٬۱۰۳ بود که هیچ پیامی نداشت — و بدترش، آن جدولی که مرکزی را اول نشان داد.

📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ نهایی یک سطر یک شعبه است، و هر عددش روی کتاب‌ها حساب شده نه روی امانت‌ها. با کدام شمارشِ دوم سنجیدی؟ با سه‌تا: COUNT(*) قبل و بعد از پیوند (۱٬۲۰۰ و ۳٬۹۹۸)، جمعِ ستونِ pages که به ۵۸۸٬۰۴۹ برگشت، و جمعِ ستونِ loans که ۳٬۹۹۸ شد. چه کسی از قلم افتاد؟ در نسخهٔ غلط، ۲۰۰ کتابِ بی‌امانت — و شعبهٔ آفتاب که در هر سه جدول غایب است، چون هیچ کتابی ندارد.

🤖 از دستیارت بپرس: «چرا یک JOIN یک‌به‌چند باعث می‌شود SUM بزرگ‌تر از واقعیت شود؟» بعد این را بپرس: «چرا SUM(DISTINCT col) این را درست نمی‌کند، در حالی که COUNT(DISTINCT id) می‌کند؟» — جوابِ درست می‌گوید DISTINCT روی مقدار کار می‌کند نه روی سطر، و برای id این دو یکی می‌شوند چون id یکتاست. اگر جواب گفت «SUM(DISTINCT ...) هم درست است»، همین حالا با عددهای بخشِ ۵ ردش کن.

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

کلمه تلفظ به حروف فارسی یعنی چه
cardinality کاردینالیتی کاردینالیتی؛ اینکه هر سطر چند جفت در طرفِ مقابل دارد
one-to-many وان تو منی یک‌به‌چند؛ یک سطر در این طرف، چند سطر در آن طرف
fan-out فن‌اوت تکثیرِ سطرها در اثرِ یک پیوندِ یک‌به‌چند
pre-aggregation پری‌اگریگیشن تجمیع قبل از پیوند، تا رابطه یک‌به‌یک شود

تمرین‌ها

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

در فصل بعد#

تا اینجا همیشه دو جدولِ متفاوت را به هم وصل کردیم. فصلِ بعد یک جدول را به خودش وصل می‌کند — و با آن، ۲۸ جفت کتاب پیدا می‌کند که عنوان و نویسنده‌شان یکی است. بعد سراغِ CROSS JOIN می‌رود که عمداً همان کاری را می‌کند که این فصل تصادفی‌اش را ترسناک نامید، و نشان می‌دهد ۱٬۲۰۰ ضربدر ۳٬۹۹۸ یعنی چه.

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

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