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

فصل ۷ از ۸

پیشرفت ترم
۰٪

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

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

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

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

چند حوضچهٔ پله‌ای که آب از هرکدام به بعدی می‌ریزد و هر پله شیرِ نمونه‌برداریِ خودش را دارد

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

  • یک پرسشِ چندمرحله‌ای را با WITH بنویسی و مرحله‌هایش را نام‌گذاری کنی
  • هر مرحله را جدا اجرا کنی و با یک شمارش بسنجی‌اش
  • یک CTE بازگشتی بنویسی که یک سلسله‌مراتب را می‌پیماید
  • ثابت کنی که پیمایش هیچ گره‌ای را جا نینداخته

قبل از شروع#

از فصلِ ۶: زیرپرسش در FROM. CTE همان است با یک نام و یک جای بهتر.

از فصلِ ۴ و ۵: CROSS JOIN و UNION ALL — هر دو در این فصل لازم می‌شوند.

دانه‌بندی: هر مرحلهٔ یک CTE دانه‌بندیِ خودش را دارد و این تغییرِ دانه‌بندی دقیقاً همان چیزی است که نام‌گذاری قابلِ دیدنش می‌کند.

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

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

۱. پرسشی که خواندنش از وسط شروع می‌شود#

پرسش این است: در کدام شعبه‌ها نرخِ امانت به‌ازای هر کتاب از میانگینِ کلِ کتابخانه بالاتر است؟

با ابزارِ فصلِ ۶ نوشتنی است، ولی این‌طور درمی‌آید:

nested = q("""
    SELECT br.name,
           ROUND(1.0 * SUM(COALESCE(x.n, 0)) / COUNT(*), 2) AS loans_per_book
    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
    HAVING 1.0 * SUM(COALESCE(x.n, 0)) / COUNT(*)
           > (SELECT 1.0 * (SELECT COUNT(*) FROM loans) / (SELECT COUNT(*) FROM books))
    ORDER BY loans_per_book DESC
""")
print(nested)
    name  loans_per_book
0  مرکزی            5.65
1   نسیم            4.02
2   دانش            3.52

جوابش درست است. مشکل این است که برای فهمیدنش باید از داخلی‌ترین پرانتز شروع کنی و بیایی بیرون — و همان عبارتِ SUM(COALESCE(x.n, 0)) / COUNT(*) دو بار نوشته شده، یک بار در SELECT و یک بار در HAVING. اگر روزی یکی‌شان را عوض کنی و آن یکی را یادت برود، پرسش بی‌صدا چیزِ دیگری می‌شود.

۲. همان پرسش، مرحله به مرحله#

WITH قبل از SELECT می‌آید و هر مرحله را با یک نام تعریف می‌کند. مرحله‌ها به‌ترتیب خوانده می‌شوند و هرکدام می‌تواند از قبلی‌ها استفاده کند.

STAGES = """
    WITH loans_per_book AS (
        SELECT book_id, COUNT(*) AS n
        FROM loans
        GROUP BY book_id
    ),
    book_stats AS (
        SELECT b.branch_id, COALESCE(lpb.n, 0) AS loans
        FROM books AS b
        LEFT JOIN loans_per_book AS lpb ON lpb.book_id = b.id
    ),
    branch_stats AS (
        SELECT branch_id, COUNT(*) AS books, SUM(loans) AS loans
        FROM book_stats
        GROUP BY branch_id
    ),
    overall AS (
        SELECT 1.0 * SUM(loans) / SUM(books) AS loans_per_book
        FROM branch_stats
    )
"""

with_cte = q(STAGES + """
    SELECT br.name,
           ROUND(1.0 * s.loans / s.books, 2) AS loans_per_book
    FROM branch_stats AS s
    JOIN branches AS br ON br.id = s.branch_id
    CROSS JOIN overall AS o
    WHERE 1.0 * s.loans / s.books > o.loans_per_book
    ORDER BY loans_per_book DESC
""")
print(with_cte)
print()
print("همان نتیجهٔ بخش ۱؟", with_cte.equals(nested))
    name  loans_per_book
0  مرکزی            5.65
1   نسیم            4.02
2   دانش            3.52

همان نتیجهٔ بخش ۱؟ True

همان سه شعبه، همان سه عدد — و این بار از بالا به پایین خواندنی.

چهار نامی که گذاشتیم مستندسازی‌اند، نه تزئین. loans_per_book می‌گوید «برای هر کتاب، چند امانت»؛ book_stats می‌گوید «هر کتاب با تعدادِ امانتش»؛ branch_stats می‌گوید «هر شعبه با دو عددش». کسی که این پرسش را شش ماه بعد باز می‌کند، بدونِ اجرا کردنش می‌فهمد هر مرحله دانه‌بندی‌اش چیست.

overall هم نکتهٔ خودش را دارد: یک CTE که فقط یک سطر و یک ستون دارد — و برای رساندنش به همهٔ سطرها از CROSS JOIN فصلِ ۴ استفاده کردیم. اینجا CROSS JOIN دقیقاً همان کاری را می‌کند که باید: یک عدد را کنارِ هر سطر می‌گذارد. شش سطر ضربدر یک سطر می‌شود شش سطر، پس هیچ تکثیری در کار نیست.

⚠️ مواظب باش: یک CROSS JOIN فقط وقتی بی‌خطر است که طرفِ دیگرش دقیقاً یک سطر داشته باشد. اگر overall به‌خاطرِ یک اشتباه دو سطر برگرداند، نتیجه بی‌صدا دو برابر می‌شود — همان fan-out فصلِ ۳، این بار از راهی که به‌نظر بی‌خطر می‌آمد.

۳. اجرای تکه‌تکه: هر مرحله را جدا بشمار#

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

for stage in ("loans_per_book", "book_stats", "branch_stats"):
    print(f"{stage:<16}:", len(q(STAGES + f" SELECT * FROM {stage}")), "سطر")
print()
print(q(STAGES + " SELECT * FROM branch_stats ORDER BY branch_id"))
print()
print(q(STAGES + " SELECT SUM(books) AS books, SUM(loans) AS loans FROM branch_stats"))
loans_per_book  : 1000 سطر
book_stats      : 1200 سطر
branch_stats    : 6 سطر

   branch_id  books  loans
0          1    197   1113
1          2    208    837
2          3    192    676
3          4    174    488
4          5    225    506
5          6    204    378

   books  loans
0   1200   3998

سه عدد، سه دانه‌بندی، و هر سه قابلِ بررسی:

  • loans_per_book باید یک سطر برای هر کتابی داشته باشد که دستِ‌کم یک امانت دارد — ۱٬۰۰۰، و می‌دانیم ۲۰۰ کتاب بی‌امانت‌اند.
  • book_stats باید یک سطر برای هر کتاب داشته باشد — ۱٬۲۰۰. اگر اینجا ۱٬۰۰۰ می‌شد، یعنی LEFT JOIN را JOIN نوشته بودیم.
  • branch_stats باید یک سطر برای هر شعبه‌ای داشته باشد که کتاب دارد — ۶.

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

✅ چک کن: اگر book_stats عددی جز ۱۲۰۰ داد، مرحلهٔ دومت کتاب می‌اندازد یا تکثیر می‌کند و هیچ عددی بعد از آن قابلِ اعتماد نیست. این تنها راهِ پیدا کردنِ محلِ دقیقِ خرابی در یک پرسشِ چندمرحله‌ای است.

۴. CTE بازگشتی: پیمودنِ یک سلسله‌مراتب#

جدولِ sections بخش‌های کتابخانه را نگه می‌دارد و هر بخش به بخشِ بالادستی‌اش اشاره می‌کند:

print(q("SELECT id, name, parent_id FROM sections ORDER BY id"))
    id         name  parent_id
0    1     کتابخانه        NaN
1    2       ادبیات        1.0
2    3         علوم        1.0
3    4         کودک        1.0
4    5          شعر        2.0
5    6         رمان        2.0
6    7   شعر کلاسیک        5.0
7    8    شعر معاصر        5.0
8    9        فیزیک        3.0
9   10         زیست        3.0
10  11    تاریخ علم        3.0
11  12  داستان کودک        4.0

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

WITH RECURSIVE این را حل می‌کند. ساختارش دو تکه دارد که با UNION ALL به هم وصل شده‌اند: تکهٔ اول نقطهٔ شروع است، و تکهٔ دوم به خودِ CTE اشاره می‌کند و تا وقتی سطرِ تازه‌ای پیدا شود ادامه می‌دهد.

print(q("""
    WITH RECURSIVE tree(id, name, depth, path) AS (
        SELECT id, name, 1, name
        FROM sections
        WHERE parent_id IS NULL

        UNION ALL

        SELECT s.id, s.name, t.depth + 1, t.path || ' > ' || s.name
        FROM sections AS s
        JOIN tree AS t ON s.parent_id = t.id
    )
    SELECT depth, name, path
    FROM tree
    ORDER BY path
"""))
    depth         name                                  path
0       1     کتابخانه                              کتابخانه
1       2       ادبیات                     کتابخانه > ادبیات
2       3         رمان              کتابخانه > ادبیات > رمان
3       3          شعر               کتابخانه > ادبیات > شعر
4       4    شعر معاصر   کتابخانه > ادبیات > شعر > شعر معاصر
5       4   شعر کلاسیک  کتابخانه > ادبیات > شعر > شعر کلاسیک
6       2         علوم                       کتابخانه > علوم
7       3    تاریخ علم           کتابخانه > علوم > تاریخ علم
8       3         زیست                کتابخانه > علوم > زیست
9       3        فیزیک               کتابخانه > علوم > فیزیک
10      2         کودک                       کتابخانه > کودک
11      3  داستان کودک         کتابخانه > کودک > داستان کودک

دوازده سطر و چهار طبقه، با یک پرسش که هیچ‌جا عددِ ۴ در آن نوشته نشده.

سه چیز در همین پرسش اتفاق افتاد و هر سه ارزشِ نام بردن دارند: depth با هر قدم یکی بالا رفت، path با || مسیرِ کامل را ساخت، و JOIN tree AS t — پیوندِ CTE به خودش — همان چیزی است که «بازگشتی» را ممکن می‌کند.

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

🔧 اگر کار نکرد: اگر تکهٔ اول — نقطهٔ شروع — را جا بیندازی، پرسش هیچ جایی برای شروع ندارد و SQLite صریح اعتراض می‌کند:

try:
    q("""
        WITH RECURSIVE t(id) AS (
            SELECT s.id FROM sections AS s JOIN t ON s.parent_id = t.id
        )
        SELECT * FROM t
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql '
        WITH RECURSIVE t(id) AS (
            SELECT s.id FROM sections AS s JOIN t ON s.parent_id = t.id
        )
        SELECT * FROM t
    ': circular reference: t

ولی خطای خطرناک‌ترِ CTE بازگشتی خطا نمی‌دهد: اگر داده‌ات حلقه داشته باشد، یعنی بخشی که در نهایت به خودش برمی‌گردد، پیمایشی که داخلِ حلقه می‌افتد تا ابد سطر تولید می‌کند. پیمایشِ همین بخش از ریشه به پایین به حلقه نمی‌رسد، چون هر بخش فقط یک والد دارد، و فقط چند سطر کم می‌آورد؛ خطرِ اصلی برای پیمایشِ رو به بالاست. پس همان آزمونِ بالا را بزن: تعدادِ سطرهای نتیجه نباید از تعدادِ سطرهای جدول بیشتر شود، و برای اینکه پرسشی که تمام نمی‌شود هم جواب بدهد، LIMIT را یکی بیشتر از تعدادِ سطرهای جدول بگذار.

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

📏 اندازه بگیر: یک سطر یعنی چه؟ در loans_per_book یک کتابِ امانت‌رفته، در book_stats یک کتاب، در branch_stats یک شعبه، و در tree یک بخش از سلسله‌مراتب. چهار دانه‌بندیِ متفاوت در یک پرسش — و نام‌گذاری تنها چیزی است که این را قابلِ دیدن می‌کند. با کدام شمارشِ دوم سنجیدی؟ با سه‌تا: ۱٬۰۰۰ به‌علاوهٔ ۲۰۰ برابرِ ۱٬۲۰۰؛ جمعِ ستون‌های branch_stats که ۱٬۲۰۰ و ۳٬۹۹۸ شد؛ و ۱۲ سطرِ tree برابرِ ۱۲ سطرِ sections. چه کسی از قلم افتاد؟ شعبهٔ آفتاب، که چون کتابی ندارد در branch_stats سطری ندارد — ولی «میانگینِ کل» امانت به‌ازای هر کتاب است و مخرجش ۱٬۲۰۰ کتاب، پس نبودنِ آفتاب آن را عوض نمی‌کند؛ چیزی که گم می‌شود سطرِ خودِ آفتاب است.

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

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

کلمه تلفظ به حروف فارسی یعنی چه
CTE سی‌تی‌ای مرحلهٔ نام‌دارِ یک پرسش، که با WITH تعریف می‌شود
WITH ویت کلیدواژه‌ای که مرحله‌های پرسش را تعریف می‌کند
recursive CTE ریکرسیو سی‌تی‌ای CTEای که به خودش اشاره می‌کند و یک سلسله‌مراتب را می‌پیماید
anchor انکر تکهٔ اولِ یک CTE بازگشتی؛ نقطهٔ شروع
hierarchy هایرارکی سلسله‌مراتب؛ داده‌ای که هر سطرش به سطرِ بالادستی‌اش اشاره می‌کند

تمرین‌ها

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

در فصل بعد#

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

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

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