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

فصل ۵ از ۸

پیشرفت ترم
۰٪

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

دو دستهٔ کاملاً معمولی: ۱۶۱ کتابِ شعر و ۵۱۶ کتابی که امسال امانت رفته‌اند.

پرسش: «چند کتاب دستِ‌کم یکی از این دو ویژگی را دارند؟» جوابِ اولی که همه می‌دهند ۱۶۱ به‌علاوهٔ ۵۱۶ است، یعنی ۶۷۷. جوابِ درست ۶۱۲ است.

اختلافشان ۶۵ است و ۶۵ عددِ بی‌ربطی نیست: دقیقاً همان کتاب‌هایی هستند که هم شعرند و هم امسال امانت رفته‌اند — و در جمعِ ساده دو بار شمرده شدند.

این فصل سه عملگر می‌آورد که کارشان دقیقاً همین است: رویِ هم گذاشتنِ دو نتیجه، پیدا کردنِ اشتراکشان، و کم کردنِ یکی از دیگری.

دو دایرهٔ متقاطع روی کاغذ که ناحیهٔ اشتراکشان با هاشورِ متفاوتی مشخص شده

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

  • دو نتیجه را با UNION و UNION ALL رویِ هم بگذاری و تفاوتشان را با عدد نشان بدهی
  • اشتراک و تفاضلِ دو دسته را با INTERSECT و EXCEPT بگیری
  • ثابت کنی که چهار عددت با هم می‌خوانند
  • بگویی چرا یک عملگرِ مجموعه‌ای روی نتیجهٔ یک JOINِ یک‌به‌چند خطرناک است

قبل از شروع#

از فصلِ ۳: fan-out — یک JOIN یک‌به‌چند سطر تکثیر می‌کند. این فصل نشان می‌دهد آن سطرهای تکثیرشده وقتی وارد یک UNION ALL شوند چه بلایی سرِ عدد می‌آورند.

دانه‌بندی: هر چهار پرسشِ این فصل یک ستون برمی‌گردانند — idِ کتاب — و یک سطر یعنی یک کتاب. این را عمداً ساده نگه داشتیم چون تمامِ درسِ فصل دربارهٔ شمردنِ همان سطرهاست.

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

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

۱. JOIN کنارِ هم می‌گذارد، UNION رویِ هم#

تفاوتِ این دو در یک تصویر جا می‌شود. JOIN سطر را پهن‌تر می‌کند: ستون‌های جدولِ دوم به سطرِ جدولِ اول اضافه می‌شوند. UNION نتیجه را بلندتر می‌کند: سطرهای پرسشِ دوم زیرِ سطرهای پرسشِ اول می‌نشینند.

پس UNION یک شرطِ سخت دارد که JOIN ندارد: دو طرف باید تعدادِ ستونِ برابر داشته باشند و ستون‌هایشان به یک ترتیب بیایند.

con.execute("DROP TABLE IF EXISTS tiny_left")
con.execute("DROP TABLE IF EXISTS tiny_right")
con.execute("CREATE TABLE tiny_left (v INTEGER)")
con.execute("CREATE TABLE tiny_right (v INTEGER)")
con.executemany("INSERT INTO tiny_left VALUES (?)", [(1,), (2,), (3,)])
con.executemany("INSERT INTO tiny_right VALUES (?)", [(3,), (4,)])
con.commit()

print("UNION ALL:", list(q("SELECT v FROM tiny_left UNION ALL SELECT v FROM tiny_right")["v"]))
print("UNION    :", list(q("SELECT v FROM tiny_left UNION SELECT v FROM tiny_right")["v"]))
print("INTERSECT:", list(q("SELECT v FROM tiny_left INTERSECT SELECT v FROM tiny_right")["v"]))
print("EXCEPT   :", list(q("SELECT v FROM tiny_left EXCEPT SELECT v FROM tiny_right")["v"]))
UNION ALL: [1, 2, 3, 3, 4]
UNION    : [1, 2, 3, 4]
INTERSECT: [3]
EXCEPT   : [1, 2]

سه عدد و دو عدد. UNION ALL پنج سطر داد و عددِ ۳ را دو بار آورد؛ UNION چهار سطر داد و یک بار. UNION سطرهای تکراری را حذف می‌کند و UNION ALL نمی‌کند — و این تنها تفاوتشان است.

INTERSECT فقط چیزی را می‌دهد که در هر دو هست، و EXCEPT چیزی را که در اولی هست و در دومی نیست. دقت کن EXCEPT متقارن نیست: جای دو طرف را عوض کنی، جواب [4] می‌شود.

۲. دو دستهٔ واقعی#

متنِ دو پرسش را در دو متغیرِ پایتونی نگه می‌داریم تا بتوانیم بعداً با هر سه عملگر ترکیبشان کنیم. و برای شمردنِ نتیجه، هر پرسش را داخلِ پرانتز به‌جای نامِ جدول می‌گذاریم — همان کاری که در فصلِ ۳ برای درمانِ fan-out کردیم. فعلاً همین‌قدر بدان که هر پرسشی می‌تواند جای یک جدول بنشیند؛ فصلِ ۶ کاملاً بازش می‌کند و نامش را می‌گوید.

poetry = "SELECT id FROM books WHERE category = ?"
loaned = """
    SELECT DISTINCT b.id
    FROM books AS b
    JOIN loans AS l ON l.book_id = b.id
    WHERE l.loaned_at >= ?
"""
n_poetry = int(q(f"SELECT COUNT(*) AS n FROM ({poetry})", ("شعر",)).iloc[0, 0])
n_loaned = int(q(f"SELECT COUNT(*) AS n FROM ({loaned})", ("2025-01-01",)).iloc[0, 0])

print("کتاب‌های شعر               :", n_poetry)
print("کتاب‌هایی که امسال رفته‌اند :", n_loaned)
print("جمعِ ساده                  :", n_poetry + n_loaned)
کتاب‌های شعر               : 161
کتاب‌هایی که امسال رفته‌اند : 516
جمعِ ساده                  : 677

عددِ ۶۷۷ جوابِ هیچ پرسشی نیست. یک کتابِ شعر که امسال هم امانت رفته، در هر دو دسته هست و در این جمع دو بار شمرده شده.

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

۳. UNION همان جمع را درست انجام می‌دهد#

union_all = int(q(f"SELECT COUNT(*) AS n FROM ({poetry} UNION ALL {loaned})",
                  ("شعر", "2025-01-01")).iloc[0, 0])
union_ = int(q(f"SELECT COUNT(*) AS n FROM ({poetry} UNION {loaned})",
                ("شعر", "2025-01-01")).iloc[0, 0])
both = int(q(f"SELECT COUNT(*) AS n FROM ({poetry} INTERSECT {loaned})",
             ("شعر", "2025-01-01")).iloc[0, 0])

print("UNION ALL     :", union_all)
print("UNION         :", union_)
print("INTERSECT     :", both)
print("آزمون         :", union_all, "-", both, "=", union_all - both)
UNION ALL     : 677
UNION         : 612
INTERSECT     : 65
آزمون         : 677 - 65 = 612

۶۷۷ منهای ۶۵ می‌شود ۶۱۲، و این دقیقاً همان UNION است.

قاعده‌اش یک جمله است و در هر جایی که دو دستهٔ هم‌پوشان داری صادق است: اندازهٔ اجتماع برابرِ جمعِ دو اندازه منهای اندازهٔ اشتراک. آن ۶۵ کتاب دو بار در جمع آمده بودند، پس یک بار باید کم شوند.

و توجه کن UNION ALL عددِ ۶۷۷ داد — یعنی همان جمعِ ساده. این تصادفی نیست: UNION ALL هیچ چیزی را حذف نمی‌کند، پس همیشه دقیقاً جمعِ دو تعداد است.

🌱 ریشه‌اش کجاست: همین قاعده در احتمال هم هست و همان‌جا هم بیشترین اشتباه را می‌سازد: احتمالِ «الف یا ب» جمعِ دو احتمال نیست، مگر وقتی دو پیشامد هیچ اشتراکی نداشته باشند. اگر داشته باشند، اشتراک دو بار شمرده می‌شود و باید یک بار کم شود. ریشه ترمِ ۶ فصل ۳ دقیقاً همین را از پایه باز می‌کند.

۴. INTERSECT و EXCEPT، و آزمونی که هر چهار عدد را می‌سنجد#

only_poetry = int(q(f"SELECT COUNT(*) AS n FROM ({poetry} EXCEPT {loaned})",
                    ("شعر", "2025-01-01")).iloc[0, 0])
only_loaned = int(q(f"SELECT COUNT(*) AS n FROM ({loaned} EXCEPT {poetry})",
                    ("2025-01-01", "شعر")).iloc[0, 0])

print("فقط شعر            :", only_poetry)
print("فقط امانتِ امسال   :", only_loaned)
print("هر دو              :", both)
print()
print("آزمون ۱ — شعر    :", only_poetry, "+", both, "=", only_poetry + both, "| باید 161 باشد")
print("آزمون ۲ — امانت  :", only_loaned, "+", both, "=", only_loaned + both, "| باید 516 باشد")
print("آزمون ۳ — اجتماع :", only_poetry, "+", both, "+", only_loaned, "=",
      only_poetry + both + only_loaned, "| باید 612 باشد")
فقط شعر            : 96
فقط امانتِ امسال   : 451
هر دو              : 65

آزمون ۱ — شعر    : 96 + 65 = 161 | باید 161 باشد
آزمون ۲ — امانت  : 451 + 65 = 516 | باید 516 باشد
آزمون ۳ — اجتماع : 96 + 65 + 451 = 612 | باید 612 باشد

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

این ساده‌ترین شمارشِ دومی است که در کارِ گزارش‌نویسی سراغ داریم و نیم دقیقه وقت می‌گیرد.

✅ چک کن: «فقط شعر» و «هر دو» با هم باید دقیقاً ۱۶۱ شوند. اگر نشد، دو پرسشت شرطِ یکسانی ندارند. سطرِ تکراری علتش نیست: EXCEPT و INTERSECT هم مثلِ UNION نتیجه را یکتا می‌کنند.

۵. سازگاریِ ستون‌ها#

UNION هیچ حدسی نمی‌زند. اگر دو طرف تعدادِ ستونِ برابر نداشته باشند، صریح اعتراض می‌کند:

🔧 اگر کار نکرد: این پیام یعنی دو طرف تعدادِ ستونشان یکی نیست:

try:
    q("""
        SELECT id, title FROM books WHERE category = ?
        UNION
        SELECT id FROM books WHERE year >= ?
    """, ("شعر", 2020))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT id, title FROM books WHERE category = ?
        UNION
        SELECT id FROM books WHERE year >= ?
    ': SELECTs to the left and right of UNION do not have the same number of result columns

ولی خطای خطرناک‌تر خطا نمی‌دهد: اگر تعدادِ ستون‌ها برابر باشد و ترتیبشان فرق کند، SQLite بی‌صدا قبول می‌کند. SELECT id, title در یک طرف و SELECT title, id در طرفِ دیگر، یک نتیجهٔ کاملاً بی‌معنی می‌سازد که هیچ‌کس هشدارش را نمی‌دهد.

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

۶. UNION ALL روی نتیجه‌ای که تکثیر شده#

حالا خطری که مخصوصِ این ترم است. در بخشِ ۲ در پرسشِ دوم DISTINCT نوشتیم. اگر ننویسیم چه می‌شود؟

with_distinct = int(q("""
    SELECT COUNT(*) AS n FROM (
        SELECT DISTINCT b.id
        FROM books AS b
        JOIN loans AS l ON l.book_id = b.id
        WHERE l.loaned_at >= ?
    )
""", ("2025-01-01",)).iloc[0, 0])
without_distinct = int(q("""
    SELECT COUNT(*) AS n FROM (
        SELECT b.id
        FROM books AS b
        JOIN loans AS l ON l.book_id = b.id
        WHERE l.loaned_at >= ?
    )
""", ("2025-01-01",)).iloc[0, 0])

print("با DISTINCT   :", with_distinct, "کتاب")
print("بدون DISTINCT :", without_distinct, "سطر")
print()
print("UNION ALL با نسخهٔ بدون DISTINCT:", 161 + without_distinct)
print("جوابِ درست                      :", 612)
با DISTINCT   : 516 کتاب
بدون DISTINCT : 770 سطر

UNION ALL با نسخهٔ بدون DISTINCT: 931
جوابِ درست                      : 612

۹۳۱ در برابرِ ۶۱۲. همان fan-out فصلِ ۳: ۵۱۶ کتاب، ۷۷۰ امانت، و هر کتابی که امسال دو بار امانت رفته دو سطر دارد.

نکتهٔ ظریف این است که UNION این را درست می‌کرد و UNION ALL نه. یعنی انتخابِ بینِ آن دو، وقتی یک طرف از یک JOINِ یک‌به‌چند می‌آید، دیگر یک ترجیحِ سلیقه‌ای نیست.

قاعده: UNION ALL را وقتی بنویس که مطمئنی دو طرف هم‌پوشانی ندارند و هیچ‌کدام سطرِ تکراری ندارند — مثلاً وقتی داری داده‌های دو ماهِ متفاوت را پشتِ سرِ هم می‌گذاری. در هر حالتِ دیگری UNION بنویس، و اگر عددت با UNION ALL فرق کرد، همان تفاوت را نگاه کن: اگر هیچ طرف سطرِ تکراری نداشته باشد، اندازهٔ اشتراکشان است و باید بدانی چیست. اگر داشته باشد، تکراری‌های درونِ هر طرف هم در همان تفاوت است.

📏 اندازه بگیر: یک سطر یعنی چه؟ در هر چهار پرسشِ این فصل یک سطر یک کتاب است — تا بخشِ ۶، که یک سطر ناگهان یک امانت شد و همان عدد را از ۵۱۶ به ۷۷۰ رساند. با کدام شمارشِ دوم سنجیدی؟ با سه آزمونِ بخشِ ۴ که هر سه دسته را به ۱۶۱ و ۵۱۶ و ۶۱۲ برگرداندند، و با تفریقِ ۶۷۷ منهای ۶۵ که مستقل از آن‌ها به همان ۶۱۲ رسید. چه کسی از قلم افتاد؟ کتاب‌هایی که امسال امانت نرفته‌اند و شعر هم نیستند — ۵۸۸ کتاب از ۱٬۲۰۰ که در هیچ‌کدام از این چهار عدد نیستند، و یک گزارشِ صادقانه همین را هم می‌گوید.

🤖 از دستیارت بپرس: «چه فرقی بینِ UNION و UNION ALL هست و کِی باید کدام را بنویسم؟» بعد این را بپرس: «اگر یکی از دو طرف از یک JOINِ یک‌به‌چند بیاید، کدامشان عددِ درست می‌دهد؟» — جوابِ درست می‌گوید UNION، و دلیلش را به حذفِ سطرهای تکراری برمی‌گرداند. اگر جواب فقط گفت «UNION ALL سریع‌تر است»، پرسشِ درستی جواب نداده.

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

کلمه تلفظ به حروف فارسی یعنی چه
UNION یونیون رویِ هم گذاشتنِ دو نتیجه، با حذفِ تکراری‌ها
UNION ALL یونیون آل همان، بدونِ حذفِ تکراری‌ها
INTERSECT اینترسکت فقط سطرهایی که در هر دو نتیجه هستند
EXCEPT اکسپت سطرهایی که در اولی هستند و در دومی نه
double counting دابل کانتینگ دوباره‌شماری؛ شمردنِ اشتراکِ دو دسته در هر دو

تمرین‌ها

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

در فصل بعد#

از فصلِ ۳ یک بدهی روی دستمان مانده: آن SELECTی که داخلِ پرانتز به‌جای نامِ جدول نشست و گفتیم «فصلِ ۶ بازش می‌کند». در همین فصل هم چند بار از همان استفاده کردیم بدونِ اینکه نامش را ببریم.

فصلِ بعد بدهی را می‌پردازد: زیرپرسش — داخلِ SELECT، داخلِ FROM، و داخلِ WHERE. و با یک تلهٔ آشنا تمام می‌شود: پرسشی که با NOT IN نوشته شده، صفر سطر برمی‌گرداند در حالی که جوابِ درستش ۲۴۴ است.

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

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