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

فصل ۶ از ۸

پیشرفت ترم
۰٪

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

سه فصل است یک بدهی روی دستمان مانده. در فصلِ ۳ برای درمانِ fan-out یک SELECT را داخلِ پرانتز به‌جای نامِ جدول گذاشتیم و گفتیم «فصلِ ۶ بازش می‌کند». حالا بازش می‌کنیم، و اسمش زیرپرسش است.

و فصل با تله‌ای تمام می‌شود که ترمِ ۱ فقط اسمش را برد و اینجا واقعاً خطرناک می‌شود. پرسشِ «کدام اعضا هیچ‌کس را معرفی نکرده‌اند؟» با NOT IN نوشته می‌شود و صفر سطر برمی‌گرداند. جوابِ درست ۲۴۴ است. هیچ خطایی، هیچ هشداری.

تفاوتِ ترمِ ۱ با اینجا این است: آنجا NULL را با دست داخلِ فهرست نوشتیم تا ببینی چه می‌شود. اینجا فهرست از یک پرسشِ دیگر می‌آید و هیچ‌کس نمی‌داند داخلش NULL هست یا نه.

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

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

  • یک زیرپرسشِ اسکالر بنویسی که به‌جای یک مقدار در پرسش می‌نشیند
  • یک زیرپرسش را در FROM به‌جای جدول بگذاری و بگویی چرا fan-out را درمان می‌کند
  • بینِ IN و EXISTS انتخاب کنی و بگویی کدام با NULL امن است
  • زیرپرسشِ همبسته را از ناهمبسته تشخیص بدهی و تفاوتشان را با دو عدد نشان بدهی

قبل از شروع#

از فصلِ ۳: درمانِ fan-out با تجمیع قبل از پیوند — همان چیزی که این فصل نامش را می‌گوید.

از ترمِ ۱ فصلِ ۶: منطقِ سه‌ارزشی و اینکه NOT IN با یک NULL در فهرست، هیچ سطری برنمی‌گرداند.

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

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

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

۱. زیرپرسشِ اسکالر: یک پرسش که یک عدد است#

اگر پرسشی یک سطر و یک ستون برگرداند، مثلِ یک عدد رفتار می‌کند و هر جایی که یک عدد می‌تواند بنشیند، او هم می‌تواند.

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

print(q("""
    SELECT br.name,
           COUNT(*) AS books,
           ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM books), 1) AS share
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    GROUP BY br.id, br.name
    ORDER BY books DESC
"""))
    name  books  share
0   کوشا    225   18.8
1   نسیم    208   17.3
2   پویا    204   17.0
3  مرکزی    197   16.4
4   دانش    192   16.0
5   بهار    174   14.5

(SELECT COUNT(*) FROM books) یک بار اجرا می‌شود و عددِ ۱۲۰۰ می‌دهد، و همان عدد در مخرجِ همهٔ سطرها می‌نشیند. و آن 100.0 هم عمدی است: تقسیمِ دو عددِ صحیح در SQLite صحیح می‌ماند و جوابِ همهٔ سطرها صفر می‌شد — همان تلهٔ تقسیمِ صحیحِ ترمِ ۱، که ضرب در یک عددِ اعشاری از کارش می‌اندازد. بدونِ زیرپرسش مجبور بودی اول کل را بگیری، در پایتون نگهش داری، و در پرسشِ دوم بفرستی‌اش — یعنی دو رفت‌وبرگشت و یک عددِ دستی.

✅ چک کن: جمعِ ستونِ share باید ۱۰۰ شود. اینجا دقیقاً ۱۰۰٫۰ می‌شود، ولی این تضمین‌شده نیست: ROUND می‌تواند جمع را کمی جابه‌جا کند. اگر جمع ۱۰۰ نشد، اول شک کن به گرد کردن، بعد به پرسش — و اگر اختلاف بیشتر از چند دهم بود، مشکل از گرد کردن نیست.

و یک چیزِ مهم که این جدول نمی‌گوید: شعبهٔ آفتاب اینجا نیست، پس این «سهم از کلِ کتاب‌ها» است و مخرجش ۱۲۰۰ است — نه «سهم از شعبه‌ها».

۲. زیرپرسش در FROM — بدهیِ فصلِ ۳#

اگر پرسشی چند سطر و چند ستون برگرداند، دیگر یک عدد نیست؛ یک جدول است. و هر جایی که نامِ یک جدول می‌تواند بنشیند، او هم می‌تواند — منتها با یک شرط: باید نامِ مستعار داشته باشد، وگرنه راهی برای اشاره به ستون‌هایش نداری.

این دقیقاً همان چیزی است که در فصلِ ۳ fan-out را درمان کرد. حالا با نامش:

per_book = """
    SELECT book_id, COUNT(*) AS n
    FROM loans
    GROUP BY book_id
"""
print("زیرپرسش، به‌تنهایی — سطرها:", len(q(per_book)))
print(q(per_book + " ORDER BY book_id LIMIT 3"))
print()
print(q(f"""
    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 ({per_book}) AS x ON x.book_id = b.id
    GROUP BY br.id, br.name
    ORDER BY pages DESC
"""))
زیرپرسش، به‌تنهایی — سطرها: 1000
   book_id  n
0        1  4
1        3  3
2        4  2

    name   pages  loans
0   نسیم  108608    837
1   کوشا  106918    506
2   پویا   97273    378
3  مرکزی   94868   1113
4   دانش   93673    676
5   بهار   86709    488

۱٬۰۰۰ سطر — یعنی زیرپرسش، جدولِ ۳٬۹۹۸سطریِ loans را به یک سطر برای هر کتاب تبدیل کرد. و از آن لحظه به بعد رابطه‌اش با books یک‌به‌یک است، پس دیگر چیزی تکثیر نمی‌شود.

همین‌جاست که ارزشِ واقعیِ زیرپرسش در FROM معلوم می‌شود: دانه‌بندی را عوض می‌کند. جدولِ loans دانه‌بندی «یک امانت» دارد و books دانه‌بندی «یک کتاب»؛ زیرپرسش loans را به دانه‌بندیِ books می‌رساند تا دو طرف قابلِ پیوند شوند.

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

۱٬۰۰۰ هم شمارشِ دومِ خودش را دارد: ۱٬۰۰۰ کتابی که دستِ‌کم یک امانت دارند، به‌علاوهٔ ۲۰۰ کتابِ بی‌امانتِ فصلِ ۲، می‌شود ۱٬۲۰۰.

۳. زیرپرسش در WHERE: IN و EXISTS#

اگر پرسشی یک ستون برگرداند، یک فهرست است و می‌شود با IN سراغش رفت. EXISTS کارِ متفاوتی می‌کند: می‌پرسد «آیا این زیرپرسش دستِ‌کم یک سطر برمی‌گرداند؟»

by_in = int(q("""
    SELECT COUNT(*) AS n
    FROM books
    WHERE id IN (SELECT book_id FROM loans WHERE loaned_at >= ?)
""", ("2025-01-01",)).iloc[0, 0])
by_exists = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    WHERE EXISTS (SELECT 1 FROM loans AS l
                  WHERE l.book_id = b.id AND l.loaned_at >= ?)
""", ("2025-01-01",)).iloc[0, 0])

print("با IN     :", by_in)
print("با EXISTS :", by_exists)
print("و با JOIN + DISTINCT از فصل ۵:", 516)
با IN     : 516
با EXISTS : 516
و با JOIN + DISTINCT از فصل ۵: 516

هر سه راه به ۵۱۶ رسیدند، و این خودش یک شمارشِ سه‌گانه است.

تفاوتشان در ساختار است، نه در جواب. EXISTS به سطرِ بیرونی نگاه می‌کند — l.book_id = b.id داخلش هست — پس برای هر کتاب یک بار پرسیده می‌شود. IN فهرست را یک بار می‌سازد و بعد در آن جست‌وجو می‌کند.

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

مهم‌ترین تفاوتشان جای دیگری است، و بخشِ بعد همان است.

۴. NOT IN و تله‌ای که هیچ صدایی ندارد#

جدولِ members ستونی به نامِ referred_by دارد: اگر عضوی را عضوِ دیگری معرفی کرده باشد، شمارهٔ او آنجاست؛ وگرنه NULL.

پرسش: کدام اعضا هیچ‌کس را معرفی نکرده‌اند؟

naive = int(q("""
    SELECT COUNT(*) AS n
    FROM members
    WHERE id NOT IN (SELECT referred_by FROM members)
""").iloc[0, 0])
filtered = int(q("""
    SELECT COUNT(*) AS n
    FROM members
    WHERE id NOT IN (SELECT referred_by FROM members WHERE referred_by IS NOT NULL)
""").iloc[0, 0])
by_not_exists = int(q("""
    SELECT COUNT(*) AS n
    FROM members AS m
    WHERE NOT EXISTS (SELECT 1 FROM members AS r WHERE r.referred_by = m.id)
""").iloc[0, 0])
referrers = int(q("""
    SELECT COUNT(*) AS n
    FROM members
    WHERE id IN (SELECT referred_by FROM members)
""").iloc[0, 0])

print("NOT IN، همان‌طور که همه می‌نویسند:", naive)
print("NOT IN، با فیلترِ IS NOT NULL     :", filtered)
print("NOT EXISTS                        :", by_not_exists)
print()
print("و در مقابل، IN                    :", referrers)
print("آزمون                             :", referrers, "+", filtered, "=", referrers + filtered)
print("سطرهایی که referred_by شان NULL است:",
      int(q("SELECT COUNT(*) AS n FROM members WHERE referred_by IS NULL").iloc[0, 0]))
NOT IN، همان‌طور که همه می‌نویسند: 0
NOT IN، با فیلترِ IS NOT NULL     : 244
NOT EXISTS                        : 244

و در مقابل، IN                    : 56
آزمون                             : 56 + 244 = 300
سطرهایی که referred_by شان NULL است: 225

صفر در برابرِ ۲۴۴.

و بدترین بخشش این است: IN کاملاً درست کار کرد و ۵۶ داد. یعنی همان پرسش، با همان زیرپرسش، در یک جهت درست است و در جهتِ دیگر نه. کسی که IN را امتحان کرده و دیده جواب می‌دهد، هیچ دلیلی ندارد به NOT IN شک کند.

مکانیزمش همان ترمِ ۱ است. x NOT IN (…) یعنی «x نه با این برابر است، نه با آن، نه با آن یکی». آن زیرپرسش ۲۲۵ سطرِ NULL برمی‌گرداند، و «آیا x با یک چیزِ نامعلوم برابر نیست؟» هیچ‌وقت قطعاً درست نمی‌شود. پس کلِ زنجیره هیچ‌وقت درست نمی‌شود و هیچ سطری نمی‌ماند.

NOT EXISTS این مشکل را ندارد چون اصلاً مقایسه‌ای بینِ مقدارها انجام نمی‌دهد — فقط می‌پرسد سطری برگشت یا نه، و جوابِ آن همیشه بله یا خیر است.

قاعدهٔ عملی: NOT IN را روی ستونی که ممکن است NULL داشته باشد ننویس. یا NOT EXISTS بنویس، یا IS NOT NULL را داخلِ زیرپرسش بگذار. و چون در پرسشِ واقعی معمولاً نمی‌دانی زیرپرسشِ کسِ دیگری NULL برمی‌گرداند یا نه، NOT EXISTS انتخابِ پیش‌فرضِ امن‌تری است.

✅ چک کن: ۵۶ به‌علاوهٔ ۲۴۴ باید دقیقاً ۳۰۰ شود، یعنی کلِ اعضا. این آزمون در نسخهٔ اول شکست می‌خورد — ۵۶ به‌علاوهٔ صفر می‌شود ۵۶ — و همان یک خط جلوی انتشارِ یک عددِ غلط را می‌گیرد.

۵. همبسته در برابرِ ناهمبسته#

زیرپرسشِ ناهمبسته به سطرِ بیرونی کاری ندارد؛ یک بار حساب می‌شود و تمام. زیرپرسشِ همبسته به ستونی از سطرِ بیرونی اشاره می‌کند، پس جوابش برای هر سطر فرق می‌کند.

تفاوتشان فنی به‌نظر می‌رسد تا وقتی ببینی دو پرسشِ متفاوت‌اند، نه دو نوشتنِ یک پرسش:

global_avg = int(q("""
    SELECT COUNT(*) AS n
    FROM books
    WHERE pages > (SELECT AVG(pages) FROM books)
""").iloc[0, 0])
branch_avg = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    WHERE b.pages > (SELECT AVG(x.pages) FROM books AS x WHERE x.branch_id = b.branch_id)
""").iloc[0, 0])
only_global = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    WHERE b.pages > (SELECT AVG(pages) FROM books)
      AND b.pages <= (SELECT AVG(x.pages) FROM books AS x WHERE x.branch_id = b.branch_id)
""").iloc[0, 0])
only_branch = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    WHERE b.pages <= (SELECT AVG(pages) FROM books)
      AND b.pages > (SELECT AVG(x.pages) FROM books AS x WHERE x.branch_id = b.branch_id)
""").iloc[0, 0])

print("بالاتر از میانگینِ کل        :", global_avg)
print("بالاتر از میانگینِ شعبهٔ خودش:", branch_avg)
print("فقط در اولی                  :", only_global)
print("فقط در دومی                  :", only_branch)
print("آزمون                        :", global_avg, "-", only_global, "+", only_branch,
      "=", global_avg - only_global + only_branch)
بالاتر از میانگینِ کل        : 589
بالاتر از میانگینِ شعبهٔ خودش: 587
فقط در اولی                  : 5
فقط در دومی                  : 3
آزمون                        : 589 - 5 + 3 = 587

۵۸۹ و ۵۸۷ خیلی نزدیک‌اند و همین خطرناکشان می‌کند. اگر فقط این دو عدد را ببینی، فکر می‌کنی دو راهِ نوشتنِ یک چیزند و اختلافشان نویز است.

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

و این دقیقاً همان چیزی است که عددِ نزدیک پنهانش می‌کند. اختلافِ ۲تایی می‌گوید «فرقی نمی‌کند»؛ آزمونِ سه‌خطی می‌گوید هشت کتاب جابه‌جا شده‌اند.

🔧 اگر کار نکرد: رایج‌ترین خطای زیرپرسشِ اسکالر این است که بیشتر از یک ستون برگرداند. SQLite صریح اعتراض می‌کند:

try:
    q("SELECT id, (SELECT id, name FROM branches LIMIT 1) AS x 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 id, (SELECT id, name FROM branches LIMIT 1) AS x FROM books LIMIT 1': sub-select returns 2 columns - expected 1

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

📏 اندازه بگیر: یک سطر یعنی چه؟ در بخشِ ۲ زیرپرسش دانه‌بندی را عوض کرد: loans با دانه‌بندیِ «یک امانت» وارد شد و با دانه‌بندیِ «یک کتاب» بیرون آمد — و کلِ درمانِ fan-out همین بود. با کدام شمارشِ دوم سنجیدی؟ ۱٬۰۰۰ به‌علاوهٔ ۲۰۰ برابرِ ۱٬۲۰۰؛ ۵۶ به‌علاوهٔ ۲۴۴ برابرِ ۳۰۰؛ و ۵۸۹ منهای ۵ به‌علاوهٔ ۳ برابرِ ۵۸۷. چه کسی از قلم افتاد؟ در بخشِ ۴، نسخهٔ اول همه را انداخت — ۲۴۴ عضو، به‌خاطرِ ۲۲۵ مقدارِ NULL که هیچ‌کدامشان در پرسش دیده نمی‌شدند.

🤖 از دستیارت بپرس: «چرا NOT IN وقتی زیرپرسش NULL برمی‌گرداند صفر سطر می‌دهد، ولی IN درست کار می‌کند؟» بعد این را بپرس: «اگر زیرپرسشِ من امروز NULL ندارد ولی فردا ممکن است داشته باشد، باز هم باید NOT EXISTS بنویسم؟» — جوابِ درست بله است و دلیلش را به این برمی‌گرداند که این خطا هیچ پیامی ندارد، پس روزی که پیش بیاید کسی متوجه نمی‌شود.

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

کلمه تلفظ به حروف فارسی یعنی چه
subquery ساب‌کوئری زیرپرسش؛ پرسشی داخلِ پرسشِ دیگر
scalar subquery اسکالر ساب‌کوئری زیرپرسشی که یک سطر و یک ستون می‌دهد، پس مثلِ یک مقدار است
derived table درایوِد تیبل زیرپرسشی که در FROM می‌نشیند و مثلِ یک جدول رفتار می‌کند
EXISTS اگزیستس می‌پرسد زیرپرسش دستِ‌کم یک سطر دارد یا نه
correlated subquery کورلیتد ساب‌کوئری زیرپرسشِ همبسته؛ به سطرِ بیرونی اشاره می‌کند

تمرین‌ها

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

در فصل بعد#

پرسشِ بخشِ ۲ یک زیرپرسش داشت و خواندنش از وسط شروع می‌شد. با یکی دو مرحلهٔ بیشتر، عملاً غیرقابلِ خواندن می‌شود.

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

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

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