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

فصل ۴ از ۸

پیشرفت ترم
۰٪

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

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

«کدام کتاب‌ها دو بار در فهرست ثبت شده‌اند؟» — این پرسش دربارهٔ رابطهٔ books با جدولِ دیگری نیست؛ دربارهٔ رابطهٔ books با خودش است. جوابش ۲۸ جفت است، که ۲۴تایشان در دو شعبهٔ متفاوت‌اند.

«در کدام ماه‌ها هیچ امانتی نبوده؟» — و این یکی از پرسشِ اول هم بدتر است، چون جوابش در داده نیست. گزارشی که از loans ساخته شود ۳۰ سطر دارد و کامل به‌نظر می‌رسد؛ وقتی کنارِ یک تقویمِ ۴۸ماهه بگذاری‌اش، ۱۸ ماه پیدا می‌شود که هیچ سطری ندارند — و آن ۱۸ ماه دو معنیِ کاملاً متفاوت دارند.

یک شبکهٔ مستطیلی که از تقاطعِ یک ردیف مهره با یک ستون مهره ساخته شده و بعضی خانه‌هایش خالی مانده

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

  • یک جدول را با دو نامِ مستعار به خودش پیوند بزنی و سطرهای تکراری را پیدا کنی
  • بگویی چرا شرطِ a.id < b.id در یک self join اختیاری نیست
  • با CROSS JOIN جدولی بسازی که خانه‌های خالی‌اش دیده می‌شوند
  • تشخیص بدهی یک CROSS JOINِ تصادفی چه شکلی است

قبل از شروع#

از فصلِ ۱ تا ۳: INNER JOIN و LEFT JOIN، نامِ مستعارِ جدول، و fan-out.

از ترمِ ۱: || برای چسباندنِ متن و substr برای برداشتنِ بخشی از یک رشته — هر دو از فصلِ ۵ ترمِ ۱.

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

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

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

۱. یک جدول، دو نام#

JOIN نمی‌داند دو طرفش دو جدولِ متفاوت‌اند یا یکی. تنها چیزی که لازم دارد این است که بتوانی به هر طرف اشاره کنی — و برای همین نامِ مستعار اینجا از یک راحتی به یک ضرورت تبدیل می‌شود.

سه کتاب، که دوتایشان یکی هستند:

con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT, author TEXT)")
con.executemany("INSERT INTO tiny_books VALUES (?, ?, ?)",
                [(1, "الف", "نویسندهٔ یک"), (2, "ب", "نویسندهٔ دو"), (3, "الف", "نویسندهٔ یک")])
con.commit()

both_ways = q("""
    SELECT a.id AS id_a, b.id AS id_b, a.title
    FROM tiny_books AS a
    JOIN tiny_books AS b ON b.title = a.title AND b.author = a.author
    ORDER BY a.id, b.id
""")
one_way = q("""
    SELECT a.id AS id_a, b.id AS id_b, a.title
    FROM tiny_books AS a
    JOIN tiny_books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
    ORDER BY a.id, b.id
""")
print("بدون شرط روی id —", len(both_ways), "سطر")
print(both_ways)
print()
print("با شرط b.id > a.id —", len(one_way), "سطر")
print(one_way)
بدون شرط روی id — 5 سطر
   id_a  id_b title
0     1     1   الف
1     1     3   الف
2     2     2     ب
3     3     1   الف
4     3     3   الف

با شرط b.id > a.id — 1 سطر
   id_a  id_b title
0     1     3   الف

پنج سطر در برابرِ یک سطر، و آن پنج‌تا را می‌شود با انگشت توضیح داد: سه سطر جایی است که هر کتاب با خودش جفت شده (۱ با ۱، ۲ با ۲، ۳ با ۳)، و دو سطرِ دیگر همان یک جفتِ واقعی است که دو بار شمرده شده — یک بار ۱ با ۳ و یک بار ۳ با ۱.

پس b.id > a.id دو کار را با هم انجام می‌دهد: جفتِ هر سطر با خودش را حذف می‌کند، و از هر جفت فقط یک ترتیب را نگه می‌دارد. بدونِ آن، جوابت دقیقاً دو برابرِ واقعیت به‌علاوهٔ تعدادِ کلِ سطرها می‌شود.

۲. همان کار روی ۱٬۲۰۰ کتاب#

raw = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS a
    JOIN books AS b ON b.title = a.title AND b.author = a.author
""").iloc[0, 0])
pairs = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS a
    JOIN books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
""").iloc[0, 0])

print("بدون شرط روی id :", raw)
print("جفت‌های واقعی    :", pairs)
print("آزمون            :", 1200, "+", 2 * pairs, "=", 1200 + 2 * pairs)
بدون شرط روی id : 1256
جفت‌های واقعی    : 28
آزمون            : 1200 + 56 = 1256

۱٬۲۵۶ دقیقاً برابرِ ۱٬۲۰۰ به‌علاوهٔ دو برابرِ ۲۸ است. همان الگوی جدولِ اسباب‌بازی، روی هزار و دویست سطر — و این شمارشِ دوم است که می‌گوید عددِ ۲۸ درست است.

حالا خودِ جفت‌ها. و یک ستونِ مهم کنارشان: در یک شعبه‌اند یا دو شعبه؟

print(q("""
    SELECT a.id AS id_a, b.id AS id_b, a.title, a.author
    FROM books AS a
    JOIN books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
    ORDER BY a.id
    LIMIT 4
"""))
print()
print("جفت‌هایی که در دو شعبهٔ متفاوت‌اند:",
      int(q("""
          SELECT COUNT(*) AS n
          FROM books AS a
          JOIN books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
          WHERE b.branch_id <> a.branch_id
      """).iloc[0, 0]))
print("جفت‌هایی که در یک شعبه‌اند       :",
      int(q("""
          SELECT COUNT(*) AS n
          FROM books AS a
          JOIN books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
          WHERE b.branch_id = a.branch_id
      """).iloc[0, 0]))
   id_a  id_b            title        author
0    14   959    بازگشت به برف  فرهاد کاشانی
1    18   814      روزهای دریا    زهرا دهقان
2    25   118     بازگشت به شب    نگار سلیمی
3    33   796  در جست‌وجوی ابر  بهرام کاشانی

جفت‌هایی که در دو شعبهٔ متفاوت‌اند: 24
جفت‌هایی که در یک شعبه‌اند       : 4

۲۴ به‌علاوهٔ ۴ می‌شود ۲۸ — شمارشِ دومِ همان عدد، این بار از راهِ تقسیم به دو گروه.

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

⚠️ مواظب باش: این جفت‌ها را با «عنوان و نویسندهٔ یکسان» پیدا کردیم. در برشِ این ترم نامِ نویسنده یکدست است، ولی در ترمِ ۱ دیدیم که یک نویسنده می‌تواند با نویسهٔ عربی هم ثبت شده باشد. روی چنین داده‌ای این عدد کف است، نه سقف. جمله‌ای که در گزارش می‌نویسی باید همین را بگوید.

۳. CROSS JOIN: همهٔ جفت‌های ممکن#

CROSS JOIN بندِ ON ندارد، چون شرطی در کار نیست: هر سطرِ چپ با هر سطرِ راست.

con.execute("DROP TABLE IF EXISTS tiny_branches")
con.execute("DROP TABLE IF EXISTS tiny_quarters")
con.execute("CREATE TABLE tiny_branches (name TEXT)")
con.execute("CREATE TABLE tiny_quarters (label TEXT)")
con.executemany("INSERT INTO tiny_branches VALUES (?)", [("مرکزی",), ("نسیم",), ("دانش",)])
con.executemany("INSERT INTO tiny_quarters VALUES (?)", [("q1",), ("q2",), ("q3",), ("q4",)])
con.commit()

grid = q("""
    SELECT br.name, qt.label
    FROM tiny_branches AS br
    CROSS JOIN tiny_quarters AS qt
    ORDER BY br.rowid, qt.rowid
""")
print("۳ شعبه ×", 4, "فصل =", len(grid), "سطر")
print(grid.head(5))
۳ شعبه × 4 فصل = 12 سطر
    name label
0  مرکزی    q1
1  مرکزی    q2
2  مرکزی    q3
3  مرکزی    q4
4   نسیم    q1

سه ضربدر چهار می‌شود دوازده — همان اصلِ ضربی که در فصلِ ۳ دیدیم، این بار به‌عنوانِ کاری که عمداً می‌خواهیمش.

روی جدول‌های واقعی همین ضرب عددِ ترسناکی می‌سازد:

cross = int(q("SELECT COUNT(*) AS n FROM books CROSS JOIN loans").iloc[0, 0])
print("books CROSS JOIN loans:", cross)
print("۱۲۰۰ × ۳۹۹۸           :", 1200 * 3998)
print("یکی‌اند؟", cross == 1200 * 3998)
books CROSS JOIN loans: 4797600
۱۲۰۰ × ۳۹۹۸           : 4797600
یکی‌اند؟ True

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

۴. جدولِ تقویم: سطرهایی که در داده نیستند#

اینجا CROSS JOIN از یک کنجکاوی به یک ابزارِ جدی تبدیل می‌شود. پرسش این است: در هر ماه چند امانت داشتیم؟

اگر از loans شروع کنی، فقط ماه‌هایی را می‌بینی که سطری دارند. ماهی که هیچ امانتی نداشته، اصلاً سطری ندارد که دیده شود. راهِ حل این است که اول تقویم را بسازی و بعد داده را به آن وصل کنی.

con.execute("DROP TABLE IF EXISTS cal_years")
con.execute("DROP TABLE IF EXISTS cal_months")
con.execute("CREATE TABLE cal_years (y TEXT)")
con.execute("CREATE TABLE cal_months (m TEXT)")
con.executemany("INSERT INTO cal_years VALUES (?)",
                [("2022",), ("2023",), ("2024",), ("2025",)])
con.executemany("INSERT INTO cal_months VALUES (?)", [(f"{n:02d}",) for n in range(1, 13)])
con.commit()

print("سطرهای تقویم              :",
      int(q("SELECT COUNT(*) AS n FROM cal_years CROSS JOIN cal_months").iloc[0, 0]))
print("ماه‌هایی که در loans هستند:",
      int(q("SELECT COUNT(DISTINCT substr(loaned_at, 1, 7)) AS n FROM loans").iloc[0, 0]))
سطرهای تقویم              : 48
ماه‌هایی که در loans هستند: 30

تقویم ۴۸ ماه دارد و داده ۳۰ ماه. هجده ماه هیچ سطری ندارند — و هجده سطری که هرگز در هیچ گزارشی ظاهر نمی‌شوند مگر اینکه خودت جایشان را از قبل باز کرده باشی.

print(q("""
    SELECT y.y || '-' || m.m AS ym, COUNT(l.id) AS loans
    FROM cal_years AS y
    CROSS JOIN cal_months AS m
    LEFT JOIN loans AS l ON substr(l.loaned_at, 1, 7) = y.y || '-' || m.m
    GROUP BY ym
    HAVING loans = 0
    ORDER BY ym
"""))
         ym  loans
0   2022-01      0
1   2022-02      0
2   2022-03      0
3   2022-04      0
4   2022-05      0
5   2022-06      0
6   2022-07      0
7   2022-08      0
8   2022-09      0
9   2022-10      0
10  2022-11      0
11  2022-12      0
12  2025-07      0
13  2025-08      0
14  2025-09      0
15  2025-10      0
16  2025-11      0
17  2025-12      0

هجده ماه، و دقیقاً دو دسته — که همین تقسیمِ دو دسته، درسِ اصلیِ این بخش است.

دوازده ماهِ اولِ فهرست مالِ سالی هستند که ثبتِ امانت هنوز شروع نشده بود. شش ماهِ آخر هنوز نیامده‌اند. هیچ‌کدامشان «صفر امانت» نیستند: دستهٔ اول یعنی «نمی‌دانیم» و دستهٔ دوم یعنی «هنوز نه». ستونِ loans برای این دو حالت — و برای ماهی که واقعاً صفر امانت داشته — همان عددِ صفر را چاپ می‌کند و هیچ راهی برای تفکیکِ این سه ندارد.

و بدترین شکلش دورهٔ ناقصِ آخر است: اگر امروز نموداری بکشی که آخرین ستونش ماهِ جاری باشد، آن ستون همیشه کوتاه‌تر است — نه چون کار کم شده، بلکه چون ماه تمام نشده.

✅ چک کن: ۳۰ ماهِ دارای داده به‌علاوهٔ ۱۸ ماهِ خالی باید ۴۸ شود. اگر HAVING loans = 0 را برداری و سطرها را بشماری باید همان ۴۸ دربیاید. اگر عددِ دیگری گرفتی، LEFT JOIN را JOIN نوشته‌ای و ماه‌های خالی همان‌جا افتاده‌اند بیرون.

۵. CROSS JOINِ تصادفی#

SQL یک نگارشِ قدیمی‌تر هم دارد که جدول‌ها را با ویرگول جدا می‌کند و شرط را در WHERE می‌گذارد. کاملاً معتبر است — و دقیقاً به همین دلیل خطرناک:

with_condition = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b, loans AS l
    WHERE l.book_id = b.id
""").iloc[0, 0])
without_condition = int(q("SELECT COUNT(*) AS n FROM books AS b, loans AS l").iloc[0, 0])

print("با شرط در WHERE :", with_condition)
print("بدون شرط        :", without_condition)
print("چند برابر       :", round(without_condition / with_condition, 1))
با شرط در WHERE : 3998
بدون شرط        : 4797600
چند برابر       : 1200.0

یک خطِ WHERE جا افتاد و نتیجه ۱٬۲۰۰ برابر شد.

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

🔧 اگر کار نکرد: رایج‌ترین خطای self join این است که نامِ مستعار را جا بیندازی. آن‌وقت SQLite نمی‌داند کدام نسخهٔ جدول را می‌گویی:

try:
    q("""
        SELECT title
        FROM books
        JOIN books ON books.title = books.title
        LIMIT 1
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT title
        FROM books
        JOIN books ON books.title = books.title
        LIMIT 1
    ': ambiguous column name: title

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

🤖 از دستیارت بپرس: «چرا در یک self join باید شرطی مثلِ a.id < b.id بگذاریم؟» بعد این را بپرس: «اگر به‌جای < از <> استفاده کنم چه می‌شود؟» — جوابِ درست می‌گوید <> فقط جفتِ هر سطر با خودش را حذف می‌کند و هر جفت را همچنان دو بار می‌شمارد. اگر جواب گفت هر دو یکی‌اند، با عددهای بخشِ ۱ ردش کن.

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

کلمه تلفظ به حروف فارسی یعنی چه
self join سلف جوین پیوندِ یک جدول با خودش، با دو نامِ مستعار
CROSS JOIN کراس جوین هر سطرِ چپ با هر سطرِ راست، بدونِ شرط
Cartesian product کارتزین پرادکت ضربِ دکارتی؛ نامِ ریاضیِ همان همهٔ‌جفت‌های‌ممکن
calendar table کلندر تیبل جدولی از همهٔ دوره‌ها، تا دورهٔ بی‌داده هم دیده شود

تمرین‌ها

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

در فصل بعد#

تا اینجا همیشه دو جدول را کنارِ هم گذاشتیم تا سطری پهن‌تر بسازیم. فصلِ بعد کارِ دیگری می‌کند: دو نتیجه را رویِ هم می‌گذارد تا سطرهایشان یکی شود. و با آن سراغِ خطایی می‌رود که در هر گزارشی که دو شرط دارد کمین کرده: دو دسته ۱۶۱ و ۵۱۶ کتاب دارند، ولی تعدادِ کتاب‌هایی که دستِ‌کم یکی از دو شرط را دارند ۶۷۷ نیست — ۶۱۲ است.

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

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