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

فصل ۲ از ۹

پیشرفت ترم
۰٪

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

دو پرسش می‌نویسیم که تنها تفاوتشان یک کلمه است: در یکی WHERE نوشته شده و در دیگری HAVING. اولی می‌گوید ۴۷۱ کتاب، دومی می‌گوید ۶۰۱ کتاب. اولی هشت دسته برمی‌گرداند، دومی چهارتا. هیچ‌کدام خطا نمی‌دهند.

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

راهِ‌حل حفظ کردنِ یک قاعده نیست. راهِ‌حل این است که بدانی یک پرسش واقعاً به چه ترتیبی اجرا می‌شود — و همان ترتیب، تمامِ این فصل است.

دو خط تولید یکسان که در یکی صافی قبل از پرس گذاشته شده و در دیگری بعد از پرس، با دو سینی خروجی نابرابر

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

  • با GROUP BY از ۱٬۲۰۰ سطر یک جدولِ چندسطری بسازی و دانه‌بندیِ تازه‌اش را اعلام کنی
  • بگویی یک ستونِ گروه‌نشده در SELECT مقدارش از کجا می‌آید
  • ترتیبِ منطقیِ اجرای یک پرسش را از بر بگویی و با آن رفتارهای عجیب را پیش‌بینی کنی
  • بدانی WHERE و HAVING دو کارِ متفاوت‌اند و هرکدام کجا لازم است
  • ثابت کنی که جدولِ گروهی‌ات هیچ سطری را جا نینداخته

قبل از شروع#

از فصلِ ۱: MIN/MAX، COUNT(DISTINCT ...)، تجمیع روی عبارت، و اینکه یک تجمیعِ بدونِ گروه‌بندی همیشه یک سطر می‌دهد.

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

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

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

۱. GROUP BY: از یک عدد به یک جدول#

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

print(q("""
    SELECT branch,
           COUNT(*)      AS books,
           SUM(borrowed) AS loans
    FROM books
    GROUP BY branch
    ORDER BY books DESC
"""))
  branch  books  loans
0   نسیم    224  18364
1   بهار    214  17498
2   کوشا    200  15542
3   پویا    195  15122
4   دانش    187  13887
5  مرکزی    180  13778

۱٬۲۰۰ سطر رفت تو، ۶ سطر آمد بیرون. پرشمارترین شعبه ۲۲۴ کتاب دارد و کم‌شمارترین ۱۸۰.

و مهم‌ترین جملهٔ این فصل همین‌جاست: دانه‌بندی عوض شد. یک سطر از این خروجی دیگر «یک کتاب» نیست، «یک شعبه» است. عددِ ۲۲۴ دربارهٔ کتاب‌ها حرف می‌زند ولی خودش یک ویژگیِ شعبه است. هر بار که GROUP BY می‌نویسی همین اتفاق می‌افتد، و اگر در گزارشت ننویسی یک سطر یعنی چه، خواننده‌ات حق دارد هر چیزی فکر کند.

✅ چک کن: جمعِ ستونِ books باید دقیقاً ۱٬۲۰۰ شود و جمعِ ستونِ loans دقیقاً ۹۴٬۱۹۱ — همان دو عددی که ترمِ ۱ روی کلِ جدول گرفت. این ارزان‌ترین شمارشِ کنترلیِ هر جدولِ گروهی است و همیشه باید بگیری‌اش:

grouped = q("SELECT branch, COUNT(*) AS books, SUM(borrowed) AS loans FROM books GROUP BY branch")
whole = q("SELECT COUNT(*) AS books, SUM(borrowed) AS loans FROM books")

print("جمع سطرهای جدول گروهی:", int(grouped["books"].sum()), "|", int(grouped["loans"].sum()))
print("همان دو عدد روی کل جدول:", int(whole["books"][0]), "|", int(whole["loans"][0]))
print("گروه‌ها:", len(grouped), "| شعبه‌های جدول branches:",
      int(q("SELECT COUNT(*) FROM branches").iloc[0, 0]))
جمع سطرهای جدول گروهی: 1200 | 94191
همان دو عدد روی کل جدول: 1200 | 94191
گروه‌ها: 6 | شعبه‌های جدول branches: 6

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

۲. ستونی که نه گروه است و نه تجمیع#

قاعدهٔ استانداردِ SQL ساده است: هر ستونی که در SELECT می‌آید یا باید در GROUP BY باشد یا داخلِ یک تابعِ تجمیعی. SQLite این قاعده را اجرا نمی‌کند. پرسشِ زیر در بیشترِ پایگاه داده‌های دیگر خطا می‌دهد و اینجا با خوش‌رویی جواب می‌دهد:

print(q("""
    SELECT category, id, title, borrowed, COUNT(*) AS n
    FROM books
    GROUP BY category
    ORDER BY category
    LIMIT 3
"""))
     category  id           title  borrowed    n
0       تاریخ   4      مه پشت آتش        86  145
1        رمان   1       روزهای پل        33  146
2  زندگی‌نامه   6  در جست‌وجوی شب       169  136

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

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

و یک استثنای مستند هست که باید بشناسی، چون هم مفید است و هم گمراه‌کننده:

print(q("""
    SELECT category, id, borrowed, MIN(borrowed) AS lowest, COUNT(*) AS n
    FROM books
    GROUP BY category
    ORDER BY category
    LIMIT 3
"""))
     category   id  borrowed  lowest    n
0       تاریخ  169         0       0  145
1        رمان   51         0       0  146
2  زندگی‌نامه  259         0       0  136

حالا borrowed با lowest می‌خواند. وقتی در پرسش دقیقاً یک MIN یا یک MAX باشد، SQLite تضمین می‌کند ستون‌های گروه‌نشده از همان سطری بیایند که کمینه یا بیشینه در آن پیدا شده. این یک راهِ کوتاه و واقعاً مفید برای «رکورددارِ هر گروه» است.

ولی درست به همین دلیل خطرناک است: الگو را یک بار یاد می‌گیری، بعد یک AVG هم به همان پرسش اضافه می‌کنی، و بی‌صدا از حالتِ تضمین‌شده به حالتِ دلبخواهی می‌افتی. فصلِ ۷ همین کار را با تابعِ پنجره انجام می‌دهد، جایی که «رکورددارِ هر گروه» یک ابزارِ صریح دارد و به هیچ استثنایی تکیه نمی‌کند.

۳. ترتیبی که واقعاً اجرا می‌شود#

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

FROM   →   WHERE   →   GROUP BY   →   HAVING   →   SELECT   →   ORDER BY   →   LIMIT

بخوانش به‌عنوان یک خطِ تولید: FROM سطرها را می‌آورد؛ WHERE سطرها را غربال می‌کند — قبل از اینکه هیچ گروهی وجود داشته باشد؛ GROUP BY سطرهای بازمانده را دسته می‌کند؛ HAVING گروه‌ها را غربال می‌کند؛ SELECT ستون‌های خروجی را می‌سازد و نامِ مستعار را همین‌جا تعریف می‌کند؛ و آخر ORDER BY مرتب می‌کند و LIMIT می‌بُرد.

سه نتیجهٔ فوری که هر سه را در این فصل با عدد می‌بینی: WHERE هیچ‌وقت نمی‌تواند به یک تابعِ تجمیعی نگاه کند (هنوز گروهی نیست)؛ HAVING می‌تواند (گروه‌ها ساخته شده‌اند)؛ و نامِ مستعارِ SELECT در ORDER BY در دسترس است چون ORDER BY بعد از SELECT می‌آید.

۴. WHERE در برابرِ HAVING: یک کلمه، ۱۳۰ کتاب اختلاف#

پرسشِ واقعی: «در هر دسته چند کتابِ پرامانت داریم؟» — و «پرامانت» را می‌گوییم بیشتر از ۱۰۰ بار.

این یک شرط روی سطر است: هر کتاب یا بیشتر از ۱۰۰ بار رفته یا نرفته. پس جایش WHERE است.

right = q("""
    SELECT category, COUNT(*) AS n
    FROM books
    WHERE borrowed > ?
    GROUP BY category
    ORDER BY n DESC
""", (100,))
print(right)
print("جمع:", int(right["n"].sum()))
     category   n
0  زندگی‌نامه  65
1         هنر  64
2        کودک  63
3        رمان  62
4         شعر  61
5       فلسفه  56
6         علم  53
7       تاریخ  47
جمع: 471

هشت دسته، و جمعشان ۴۷۱. حالا همان پرسش با یک کلمه تفاوت:

wrong = q("""
    SELECT category, COUNT(*) AS n
    FROM books
    GROUP BY category
    HAVING borrowed > ?
    ORDER BY n DESC
""", (100,))
print(wrong)
print("جمع:", int(wrong["n"].sum()))
     category    n
0        کودک  167
1         هنر  161
2       فلسفه  137
3  زندگی‌نامه  136
جمع: 601

چهار دسته به‌جای هشت، و ۶۰۱ به‌جای ۴۷۱ — بدونِ یک کلمه خطا.

ترتیبِ اجرا کلِ ماجرا را توضیح می‌دهد. در پرسشِ دوم GROUP BY اول اجرا شد و هر هشت دسته را با همهٔ کتاب‌هایشان ساخت؛ بعد HAVING روی هر گروه شرطِ borrowed > 100 را سنجید — ولی borrowed یک ستونِ سطری است، نه تجمیعی. پس SQLite همان کارِ بخشِ ۲ را کرد: از یک سطرِ دلبخواهیِ آن گروه مقدار برداشت.

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

و HAVING کِی درست است؟ وقتی شرطت دربارهٔ خودِ گروه باشد، نه دربارهٔ یک سطر:

print(q("""
    SELECT category, COUNT(*) AS n
    FROM books
    GROUP BY category
    HAVING COUNT(*) > ?
    ORDER BY n DESC
""", (150,)))
  category    n
0     کودک  167
1      شعر  164
2      هنر  161

«دسته‌هایی که بیشتر از ۱۵۰ کتاب دارند» جمله‌ای است که دربارهٔ یک کتابِ تنها اصلاً معنا ندارد. قاعدهٔ عملی: اگر شرطت را می‌شود برای یک سطرِ تنها سنجید، جایش WHERE است؛ اگر برای سنجیدنش باید کلِ گروه را دیده باشی، جایش HAVING است.

۵. چرا COUNT(*) در WHERE خطا می‌دهد#

حالا برعکسش را امتحان کنیم — شرطِ گروهی را در WHERE بگذاریم:

🔧 اگر کار نکرد: این پرسش خطا می‌دهد، و پیامش دقیقاً همان چیزی است که ترتیبِ اجرا پیش‌بینی می‌کند:

try:
    q("SELECT category, COUNT(*) AS n FROM books WHERE COUNT(*) > ? GROUP BY category", (150,))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql 'SELECT category, COUNT(*) AS n FROM books WHERE COUNT(*) > ? GROUP BY category': misuse of aggregate: COUNT()

misuse of aggregate یعنی «این تابع را جایی گذاشته‌ای که هنوز کاری برایش وجود ندارد». وقتی WHERE اجرا می‌شود هیچ گروهی ساخته نشده، پس چیزی برای شمردن نیست. رفعش همیشه یکی است: شرط را به HAVING ببر.

و یک نکتهٔ مخصوصِ SQLite که باید بدانی چون بینِ موتورها فرق می‌کند:

print("نام مستعار در ORDER BY — همه‌جا کار می‌کند:")
print(q("SELECT category, COUNT(*) AS n FROM books GROUP BY category ORDER BY n DESC LIMIT 2"))
print()
print("نام مستعار در HAVING — SQLite قبولش می‌کند:")
print(q("SELECT category, COUNT(*) AS n FROM books GROUP BY category HAVING n > 160 ORDER BY n DESC"))
print()
print("نام مستعار غیرتجمیعی در WHERE — SQLite این را هم قبول می‌کند:")
print(q("SELECT title, borrowed * 2 AS doubled FROM books WHERE doubled > 378 ORDER BY id LIMIT 2"))
نام مستعار در ORDER BY — همه‌جا کار می‌کند:
  category    n
0     کودک  167
1      شعر  164

نام مستعار در HAVING — SQLite قبولش می‌کند:
  category    n
0     کودک  167
1      شعر  164
2      هنر  161

نام مستعار غیرتجمیعی در WHERE — SQLite این را هم قبول می‌کند:
             title  doubled
0   بازگشت به خانه      380
1  بازگشت به پنجره      380

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

ORDER BY بعد از SELECT اجرا می‌شود، پس نامِ مستعار آنجا واقعاً وجود دارد. ولی WHERE و HAVING هر دو قبل از SELECT اجرا می‌شوند و طبقِ استاندارد نباید نامِ مستعار را ببینند؛ SQLite از سرِ راحتیِ کاربر اجازه‌شان می‌دهد و پشتِ پرده عبارت را جای نام می‌گذارد.

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

۶. گروه‌بندی روی عبارت، و نامی که ستونِ دیگری را صدا می‌زند#

GROUP BY فقط ستون نمی‌گیرد؛ هر عبارتی می‌گیرد. این تنها راهی است که با آن می‌شود «هر سال» یا «هر ماه» را گروه کرد، چون در SQLite تاریخ متن است.

پرسشِ زیر کاملاً بی‌آزار به نظر می‌رسد و شمارشِ کنترلی‌اش هم پاس می‌شود:

by_year = q("""
    SELECT strftime('%Y', added_at) AS year,
           COUNT(*)                 AS n
    FROM books
    GROUP BY year
    ORDER BY year
""")
print(by_year.head(4))
print("جمع:", int(by_year["n"].sum()), "| گروه‌ها:", len(by_year))
   year   n
0  2019  20
1  2019  29
2  2019  34
3  2019  33
جمع: 1200 | گروه‌ها: 45

جمع ۱٬۲۰۰ شد — شمارشِ کنترلی پاس شد — و ۴۵ گروه داریم برای هفت سال.

این همان الگویی است که ترمِ ۱ فصلِ ۵ هم دیدیم: آزمونِ «جمعِ اجزا برابرِ کل» خطاهایی را می‌گیرد که سطر گم می‌کنند، نه خطاهایی که سطرها را جای اشتباه می‌گذارند. اینجا هیچ سطری گم نشده و گروه‌بندی کاملاً چیزِ دیگری است.

دلیلش یک تصادفِ نام است: جدولِ books از قبل ستونی به نامِ year دارد — سالِ انتشارِ کتاب — و در GROUP BY، SQLite اول دنبالِ ستونِ واقعی می‌گردد و فقط اگر پیدا نکرد سراغِ نامِ مستعارِ SELECT می‌رود. پس گروه‌بندی روی سالِ انتشار انجام شد، که ۴۵ مقدارِ متفاوت دارد.

و بدترین قسمتش این است که ستونِ اولِ خروجی همچنان مثلِ سالِ افزودن به نظر می‌رسد: آن ستون همان ستونِ گروه‌نشدهٔ بخشِ ۲ است و مقدارش از یک سطرِ دلبخواهیِ هر گروه آمده.

print("مقدارهای متفاوت ستون year در جدول:",
      int(q("SELECT COUNT(DISTINCT year) FROM books").iloc[0, 0]))

by_added = q("""
    SELECT strftime('%Y', added_at) AS added_year,
           COUNT(*)                 AS n
    FROM books
    GROUP BY added_year
    ORDER BY added_year
""")
print(by_added)
print("جمع:", int(by_added["n"].sum()), "| گروه‌ها:", len(by_added))
مقدارهای متفاوت ستون year در جدول: 45
  added_year    n
0       2019  193
1       2020  206
2       2021  166
3       2022  165
4       2023  188
5       2024  178
6       2025  104
جمع: 1200 | گروه‌ها: 7

با یک نامِ مستعارِ متفاوت، هفت گروه گرفتیم و جمع باز هم ۱٬۲۰۰ است.

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

و حالا به سطرِ آخرِ جدولِ درست نگاه کن و یک لحظه صبر کن: سالِ آخر تقریباً نصفِ بقیه است. این کاهشِ ناگهانی یک واقعیت نیست، یک لبهٔ داده است — این پایگاه داده تا اواسطِ سالِ آخر بیشتر ندارد، و آن سطر یک دورهٔ ناقص است.

اگر همین جدول را در یک گزارش بگذاری، خواننده‌ات از آن یک روندِ نزولی می‌خواند که وجود ندارد. فصلِ ۷ همین سطر را دوباره برمی‌دارد و نشان می‌دهد چطور به یک عددِ درصدیِ کاملاً گمراه‌کننده تبدیل می‌شود.

📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ بخشِ ۱ یک سطر یک شعبه است، در بخشِ ۴ یک دسته، و در بخشِ ۶ یک سالِ افزودن — سه دانه‌بندیِ متفاوت از یک جدولِ ورودیِ ثابت. با کدام شمارشِ دوم سنجیدی؟ با «جمعِ اجزا برابرِ کل»: جمعِ ستونِ books شش گروه شد ۱٬۲۰۰ و جمعِ loans شد ۹۴٬۱۹۱. و با تعدادِ گروه‌ها، که کنترلِ دومِ مستقلی است: شش گروه در برابرِ شش سطرِ جدولِ branches، و در بخشِ ۶ همین شمارش بود که ۴۵ گروه را لو داد در حالی که جمعِ سطرها هنوز درست بود. چه کسی از قلم افتاد؟ سه گروه: شعبه یا دسته‌ای که هیچ کتابی ندارد و در خروجی هیچ سطری تولید نمی‌کند؛ نیمهٔ دومِ سالِ آخر که هنوز اتفاق نیفتاده و سطرش را کوتاه نشان می‌دهد؛ و همچنان ۲۴۰ سطرِ arrivals.csv.

🤖 از دستیارت بپرس: «ترتیبِ منطقیِ اجرای یک پرسشِ SQL چیست و چرا با ترتیبِ نوشتنش فرق دارد؟» بعد این را بپرس: «چرا SQLite اجازه می‌دهد ستونی که در GROUP BY نیست در SELECT بیاید، در حالی که استاندارد اجازه نمی‌دهد؟» — دنبالِ کلمهٔ «تضمین‌نشده» در جواب بگرد؛ اگر جواب فقط گفت «SQLite سخت‌گیر نیست» و نگفت مقدار از کجا می‌آید، ناقص است.

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

کلمه تلفظ به حروف فارسی یعنی چه
GROUP BY گروپ بای بندی که سطرها را بر اساسِ یک مقدار دسته می‌کند
HAVING هوینگ بندی که گروه‌ها را غربال می‌کند
logical order لاجیکال اوردر ترتیبی که بندهای یک پرسش واقعاً به آن اجرا می‌شوند
bare column بر کالمن ستونی که نه گروه است و نه تجمیع
grain گرین دانه‌بندی؛ اینکه یک سطرِ خروجی یعنی چه

تمرین‌ها

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

در فصل بعد#

حالا می‌توانی برای هر گروه یک میانگین بسازی — و بلافاصله باید بپرسی آن میانگین چه چیزی را پنهان کرده. فصلِ بعد یک جدولِ ده‌سطری می‌سازد که به دو قفسهٔ پنج‌تایی تقسیم شده، و میانگین و میانهٔ آن دو دقیقاً برابر درمی‌آید در حالی که شکلشان هیچ شباهتی ندارد. بعد سراغِ همین پایگاه داده می‌رود و یک عددِ عجیب درمی‌آورد: میانگینِ میانگین‌های همین جدول ۴۸٫۹۷ است، در حالی که میانگینِ واقعیِ همان ستون ۷۸٫۴۹ است.

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

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